Great Tips for Switchover to Physical Standby Database!!

 Author: Yuri van Buren

Real Application Clusters Database:

Oracle Real Application Clusters (Oracle RAC) enables you to deploy single database across a cluster of servers. No application changes are necessary and you can experience fault tolerance, performance and scalability.

Switchover in a Real Application Clusters Database:

A single primary instance and only one standby instance can be active during a switchover in a real application clusters database and hence you have to shutdown all but one primary and one standby instance before a switchover.

Once a switchover is completed, you are required to restart those primary and standby instances that were shut down during the switchover process.

Performing switchover when Other Instances are running:

If you try to perform a switchover when other instances are running then you will get ORA-01105 as follows

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ORA-01105: mount is incompatible with mounts by other instances

Switchover on Primary Database:

In order to perform a switchover, run below command on the primary database.

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

The above statement first terminates all active sessions by closing the primary database. Then any non-archived redo log files are transmitted and applied to standby database. Apart from that an end-of-redo marker is added to the header of the last log file that was archived.

A backup of current control file is created and the current control file is converted into a standby control file.

Bringing Standby Database as Primary Database:

You can bring up the standby database as the primary database on the standby database server in case of primary site destroyed by a disaster.

The ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL command is used to cancel the Redo Apply or real-time apply.

The standby database is activated by using the ALTER DATABASE ACTIVATE STANDBY DATABASE command. This command converts the standby database to a primary database, creates a new reset logs branch, and then opens the database.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Transition Standby Database to Primary Role:

The ACTIVATE statement used in above command is used to immediately transition the standby database to the primary role. No attempt is made to apply any additional redo data that may be exist at the standby location.

The ACTIVATE statement should be carefully used as the cost of data loss at standby location should be balanced against the extended downtime period that is required to repair the primary database.

Changed Database Role:

You can issue below command to check the changed database role.

select database_role, db_unique_name, name from v$database;

DATABASE_ROLE    DB_UNIQUE_NAME                 NAME
————————-    ——————————             ————
PRIMARY                             stdb                                      PRAC

Physical Standby Database with Time Lag:

You can also create a physical standby database with a time lag. You can set a delay for standby database by modifying the LOG_ARCHIVE_DEST_2 initialization parameter on the primary database . You can run below command to add a six hour delay.

The following is an example of how to add a 4-hour delay:

alter system set log_archive_dest_2='SERVICE=stdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=stdb' DELAY=360 ' scope=both; Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   DBA Tips for Effective Schema Management!!
   Performance boost up with Oracle row re-sequencing!!
   Database Tuning Guide for Third Party Applications
   Beware of Oracle Automatic Memory Management Pitfalls!!