Prepare primary physical database for switchover Oracle Database Tips by Donald BurlesonDecember 9, 2015

Oracle Data Guard- Switchover and Failover

Preparing the Oracle instance for Switchover

The preparation of the Oracle instance is explained in the following two sections:

Physical Standby Database

On the physical standby database, the following checks should be performed before starting the switchover operation:

• If a symmetrical initialization parameter file is not being used, update the initialization parameter file on the physical Oracle instance to include all other standby databases and the original primary database as the log archive destination.
   

• The database should be mounted as the standby database. The Oracle instance can be in managed recovery mode or open for read only. If the database is open for query, the switchover operation will take longer to complete.
   

• Verify that the Oracle instance is in ARCHIVELOG mode. The Oracle instance should be placed in ARCHIVELOG mode before switching over the role.
   

• If any delay in the log apply service has been set for the physical Oracle instance to be switched over as the primary, reset the delay to ZERO.

Logical Standby Database

• The initialization parameter file should be updated to include the other logical standby databases and the original primary database as the archival destination so that the new primary database will start transmitting the redo data after the switchover operation.
   

• The logical Oracle instance to be converted into the primary database should be open and the log apply service should be running.
   

• The logical Oracle instance should be running in ARCHIVELOG mode.
   

• All delays in the log apply service should be removed using the DBMS_LOGSTDBY package. The following statement can be used to remove the delay in the log application:

DBMS_LOGSTDBY.APPLY_UNSET ('APLY_DELAY');

• Ensure that the logical standby database is in sync with the primary database. This will minimize the overall time required for the switchover operation.

Switchover to a Physical Standby Database

After performing the initial verifications on the primary and the standby database, the switchover operation is ready to be performed. The switchover operation involves changing the original primary database role to standby and one of the physical standby databases to primary. During this operation, the database service will be unavailable for the period of time when the primary is converted to standby but the standby has not yet changed roles. Creating redo logs on the Oracle instance prior to the switchover operation can minimize the downtime. Make sure there are no users connected to the database during the switchover operation. Connect to the primary site and execute the following steps:

Switchover to a Physical Standby Database

• Convert the primary database to the standby database using following statement:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

• Shutdown the original primary database and mount it as a Oracle instance using the following statement:

SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION;

• Connect to the physical standby database that is taking the role of the new primary database and execute the following statement to verify the switchover status:

Select
   Switchover_status
From
   V$DATABASE;

Output:

SWITCHOVER_STATUS
------------------
TO PRIMARY

Switchover to a Physical Standby Database

• On the standby database, after the primary database is switched over, the switchover status of TO PRIMARY indicates that the Oracle instance is ready for role transition.
   

• Execute the following statement to transition the role of the Oracle instance to the primary:

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

• This statement will convert the Oracle instance to the primary role. The online log files were not created during the Oracle instance creation, this statement will create the physical files for the online redo logs. The time taken to complete the transition depends on the size of the online redo logs of the original primary database. Once this step is complete, the new primary database is ready to be restarted.
   

• If the Oracle instance and the primary database reside on the same host machine and the lock_name_space parameter is being used for the standby database, the following warning will appear in the alert log file when restarting the new primary database:

            "WARNING: Setting LOCK_NAME_SPACE on non-Oracle instance can be very dangerous. It may even cause database corruption. Use it with caution"

• It is recommended that this parameter be used on the new Oracle instance and also removed from the new primary database. In addition to the lock_name_space parameter, the instance_name parameter of both the databases involved in the switchover and the Oracle Net entries will need to be updated to reflect the correct instance names. This is not required if the primary and standby databases involved in the switchover are located on two different hosts.