Flashing Back a Failed Primary Database into a Physical Standby Database
The following steps assume the user has already performed a failover involving a physical standby database and Flashback Database has been enabled on the old primary database. This procedure brings the old primary database back into the Data Guard configuration as a new physical standby database.
Step 1 Determine the SCN at which the old standby database became the primary database.
On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
Step 2 Flash back the failed primary database.
To create a new physical standby database, shut down the old primary database (if necessary), mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in Step 1:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;
SQL> flashback database to scn 3188744;
flashback database to scn 3188744
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
========================================================
DIFFERENT OPTION OF PERFORMING FLASHBACK
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE – 2/24);
SQL> FLASHBACK DATABASE TO SCN n;
SQL> FLASHBACK DATABASE TO SEQUENCE=M THREAD=N;
SQL> FLASHBACK DATABASE TO TIMESTAMP timestamp ‘2012-01-25 13:54:00’;
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp(‘2012-01-25 13:54:00’, ‘YYYY-MM-DD HH24:MI:SS’);
========================================================
If you get like abover error than
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
——————– ——— —————- ————–
ESTIMATED_FLASHBACK_SIZE
————————
3192224 24-APR-13 1440 157286400
SQL> select to_char(oldest_flashback_time,’mm/dd/yy hh24:mi:ss’) from v$flashback_database_log;
TO_CHAR(OLDEST_FL
—————–
04/24/13 14:46:02
SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;
SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)
———————- ———————–
357 102400
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp(’04/24/13 14:46:02′,’mm/dd/yy hh24:mi:ss’) ;
## ignore any other errors like incomplete media recovery and file not consistent and preoceed to convert to physical standby
Step 3 Convert the database to a physical standby database.
Perform the following steps on the old primary database:
Issue the following statement on the old primary database:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
This statement will dismount the database after successfully converting the control file to a standby control file.
Shut down and restart the database:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Step 4 Restart transporting redo to the new physical standby database.
Before the new standby database was created, the new primary database probably stopped transmitting redo to the remote destination. To restart redo transport services, perform the following steps on the new primary database:
Issue the following query to see the current state of the archive destinations:
SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL
2> FROM V$ARCHIVE_DEST_STATUS;
If necessary, enable the destination:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;
Perform a log switch to ensure the standby database begins receiving redo data from the new primary database, and verify it was sent successfully. At the SQL prompt, enter the following statements:
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL
2> FROM V$ARCHIVE_DEST_STATUS;
On the new standby database, you may also need to change the LOG_ARCHIVE_DEST_n initialization parameters so that redo transport services do not transmit redo data to other databases. This step can be skipped if both the primary and standby database roles were set up with the VALID_FOR attribute in one server parameter file (SPFILE). By doing this, the Data Guard configuration operates properly after a role transition.
Step 5 Start Redo Apply.
Start Redo Apply or real-time apply on the new physical standby database:
To start Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
To start real-time apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> USING CURRENT LOGFILE DISCONNECT;
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.
