用logminer排查ORA-00257 问题

环境：
Redhat5.5
Oracle11g RAC
文件采用ASM管理

问题现象
    有客户反映传输数据时报ORA-00257错误





 

用PL/SQL登录数据库同样报错


以sysdba登录，检查flash recovery area的使用情况,可以看见archivelog已经很大了，达到96.62


SQL> select * fromV$FLASH_RECOVERY_AREA_USAGE;


FILE_TYPE   PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES


------------ ------------------------------------------- ---------------


CONTROLFILE                 .13                        0               1


ONLINELOG                  2.93                        0               3


ARCHIVELOG                96.62                       0              141


BACKUPPIECE                   0                         0               0


IMAGECOPY                      0                         0               0


FLASHBACKLOG                0                         0               0


 


 


SQL> show parameter recover;


 


NAME                                 TYPE        VALUE


----------------------------------------------- ------------------------------


db_recovery_file_dest                string      +FRA


db_recovery_file_dest_size           big integer 30000M


db_unrecoverable_scn_tracking        boolean     TRUE


recovery_parallelism                 integer     0


 


 


查看空间使用情况


 


[root@ora1 ~]# su - grid


[grid@ora1 ~]$ asmcmd


ASMCMD> ls


DATA/


FRA/


ORA_VOT/


ASMCMD> cd fra


ASMCMD> ls


CRMLS/


GPPLS/


HDQTLS/


MKT01/


PCMLS/


POS01/


ASMCMD> cd hdqtls  


ASMCMD> ls


ARCHIVELOG/


CONTROLFILE/


ONLINELOG/


ASMCMD> cd archivelog


ASMCMD> ls


2014_07_24/


ASMCMD> cd 2014_07_24


ASMCMD> ls


thread_1_seq_1379.533.853785231


thread_1_seq_1380.543.853785267


thread_1_seq_1381.739.853785307


thread_1_seq_1382.791.853785345


thread_1_seq_1383.787.853785381


thread_1_seq_1384.853.853785423


thread_1_seq_1385.826.853785461


...此处省略若干


ASMCMD> du


Used_MB     Mirror_used_MB


   29893               29893


 


经查，只有当天的归档日志，并且达到了近30G（仅12小时产生的量）


 


提出问题


 


服务器每天备份脚本都会自动rman备份归档日志，并自动清除归档日志文件，而且客户的交易量较小，按照正常情况下，数据库不可能一天产生这么大的归档日志量。


 


于是采用logminer分析日志


使用方法：


-- 1.指定要分析的日志文件


execsys.dbms_logmnr.add_logfile(logfilename=>'+fra/hdqtls/archivelog/2014_07_24/thread_2_seq_1598.629.853786167',options=> dbms_logmnr.new);


 


-- 2.使用本地的在线数据字典分析归档日志


exec sys.dbms_logmnr.start_logmnr(options=> sys.dbms_logmnr.dict_from_online_catalog);


 


-- 3.查询分析出来的归档日志内容，例如统计最大修改量的Schema


select seg_owner,count(*) fromv$logmnr_contents group by seg_owner;


 


-- 4.增加别的日志文件


exec sys.dbms_logmnr.add_logfile(logfilename=>+fra/hdqtls/archivelog/2014_07_24/ thread_1_seq_1385.826.853785461');


 


-- 5.结束分析归档日志


exec sys.dbms_logmnr.end_logmnr;


 


具体过程：


 


SQL>exec sys.dbms_logmnr.add_logfile(logfilename=>'+fra/hdqtls/archivelog/2014_07_24/thread_2_seq_1598.629.853786167',options=> dbms_logmnr.new);


 


PL/SQL procedure successfully completed

SQL> execsys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed

SQL> select seg_owner,count(*) fromv$logmnr_contents group by seg_owner;

SEG_OWNER                          COUNT(*)

-------------------------------- ----------


                                                  189819


DBUSRMKT                              50948


 SQL> column SQL_REDO format a2000;


SQL> select SQL_REDO fromv$logmnr_contents ;

SQL_REDO

--------------------------------------------------------------------------------


delete from"DBUSRMKT"."SALEGOODSLIST" where "SGLDATE" =TO_DATE('24-JUL-14', 'D


delete from"DBUSRMKT"."SALEGOODSLIST" where "SGLDATE" =TO_DATE('24-JUL-14', 'D


delete from "DBUSRMKT"."SALEGOODSLIST"where "SGLDATE" = TO_DATE('24-JUL-14', 'D



结论：


从上面的结果来看日志主要由dbusrmkt用户产生（空白的为ORACLE内部调度）


从执行的内容来看有大量的对表SALEGOODSLIST的删除插入操作，最后定位到一个通讯的JOB有个where条件没有指定好，会删除当天客户某商品所有的交易记录再重新插入。


修改后备份归档日志并清除后解决问题。