Follow these steps to complete a failover on the physical standby Data Guard environment:
SQL> alter system flush redo to INDIA_UN;
Use DB_UNIQUE_NAME
of the standby database so that redo will be sent to the respective standby database.
MOUNT
state, check the maximum archive log sequence that has been generated as shown in the following code:SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 462
Fri Oct 12 22:20:30 2012 Thread 1 advanced to log sequence 462 (LGWR switch) Current log# 1 seq# 462 mem# 0: /u01/app/oracle/oradata/orcl/redo01.log ........... Archived Log entry 1064 added for thread 1 sequence 462 ID 0x4eede1f7 dest 1:
Optionally, you can use the following:
[oracle@oracle-primary 2012_10_12]$ls -alrt -rw-r----- 1 oracle oinstall 40261120 Oct 12 22:20 o1_mf_1_461_87jllpq3_.arc -rw-r----- 1 oracle oinstall 41197056 Oct 12 23:08 o1_mf_1_462_87jodh9n_.arc
The maximum archive sequence generated is 462
, which we can see by querying v$archived_log
, the alert logfile, or the file systems.
SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 449
There are 13 archive logs that are not applied on the standby database. If they're not shipped from primary, you should transfer those archived logfiles and register and apply them to the standby database. If shipped but not applied, you must start Redo Apply on the standby database.
If the primary server is completely unavailable, you have to perform recovery on the standby database until the maximum transported archive log sequence.
recover
command with the finish force
option on the standby database as shown in the following command:SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database recover managed standby database finish force; Database altered.
On the alert logfile you will see the following:
Terminal Recovery: log 10 reserved for thread 1 sequence 463 Recovery of Online Redo Log: Thread 1 Group 10 Seq 463 Reading mem 0 Mem# 0: /u02/app/oracle/oradata/orcl/standby_redo01.log Identified End-Of-Redo for thread 1 sequence 463 Incomplete Recovery applied until change 3476339 time 10/12/2012 23:08:22 Media Recovery Complete (INDIA) Terminal Recovery: successful completion
If the recovery command raises an error because of a possible gap, try to resolve it. If this is not possible, continue failover with the following command and proceed to step 5.
SQL> alter database activate physical standby database;
If the recover
command completes successfully, continue with the next step.
SQL> alter database commit to switchover to primary with session shutdown; Database altered.
On the alert logfile you will see the following:
Standby became primary SCN: 3476337 Fri Oct 12 23:34:36 2012 Setting recovery target incarnation to 3 Switchover: Complete - Database mounted as primary Completed: alter database commit to switchover to primary
After that, perform the following code:
SQL> select db_unique_name,database_role,standby_became_primary_scn from v$database; DB_UNIQUE_NAME DATABASE_ROLE STANDBY_BECAME_PRIMARY_SCN -------------------- ---------------- -------------------------- INDIA_UN PRIMARY 3476337
MOUNT
state. Shut down and start up the new primary database as shown in the following code:SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;
We have just performed a failover to a physical standby database. Now go ahead and perform a failover to the logical standby database using SQL*Plus. In this case, after step 1 and 2, you just need to use the following command on the logical standby to perform a failover:
SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;
Performing failover with a logical standby database has some disadvantages because of the following points:
So it's not recommended to perform failover to the logical standby database if it's possible to perform failover to a physical standby. Also, depending on the RTO, RMAN restore and recovery is preferred over failover to a logical standby.