Physical Standby Read/Write Testing With Belated Flashback Database

BUG:8477885 - FLASHBACK STANDBY CONVERTED FROM ACTIVATED PHYSICAL STANDBY (PRIMARY) NOT WORK
NOTE:805438.1 - How To Open Physical Standby For Read Write Testing and Flashback

Physical Standby Read/Write Testing With Belated Flashback Database [ID 838249.1]
		修改时间 22-MAR-2010 类型 PROBLEM 状态 PUBLISHED

In this Document
Symptoms
Cause
Solution
References

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
Information in this document applies to any platform.

Symptoms

Starting MRP on Physical standby database after Read/Write testing reports below errors in alert log

Media Recovery apply resetlogs offline range for datafile 1, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 2, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 3, incarnation : 0

and MRP asks for old logs from previous incarnation.

Cause

The correct & documented steps for Physical Standby read/write testing in short is

1) Create restore point on Physical Standby
2) Activate standby database & Do necessary testing
3) Flashback the database to restore point
4) Convert to Standby
For correct steps please follow the below Note 805438.1 How To Open Physical Standby For Read Write Testing and Flashback

However the step 3 was belated after step 4.

When the standby was originally activated(step 2) , there was a resetlogs and new incarnation was created (lets say inc#=17). When the activated standby was later converted back to a standby we check the current resetlogs, and saw the database was still in the activated incarnation [ flashback (step3) was completely missed ] so thd RDI - Recovery Database Incarnation stayed at inc# 17. When MRP recovery was then started, it began at the current SCN in the activated incarnation, and MRP waited for redo logs from the activated incarnation (inc#17).

The belated flashback (step3) did restore the datafiles, database SCN, and resetlogs all back to their state as of the time restore point wascreated, but the flashback will not modify the RDI, so the RDI stayed at inc#17 so when recovery resumed, MRP immediately followed the redo branch from the pre-activation incarnation (inc#1) to the activated incarnation (inc#17), and then waited for redo logs from the activated incarnation (inc#17).

Solution

In the standby database
================
Use SQLPLUS

shutdown
startup mount
flashback database to restore point <restore point name>;
select name, database_incarnation# from v$restore_point where name='<restore point name>';

Reset the database incarnation to the before activation.
======================================
Use RMAN
connect target
list incarnation;
reset database to incarnation <n>;

Now starting the MRP will continue to receive and apply the logs from PRIMARY.