Fixing Physical Standby after clonning Primary Database

This is a common scenario where the server of the Primary Database is clonned for testing purposes or to create a Dev environment for developers. When the server is clonned all the data is clonned but also all the parameters of the instance. Including the parameter "log_archive_dest_*" and the parameter "log_archive_config". When the new clonned database is started up, The database instance is started and the database is open with "OPEN RESETLOGS" this creates a new Incarnation in the clonned database. Once the database is open, it starts sending archived logs to the standby, at the same time that the original primary database is also sending archivelogs. The result of this is that the standby database gets the new incarnation and switch itself to be an standby database of the clonned database.

 

 

How to fix it?

These are the steps that I usually follow:

Remove data guard parameters from the clonned database instance:

alter system set dg_broker_start=false scope=both;
alter system reset log_archive_dest_2 ; (it can be other number)
alter system reset log_archive_config;

Removing data guard configuration files in the clonned database:

rm -rf /oracle/DB1/121/dbs/dr1DB1.dat
rm -rf /oracle/DB1/121/dbs/dr2DB1.dat

Rebooting the clonned database:

shutdown immediate;

startup;

Ensure the standby database is not open in read-only:

select open_mode from v$database;

List incarnations in the Standby Database:

 RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB1      1685885605       PARENT  1          20-APR-17
2       2       DB1      1685885605       CURRENT 10009258336 08-APR-22

Reset the incarnation in Standby Database:

RMAN> reset database to incarnation 1;

database reset to incarnation 1

Verify the current incarnation:

RMAN>  list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB1      1685885605       CURRENT 1          20-APR-17
2       2       DB1      1685885605       ORPHAN  10009258336 08-APR-22

Reboot the Standby Database:

shutdown immediate;
startup nomount;
alter database mount standby database;