Effect of PITR on Dataguard Environment (Standby MRP Crashed with ORA-39873)

This document explains how to overcome MRP Crash with ORA-39874 and ORA-39873 after a Point in time (PITR) recovery on Primary.

-----Stnadby Alert log-----------

Recovery of pluggable database PDBBOSTON aborted due to pluggable database open resetlog marker.
To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 3961537, or timestamp before 10/10/2013 04:55:31, and restart recovery
MRP0: Background Media Recovery terminated with error 39874
.
Errors in file /u01/app/oracle/diag/rdbms/chicago/chicago/trace/chicago_mrp0_15640.trc:
ORA-39874: Pluggable Database PDBBOSTON recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 3961537.

Cause

Standby is ahead of primary RESETLOGS changes on PDBBOSTON . pdbboston have two datafiles i.e datafile 12 and 13.

Solution

 1. Make sure the corresponding standby PDB is closed.

SQL>alter pluggable database pdbboston close;

2. If DG Broker enabled then Stop the Apply via DG broker to avoid further restart of MRP before the PDB restore.

DGMGRL>edit database chicago set state=apply-off;

3. If Standby in ADG then mount it.

SQL>select database_role,open_mde from v$database;

SQL>shutdown immediate;
SQL>startup mount

We have two options to recover Standby PDB.

OPTION I (Restore PDB)

1. If Standby is the backup server the go for direct restore because standby have the old backups.

On Standby,

RMAN> list  backup of pluggable database pdbboston;

NOTE : The backup SCN should be less or equal to PITR SCN. If no backup exist of lessr SCN then copy backuppiece which have less or equal SCN from primary

2. Get the PDB name SCN from the standby alert log erro message (ORA-39874 and ORA-39873).

RMAN>restore pluggable database <PDB DB> until scn <primary PITR SCN>;

Ex,

RMAN>restore pluggable database pdbboston until scn 3961537;

------Standby Alert log---------------

Restore ..until SCN 2. Falshback to SCN
Full restore complete of datafile 12 /oradata/pdbchicago/pdbseed/system01.dbf.  Elapsed time: 0:00:14
  checkpoint is 3961537
  last deallocation scn is 3961537
  Undo Optimization current scn is 3951674
Thu Oct 10 05:55:45 2013
Full restore complete of datafile 13 /oradata/pdbchicago/pdbseed/sysaux01.dbf.  Elapsed time: 0:00:25
  checkpoint is 3961537
  last deallocation scn is 1362129

NOTE :After Restore do not start MRP immediately. If MRP started by mistakenly before the standby SYNCup with primary then MRP will crash with ORA-65138,

Managed Standby Recovery starting Real Time Apply
MRP0: Background Media Recovery terminated with error 65138
.
Errors in file /u01/app/oracle/diag/rdbms/chicago/chicago/trace/chicago_mrp0_18820.trc:
ORA-65138: Data file 12 of pluggable database PDBBOSTON belongs to an orphan PDB incarnation.
ORA-01110: data file 12: '/oradata/pdbchicago/pdbseed/system01.dbf'
Managed Standby Recovery not using Real Time Apply

 To Resolve,

 a. Take fresh backup of PDB from primary

RMAN> backup pluggable database pdbboston;

 b. Copy the backuppiece to Standby.
 c. On Standby restore PDB.

RMAN> restore pluggable database pdbboston;

3. Finally Open database,PDB and start MRP.

RMAN>alter database open;
RMAN>alter pluggable database pdbboston open;
RMAN>alter database recover managed standby database disconnect;

 NOTE : If DG broker enable,start MRP using,
 DGMGRL>edit database chicago ser state=apply-on;
 
OPTION II

1. If Flashback is enabled then do flashback to scn 1362129.

SQL>flashback database to scn <primary PITR SCN>;

Ex,

SQL>flashback database to scn 3961537;

2. Open database,PDB and start MRP.

 

RMAN>alter database open;
RMAN>alter pluggable database pdbboston open;
RMAN>alter database recover managed standby database disconnect;
RMAN> restore pluggable database pdbboston;

  

NOTE : If DG broker enable,start MRP using,
DGMGRL>edit database chicago ser state=apply-on;

 

Thanks

Srini