Sunday, March 6, 2022

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.



No comments:

Post a Comment