Yesterday, a backup database, a fault in the Open? Read? Only, resulting in a backup of Standby failed. The information in Alert log is as follows:
Errors in file /opt/oracle/admin/crmhz/udump/crmhz_ora_13914.trc:ORA-00604: error occurred at recursive SQL level 1ORA-16000: database open for read-only accessError 604 happened during db open, shutting down databaseUSER: terminating INSTANCE DUE TO ERROR 604INSTANCE TERMINATED BY User, PID = 13914ORA-1092 Signalled Database: Alter Database Open Read Only ... Tue Aug 10 09:31:51 2004
View Trace File Information
[Root @ crmsb bdump] # more ?? /opt/oracle/admin/crmhz/udump/crmhz_ora_13914.trc/opt/oracle/admin/crmhz/udump/crmhz_ora_13914.trcOracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning OLAP AND ORACLE DATA MINING OPTIONSJSERVER Release 9.2.0.1.0 - ProductionOracle_Home = /opt/oracle/product/9.2.0system name: ?????? CrmsBrelease: ??????? 2.4. 9-E.3SMPVERSION: ??????? # 1 SMP fri May 3 16:48:54 EDT 2002Machine: ??????? I686instance name: crmhzredo thread mounted by this instance: 1Oracle Process Number: 11Unix Process PID: 13914, Image: Oracle @ CRMSB (TNS V1-V3)
*** session ID: (10.3) 2004-08-10 09: 30: 37.408 *** 2004-08-10 09: 30: 37.408Managed recovery: Cancel posted.ora-00604: Error Occurred At Recursive SQL Level 1 OA- 16000: Database Open for Read-Only Access
I have not provided what valuable information, I am very strange to have 604 errors.
So I decided TRACE? OPEN process
Startup Nomount
ALTER DATABASE MOUNT STANDBY? DATABASE;
SELECT SID AUM = 1;
SELECT SID, Serial # from v $ session where sid =?;
Exec dbms_system.set_ev (SID, Serial #, 10046, 12, '');
Alter Database Open Read Only;
This is the database crash, find TRACE FILE, found that the end is
Select Grantee #, Privilege #, NVL (Col #, 0), Max (MOD (NVL (option $, 0), 2)) from Objauth $ Where Obj # =: 1 Group by Grantee #, Privile #, NVL (ColL #, 0) Order by grantee # end of stmtparse # 13: c = 0, E = 485, P = 0, Cr = 0, Cu = 0, MIS = 1, R = 0, DEP = 2, OG = 0, TIM = 1066522815818430BINDS # 13:? bind 0: DTY = 2 mxl = 22 (22) mAL = 00 SCL = 00 pre = 00 oacflg = 08 oacfl2 = 1 size = 24 OFFSET = 0 ?? bfp = 40647620 BLN = 22 avl = 02 FLG = 05 ?? Value = 72exec # 13: c = 0, E = 518, P = 0, Cr = 0, Cu = 0, MIS = 0, R = 0, DEP = 2, OG = 4, TIM = 1066522815819173FETCH # 13: C = 0, E = 58, P = 0, Cr = 2, Cu = 0, MIS = 0, R = 0, DEP = 2, OG = 4, TIM = 1066522815819296 ======= ============== PARSING IN CURSOR # 12 LEN = 34 Dep = 1 UID = 0 OCT = 7 LID = 0 TIM = 1066522815819480 HV = 3008185193 AD = '56fcbaac'delete from IDL_UB1 $ Where Obj # =: 1END OF STMTPARSE # 12: C = 10000, E = 14804, P = 6, Cr = 79, Cu = 0, MIS = 1, R = 0, DEP = 1, Og = 0, TIM = 1066522815819472BINDS # 12:? Bind 0: DTY = 2 mxl = 22 (22) mAL = 00 SCL = 00 pre = 00 oacflg = 08 oacfl2 = 1 size = 24 offset = 0 ?? bfp = 40645A48 BLN = 22 AVL = 06 flg = 05 ?? Value = 4294951150Wait # 12: Nam = 'DB file sequential read' ELA = 41 p1 = 1 p2 = 22839 P3 = 1Wait # 12: NAM = 'DB File Sequential Read' ELA = 27 P1 = 1 P2 = 9 P3 = 1exec # 12: C = 0, E = 884, P = 2, Cr = 4, Cu = 2, MIS = 0, R = 0, DEP = 1, og = 4, TIM = 1066522815820884 error # 12: err = 16000 TIM =
1837753940ORA-00604: Error Occurred At Recursive SQL Level 1Or-16000: Database Open for Read-Only AccessExec # 1: C = 370000, E = 375493, P = 304, Cr = 5505, Cu = 4, MIS = 0, R = 0, DEP = 0, OG = 4, TIM = 1066522815826303 error # 1: err = 1092 TIM = 1837753940 is very strange, normal Standby Database Open read Only is not delete, there is actually DML here. So I started to try to study IDL_UB1 $ is a table, and the result is found to store the PL / SQL code, it is very strange, why is this database that occurs, continue in $ oracle_home / rdbms / admin, grep -ir idl_ub1 $ *, The result is found and a X $ table is hung on the relationship to connect, unnatural, and through the delete from idl_ub1 $ where obj # =: 1 Bind variable value = 4294951150 to combine the search-out View lookup, there is no progress. In fact, according to Wait # 12: nam = 'db file sequential read' ELA = 27 p1 = 1 p2 = 9 p3 = 1 can know this delete is a data block that accesses File # = 1 block # = 9, it is clear, This is the block of the system back to the segment. According to this, we can know here, this is a rollback operation, that is, due to the failure of DDL in the primary database, the system returning segment records the backup information of the operation, However, the database has not been rolled back yet, and there is this situation in STANDBY.
In order to try to solve this problem, continue to apply a few logs, then Open read only, the result failed, that is, the failure of the DDL has not rolled back in the primary database, and the database has not been found in any abnormal process or transaction, lock It can only be considered that the system has an abnormality. So, in the case of the consent of the relevant person in charge, we decided to restart the main database once again after get off work, so that the force returned to the DDL of the failed. After get off work, restart the database, switch the log and archive, apply the log to the archive log after Standby to restart the database, and then Open read Only is successful, the problem is resolved.
