Physical Standby Database SWITCHOVER & FAILOVER Operations

SWITCHOVER:

Article I had written previously was the  creation standby database with RMAN active database duplication. In this article, I will write about  how to switchover from the previous article scenario.

In short, switchover is the planned role change. So, primary database will operate as standby and standby will operate as primary database. Spare our database interoperability testing, to do maintenance work in production (primary) database , etc. can make for things like switchover. Switchover process  can be done in Enterprise Manager, Data Guard Broker and also manually via sqlplus. In this article I will use sqlplus on the manual switch method. I will  remind you the servername  [server]  before each processing step .I want to make more of a reminder. If we run commands on linux, it will start with “$”,  on sqlplus is “SQL>” and, while on rman “RMAN>” as a start.

1. [istanbul] Switch log file on primary database.

SQL>alter system switch logfile;

2. [istanbul] Check switchover status before switching database.

SQL>select switchover_status from v$database;

You must see “TO_STANDBY” as result.

3. [istanbul] Switch primary database to standby database.

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

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

4. [istanbul] Defer for archive log apply. Because I didn’t set  my standby database as primary yet.

SQL>alter system set log_archive_dest_state_2=defer;

5. [Baku] Switch standby database to primary. Check switchover status before switching database.

SQL>select switchover_status from v$database;

You must see “TO_PRIMARY” as result. Now let’s swtich

SQL>alter database commit to switchover to primary;

SQL>shutdown immediate;

SQL>startup;

Our switchover process is successfully completed .
6. [Istanbul] Start real-time recovery process..

SQL>recover managed standby database using current logfile disconnect;

Finally let’s open our database with “Read Only with Apply”.

SQL>recover managed standby database cancel;

SQL>alter database open;

SQL>recover managed standby database using current logfile disconnect;

FAILOVER:

In short, the failover is the deformation of the production (primary) database and activating standby database as the primary. It is not reversible. When enabled, re-create the standby database. What to do in case of failover:

(Important note: Istanbul is the primary server and Baku is the standby server)

1. [Istanbul] If the primary database is accessible and running, then it must provided  to send redo buffer to the standby database.

SQL> alter system flush redo to standby_db_name;

SQL>alter system archive log current;

If you don’t receive an error, you can continue with step 5th. In this case, the system can be opened by zero data loss. If you receive an error, We continue with step 2 to open the system at least data loss.

2. [Baku] We must run the following query to learn last applied archive log sequence number.

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

3. [Istanbul’dan Baku’ye] If you can access archive logs which are not copied to standby then copy archives to standby. After copy archive log files we must register them to standby database. This operation must be done for every thread.

SQL> alter database register physical logfile '/oracle/ora11g/dbs/arch/ TALIP_991834413_1_102.arc ';

4. [Baku] Check the standby database for redo gap. If there is a gap then we must copy archive log files and register.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

SQL> alter database register physical logfile '/oracle/ora11g/dbs/arch/ TALIP_991834413_1_101.arc ';

As a result of the above query until it returns to zero.

5. [Baku] Stop the redo apply process in standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

6. [Baku] Finish to apply archive logs copied from primary.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

If you get an error, it means there are redo logs not applied. Consider 2th and 4th steps. You can also continue with following command;

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

In this situation you can open database in 8th step.  If you get no error, continuw with 7th step.

7. [Baku] Switch standby database to primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

8. [Baku] Open database.

SQL> ALTER DATABASE OPEN;

After opening standby database as primary with failover you must take full backup.

Failover ile Standby veritabanı primary olarak açıldığında mutlaka full yedek alınmalıdır. And as soon as possible  you must configure a new standby database. Please note that our data are very valuable for us 🙂

Talip Hakan Öztürk

Advertisement