Oracle Standby Database - Switchover and Failover & Reinstating Failed Primary Database

Oracle Standby Database - Switchover and Failover


DBA might need to perform Switchover and Failover for maintenance or DR activity .

Oracle Datagurad Best Practice for Switchover:


1)Gracefully stop application processing against the database or disconnect all sessions possible using the ALTER SYSTEM KILL SESSION SQL*Plus command.
2)Stop job processing by setting the AQ_TM_PROCESSES parameter to 0.
3)Cancel any specified apply delay by setting the Broker property DelayMins=0
  To accomplish the same where broker is not configured issue the following commands on the standby database in SQL*Plus.
  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE NODELAY DISCONNECT;
  The USING CURRENT LOGFILE clause is no longer required in 12.1 and above. By default SRLs are used where they exist.
  You can view the current delay setting on the primary database by referencing the broker property DelayMins from the broker ‘show database <db_unique_name> verbose’ output or by querying the DELAY_MINS column of
  the V$ARCHIVE_DEST view. 
4)Configure the standby database to use real-time apply and, if possible, ensure the databases are synchronized before the switchover operation to optimize switchover processing.
  Using real-time apply ensures that the redo data is applied to the standby database as soon as it is received,and the standby database is synchronized with the primary database before the switchover operation to
  minimize switchover time.
  The Data Guard Broker always uses real-time apply when the Broker property DelayMins is set to 0 so you can skip this step if you are using the Broker.
  To enable real-time apply use the following SQL*Plus statement:
  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;
  The USING CURRENT LOGFILE clause is no longer required in 12.1 and above. Real-time apply is the default where SRLs exist in 12c provided there is no apply delay explicitly specified.
5)If not using the Data Guard broker or Enterprise Manager you will need to manually shutdown the non-apply standby instances prior to Data Guard switchover. The broker and Enterprise Manager handle this
  automatically for you.
6)For the target physical standby database, reduce the number of archiver (ARCn) processes to the minimum needed for both remote and local archiving.
  ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=((#of defined LOG_ARCHIVE_DEST_n )+(#threads/instances)) SCOPE=MEMORY SID=’*’; 

Switchover Method:


1) Manual Method
2) Dataguard broker

1) Manual Method 1:

Steps on Production :

Perform log Switchover in Primary database :
SQL> alter system archive log current;

SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
TO STANDBY PRIMARY

Convert Primary Database to Standby database :
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> shutdown immediate;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> startup nomount
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 171968712 bytes
Database Buffers 352321536 bytes
Redo Buffers 7942144 bytes
SQL>

Mount old primary database    :

Mount the Old Primary Database in this step as Standby
SQL> alter database mount standby database;
Database altered.
SQL> alter system set log_archive_dest_state_2=defer;
System altered.

Steps On Dataguard : 

Perform Switchover of standby database to primary .

SQL> select switchover_status,database_role from v$database;
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.

SQL> shutdown immediate;
SQL> startup
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 163580104 bytes
Database Buffers 360710144 bytes
Redo Buffers 7942144 bytes
Database mounted.
Database opened.

Start MRP process on old Primary 

SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL>

Manual Method 2:

Switchover is a two way process in Oracle Dataguard. This way of role transition where the current primary database starts behaving as a Physical Standby and on the other hand the current Physical standby database starts behaving as a Primary database. Switchover in 12c is still the same, but new features have been incorporated and the usage of the “switchover commands” have in a great change.
Oracle has introduced the “Verify” functionality which does not perform the switchover but does verify if the switchover can be performed to the target standby database.

The new switchover statement has a VERIFY option that results in checks being performed of many conditions required for switchover. Some of the items checked are: whether Redo Apply is running on the switchover target; whether the release version of the switchover target is 12.1 or later; whether the switchover target is synchronized; and whether it has MRP running.

The syntax used is “Alter database switchover to <target_standby_database_unique_name> verify;” where in the “target_standby_database_unique_name” is the unique name of the target database to which the switchover needs to be
performed.

Step1) Suppose the primary database has a DB_UNIQUE_NAME of ntulearn and the switchover target standby database has a DB_UNIQUE_NAME of ntulearndr. On the primary database ntulearn, issue the following SQL statement to verify that the switchover
target, ntulearndr, is ready for switchover: (While running below command to keep the alert log for both the databases open)
SQL> ALTER DATABASE SWITCHOVER TO ntulearndr VERIFY;
If any error after above command resolve the error and reissue command. If this operation is successful, a "Database Altered" message will be returned.
Check the role of database using below command on both PRIMARY and STANDBY database:
SQL> select status,instance_name,database_role from v$database,v$instance;
Shut down all instances except one on both primary and Standby Side.
NOTE: If you are switching over to a physical standby database using Dataguard broker method, the broker shuts down all but one instance on the current primary database. No instances will be shut down on the target
physical standby database.

Step 2) Initiate the switchover on the primary database, ntulearn, by issuing the following SQL statement:(Keep alert log open for both primary and standby database)

SQL> ALTER DATABASE SWITCHOVER TO ntulearndr;
If this statement completes without any errors, proceed to Step 3.
If an error occurs, mount the old primary database (ntulearn) and the old standby database (ntulearndr). On both databases, query DATABASE_ROLE from V$DATABASE. There are three possible combinations of database roles for
ntulearn and ntulearndr. The following table describes these combinations and provides the likely cause and a high level remedial action for each situation. 

2.a)Value of DATABASE_ROLE column in V$DATABASE Cause and Remedial Action
ntulearn database is primary, ntulearndr database is standby
Cause: The ntulearn database failed to convert to a standby database role.
Action: See the alert log for details on the error that prevented ntulearn from switching to a standby role, take the necessary actions to fix the error, reopen one of the nodes of ntulearn if necessary, and repeat the switchover process from Step 1.

2.b)ntulearn database is standby, ntulearndr database is standby
Cause: The ntulearndr database failed to convert to a primary database role.
Action: Issue the following SQL statement to convert either ntulearn or ntulearndr to a primary database:
SQL> ALTER DATABASE SWITCHOVER TO target_db_name FORCE;
For example:
On the ntulearndr database, issue the following SQL statement to convert it to a primary database:
ALTER DATABASE SWITCHOVER TO ntulearndr FORCE;
On the ntulearn database, issue the following SQL statement to convert it to a primary database:
ALTER DATABASE SWITCHOVER TO ntulearn FORCE;
If the SQL statement fails with an ORA-16473 error, then you must start Redo Apply before reissuing the command.

Restart Redo Apply as follows:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Reissue the switchover command as follows:
SQL> ALTER DATABASE SWTICHOVER TO ntulearn FORCE;
Database altered.
2.c)ntulearn database is standby, ntulearndr database is primary
Cause: The ntulearn and ntulearndr databases have successfully switched to their new roles, but there was an error communicating the final success status back to ntulearn.
Action: Continue to Step 3 to finish the switchover operation.

Step 3)Issue the following SQL statement on the new primary database, ntulearndr, to open it.
SQL>select status,instance_name,database_role from v$database,v$instance;
SQL> alter database open;

Step 4)Issue the following SQL statement to mount the new physical standby database, ntulearn:
SQL>select status,instance_name,database_role from v$database,v$instance;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Check Sync Status:
 On the Primary database:
 select max(sequence#) from v$archived_log;
 On the Physcial Standby Database:
 select max(sequence#) from v$archived_log where applied='YES';
 
Switch Few log group to check the log shipping.
On New primary :
alter system switch logfile;
alter system switch logfile;
Start the Stopped instances both side on Primary and Standby Side.

2) DATAGUARD BROKER Method:


1. Shutdown all the instance on Primary except one.
2. Shutdown all the instance on Standby except one.
3. Connect to dgmgrl on primary.
4. Execute switchover to 'standby' on dgmgrl.
5. After the successful Switchover operation , start the remaining instances on both the databases.

1. SHUTDOWN ALL THE INSTANCE ON PRIMARY EXCEPT ONE.

$ srvctl status database -d primary
Instance primary1 is running on node oraclerac01
Instance primary2 is running on node oraclerac02
oracle on oraclerac01 :
$ srvctl stop instance -d primary -i primary2
oracle on oraclerac01 :
$ srvctl status database -d primary
Instance primary1 is running on node oraclerac01
Instance primary2 is not running on node oraclerac02

2. SHUTDOWN ALL THE INSTANCE ON STANDBY EXCEPT ONE.

oraware(oraware) on oracleracstd02 : </usr/users/oraware>
$ srvctl status database -d standby
Instance standby1 is running on node oracleracstd01
Instance standby2 is running on node oracleracstd02
oraware(oraware) on oracleracstd02 : </usr/users/oraware>
$ srvctl stop instance -d standby -i standby1
oraware(oraware) on oracleracstd02 : </usr/users/oraware>
$ srvctl status database -d standby
Instance standby1 is not running on node oracleracstd01
Instance standby2 is running on node oracleracstd02

3. CONNECT TO DGMGRL ON PRIMARY:

oracle on oraclerac01 :
$ . oraenv
ORACLE_SID = [primary1] ?
The Oracle base remains unchanged with value /fs01/app/oracle
oracle on oraclerac01 :
$ dgmgrl
DGMGRL for Solaris: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@primary
Password:
Connected.
DGMGRL> show configuration;
Configuration - DG_Config
  Protection Mode: MaxAvailability
  Databases:
    primary - Primary database
    standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

4. EXECUTE SWITCHOVER TO 'STANDBY' ON DGMGRL:

DGMGRL> switchover to 'standby';
Performing switchover NOW, please wait...
New primary database "standby" is opening...
Operation requires shutdown of instance "primary1" on database "primary"
Shutting down instance "primary1"...
ORACLE instance shut down.
Operation requires startup of instance "primary1" on database "primary"
Starting instance "primary1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "standby"
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - DG_Config
  Protection Mode: MaxAvailability
  Databases:
    standby - Primary database
    primary - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>

5. START THE REMAINING INSTANCES ON BOTH THE DATABASES. 

oracle on oraclerac01 :
$ srvctl status database -d primary
Instance primary1 is running on node oraclerac01
Instance primary2 is running on node oraclerac02
SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIMARY   MOUNTED              PHYSICAL STANDBY
SQL> select INSTANCE_NAME, HOST_NAME, STATUS from v$instance;
INSTANCE_NAME    HOST_NAME            STATUS
---------------- -------------------- ------------
primary1         oraclerac01     MOUNTED
SQL> select INSTANCE_NAME, HOST_NAME, STATUS from v$instance;
INSTANCE_NAME    HOST_NAME            STATUS
---------------- -------------------- ------------
primary2         oraclerac02     MOUNTED

oraware(oraware) on oracleracstd02 : </usr/users/oraware>
$ srvctl status database -d standby
Instance standby1 is running on node oracleracstd01
Instance standby2 is running on node oracleracstd02
SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIMARY   READ WRITE           PRIMARY
SQL> select INSTANCE_NAME, HOST_NAME, STATUS from v$instance;
INSTANCE_NAME    HOST_NAME            STATUS
---------------- -------------------- ------------
standby2         oracleracstd02        OPEN

SQL> select INSTANCE_NAME, HOST_NAME, STATUS from v$instance;
INSTANCE_NAME    HOST_NAME            STATUS
---------------- -------------------- ------------
standby1         oracleracstd01        OPEN
Check the archive logs status in standby database using the following command.
set lin 500
col NAME format a90
select NAME, THREAD#, SEQUENCE#, ARCHIVED, APPLIED from v$archived_log;

FAILOVER STEPS:   


Flashback must be on for primary & standby database before to do failover steps for doing reinstate.
SQL>  select database_name, FLASHBACK_ON from v$database;
DATABASE_NAME        FLASHBACK_ON
-------------------- ------------------
PRIMARY              YES
oracle on oraclestd01 : </usr/users/oraware>
$ . oraenv
ORACLE_SID = [oraware] ? standby1
The Oracle base has been set to /u01/app/oracle
oracle on oraclestd01 : </usr/users/oraware>
$ dgmgrl
DGMGRL for Solaris: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@standby
Password:
Connected.
DGMGRL> show configuration;
Configuration - DG_Config
  Protection Mode: MaxAvailability
  Databases:
    primary - Primary database
    standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
1. Terminate primary database:
@ First Primary Instance
oracle on oraclepri01 : </usr/users/oracle>
$ . oraenv
ORACLE_SID = [primary1] ?
The Oracle base remains unchanged with value /fs01/app/oracle
oracle on oraclepri01 : </usr/users/oracle>
$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 4 16:52:18 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
@ Second Primary Instance
oracle on oraclepri02 : </usr/users/oracle>
$ . oraenv
ORACLE_SID = [primary2] ?
The Oracle base remains unchanged with value /fs01/app/oracle
oracle on oraclepri02 : </usr/users/oracle>
$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 4 16:52:45 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> shutdown abort;
ORACLE instance shut down.
SQL>

2. LOGIN INTO STANDBY DGMGRL AND CHECK THE CONFIGURATION:

oracle on oraclestd01 : </usr/users/oraware>
$ . oraenv
ORACLE_SID = [standby1] ?
The Oracle base remains unchanged with value /u01/app/oracle
oracle on oraclestd01 : </usr/users/oraware>
$ dgmgrl
DGMGRL for Solaris: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@standby
Password:
Connected.
DGMGRL>
DGMGRL> show configuration;
Configuration - DG_Config
  Protection Mode: MaxAvailability
  Databases:
    primary - Primary database
    standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-16625: cannot reach database "primary"
DGM-17017: unable to determine configuration status
DGMGRL>
3. Failover to Standby:
DGMGRL> failover to 'standby';
Performing failover NOW, please wait...
Failover succeeded, new primary is "standby"
DGMGRL>


oracle on oraclestd01 : </usr/users/oraware>
$ . oraenv
ORACLE_SID = [standby1] ?
The Oracle base remains unchanged with value /u01/app/oracle
oracle on oraclestd01 : </usr/users/oraware>
$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 4 16:50:06 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIMARY   READ WRITE           PRIMARY


Reinstating a Failed Primary Database. 


To reinstate my failed primary database as a new physical standby database, the original primary database has to be shut down and then brought back to MOUNT state. Otherwise, the initial attempt at reinstatement will fail.

1. STARTUP MOUNT THE FAILED PRIMARY DATABASE
oracle on oraclepri01 : </usr/users/oracle>
$ . oraenv
ORACLE_SID = [primary1] ?
The Oracle base remains unchanged with value /fs01/app/oracle
oracle on oraclepri01 : </usr/users/oracle>
$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 4 17:07:54 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  730931200 bytes
Fixed Size                  2162904 bytes
Variable Size             599793448 bytes
Database Buffers          121634816 bytes
Redo Buffers                7340032 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
oracle on oraclepri01 : </usr/users/oracle>

2. Login into current primary database and execute reinstate database command:

oraware on oraclestd01 : </usr/users/oraware>
$ . oraenv
ORACLE_SID = [standby1] ?
The Oracle base remains unchanged with value /u01/app/oracle
oraware on oraclestd01 : </usr/users/oraware>
$ dgmgrl
DGMGRL for Solaris: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@standby
Password:
Connected.
DGMGRL> reinstate database primary;
Reinstating database "primary", please wait...
Operation requires shutdown of instance "primary1" on database "primary"
Shutting down instance "primary1"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "primary1" on database "primary"
Starting instance "primary1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "primary" ...
Reinstatement of database "primary" succeeded
DGMGRL>
DGMGRL> show configuration;
Configuration - DG_Config
  Protection Mode: MaxAvailability
  Databases:
    standby - Primary database
    primary - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>

==========================================================

Please check our other blogs for  Dataguard ,Standby  & DR.

Comments

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

Oracle Block Corruption - Detection and Resolution

Install & Configure MySQL Router - MySQL InnoDB Cluster