Manual Switchover without Dataguard in Oracle

Manual Switchover without Dataguard in Oracle

Tips and technical articles of Oracle DBA

Switchover can be done without dataguard. Using switchover operation we can convert physical standby database to primary and primary database to physical standby database in Oracle. Switchover operation can perform without dataguard and with dataguard. Here we can see how to perform switchover operation without dataguard configuration.

In following example we assume that our primary database and physical standby database are working properly. Standby database is up with managed recovery mode and there is no archive log gap found in physical standby database. In short we can say that our standby database is synchronize with primary database. Find out step by step switchover operation. This switchover calls as manual switchover.

Primary Database:

First we should need to check our job queue processes and aq processes parameters. Check the value of both parameter and save in your log book. These value should need to restore while standby switchover finished. After get value of both parameter disable those parameter with value 0. Execute log switch forcefully for all recent changes also reflect to standby database. After finishing both tasks, execute switchover command in primary database. Once successfully execution of switchover command bring database down. Detail commands have been given below with exact steps by steps.

SQL> show parameter aq_tm_processes

SQL> show parameter job_queue_processes

SQL> alter system set aq_tm_processes=0;

SQL> alter system set job_queue_processes=0;

SQL>alter system switch logfile;

SQL>select switchover_status from v$database;

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

SQL>shutdown immediate;

Physical Standby Database:

First check archive sequence applied in standby database. Because Physical Standby database needs to synchronize with primary database. Disable log archive destination which contains service of Primary database. Execute switchover command and wait for finishing. After successfully completion of switchover command, shutdown database.

SQL>startup nomount

SQL>alter database mount standby database;

SQL>alter system set log_archive_dest_state_2=defer scope=both;

SQL>select switchover_status from v$database;

SQL>alter database commit to switchover to primary;

SQL>shutdown immediate

Now switchover operation is almost finished. We should need to start both Primary (switched from physical standby) database and Physical standby (switched from primary) database carefully.

Now start NEW PRIMARY database (it was previous physical standby database).

SQL>startup

SQL> select switchover_status from v$database;

Now start NEW PHYSICAL STANDBY database (It was previous primary database).

SQL>startup nomount

SQL>alter database mount standby database;

SQL> select switchover_status from v$database;

SQL>recover managed standby database disconnect from session;

In NEW Primary Database:

Enable archive log destination which contains service of standby database. Add tempfile in new primary database because before it was physical standby. Restore all processes parameters which we got in first step. Switch log forcefully to monitor archive shipping to physical standby database.

SQL>alter system set log_archive_dest_state_2=enable scope=both;

SQL> alter tablespace TEMP add tempfile '/u01/orcl/oradata/temp/temp01.dbf' size 1000M;

SQL> alter system set job_queue_Processes=10;

SQL> alter system set aq_tm_processes=5;

SQL>alter system switch logfile;

Check connectivity from new physical standby to new primary database. Also monitor archive log shipping from primary database to physical standby database. Post check for switchover_status of v$database from both new databases. Manual switchover without dataguard is finished.

Dbametrix is expert remote dba service provider team. Dbametrix has solid understanding to make SLA as per specification and requirement of client and end users. Dbametrix believes to provide remote services of database administration using SLA. Due to this reason Dbametrix offers SLA based cost effective remote dba plans. Client of Dbametrix can able to put trust on company because Dbametrix delivers cost effective remote dba plan using Service Level Agreement SLA and response time matrix.