The rollback segment of the active transaction is damaged in the production report database (2)

Now I am trying to find a way to deal with this active transaction and the rollback segment containing the active transaction.

 

First, try to drop the table (back up first, drop, and then recreate ):

Use CTAs to back up the table:

SQL> Create Table wap_authprice_user_sp_rd060308 tablespace index_subn01 nologging
Select * From wap_authprice_user_sp_rd where 1 = 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>

We see that every time you scan the table to the database in that rollback segment, the system will report the ORA-01555.

It can be inferred that all the table's FTS will fail, such as Exp:

Oracle @ rdb01:/Oracle> exp report/ABC parfile = A. Par

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

Copyright (c) 1982,200 2, 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 @ rdb01:/Oracle>

 

Now, only DUl is used:

DUL> unload report. wap_authprice_user_sp_rd;
. Unloading table wap_authprice_user_sp_rd
9430056 rows unloaded
DUL> exit

Oracle @ rdb01:/data/tmp/DUl> gzip report_wap_authprice_user_sp_rd.dmp
Oracle @ rdb01:/data/tmp/DUl> ll report_wap_authprice_user_sp_rd.dm *
-RW-r -- 1 Oracle DBA 49514464 Mar 13 report_wap_authprice_user_sp_rd.dmp.gz
Oracle @ rdb01:/data/tmp/DUl>

At this time, it may be a bit rewarding to use oracledebug to track the process or session:

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 & gt; select PID, spid, username from V $ process where spid = 15664;

PID spid Username
-------------------------------------
63 15664 Oracle

SQL>
SQL> oradebug setorapid 63
ORA-00072: Process & quot; UNIX process PID: 15664, image: Oracle @ rdb01 (TNS V1-V3) & quot; is not active
SQL>

Obviously, this result is consistent with the kill process on the operating system and the kill session in the database. We cannot find the actions of these processes and sessions.

Alert. log has been cleared, but the following information can be seen in the database:

USN name status tablespace_name ADDR Sid serial # username program machine osuser
--------------------------------------------------------------------------------------------------------------------------------------
19 _ syssmu19 $Pending offlineUndotbs1 c0000001a7bf5218 71 39809 reportSqlldr @ rpt01(TNS cs_rpt01 report16

This rollback segment is corrupted and the active transaction is a sqlloader process (the SQL statement that Sid 71 is executing in the database just now is such an insert Statement ). This confirms my initial guess. For some reason, the developer killed the sqlloader process being executed, and there was a row of activity in the rollback segment. This happened after 01:48:33, 12/30, because the problem was not found and handled in a timely manner, the database logs have been overwritten multiple times. Then, what we can see is happening.

Although the alert. Log content is no longer available, we found the corresponding trace -- report_ora_15664.trc based on the time when the event occurred, which contains the following content:

* ** Session ID: (71.25999) 01:48:33. 580
* ** 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 values (locationid, icpid, subscat, icpattr, icpcode, counts, period, stat_time) values (: locationid,: icpid,: subscat,: icpattr,: icpcode,: counts,: Period, to_date (: stat_time, 'yyyy-mm-dd hh24: MI: ss '))

ORA-00600 [4193], this type of errors is generally the activity rollback segment damage a type of error, the specific can view Metalink.

Now I have developed an implementation plan and will start to solve this problem next Monday (because 24x7, such risky operations must be reported to the company's leaders and customer leaders for approval at various layers, haha )...