Switchover of Databases (Primary to standby and standby to primary)

A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
Setup physical standby database

After completing physical standby database creation.

standby database creation

Follow below steps to switchover database from primary to standby and vice versa

SETUP:

PRIMARY (Primary Database with connection string as “TESTDB”)

[oracle@NVMBD1BZY150D00 301214]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 5 15:33:44 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> select instance_name,status,database_role from v$database,v$instance;

 

INSTANCE_NAME    STATUS       DATABASE_ROLE

---------------- ------------ ----------------

TESTDB           OPEN         PRIMARY

STANDBY (Secondary Database with connection string as “STANDBY”)

SQL> select instance_name,status,database_role from v$database,v$instance;

 INSTANCE_NAME    STATUS       DATABASE_ROLE

---------------- ------------ ----------------

STANDBY          OPEN         PHYSICAL STANDBY

 

Archivelog Destination 1 of Primary Database is “/data1/archive/testdb” and Archivelog destination 2 of Primary database is pointing to the service-name of the standby database.

Archivelog Destination 1 of PRIMARY:

SQL> sho parameter log_archive_dest_1

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_1                   string      LOCATION=/data1/archive/testdb VALID_

                                                 FOR= (ALL_LOGFILES, ALL_ROLES) D

                                                 B_UNIQUE_NAME=TESTDB

Archivelog Destination 2 of PRIMARY:

SQL> sho parameter log_archive_dest_2

 NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2                   string      SERVICE=STANDBY VALID_FOR=(ONL

                                                 INE_LOGFILES, PRIMARY_ROLE) DB_

                                                 UNIQUE_NAME=STANDBY

Archivelog Destination 1 of Standby database is “/data1/archive/STANDB” and archivelog destination 2 of standby database is pointing to the service-name of the primary database. (Note: destination 2 in standby database is not mandatory. It is required only if the standby database would be running in as primary database during switchover or failover. Hence it would be a good practice to set this parameter to avoid problems during the switchover or failover.)

Archive log destination 1 of STANDBY:

SQL> sho parameter log_archive_dest_1

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_1                   string      LOCATION=/data1/archive/STANDB

                                                 Y VALID_FOR=(ALL_LOGFILES,ALL_

                                                 ROLES) DB_UNIQUE_NAME=STANDBY

Archive log destination 2 of STANDBY:

SQL> sho parameter log_archive_dest_2

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2                   string      SERVICE=TESTDB VALID_FOR=(ONLI

                                                 NE_LOGFILES,PRIMARY_ROLE) DB_U

                                                 NIQUE_NAME=TESTDB

Pre-Switchover Checks:

These checks will have to be performed before the switchover activity is performed.

Verify whether Managed Recovery process is running on the standby database

Use the following query to check if the managed recovery process is running on the standby database.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

--------- ------------ ----------

ARCH      CLOSING             157
ARCH      CLOSING             155
ARCH      CONNECTED             0
ARCH      CLOSING             156
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                158
MRP0      WAIT_FOR_LOG        158

The above fig. shows that the Managed Recovery Process (MRP0) is running on the standby database. If MRP is not running, then start the process with real time enabled using the below query in the standby database.

SQL>alter database recover managed standby disconnect from session;

Once when the MRP has started on the standby database, make sure that the archive logs generated at the primary end are shipped and getting applied to the standby database.

At the primary side check the maximum archive log sequence that has got generated:

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

 THREAD# MAX(SEQUENCE#)

---------- --------------

1                163

          

At the standby side, check the maximum archive log sequence that has been applied from the primary database:

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

 THREAD# MAX(SEQUENCE#)

---------- --------------

         1            163

In the above case, sequence# 163 is the maximum sequence generated at the primary database and the same has been applied to the standby database.

Verify primary and standby tempfiles match

For each temporary tablespace on the standby, verify that temporary files associated with that tablespace on the primary database also exist on the standby database. Tempfiles added after initial standby creation are not propagated to the standby. Run this query on both the primary and target physical standby databases and verify that they match.

 SQL>select tmp.name filename,bytes,ts.name tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;

 

Verify that all datafiles are online on both primary and standby databases

Check whether all the datafiles are online prior to the switchover on both primary and standby databases

SQL>select name from v$datafile where status=’OFFLINE’;

 On Primary side:

 SQL> select name from v$datafile where status='OFFLINE';

 no rows selected

On Standby side:

SQL> select name from v$datafile where status='OFFLINE';

no rows selected

If there are any offline datafiles, then bring them online using the below query

 

SQL>alter database datafile <datafile name> online;

 Switchover Steps:

 These steps are performed during the switchover process at the primary database side.

 

Check if there are any jobs running on the primary database using the below query.

SQL> select * from dba_jobs_running;

no rows selected

If there are any jobs running on the primary database and if its execution is not very important, then terminate the job to continue further.

 

Block further job submission by setting the job_queue_processes parameter to 0 so that there would be no jobs running during switchover.

SQL> sho parameter job_que

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes                  integer     1000

In the above fig. the job_queue_processes parameter is set to 1000. Set this parameter to the value 0.

SQL>alter system set job_queue_processes=0 scope=spfile;

System altered

SQL>

Verify that the primary database can be switched over to the standby

Query the switchover_status column of the v$database view on the primary database to determine whether the primary database can be switched over to the standby.

SQL> select switchover_status from v$database;

 SWITCHOVER_STATUS

--------------------

TO STANDBY

A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly.

Switchover the primary database to standby

Once when value of switchover_status returns “TO STANDBY” or “SESSIONS ACTIVE” on the primary database, then perform the switchover using the below query

SQL> select switchover_status from v$database;

 SWITCHOVER_STATUS

--------------------

TO STANDBY

 SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

Now the primary database is switched over to the standby database. The execution of the above command may take some time and the archive logs generated during its execution would be automatically applied to the standby database. Once when the command is executed with the output as “Database altered”, it means that the primary database has been switched over to the standby.

Note: Always perform the switchover of the primary database to standby database first and then switchover the standby database to primary. If not, then you would end up landing with two primary databases

Switchover the standby database to primary

Query the switchover_status column from the v$database view at the standby side to determine whether the standby database can be switched over to the primary database.
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that redo apply is active and that redo transport is configured and working properly. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

Once when the value of switchover_status returns “TO PRIMARY” or “SESSIONS ACTIVE” on the standby database, then perform the switchover using the below query

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

Now the standby database has been switched over to the primary database.

Open the new primary database (STANDBY)

The new primary database will be in mount state. Open this new primary database using the below query.

SQL> alter database open;

Database altered.

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE

---------------- ------------ ----------------

STANDBY          OPEN         PRIMARY

 

 
Restart the new standby database

Restart the new standby database (old primary database prim), bring it to the mount stage and start the managed recovery process.

Shutdown the new standby database (TESTDB)

SQL>shutdown immediate;

Startup the new standby database (TESTDB) in mount stage

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  601272320 bytes

Fixed Size                  2230712 bytes

Variable Size             335545928 bytes

Database Buffers          251658240 bytes

Redo Buffers               11837440 bytes

Database mounted

Start the managed recovery process on the the new standby database (TESTDB)

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE

---------------- ------------ ----------------

TESTDB           MOUNTED      PHYSICAL STANDBY

 Post-Switchover tasks

Reset the job_queue_processes parameter to its previous value

Set the job queue processes to its original value on the new standby (TESTDB). 

SQL> sho parameter job_queue_processes

 NAME                                 TYPE        VALUE

----------------------------------- ----------- ------------------------------

job_queue_processes                  integer     1000

 

In the above fig. the job_queue_processes parameter is set to 1000. Set this parameter to the value 0.
SQL>alter system set job_queue_processes=1000 scope=spfile;

System altered

Now the roles of the databases have been changed. The primary database (TESTDB) has been changed to standby database and the standby database (STANDBY) has been changed to primary database.

The archive logs that get generated in the new primary database (STANDBY) get shipped automatically to the new standby database (TESTDB) and they are applied on it automatically.

Maximum archivelog generated at the new primary database (STANDBY)

SQL> select thread#,max(sequence#) from v$archived_log where applied=YES’ group by thread#;

  THREAD# MAX(SEQUENCE#)

---------- --------------

         1            163

Maximum archivelog that has been shipped and applied to the new standby database (TESTDB)

SQL> select thread#,max(sequence#) from v$archived_log where applied=YES’ group by thread#;

    THREAD# MAX(SEQUENCE#)

---------- --------------

         1            163

 

The roles can again be reversed by following the same above procedures.

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

 

How to setup active dataguard in oracle 11g

The main advantage of setting up dataguard in 11g is that the database can be opened in Read-Only mode allowing the Users to access the physical standby database for fetching reports and on the same time the physical standby database can be in recovery mode. In other words, the physical standby database would be in recovery mode and hand in hand the standby database can be used for reporting purposes.

The read-only physical standby database can be used to offload query from the primary database. Users can use select statements and complex queries against this this database and thereby decreasing the load on the primary database. While the standby is open read-only, the following operations are disallowed

• Any Data Manipulation Language (DML) except for select statements
• Any Data Definition Language (DDL)
• Access of local sequences
• DMLs on local temporary tables

Steps on how to setup the active dataguard:

Once you setup the physical standby database as described in

CREATING PHYSICAL STANDBY DATABASE

 Follow the below steps to setup the active dataguard.

Step 1: Check the status of the Primary database and the latest sequence generated in the primary database.

SQL> select status,instance_name,database_role from v$instance,v$database;

STATUS       INSTANCE_NAME    DATABASE_ROLE

------------ ---------------- ----------------

OPEN         prim             PRIMARY

SQL> select max (sequence#) from v$archived_log;

MAX (SEQUENCE#)

--------------

40

Step 2: Check the status of the physical standby database and the latest sequence applied on the physcial standby database.

SQL> select status,instance_name,database_role from v$database,v$instance;

 STATUS   INSTANCE_NAME DATABASE_ROLE

-------- ------------- ---------------------

MOUNTED stand          PHYSICAL STANDBY

 SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX (SEQUENCE#)

--------------

40

Step 3: Check if the Managed Recovery Process (MRP) is active on the physical standby database.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

--------- ------------ ----------

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

RFS       IDLE          41

RFS       IDLE          0

RFS       IDLE          0

RFS       IDLE          0

MRP0      WAIT_FOR_LOG 41

 9 rows selected.

Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.

Step 4: Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE    OPEN_MODE

------ -------------- ---------------- ---------------

OPEN   stnd           PHYSICAL STANDBY READ ONLY

Step 6: Now start the MRP on the physical standby database.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

 SQL> select process,status,sequence# from v$managed_standby;

 PROCESS   STATUS        SEQUENCE#

--------- ------------ ----------

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

RFS       IDLE          41

RFS       IDLE          0

RFS       IDLE          0

RFS       IDLE          0

MRP0      WAIT_FOR_LOG 41

9 rows selected.

Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.

 I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!