ORACLE DATA GUARD SWITCHOVER FROM DC(PRIMARY) TO DR(STANDBY)
Switchover and Failover.
This section is what Data Guard is really all about, its about a standby database taking over the production database, and how to revert back when problems have been fixed.
Role transition is divide into two types switchover and failover, they also might be called switchback, failback but they all mean the same thing to transition the state of Data Guard from one state to another.
Switchover
Switchover is the act of change the standby database into the primary but in a controlled manor, the planned event means that it is safe from data loss because the primary database must complete all redo generation on the production data before allowing the switchover to commence. The switchback does not exists as it is a switchover but in the reserve order, which would restore the database back on its original server. This planned event normally happens during a quiet period, the reason for the switchover might be DR testing, patch, hardware changes, implementing RAC, etc.
Once the switchover is complete the redo from the new primary will send it to the remaining standby servers, including the old primary, if using either grid control or the broker this should be all automatically do for you, but if you are using SQLPlus you have to performance some manual work.
You always start the switchover on the primary database, the actual switchover command is below whether you are using Grid Control, Broker or SQLPlus.
SQL> alter database commit to switchover to standby;
When the switchover command is executed the redo generation is stopped, all DML related cursors are invalidated and users are either prevented from executing transactions or terminated and he current redo log is archived for each tread. A special switchover marker called the EOR (end of redo) is then placed in the header of the next sequence for each thread, and the online redo files are archived a second time, sending the final sequences to the standby databases. At this point the physical standby database is closed and the final log switch is done without allowing the primary database to advance the sequence numbers for each thread.
After the EOR redo is sent to the standby databases, the original primary database is finalized as a standby and its control file backed up to the trace file and converted to the correct type of standby control file. In the case of a physical standby switchover the managed recovery process (MRP) is automatically started on the original primary to apply the final archive logs that contain the EOR so that all the redo ever generated is processed. The primary is then dismounted and must then be restarted as a standby database in at least the mount state.
The standby database must received this EOR redo otherwise the switchover cannot occur, once this redo has been received and applied to complete the switchover you run the following command, this will be automatic if you are using the Grid Control or the Broker
SQL> alter database commit to switchover to primary;
The physical standby switchover will wait for the MRP process to exit after processing the EOR redo and then convert the standby control file into a normal production control file. The final thing to do is to open the database for general production use
SQL> alter database open;
A logical standby also has to wait for the EOR redo from the primary to be applied and SQL apply to shut down before the switchover command can complete, once the EOR has been processed, the GUARD can be turned off and production processing can begin.
Failover
A failover is a unplanned event when something has happened to hardware, networking, etc. This is when you invoke you DR procedures (hopefully documented), and you will have full confidence in getting the new primary up and running as quickly as possible. Unlike the switchover which begins on the primary, no primary is involved which means you will not be able to get the redo from the primary. Depending on what protection mode you have chosen there may be data loss (less you have a Maximum Protection mode enabled), you start be telling Data Guard to apply the remaining redo that it can. Once the redo has been applied you run the same command that you do with a physical standby to switchover the standby to a primary
SQL> alter database commit to switchover to primary;
Once difference is when the switchover has completed the protection mode will be maximum performance regardless what it was before, to get it back to your original protection mode you must get a standby database back up and running, then manually execute the steps to get it into the protection mode you want.
# Choose what level of protection you require
SQL> alter database set standby to maximize performance;
SQL> alter database set standby to maximize availability;
SQL> alter database set standby to maximize protection;
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.
ON ((DC-PRIMARY)
1. Check the Parameter
SQL>show parameter fal
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string prmyrrb1
fal_server string stdyrrb1
SQL>show parameter dest_2
NAME TYPE VALUE
--------------------------------------- ----------- ------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string SERVICE=stdyrrb1 OPTIONAL vali
d_for=(ALL_LOGFILES,ALL_ROLES)
db_unique_name=stdyrrb1 reopen=120
SQL>show parameter state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
SQL> select count(*) from v$session where type <>'BACKGROUND';
COUNT(*)
----------
140
SQL>select max(SEQUENCE#) "Last Generated" from v$log_history;
Last Generated
--------------
16837
SQL> select name,switchover_status, open_mode, database_role from v$database;
NAME SWITCHOVER_STATUS OPEN_MODE DATABASE_ROLE
-------------- ------------------------------- ------------------ ----------------------
CNBKRRB1 SESSIONS ACTIVE READ WRITE PRIMARY
2. Apply some switch logfile before start actives.
SQL>alter system switch logfile;
alter database
SQL>alter system switch logfile;
alter database
SQL>alter system switch logfile;
alter database
SQL>alter system switch logfile;
alter database
New Archivelog file will generated on DC(PRIMARY) side
3. Verify the same is replicated in standby database as below. i,e ON DR(STANDBY)
SQL>select process, sequence#, status, blocks, block# from v$managed_standby ;
PROCESS SEQUENCE# STATUS BLOCKS BLOCK#
------------- ----------------- ----------------- ------------ ----------
ARCH 0 CONNECTED 0 0
ARCH 0 CONNECTED 0 0
MRP0 16838 WAIT_FOR_LOG 0 0
RFS 0 IDLE 0 0
Once its applied to DR standby db we have to fire switch over command from DC primary
4. If the status is showing active session do the following.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
5. The above command execution will take time; monitor the alert log file for that database. Shutdown the new Primary (DR) and mount as standby database;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
6. Defer the remote destination on the new primary (DR).
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH;
7. Enble MRP process at New Standby side.
SQL> alter database recover managed standby database disconnect from session;
8. Verify switch over status in the database;
SQL> select name,switchover_status, open_mode, database_role from v$database;
SWITCHOVER_STATUS OPEN_MODE DATABASE_ROLE
-------------------- ---------- ----------------
TO PRIMARY MOUNTED PHYSICAL STANDBY
ON ((DR-STANDBY)
Check the Parameter
SQL>show parameter fal
SQL>show parameter dest_2
SQL>show parameter state_2
SQL>select max(SEQUENCE#) "Last Generated" from v$log_history;
SQL> select name,switchover_status, open_mode, database_role from v$database;
SWITCHOVER_STATUS OPEN_MODE DATABASE_ROLE
-------------------- ---------- ----------------
TO PRIMARY MOUNTED PHYSICAL STANDBY
If the status is showing as session active then do the following.
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
The above commands execution will take time, monitor the alert log file for that database. If a media recovery is required do the following.
SQL> RECOVER STANDBY DATABASE;
Shutdown and Startup the Primary (DC). Enable remote archiving on primary.
SQL> SHUTDOWN IMMEDIATE;
SQL>STARTUP
On the primary database do the following.
SQL> ALTER SYSTEM SWITCH LOGFILE;
Verify the same is replicated in new standby database as below.
SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
SQL> select name,switchover_status, open_mode, database_role from v$database;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
After opening standby database as primary with failover you must take full backup using RMAN.
Switchover and Failover.
This section is what Data Guard is really all about, its about a standby database taking over the production database, and how to revert back when problems have been fixed.
Role transition is divide into two types switchover and failover, they also might be called switchback, failback but they all mean the same thing to transition the state of Data Guard from one state to another.
Switchover
Switchover is the act of change the standby database into the primary but in a controlled manor, the planned event means that it is safe from data loss because the primary database must complete all redo generation on the production data before allowing the switchover to commence. The switchback does not exists as it is a switchover but in the reserve order, which would restore the database back on its original server. This planned event normally happens during a quiet period, the reason for the switchover might be DR testing, patch, hardware changes, implementing RAC, etc.
Once the switchover is complete the redo from the new primary will send it to the remaining standby servers, including the old primary, if using either grid control or the broker this should be all automatically do for you, but if you are using SQLPlus you have to performance some manual work.
You always start the switchover on the primary database, the actual switchover command is below whether you are using Grid Control, Broker or SQLPlus.
SQL> alter database commit to switchover to standby;
When the switchover command is executed the redo generation is stopped, all DML related cursors are invalidated and users are either prevented from executing transactions or terminated and he current redo log is archived for each tread. A special switchover marker called the EOR (end of redo) is then placed in the header of the next sequence for each thread, and the online redo files are archived a second time, sending the final sequences to the standby databases. At this point the physical standby database is closed and the final log switch is done without allowing the primary database to advance the sequence numbers for each thread.
After the EOR redo is sent to the standby databases, the original primary database is finalized as a standby and its control file backed up to the trace file and converted to the correct type of standby control file. In the case of a physical standby switchover the managed recovery process (MRP) is automatically started on the original primary to apply the final archive logs that contain the EOR so that all the redo ever generated is processed. The primary is then dismounted and must then be restarted as a standby database in at least the mount state.
The standby database must received this EOR redo otherwise the switchover cannot occur, once this redo has been received and applied to complete the switchover you run the following command, this will be automatic if you are using the Grid Control or the Broker
SQL> alter database commit to switchover to primary;
The physical standby switchover will wait for the MRP process to exit after processing the EOR redo and then convert the standby control file into a normal production control file. The final thing to do is to open the database for general production use
SQL> alter database open;
A logical standby also has to wait for the EOR redo from the primary to be applied and SQL apply to shut down before the switchover command can complete, once the EOR has been processed, the GUARD can be turned off and production processing can begin.
Failover
A failover is a unplanned event when something has happened to hardware, networking, etc. This is when you invoke you DR procedures (hopefully documented), and you will have full confidence in getting the new primary up and running as quickly as possible. Unlike the switchover which begins on the primary, no primary is involved which means you will not be able to get the redo from the primary. Depending on what protection mode you have chosen there may be data loss (less you have a Maximum Protection mode enabled), you start be telling Data Guard to apply the remaining redo that it can. Once the redo has been applied you run the same command that you do with a physical standby to switchover the standby to a primary
SQL> alter database commit to switchover to primary;
Once difference is when the switchover has completed the protection mode will be maximum performance regardless what it was before, to get it back to your original protection mode you must get a standby database back up and running, then manually execute the steps to get it into the protection mode you want.
# Choose what level of protection you require
SQL> alter database set standby to maximize performance;
SQL> alter database set standby to maximize availability;
SQL> alter database set standby to maximize protection;
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.
ON ((DC-PRIMARY)
1. Check the Parameter
SQL>show parameter fal
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string prmyrrb1
fal_server string stdyrrb1
SQL>show parameter dest_2
NAME TYPE VALUE
--------------------------------------- ----------- ------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string SERVICE=stdyrrb1 OPTIONAL vali
d_for=(ALL_LOGFILES,ALL_ROLES)
db_unique_name=stdyrrb1 reopen=120
SQL>show parameter state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
SQL> select count(*) from v$session where type <>'BACKGROUND';
COUNT(*)
----------
140
SQL>select max(SEQUENCE#) "Last Generated" from v$log_history;
Last Generated
--------------
16837
SQL> select name,switchover_status, open_mode, database_role from v$database;
NAME SWITCHOVER_STATUS OPEN_MODE DATABASE_ROLE
-------------- ------------------------------- ------------------ ----------------------
CNBKRRB1 SESSIONS ACTIVE READ WRITE PRIMARY
2. Apply some switch logfile before start actives.
SQL>alter system switch logfile;
alter database
SQL>alter system switch logfile;
alter database
SQL>alter system switch logfile;
alter database
SQL>alter system switch logfile;
alter database
New Archivelog file will generated on DC(PRIMARY) side
3. Verify the same is replicated in standby database as below. i,e ON DR(STANDBY)
SQL>select process, sequence#, status, blocks, block# from v$managed_standby ;
PROCESS SEQUENCE# STATUS BLOCKS BLOCK#
------------- ----------------- ----------------- ------------ ----------
ARCH 0 CONNECTED 0 0
ARCH 0 CONNECTED 0 0
MRP0 16838 WAIT_FOR_LOG 0 0
RFS 0 IDLE 0 0
Once its applied to DR standby db we have to fire switch over command from DC primary
4. If the status is showing active session do the following.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
5. The above command execution will take time; monitor the alert log file for that database. Shutdown the new Primary (DR) and mount as standby database;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
6. Defer the remote destination on the new primary (DR).
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH;
7. Enble MRP process at New Standby side.
SQL> alter database recover managed standby database disconnect from session;
8. Verify switch over status in the database;
SQL> select name,switchover_status, open_mode, database_role from v$database;
SWITCHOVER_STATUS OPEN_MODE DATABASE_ROLE
-------------------- ---------- ----------------
TO PRIMARY MOUNTED PHYSICAL STANDBY
ON ((DR-STANDBY)
Check the Parameter
SQL>show parameter fal
SQL>show parameter dest_2
SQL>show parameter state_2
SQL>select max(SEQUENCE#) "Last Generated" from v$log_history;
SQL> select name,switchover_status, open_mode, database_role from v$database;
SWITCHOVER_STATUS OPEN_MODE DATABASE_ROLE
-------------------- ---------- ----------------
TO PRIMARY MOUNTED PHYSICAL STANDBY
If the status is showing as session active then do the following.
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
The above commands execution will take time, monitor the alert log file for that database. If a media recovery is required do the following.
SQL> RECOVER STANDBY DATABASE;
Shutdown and Startup the Primary (DC). Enable remote archiving on primary.
SQL> SHUTDOWN IMMEDIATE;
SQL>STARTUP
On the primary database do the following.
SQL> ALTER SYSTEM SWITCH LOGFILE;
Verify the same is replicated in new standby database as below.
SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
SQL> select name,switchover_status, open_mode, database_role from v$database;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
After opening standby database as primary with failover you must take full backup using RMAN.
