How To Manually Failover to Standby Database When Primary Database Got Issues

In normal situation, DBA can use Data Guard utility to switchover or failover database roles. DBA can also use SQL command to switchover or failver database roles.

This post demonstrate how to failover to standby database in 12c when Primary Databases is lost due to various reasons.

Check Database Role

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY

Stop Standby Database Recovery

SQL> alter database recover managed standby database cancel;
Database altered.

Complete Recovery and Transit Current Standby as Primary

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.

After this command, this standby database will NOT be able to go back to standby database any more.

Activate the Standby Database

SQL>alter database activate standby database;
Database altered.

Open the Database

SQL>alter database open;


SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
---------- -------------------- ----------------
MOUNTED    MAXIMUM PERFORMANCE   PRIMARY

The Last and Not the Least

  • Backup new primary database ASAP.
  • Reinstate the old primary as new standby database, if flashback on. Otherwise rebuild the standby database as per normal processes.

Role Transitions with Oracle Data Guard by using SQL

As a DBA, there are many situations required to switchover database role in Data Guard environment. For post 12c, the easy way is to execute one line command to switchover database:

SQL> alter database switchover to SBYTEST;

OR

DGMGRL> switchover to SBYTEST; 

This post demonstrates how to implement role transitions step by step by using SQL.

On Primary Database

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      READ WRITE           PRIMARY
SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
TO STANDBY
The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request
SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

Verify switchover is OK or not.

SQL>  alter database switchover to SBYTEST verify;
 alter database switchover to SBYTETS verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

Check alert log, and fix the problem until getting the following result.

SQL> alter database switchover to SBYTEST verify;


Database altered.

Primary role transition to standby, and check alert log for details.

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

Database altered.

After the previous step, the new standby database is down. so we have to manually startup and start recovery.

SQL> startup nomount;

SQL> alter database mount standby database;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      MOUNTED              PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered. 

Now we move onto standby database.

On Standby Database

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    MOUNTED              PHYSICAL STANDBY

SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

This is the initial switchover status before primary switchover to standby yet.

After primary switchover to standby already, then continue the following steps.

SQL>  select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL>  alter database commit to switchover to PRIMARY with session shutdown;

Database altered.


SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
SBYTEST  MOUNTED              PRIMARY


SQL> alter database open;

Database altered.

Reconfigure Data Guard Broker because databases role changed.

DGMGRL > show configuration;

DGMGRL > disable configuration;

DGMGRL > enable configuration;

Finally if it is RAC environment, modify database configurations like “Start options” and “Database role”, etc.

$srvctl config database -d TEST

$ srvctl modify  database -d TEST -role PHYSICAL_STANDBY -startoption "READ ONLY"

$ srvctl stop database -d TEST

$srvctl start database -d TEST
$srvctl config database -d SBYTEST

$srvctl modify  database -d SBYTEST -role PRIMARY -startoption OPEN 

$ srvctl stop database -d SBYTEST

$srvctl start database -d SBYTEST
DGMGRL> show configuration;

Configuration - TESTDB

  Protection Mode: MaxPerformance
  Members:
  SBYTEST - Primary database
     TEST  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

How To Relocate Data Guard Broker Configuration File Onto ASM

Just after the database migrated onto ASM, it is noticed that DG broker configuration files are still sitting on the default locations as following:

SQL>  show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u02/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/dr1TESTDB.dat
dg_broker_config_file2               string      /u02/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/dr2TESTDB.dat

Stop the broker

SQL> alter system set dg_broker_start = FALSE;

Set the dg_broker_config_file1 & 2 parameters to new location

SQL> alter system set dg_broker_config_file1 = '+DATAC1/TESTDB/dr1TESTDB.dat';
SQL> alter system set dg_broker_config_file2 = '+DATAC1/TESTDB/dr2TESTDB.dat'

Restart the broker

SQL> alter system set dg_broker_start = TRUE;

Recreate the Data Guard Configuration

DGMGRL>  CREATE CONFIGURATION 'TESTDB' AS PRIMARY DATABASE IS 'TESTDB' CONNECT IDENTIFIER IS 'TESTDB' ;
Configuration "TESTDB" created with primary database "TESTDB"

DGMGRL>ADD DATABASE 'TESTBYDB' AS CONNECT IDENTIFIER IS 'TESTBYDB' MAINTAINED AS PHYSICAL;
Database "TESTBYDB" added

DGMGRL> enable configuration;
Enabled.

Verify the results

ASMCMD> pwd
+DATAC1/TESTDB
ASMCMD>  ls -lt
Type             Redund  Striped  Time             Sys  Name
                                                   Y    TEMPFILE/
                                                   Y    PASSWORD/
                                                   Y    PARAMETERFILE/
                                                   Y    ONLINELOG/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    DATAFILE/
                                                   Y    CONTROLFILE/
                                                   Y    2347F41E951336CFE053200F030A8127/
                                                   Y    2346D55DBF2B64B2E053200F030A299D/
DATAGUARDCONFIG  HIGH    COARSE   OCT 04 17:00:00  N    dr2testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.290.1085073589
DATAGUARDCONFIG  HIGH    COARSE   OCT 04 17:00:00  N    dr1testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.561.1085073589

How to Configure Multiple Standby Databases in Data Guard

The following environment and naming are involved in this post:

Primary Database :
db_name : PRIMDB
db_unique_name : PRIMDB
Standby Database 1 :
db_name : PRIMDB
db_unique_name : STDBY1DB
Standby Database 2:
db_name : PRIMDB
db_unique_name : STDBY2DB
Standby Database 3 :
db_name : PRIMDB
db_unique_name : STDBY3DB

Create First Standby and Configure Data Guard

For how to create a standby database and configure DG, please refer to Oracle Doc, or our posts in this blog.
How to Setup Data Guard Physical Standby in Oracle Database 18c
Create Physical Standby Database from Active Database in 12c
Create Standby Database From Active Database Duplication in 11gR2
Create a 12c RAC Standby Database From Overnight RMAN Tape Backups
Create Standby Database From RMAN Backup

After first standby database “STDBY1DB” successfully created, the Data Guard is configure as the following:

DGMGRL> show configuration

Configuration - PRIMDB

  Protection Mode: MaxPerformance
  Members:
      PRIMDB - Primary database
    STDBY1DB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 6 seconds ago)

Create the Rest Standby Databases

Now it is time to create standby database STDBY2DB and STDBY3DB, the typical parameters for STDBY3DB will be the followings:

db_unique_name = 'STDBY3DB'
db_name='PRIMDB'
fal_server=PRIMDB,STDBY1DB,STDBY2DB
fal_client=STDBY3DB
db_file_name_convert  = 'PRIMDB','STDBY3DB'
log_file_name_convert = 'PRIMDB','STDBY3DB'
log_archive_dest_1 =LOCATION=USE_DB_RECOVERY_FILE_DEST 

here make sure fal_server include all primary and standby databases , except itself.

Set log_archive_config Parameter in All the Databases

Run the following sql command on all databases including primary and standby databases.

SQL>alter system set log_archive_config='dg_config=(PRIMDB,STDBY1DB,STDBY2DB,STDBY3DB)';

Enable DG Broker in Second and Third Standby

Run the following command in second and third standby databases:

SQL> ALTER SYSTEM SET DG_BROKER_START = TRUE;

Add the Second and Third Standby Database to DG

Run DGMGRL utility on primary database:

DGMGRL> add database 'STDBY2DB' as connect identifier is 'STDBY2DB' maintained as physical;
Database "STDBY2DB" added

DGMGRL> add database 'STDBY3DB' as connect identifier is 'STDBY3DB' maintained as physical;
Database "STDBY3DB" added
DGMGRL> enable database 'STDBY2DB' ;
Enabled.

DGMGRL> enable database 'STDBY3DB' ;
Enabled.

Verify DG Configuration

DGMGRL>  show configuration;

Configuration - PRIMDB 

  Protection Mode: MaxPerformance
  Members:
      PRIMDB - Primary database
    STDBY1DB - Physical standby database
    STDBY2DB - Physical standby database
    STDBY3DB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 4 seconds ago)