DataGuard slave database ORA-01196 fault recovery, dataguardora-01196

DataGuard slave database ORA-01196 fault recovery, dataguardora-01196

Symptom

When shutdown abort is used to stop the slave uard slave database, the slave database cannot be open, and a ORA-01196 error is reported.

 

The details are as follows:

 

If logs cannot be applied to a slave database, no error is found when you view the logs of the slave database. If you suspect that the Application Log Service of the slave database is stopped, you try to restart the slave database;

The shutdown abort command may be used because the standby database is busy with reading services and the shutdown immediate takes too long to close the standby database;

However, an error is reported later when the slave database is started, causing damage to the data file. The scn numbers of the control file and the data file are inconsistent.

 

-- An error is reported when the slave database is started.

SQL> startup

The ORACLE routine has been started.

 

Total System Global Area 2.0310E + 10 bytes

Fixed Size 2235256 bytes

Variable Size 9328133256 bytes

Database Buffers 1.0939E + 10 bytes

Redo Buffers 40894464 bytes

The database has been loaded.

 

ORA-10458: standby database requiresrecovery

ORA-01196: File 1 inconsistent due to media recovery session failure

ORA-01110: DATA File 1: '+ DATA/htdb5/datafile/system.261.759082693'

 

 

-- View logs

Alter database open

Data Guard Brokerinitializing...

Data Guard Brokerinitialization complete

Beginning standby crash recovery.

Serial Media Recovery started

Managed Standby Recoverystarting Real Time Apply

Media Recovery Log + FRA/htdb5/archivelog/2015_07_16/thread_eclipseq_180068.1541.885192077

Thu Jul 16 12:00:47 2015

Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:

ORA-01013: user request to cancel current action

ORA-10567: Redo is inconsistentwith data block (file #47, block #1187724, file offset is 1139900416 bytes)

ORA-10564: tablespace JDYWP_IDX

ORA-01110: DATA File 47: '+ DATA/htdb5/datafile/jdywp_idx.336.837967805'

ORA-10561: block type 'transactional managed index Block', data object #251837

Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:

ORA-00339: archiving logs do not contain any redo

ORA-00334: archiving logs: '+ DATA/htdb5/onlinelog/group_2.280.759082845'

ORA-10567: Redo is inconsistentwith data block (file #47, block #1187724, file offset is 1139900416 bytes)

ORA-10564: tablespace JDYWP_IDX

ORA-01110: DATA File 47: '+ DATA/htdb5/datafile/jdywp_idx.336.837967805'

ORA-10561: block type 'transactional managed index Block', data object #251837

Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc (incident = 116743 ):

ORA-00600: Internal error code, parameter: [3020], [47], [1187724], [198320012], [], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistentwith data block (file #47, block #1187724, file offset is 1139900416 bytes)

ORA-10564: tablespace JDYWP_IDX

ORA-01110: DATA File 47: '+ DATA/htdb5/datafile/jdywp_idx.336.837967805'

ORA-10561: block type 'transactional managed index Block', data object #251837

Incident details in:/u01/app/ora11g/diag/rdbms/htdb5/htdb5/incident/incdir_116743/htdb5_ora_10154_i116743.trc

Use ADRCI or Support Workbenchto package the incident.

See Note 411.1 at My OracleSupport for error and packaging details.

Standby crash recovery aborteddue to error 600.

Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:

ORA-00600: Internal error code, parameter: [3020], [47], [1187724], [198320012], [], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistentwith data block (file #47, block #1187724, file offset is 1139900416 bytes)

ORA-10564: tablespace JDYWP_IDX

ORA-01110: DATA File 47: '+ DATA/htdb5/datafile/jdywp_idx.336.837967805'

ORA-10561: block type 'transactional managed index Block', data object #251837

Recovery interrupted!

Some recovered datafiles maybeleft media fuzzy

Media recovery may continue butopen resetlogs may fail

Completed standby crashrecovery.

Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:

ORA-10458: standby databaserequires recovery

ORA-01196: File 1 inconsistent due to media recovery session failure

ORA-01110: DATA File 1: '+ DATA/htdb5/datafile/system.261.759082693'

ORA-10458 signalled during: alter database open...

Thu Jul 16 12:00:49 2015

Sweep [inc] [116743]: completed

Sweep [inc2] [116743]: completed

Thu Jul 16 12:00:49 2015

Dumping diagnostic data indirectory = [cdmp_2015071631649], requested by (instance = 1, osid = 10154), summary = [incident = 116743].

Thu Jul 16 12:01:50 2015

 

 

Solution:

 

The time point when the standby database returns to the normal state.

 

-- The prerequisite is that the database has been enabled before it is flashed back.

 

SQL> select FLASHBACK_ON from v $ database;

 

FLASHBACK_ON

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

YES

 

 

SQL> Flashback database to timestamp to_timestamp ('2017-07-16 4:00:05 ', 'yyyy-mm-ddhh24: mi: ss ');

-- Or use Flashbackdatabase to scn 947921

 

SQL> alter database open;

 

 

SQL> select open_mode from v $ database;

 

OPEN_MODE

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

READ ONLY

 

-- Start real-time applications

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 

SQL> select open_mode from v $ database;

 

OPEN_MODE

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

READ ONLY WITH APPLY

 

-- View the log to see that the log has been applied since the time point of flash back.

Thu Jul 16 13:36:01 2015

Flashback database to timestampto_timestamp ('2017-07-16 4:00:05 ', 'yyyy-mm-dd hh24: mi: ss ')

Flashback Restore Start

Thu Jul 16 13:39:30 2015

Flashback Restore Complete

Flashback Media Recovery Start

Started logmerger process

Parallel Media Recovery startedwith 16 slaves

Flashback Media Recovery Log + FRA/htdb5/archivelog/2015_07_16/thread_eclipseq_180047.2212.885180637

Thu Jul 16 13:41:54 2015

Flashback Media Recovery Log + FRA/htdb5/archivelog/2015_07_16/thread_eclipseq_180061.2611.885182343

Thu Jul 16 13:42:04 2015

Flashback Media Recovery Log + FRA/htdb5/archivelog/2015_07_16/thread_eclipseq_180062.2861.885182537

Thu Jul 16 13:42:12 2015

Incomplete Recovery applieduntil change 71489772016 time 07/16/2015 04:00:06

Flashback Media RecoveryComplete

Completed: Flashback databaseto timestamp to_timestamp ('2017-07-16 4:00:05 ', 'yyyy-mm-dd hh24: mi: ss ')

Thu Jul 16 13:43:25 2015

Deleted Oracle managed file + FRA/htdb5/archivelog/2015_07_15/thread_eclipseq_179690.2885.885083087

Thu Jul 16 13:43:25 2015

Standby controlfile consistentwith primary

RFS [3]: Selected log 8 forthread 1 sequence 180122 dbid 1083719948 branch 759079182

Archived Log entry 180115 addedfor thread 1 sequence 180121 ID 0x40a48484 dest 1:

Thu Jul 16 13:45:41 2015

Alter database open

Data Guard Brokerinitializing...

Data Guard Brokerinitialization complete

 

SMON: enabling cache recovery

Dictionary check beginning

Dictionary check complete

Database Characterset isZHS16GBK

No Resource Manager plan active

Replication_dependency_trackingturned off (no async multimaster replication found)

Physical standby databaseopened for read only access.

Completed: alter database open

Thu Jul 16 13:45:44 2015

ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE THROUGH ALL SWITCHOVERDISCONNECT USING CURRENT LOGFILE

Attempt to start backgroundManaged Standby Recovery process (htdb5)

Thu Jul 16 13:45:44 2015

MRP0 started with pid = 51 and OSid = 14743

MRP0: Background ManagedStandby Recovery process started (htdb5)

Started logmerger process

Thu Jul 16 13:45:50 2015

Managed Standby Recoverystarting Real Time Apply

Parallel Media Recovery startedwith 16 slaves

Waiting for all non-currentORLs to be archived...

All non-current ORLs have beenarchived.

Media Recovery Log + FRA/htdb5/archivelog/2015_07_16/thread_eclipseq_180062.2861.885182537

Completed: ALTER DATABASERECOVER MANAGED STANDBY DATABASE THROUGHALL SWITCHOVER DISCONNECT USING CURRENTLOGFILE

Thu Jul 16 13:46:08 2015

Media Recovery Log + FRA/htdb5/archivelog/2015_07_16/thread_eclipseq_180063.20.3.885182777

Thu Jul 16 13:46:35 2015

Media Recovery Log + FRA/htdb5/archivelog/2015_07_16/thread_eclipseq_180064.2542.885183119

Thu Jul 16 13:47:07 2015

Media Recovery Log + FRA/htdb5/archivelog/2015_07_16/thread_eclipseq_180065.2717.885183615

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.