alert日志中的两种ORA错误分析

今天在巡检系统的时候，发现alert日志中有两种类型的ora错误。
Errors in file /U01/app/oracle/diag/rdbms/XX/XX/trace/xxdb_j002_20401.trc:
ORA-12012: error on auto execute of job "XXDATA"."S_XXXX_HIST_OPS_SERINFO_K"
ORA-12170: TNS:Connect timeout occurred
ORA-06512: at "XXDATA.L_XXXX_HIST_OPS_SERINFO_K", line 6
...
DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /U01/app/oracle/diag/rdbms/XX/XX/trace/xxdb_j003_21375.trc:
ORA-20011: Approximate NDV failed: ORA-06564: object IMPDP20130506 does not exist
而且时间错误的间隔还很短，初步感觉这两种ORA错误似乎是有关联的，我们一个一个来分解这些ora错误。
首先查看第一种错误的trace日志，根据提示是job运行有问题，甚至指向了对应的代码部分，显示是超时错误。而在对应的代码里面可以看到其实是用到了db link，但是连接信息发生了变化，导致db link对应的数据库不可访问，结果就出现了超时的问题，最后在运行的时候抛错。
*** 2015-08-04 06:01:00.448
*** SESSION ID:(389.3953) 2015-08-04 06:01:00.448
*** CLIENT ID:() 2015-08-04 06:01:00.448
*** SERVICE NAME:(SYS$USERS) 2015-08-04 06:01:00.448
*** MODULE NAME:(DBMS_SCHEDULER) 2015-08-04 06:01:00.448
*** ACTION NAME:(S_XXXX_HIST_OPS_SERINFO_K) 2015-08-04 06:01:00.448
ORA-12012: error on auto execute of job "XXDATA"."S_XXXX_HIST_OPS_SERINFO_K"
ORA-12170: TNS:Connect timeout occurred
ORA-06512: at "XXDATA.L_XXXX_HIST_OPS_SERINFO_K", line 6
明白了问题，解决的思路相对来说就容易了很多，一种是解决db link的连接问题，另外一种是把job给禁用或者删除，经过确认选择第二种方法。
使用dba_jobs来查看对应的job信息，竟然查不到对应的job,其实需要查看的是scheduler部分，在10g有了重大的改变。
select job_name ,status,owner from DBA_SCHEDULER_JOB_LOG where owner='xxxxx'
根据条件能够找到对应的job了，然后在sys下直接调用dbms_scheduler来禁用Job.

SQL>   exec dbms_scheduler.DISABLE('S_XXXX _HIST_OPS_SERINFO_INUSE',force=>true);

BEGIN dbms_scheduler.DISABLE('S_XXXX _HIST_OPS_SERINFO_INUSE',force=>true); END;
*

ERROR at line 1:

ORA-27476: "SYS.S_XXXX _HIST_OPS_SERINFO_INUSE" does not exist

ORA-06512: at "SYS.DBMS_ISCHED", line 4407

ORA-06512: at "SYS.DBMS_SCHEDULER", line 2737

ORA-06512: at line 1
报出的错误还是有些奇怪，仔细查看日志，其实默认是会从当前的schema下查找对应的job. 指定对应的schema就可以了。

SQL>   exec dbms_scheduler.DISABLE('XX DATA.S_XXXX _HIST_OPS_SERINFO_INUSE',force=>true);
PL/SQL procedure successfully completed.
第一类问题的解决告一段落，我们来看看第二种问题，是不是和第一类相关。
第二类中的trace也比较有限,但是能够看出来是在做统计信息收集的时候报出了错误。所以从这一点来看应该和第一类问题没有直接的联系，根据错误提示是有一个对象找不到，通过字面意思可以看出来似乎和datapump有关。
DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /U01/app/oracle/diag/rdbms/xxxx/xxxx/trace/bidb_j003_21375.trc:
ORA-20011: Approximate NDV failed: ORA-06564: object IMPDP20130506 does not exist
对于这个对象，问题还是能够简单复现的。

SQL> select count(*) from "ET$00E73C1D0001";
select count(*) from "ET$00E73C1D0001"
                     *
ERROR at line 1:
ORA-06564: object IMPDP20130506 does not exist
对象既然不存在，那就使用desc来看看，到底可以不，但是desc又可以。
从这一点来说，这个对象还是有点特别。
SQL> desc "ET$00E73C1D0001";
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ID                                                                                                                         NUMBER(15)
 SN                                                                                                                         VARCHAR2(24)
 GROUP_ID                                                                                                                   NUMBER(6)
 SERVER_IP                                                                                                                  VARCHAR2(15)
 SERVER_NAME                                                                                                                VARCHAR2(40)
 WORD                                                                                                                       NUMBER(4)
 SERVER                                                                                                                     NUMBER(4)
 SCENE                                                                                                                      NUMBER(4)
 CN_GUID                                                                                                                    VARCHAR2(30)
 BUY_TIME                                                                                                                   DATE
 JEWEL_TOTAL                                                                                                                NUMBER(7)
 CN                                                                                                                         VARCHAR2(80)
 CHARACTER_PUT                                                                                                              VARCHAR2(50)
 IP                                                                                                                         VARCHAR2(15)
 WEAPONID                                                                                                                   NUMBER(15)
 PUT_DATE                                                                                                                   DATE
 WEAPONID_NEW                                                                                                               NUMBER(15)
 COUNT                                                                                                                      NUMBER
 USER_CLASS                                                                                                                 NUMBER
 CONSUME_WAY                                                                                                                VARCHAR2(40)
通过上面的信息，可以很容易联想到应该是datapump中的临时表之类的，可能在上次datapump做expdp或者Impdp的时候出现了问题，结果这个临时表保留了下来。在做统计信息收集的时候就报出了错误。
但是上面还仅仅是个猜想，怎么验证呢，还是通过一个数据字典表dba_external_tables
select *from dba_external_tables where table_name='ET$00E73C1D0001';

OWNER                          TABLE_NAME                     TYP TYPE_NAME                      DEF DEFAULT_DIRECTORY_NAME         REJECT_LIMIT                      ACCESS_
------------------------------ ------------------------------ --- ------------------------------ --- ------------------------------ ---------------------------------------- -------
ACCESS_PARAMETERS                                                                PROPERTY
-------------------------------------------------------------------------------- ----------
SYS                            ET$00E73C1D0001                SYS ORACLE_DATAPUMP                SYS IMPDP20130506                  UNLIMITED                         CLOB
DEBUG = (0 , 0) DATAPUMP INTERNAL TABLE "XXDATA"."CONSUME_LOG_XXXX_BEFORE201201   ALL
可以清晰的看到是在之前做impdp的时候抛出了错误，这个表是Impdp过程中产生的临时表。
还有一个思路就是在expdp/impdp等操作时，在数据库日志中也会有一定的信息标识，但是尝试查看数据库日志，这个问题是好几年前的了，几年前的alert日志已经被清空了，所以也无法求证在当时问题发生的时候到底是什么样的一个情况。
解决问题的步骤就很简单了，需要直接删除这个外部表即可。

SQL> drop table "ET$00E73C1D0001";
Table dropped.
通过这个案例可以看到，对于这些ORA错误还是需要通过日志来一步一步分析，逐个击破，可以大胆猜想，但是要小心求证，问题了解清楚了，解决起来都是很容易的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1765141/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1765141/