Step by Step how to Manually Switchover to Physical Standby

<<Back to Oracle DataGuard Main Page

How to Successfully Perform Manual Switchover to Physical Standby Database

Hosts and Databases Used in this Example
DB_ROLE	PRIMARY	STANDBY
HOST_NAME	test1	test2
DB_UNIQUE_NAME	TST1TP	TST1TS
DB_NAME	TST1T	TST1T

Step1> Prerequisite for switchover to be successful

• Verify and Ensure Primary and standby must be in sync

From Primary: Check the latest generated Log sequence
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1           4568
From Standby: Check the latest received and applied archivelog.
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 
Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------
         1                   4568                  4568          0

• Verify that there is no error in v$archive_dest_status.error in Primary and standby database

SQL>  select error from v$archive_dest_status where error is not null;
no rows selected

• From Primary: Check the Switchover Status

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
Step2> Trigger the Switchover On Primary
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
Step3> Trigger the Switchover On Standby
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary;
Database altered.
Step4> Start New primary Database
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1912605984 bytes
Database Buffers          218103808 bytes
Redo Buffers                7979008 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TST1T     READ WRITE           PRIMARY
Step5> Start New Standby  Database in Mont Mode
$ sqlplus "/as sysdba"
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1912605984 bytes
Database Buffers          218103808 bytes
Redo Buffers                7979008 bytes
Database mounted.
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TST1T     MOUNTED              PHYSICAL STANDBY
Step5> Configure Log Shipping from New Primary To New standby
SQL> alter system set log_archive_dest_2='service=TST1TP';
System altered.
Step6> Start Media Recovery on New Standby Database
SQL> recover managed standby database disconnect from session;
Media recovery complete.
Step7> Post Switchover task

• Optionally Defer the log_archive_dest_state_2 Parameter in Current Standby

SQL> alter system set log_archive_dest_state_2=defer;
System altered.

• Perform The Log Switch in Current Primary Database

SQL> alter system switch logfile;
System altered.

• Add Tempfile  in Current Primary Database

SQL> alter tablespace TEMP add tempfile '+DG_TEST_DATA' size 1000M;