Manual Switchover without Dataguard in Oracle

by / 2010-2011 / Published in Oracle DBA Tips

standby database

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.

Step By Step Activity for 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;

Step By Step Activity for Physical Standby Database:

Oracle DBA Interview Questions

Now need to 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>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;

Activity needs to perform at 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.

In remote dba services, these type of Oracle errors are very critical to manage if you haven't access of some commands or not having more knowledge of operating system commands. Expert remote dba team of Dbametrix offers ultimate remote dba work with high availability and lowest cost remote plans.

Dbametrix is world wide leader in remote dba support. Expert remote DBA team of Dbametrix is offering high quality professional Oracle DBA support with strong response time to fulfill your SLA. Contact our sales department for more information.