Time for action – failover with a physical standby database using SQL*Plus

Follow these steps to complete a failover on the physical standby Data Guard environment:

  1. If you're able to mount a primary database, perform the following command to flush the redo from the primary online redo logfiles:
    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.

  2. Check the status of both the primary and standby databases. With the primary database in the 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
  3. If the primary database is inaccessible, refer to the alert logfile for the latest log switch sequence or go to the archive log location and check the maximum sequence number as shown in the following command:
    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.

  4. Now check the maximum sequence applied on the standby database using the following code:.
    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.

  5. Initiate failover by stopping Redo Apply and running the 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.

    Tip

    The FINISH keyword is used for failover and recovers the current standby redo logfiles. The FORCE keyword is used to terminate RFS processes immediately so that failover will not wait for them to exit.

    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

    Tip

    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.

  6. Complete failover to the physical standby database by converting it from the standby role to primary as follows:
    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
  7. After performing failover, the new primary database will be in the MOUNT state. Shut down and start up the new primary database as shown in the following code:
    SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Have a go hero

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

Performing failover with a logical standby database has some disadvantages because of the following points:

  • A logical standby database functions with SQL Apply instead of Redo Apply and there are limitations to accept the incoming DML from primary and also unsupported data types. So we can't guarantee that all the changes from the primary database have been successfully applied on the logical standby database.
  • Once you perform failover, you have to recreate other standby databases for the configuration.

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.21.12.140