Manual Switchover Activity in Oracle Database 12c

Manual Switchover In Oracle Database (12c)

Switchover Steps

Primary Database:

tnsping QPROD

tnsping QPRODR

echo $ORACLE_SID

sqlplus / as sysdba

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

SQL> select switchover_status from v$database;

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

SQL> shutdown immediate;

SQL> startup mount;

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

SQL> recover managed standby database disconnect from session;

SQL> !ps -ef | grep mrp

SQL> !lsnrctl status

Standby Database:

tnsping QPROD

tnsping QPRODR

echo $ORACLE_SID

sqlplus / as sysdba

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

SQL> alter database commit to switchover to primary;

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

SQL> alter database open;

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

SQL> !lsnrctl status

Switchback Steps:

Follow same steps in reverse order.

Actual Steps:

PRIMARY:

Check the database status:

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

NAME      STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD      OPEN         PRIMARY          READ WRITE

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

   THREAD# MAX(SEQUENCE#)

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

         1          23326

SQL>

Check the sync status:

SQL> @sync_p.sql

   THREAD# MAX(SEQUENCE#)

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

         1          23307

Check the switchover status:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

Issue the command:

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

Database altered.

SQL> shut immediate;

ORA-01012: not logged on

SQL> exit

Make sure the background processes of primary is down.

ps -ef|grep pmon

Startup the database mount state:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3.2212E+10 bytes

Fixed Size                  5292336 bytes

Variable Size            4294975184 bytes

Database Buffers         2.7850E+10 bytes

Redo Buffers               61808640 bytes

SQL> alter database mount;

Database altered.

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

NAME      INSTANCE_NAME    STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD        QPROD        MOUNTED      PHYSICAL STANDBY MOUNTED

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL> !ps -ef|grep mrp

oraprod  5060  4931   0 22:22:09 pts/1       0:00 /usr/bin/bash -c ps -ef|grep mrp

oraprod  5062  5060   0 22:22:09 pts/1       0:00 grep mrp

oraprod  5025     1   0 22:21:54 ?           0:03 ora_mrp0_QPROD

Standby:

Check the database status:

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

NAME      INSTANCE_NAME    STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD     QPRODR           MOUNTED      PHYSICAL STANDBY MOUNTED

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

   THREAD# MAX(SEQUENCE#)

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

         1          23326

Check the sync status:

SQL> @sync_s.sql

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                  23307                 23307          0

Issue the command:

SQL> alter database commit to switchover to primary;

Database altered.

Open the database:

SQL> alter database open;

Database altered.

Check the database status:

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

NAME      INSTANCE_NAME    STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD     QPRODR           OPEN         PRIMARY          READ WRITE

SQL> alter system switch logfile;

System altered.