ORA-00600 3020 和ORA-10567 問題定位

SQL> recover standby database;
ORA-00279: change 7718749264083 generated at 08/11/2012 16:02:01 needed for
thread 1
ORA-00289: suggestion : /arch_local/arch/arch_773961770768202.arch
ORA-00280: change 7718749264083 for thread 1 is in sequence #77396

 Specify log: {=suggested | filename | AUTO | CANCEL}
 auto
 ORA-00283: recovery session canceled due to errors
 ORA-12801: error signaled in parallel query server P006
 ORA-00600: internal error code, arguments: [3020], [22], [1017752],
 [93292440],
 [], [], [], []
 ORA-10567: Redo is inconsistent with data block (fi

 ORA-01112: media recovery not started

透過查詢metalink 文件，可能原因是丟失redo,或者邏輯損壞等等,

Cause
The ORA-600 [3020] stuck recovery error could occur on the Standby database for several reasons including: a lost write on the Primary, a lost write on the Standby, missing redo, or logical corruption on the primary resulting in an incomplete redo chain

有以下2種方法:

1：根據資料庫日誌告警找到錯誤檔案號和塊號，對物件進行重建，然後跳過損壞的物件

SQL> select SEGMENT_NAME from DBA_EXTENTS
     where FILE_ID=&file_number and
     &block_number BETWEEN BLOCK_ID and BLOCK_ID+BLOCKS-1;
If the error provides the object number determine the affected object with the following query:

SQL> select OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
     from DBA_OBJECTS
     where DATA_OBJECT_ID = &object_number;2.If feasible, drop and recreate the affected objects on the primary.

3.Once the objects have been recreated, use the following procedure to skip corrupted block on the standby:

1.Temporarily disable lost write protection on the standby:

SQL> ALTER SYSTEM SET DB_LOST_WRITE_PROTECT = NONE; 2.Allow recovery to proceed in spite of block corruptions by running the RECOVER command with the ALLOW n CORRUPTION clause, where n is the number of allowable corrupt blocks.

SQL> alter database recover automatic standby database
     allow 1 corruption; 3.Once the alert log indicates the blocks have been marked corrupt, restart managed recovery.

SQL> alter database recover cancel;
SQL> alter database recover managed standby database
     using current logfile disconnect;

 

2：啟用standby資料庫轉變為主庫，重做dataguard備庫

Option 2: Activate the standby database

If the affected objects cannot be recreated then activate the standby database. By activating the standby database you will be experiencing data loss but data integrity will be ensured.

1.Issue the following SQL statement on the standby database to convert it to a primary:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
2.Back up the new primary. Performing a backup immediately is a necessary safety measure, because you cannot recover changes made after the failover without a complete backup copy of the database. As a result of the failover, the original primary database can no longer participate in the Data Guard configuration, and all other standby databases will now receive and apply redo data from the new primary database.

3.Open the new primary database.

4.An optional step is to recreate the failed primary as a physical standby. This can be done using the database backup taken at the new primary in step 3. (You cannot use flashback database or the Data Guard broker to reinstantiate the old primary database in this situation.)