switchover to standby Archives

Performing Switchover with No Broker (Manual)

Overview:

A switchover is a role reversal between the primary database and one of its standby databases. A switchover operation 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. The transition occurs without having to recreate either database.

0. Enviroment
1. Pre-Switchover tasks
2. Steps to perform switchover with Physical Standby databases
3. Additional Information

0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1

1. Pre-Switchover tasks

– Verify that there is network connectivity between the primary and standby locations.
– Always recommened test the switchover in your testing system befre working on production.
– Verify primary database READ WRITE and standby instance mounted.
– Verify there are no active users connected to database.
– Make sure last redo data transmitted from primary to standby and applied.
– Set job_queue_processes=0 and aq_tm_processes = 0, dbwr_io_slaves = 0 on both Primary and DR
alter system set job_queue_processes=0 ;
alter system set aq_tm_processes = 0 ;
alter system set dbwr_io_slaves = 0 ;

On Primary

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005pr

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

MAX(SEQUENCE#)
--------------
            20

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

no rows selected

SQL> select * from dba_jobs_running;

no rows selected

SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000
SQL>

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

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

System altered.

SQL>

On Standby

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

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

MAX(SEQUENCE#)
--------------
            20

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

no rows selected

SQL>

2. Steps to perform switchover with Physical Standby databases

On PRIMARY

Note: Always perform the switchover of the primary database to standby database first and then 
switchover the standby database to primary. 

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005pr

SQL>

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL>

Note: If status “TO STANDBY” or “SESSIONS ACTIVE” on the primary database, then perform the switchover
using the below query

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

Database altered.

SQL>
SQL> shut immediate;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 10 01:15:12 2016

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             822083824 bytes
Database Buffers          436207616 bytes
Redo Buffers                8847360 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open;

Database altered.

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

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005pr

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

MAX(SEQUENCE#)
--------------
            22

On STANDBY

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
W5005PR   MOUNTED

SQL> alter database open;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005prg

SQL> SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

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

MAX(SEQUENCE#)
--------------
            22

SQL>

3. Additional Information

The SWITCHOVER_STATUS column of v$database can have the following values:

NOT ALLOWED - Either this is a standby database and the primary database 
              has not been switched first, or this is a prstimary 
              database and there are no standby databases.

SESSIONS ACTIVE - Indicates that there are active SQL sessions attached
                  to the primary or standby database that need to be 
                  disconnected before the switchover operation is 
                  permitted.

SWITCHOVER PENDING - This is a standby database and the primary database 
                     switchover request has been received but not 
                     processed.

SWITCHOVER LATENT - The switchover was in pending mode, but did not 
                    complete and went back to the primary database.

TO PRIMARY - This is a standby database, with no active sessions, that is 
             allowed to switch over to a primary database. 

TO STANDBY - This is a primary database, with no active sessions, that is 
             allowed to switch over to a standby database.

RECOVERY NEEDED - This is a standby database that has not received the 
                  switchover request.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Bright DBA

ORACLE DBA – Tips and Techniques

Tag Archives: switchover to standby

Switchover with No Broker

Performing Switchover with No Broker (Manual)