Converting a Failed Primary Into a Standby Database Using Flashback Database

Brief: After a failover occurs, the original primary database can no longer participate in the Data Guard configuration until it is repaired and established as a standby database in the new configuration. To do this, you can use the Flashback Database feature to recover the failed primary database to a point in time before the failover occurred, and then convert it into a physical or logical standby database in the new configuration.

 Prerequisite:

Configuring Oracle 11gR2 Data Guard - Physical Standby (without DG Broker)

Steps:
The following steps assume that a failover has been performed to a physical standby database and that Flashback Database was enabled on the old primary database at the time of the failover. This procedure brings the old primary database back into the Data Guard configuration as a physical standby database.


1) Determine the SCN at which the old standby database became the primary database.
On the new primary database (riyadh), issue the following query to determine the SCN at which the old standby (riyadh) database became the new primary database:


SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
1908822

2) Flash back the failed primary database.
Shut down the old primary (dubai) database (if necessary), mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in step 1

SQL> startup mount
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size                  2254624 bytes
Variable Size             436209888 bytes
Database Buffers          738197504 bytes
Redo Buffers                9191424 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 1908822;

Flashback complete.

SQL>

3) Convert the database to a physical standby database.
Perform the following on the old primary (dubai) database:

SQL>  ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
This statement will dismount the database after successfully converting the control file to a standby control file so Shut down and restart the database.


SQL> select name,open_mode,database_role from v$database;
select name,open_mode,database_role from v$database
                                         *
ERROR at line 1:
ORA-01507: database not mounted

SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size                  2254624 bytes
Variable Size             436209888 bytes
Database Buffers          738197504 bytes
Redo Buffers                9191424 bytes
Database mounted.
SQL>

4) Start transporting redo to the new physical standby database.
Perform the following on the new primary (riyadh) database:

-- see the current state of the archive destinations:
SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL
FROM V$ARCHIVE_DEST_STATUS
where DEST_ID <=2

DEST_ID	DEST_NAME	STATUS	PROTECTION_MODE	DESTINATION	ERROR	SRL
1	LOG_ARCHIVE_DEST_1	VALID	MAXIMUM PERFORMANCE			NO
2	LOG_ARCHIVE_DEST_2	VALID	MAXIMUM PERFORMANCE	dubai		YES

-- If necessary, enable the destination, in my case it was not required
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;
-- Perform a log switch to ensure the standby (dubai) database begins receiving redo data from the new primary (riyadh) database, and verify it was sent successfully. Issue the following SQL statement on the new primary (riyadh) database
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

After log switch see the archive logs on both new primary (riyadh) and old primary but currently standby (dubai) FRA location whether they are existing or not.

5)  Start Redo Apply on the new physical standby database.
Issue the following SQL statement on the new physical standby (dubai) database:

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

Database altered.

Redo Apply automatically stops each time it encounters a redo record that is generated as the result of a role transition, so Redo Apply will need to be restarted one or more times until it has applied beyond the SCN at which the new primary database became the primary database. Once the failed primary database is restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles.