耗损报表数据库出现了勾当事务的回滚段破坏（二）

根源&＃xff1a;网海拾贝

起首准备drop这个表尝尝看&＃xff08;先备份&＃xff0c;然后drop&＃xff0c;然后重修&＃xff09;&＃xff1a;

先是行使CTAS备份这个表&＃xff1a;

SQL> create table WAP_AUTHPRICE_USER_SP_RD060308 tablespace INDEX_SUBN01 nologging as
select * from WAP_AUTHPRICE_USER_SP_RD where 1&＃61;2;
  2 
Table created.

SQL> insert into WAP_AUTHPRICE_USER_SP_RD060308 nologging
  2  select * from WAP_AUTHPRICE_USER_SP_RD;
insert into WAP_AUTHPRICE_USER_SP_RD060308 nologging
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19 $"
too small

SQL> insert into WAP_AUTHPRICE_USER_SP_RD060308 nologging
  2  select * from WAP_AUTHPRICE_USER_SP_RD where rownum<100;

99 rows created.

SQL> rollback;

Rollback complete.

SQL>

我们看到&＃xff0c;每次扫表到阿谁回滚段中的数据库时&＃xff0c;系统就会报ORA-01555了。

由此推测&＃xff0c;齐备绝对的对表的FTS都会失落败&＃xff0c;比如exp&＃xff1a;

oracle&＃64;rdb01:/oracle > exp report/slienttop parfile&＃61;a.par

Export: Release 9.2.0.5.0 - Production on Thu Mar 9 14:31:45 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning and OLAP options
JServer Release 9.2.0.5.0 - Production
Export done in ZHS16GBK character set and UTF8 NCHAR character set

About to export specified tables via Direct Path ...
. . exporting table       WAP_AUTHPRICE_USER_SP_RD
EXP-00056: ORACLE error 942 encountered
ORA-00942: table or view does not exist
Export terminated successfully with warnings.
oracle&＃64;rdb01:/oracle >

现在&＃xff0c;只需行使DUL了&＃xff1a;

DUL> unload report.WAP_AUTHPRICE_USER_SP_RD;
. unloading table  WAP_AUTHPRICE_USER_SP_RD
 9430056 rows unloaded
DUL> exit

oracle&＃64;rdb01:/data/tmp/dul > gzip REPORT_WAP_AUTHPRICE_USER_SP_RD.dmp
oracle&＃64;rdb01:/data/tmp/dul > ll REPORT_WAP_AUTHPRICE_USER_SP_RD.dm*
-rw-r--r--   1 oracle     dba        49514464 Mar 13 14:18 REPORT_WAP_AUTHPRICE_USER_SP_RD.dmp.gz
oracle&＃64;rdb01:/data/tmp/dul >

这时&＃xff0c;突然想到行使oracledebug来跟踪一下进程大概会话&＃xff0c;大概会有点收获&＃xff1a;

SQL> oradebug setospid 15664 
ORA-00072: process "Unix process pid: 15664, image: " is not active
SQL> oradebug setospid 15664 
ORA-00072: process "Unix process pid: 15664, image: " is not active
SQL>

SQL> select pid, spid, username from v $process where spid&＃61;15664; 

       PID SPID         USERNAME
---------- ------------ ---------------
        63 15664        oracle

SQL>
SQL> oradebug setorapid 63 
ORA-00072: process "Unix process pid: 15664, image: oracle&＃64;rdb01 (TNS V1-V3)" is not active
SQL>

显然&＃xff0c;这个效果是和我们方才在利用系统上kill process&＃xff0c;以及在数据库中kill session的效果是不同的——我们不能找到这些process和session的活动了。

由于alert.log的内容已经被人清空了&＃xff0c;然则数据库中可以看到&＃xff1a;

       USN NAME       STATUS          TABLESPACE_NAME      ADDR                SID SERIAL# USERNAME   PROGRAM              MACHINE    OSUSER
---------- ---------- --------------- -------------------- ---------------- ------ ------- ---------- -------------------- ---------- ----------
        19 _SYSSMU19 $ PENDING OFFLINE UNDOTBS1             C0000001A7BF5218     71   39809 REPORT     sqlldr&＃64;RPT01 (TNS CS_RPT01   report16

这个回滚段已经破坏&＃xff0c;并且勾当事务是一个sqlloader的进程&＃xff08;方才看到的数据库中sid 71正在实行的sql语句正好也是如许一个insert语句&＃xff09;。由此证实了我最后的推想。由于某种缘由&＃xff0c;启示人员将正在实行的sqlloader进程kill了&＃xff0c;而此时正好有一行勾当在回滚段中&＃xff0c;这个事故孕育爆发在12/30/05 01:48:33 此后&＃xff0c;由于没有及时的发现和处理赏罚这个效果&＃xff0c;数据库的日记已经被掩饰笼罩一再&＃xff0c;再之后&＃xff0c;就孕育爆发了我们现在看到的环境。

固然alert.log的内容不在了&＃xff0c;然则根据事故孕育爆发的时光&＃xff0c;我们找到了相应的trace--report_ora_15664.trc&＃xff0c;外表有如下的内容&＃xff1a;

*** SESSION ID:(71.25999) 2005-12-30 01:48:33.580
*** 2005-12-30 01:48:33.580
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4193], [2987], [2984], [], [], [], [], []
Current SQL statement for this session:
INSERT INTO WAP_AUTHPRICE_USER_SP_RD   (LOCATIONID,ICPID,SUBSCAT,ICPATTR,ICPCODE,COUNTS,PERIOD,STAT_TIME) VALUES (:LOCATIONID,:ICPID,:SUBSCAT,:ICPATTR,:ICPCODE,:COUNTS,:PERIOD,TO_DATE(:STAT_TIME,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;))

ORA-00600 [4193]&＃xff0c;这类错误一般便是勾当回滚段破坏一类的错误了&＃xff0c;详细的可以检查metalink。

现在我已经制定了一套实行规画&＃xff0c;下周一末尾处理赏罚这个效果&＃xff08;由于24*7&＃xff0c;这类有危害的利用必需求写陈说报到公司的带领和客户的带领&＃xff0c;层层审批&＃xff0c;呵呵&＃xff09;。。。




版权声明&＃xff1a; 原创作品&＃xff0c;容许转载&＃xff0c;转载时请务必以超链接体式格局标明文章 原始情由 、作者信息和本声明。否则将究查法律责任。