查看监听状态的时候发现出现大量的service

[oracle@rac1 ~]$ lsnrctl status

 

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 22-MAR-2017 14:40:57

 

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 – Production

Start Date                03-JAN-2017 15:46:30

Uptime                    77 days 22 hr. 54 min. 26 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/grid/network/admin/listener.ora

Listener Log File         /oracle/base/diag/tnslsnr/rac1/listener/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.29.129.132)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.29.129.134)(PORT=1521)))

Services Summary…

Service "+ASM" has 1 instance(s).

  Instance "+ASM1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$C_1_20161003235903.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$C_1_20161011235904.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$C_1_20161017235903.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$C_1_20161026235904.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$C_1_20161030235908.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$C_1_20161103235905.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$C_1_20161107235903.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$S_1_20160924235902.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$S_1_20161009235902.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$S_1_20161012235909.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$S_1_20161031235909.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$S_1_20161101235902.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$S_1_20161102235907.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "SYS$SYS.KUPC$S_1_20161108235903.ORCL" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "orcl" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

Service "orclXDB" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service…

The command completed successfully

 

可能原因是自动备份的JOB数满了

 

解决方法:

1.查看crontab里的每日备份情况:

[oracle@rac1 ~]$ crontab -l

0,30 * * * * /home/oracle/scripts/loadhalf.sh > /tmp/loadhalf.out

01 01 * * * /home/oracle/scripts/loadday.sh > /tmp/loadday.out

59 23 * * * /home/oracle/scripts/bakfull.sh > /tmp/loadday.out

 

 

 

[oracle@rac1 ~]$ more /home/oracle/scripts/bakfull.sh

source ~/.bash_profile

RUSER=oracle

RPASS=oracle

ORACLE_USER=oracle

TARGET_CONNECT_STR=/

BACKUP_HOME=/Tbackup

DAY=`date +%Y_%m_%d`

FILE_TARGET=expdphis_$DAY.dmp

FILE_LOG=expdphis_$DAY.log

export FILE_TARGET

export FILE_LOG

echo "Begin backup database by expdp at Time:"`date`

expdp \'/ as sysdba\' directory=dump_dir dumpfile=$FILE_TARGET full=y logfile=$FILE_LOG

echo "Export mission over at Time:"`date`

echo "Delete  1 days ago Export File"

find /Tbackup -name "exp*.dmp" -mtime 1 -exec rm {} \;

find /Tbackup -name "exp*.log" -mtime 1 -exec rm {} \;

echo "ALL WORKS COMPLETE! GOOD LUCK!"

 

 

2.SQL里查询expdpJOB情况,发现出现大量的NOT RUNNINGJOB

SQL> select job_name,state from dba_datapump_jobs;

 

JOB_NAME                       STATE

—————————— ——————————

SYS_EXPORT_SCHEMA_01           NOT RUNNING

SYS_EXPORT_FULL_64             NOT RUNNING

SYS_EXPORT_FULL_54             NOT RUNNING

SYS_EXPORT_FULL_98             NOT RUNNING

SYS_EXPORT_FULL_31             NOT RUNNING

SYS_EXPORT_FULL_75             NOT RUNNING

SYS_EXPORT_FULL_71             NOT RUNNING

SYS_EXPORT_FULL_96             NOT RUNNING

SYS_EXPORT_FULL_60             NOT RUNNING

SYS_EXPORT_FULL_43             NOT RUNNING

SYS_EXPORT_FULL_70             NOT RUNNING

 

JOB_NAME                       STATE

—————————— ——————————

SYS_EXPORT_FULL_57             NOT RUNNING

SYS_EXPORT_FULL_44             NOT RUNNING

SYS_EXPORT_FULL_91             NOT RUNNING

SYS_EXPORT_FULL_04             NOT RUNNING

SYS_EXPORT_FULL_79             NOT RUNNING

SYS_EXPORT_FULL_88             NOT RUNNING

SYS_EXPORT_FULL_69             NOT RUNNING

SYS_EXPORT_FULL_78             NOT RUNNING

SYS_EXPORT_FULL_32             NOT RUNNING

SYS_EXPORT_FULL_34             NOT RUNNING

SYS_EXPORT_FULL_62             NOT RUNNING

 

JOB_NAME                       STATE

—————————— ——————————

SYS_EXPORT_FULL_58             NOT RUNNING

SYS_EXPORT_FULL_08             NOT RUNNING

SYS_EXPORT_FULL_24             NOT RUNNING

SYS_EXPORT_FULL_45             NOT RUNNING

SYS_EXPORT_FULL_97             NOT RUNNING

SYS_EXPORT_FULL_82             NOT RUNNING

SYS_EXPORT_FULL_95             NOT RUNNING

SYS_EXPORT_FULL_74             NOT RUNNING

SYS_EXPORT_FULL_26             NOT RUNNING

SYS_EXPORT_FULL_36             NOT RUNNING

SYS_EXPORT_FULL_19             NOT RUNNING

 

JOB_NAME                       STATE

—————————— ——————————

SYS_EXPORT_FULL_05             NOT RUNNING

SYS_EXPORT_FULL_87             NOT RUNNING

SYS_EXPORT_FULL_56             NOT RUNNING

SYS_EXPORT_FULL_68             NOT RUNNING

SYS_EXPORT_FULL_66             NOT RUNNING

SYS_EXPORT_FULL_86             NOT RUNNING

SYS_EXPORT_FULL_85             NOT RUNNING

SYS_EXPORT_FULL_48             NOT RUNNING

SYS_EXPORT_FULL_12             NOT RUNNING

SYS_EXPORT_FULL_30             NOT RUNNING

SYS_EXPORT_FULL_46             NOT RUNNING

 

JOB_NAME                       STATE

—————————— ——————————

SYS_EXPORT_FULL_55             NOT RUNNING

SYS_EXPORT_FULL_84             NOT RUNNING

SYS_EXPORT_FULL_92             NOT RUNNING

SYS_EXPORT_FULL_50             NOT RUNNING

SYS_EXPORT_FULL_53             NOT RUNNING

SYS_EXPORT_FULL_22             NOT RUNNING

SYS_EXPORT_FULL_42             NOT RUNNING

SYS_EXPORT_FULL_52             NOT RUNNING

SYS_EXPORT_FULL_77             NOT RUNNING

SYS_EXPORT_FULL_33             NOT RUNNING

SYS_EXPORT_FULL_03             NOT RUNNING

 

JOB_NAME                       STATE

—————————— ——————————

SYS_EXPORT_FULL_16             NOT RUNNING

SYS_EXPORT_FULL_20             NOT RUNNING

SYS_EXPORT_FULL_21             NOT RUNNING

SYS_EXPORT_FULL_28             NOT RUNNING

SYS_EXPORT_FULL_09             NOT RUNNING

SYS_EXPORT_FULL_10             NOT RUNNING

SYS_EXPORT_FULL_47             NOT RUNNING

SYS_EXPORT_FULL_83             NOT RUNNING

SYS_EXPORT_FULL_25             NOT RUNNING

SYS_EXPORT_FULL_18             NOT RUNNING

SYS_EXPORT_FULL_99             NOT RUNNING

 

JOB_NAME                       STATE

—————————— ——————————

SYS_EXPORT_FULL_27             NOT RUNNING

SYS_EXPORT_FULL_51             NOT RUNNING

SYS_EXPORT_FULL_35             NOT RUNNING

SYS_EXPORT_FULL_06             NOT RUNNING

SYS_EXPORT_FULL_67             NOT RUNNING

SYS_EXPORT_FULL_72             NOT RUNNING

SYS_EXPORT_FULL_13             NOT RUNNING

SYS_EXPORT_FULL_40             NOT RUNNING

SYS_EXPORT_FULL_11             NOT RUNNING

SYS_EXPORT_FULL_65             NOT RUNNING

SYS_EXPORT_FULL_63             NOT RUNNING

 

JOB_NAME                       STATE

—————————— ——————————

SYS_EXPORT_FULL_01             NOT RUNNING

SYS_EXPORT_FULL_80             NOT RUNNING

SYS_EXPORT_FULL_59             NOT RUNNING

SYS_EXPORT_FULL_81             NOT RUNNING

SYS_EXPORT_FULL_23             NOT RUNNING

SYS_EXPORT_FULL_61             NOT RUNNING

SYS_EXPORT_FULL_93             NOT RUNNING

SYS_EXPORT_FULL_41             NOT RUNNING

SYS_EXPORT_FULL_73             NOT RUNNING

SYS_EXPORT_FULL_39             NOT RUNNING

SYS_EXPORT_FULL_17             NOT RUNNING

 

JOB_NAME                       STATE

—————————— ——————————

SYS_EXPORT_FULL_29             NOT RUNNING

SYS_EXPORT_FULL_37             NOT RUNNING

SYS_EXPORT_FULL_49             NOT RUNNING

SYS_EXPORT_FULL_14             NOT RUNNING

SYS_EXPORT_FULL_76             NOT RUNNING

SYS_EXPORT_FULL_15             NOT RUNNING

SYS_EXPORT_FULL_02             NOT RUNNING

SYS_EXPORT_FULL_94             NOT RUNNING

SYS_EXPORT_FULL_89             NOT RUNNING

SYS_EXPORT_FULL_07             NOT RUNNING

SYS_EXPORT_FULL_38             NOT RUNNING

 

JOB_NAME                       STATE

—————————— ——————————

SYS_EXPORT_FULL_90             NOT RUNNING

 

100 rows selected.

 

 

JOB数满100以后,就不会再增加新的JOB,导致每日的expdp无法正常运行

这时我们需要清理这些NOT RUNNINGJOB(非人为暂停)

 

3.通过 SQL*Plus 找到相关的 master 表:

 

查找数据泵的 master 表:

SELECT o.status, o.object_id, o.object_type,

       o.owner||'.'||object_name "OWNER.OBJECT"

  FROM dba_objects o, dba_datapump_jobs j

 WHERE o.owner=j.owner_name AND o.object_name=j.job_name

   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

 

STATUS   OBJECT_ID OBJECT_TYPE  OWNER.OBJECT

——- ———- ———— ————————-

VALID        85283 TABLE        SCOTT.EXPDP_20051121

VALID        85215 TABLE        SCOTT.SYS_EXPORT_TABLE_02

VALID        85162 TABLE        SYSTEM.SYS_EXPORT_FULL_01

 

 

4. 对于过去被终止的和根本不会再启动的作业,删除它的 master 表,例如,

 

DROP TABLE scott.sys_export_table_02;

 

对于启用了 recycle bin 的系统,需要额外运行:

purge dba_recyclebin;

 

5.重新运行第1步和第4步对 dba_datapump_jobs dba_objects 的查询。

 

 

 

具体情况可参考文档ID1626201.1

 

 

 

 




Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

no comment untill now

Add your comment now

无觅相关文章插件