How to Test Data Guard Fast-Start Failover by Shutting Down Primary Server

by Michael Dinh on Jan 20, 2020 12:00:00 AM

This post will demonstrate the procedure to test Oracle Data Guard Fast-Start Failover by shutting down the server where the primary database is running from. The environment is a single instance database without any grid Infrastructure components. The same process should work for RAC environment as my colleague has used the same process to test for RAC running on ODA. Note: Primary Database: cdb1_stby is because the failover was previously performed. This also demonstrates why it may not be a good idea to suffix stby for standby database.

Review Data Guard using sqlplus:

OL7-121-DG2:(SYS@cdb1):PRIMARY> show parameter db%name
 
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_file_name_convert string
 db_name string cdb1
 db_unique_name string CDB1_STBY
 pdb_file_name_convert string
 OL7-121-DG2:(SYS@cdb1):PRIMARY> show parameter fal
 
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 fal_client string
 fal_server string cdb1
 OL7-121-DG2:(SYS@cdb1):PRIMARY>
 
 ********************************************************************************
 
 OL7-121-DG1:(SYS@cdb1):PHYSICAL STANDBY> show parameter db%name
 
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_file_name_convert string
 db_name string cdb1
 db_unique_name string cdb1
 pdb_file_name_convert string
 OL7-121-DG1:(SYS@cdb1):PHYSICAL STANDBY> show parameter fal
 
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 fal_client string
 fal_server string cdb1_stby
 OL7-121-DG1:(SYS@cdb1):PHYSICAL STANDBY>

Review Data Guard configuration:

DGMGRL> show configuration verbose
 Configuration - my_dg_config
 
 Protection Mode: MaxPerformance
 Members:
 cdb1_stby - Primary database
 cdb1 - Physical standby database
 
 Properties:
 FastStartFailoverThreshold = '30'
 OperationTimeout = '30'
 TraceLevel = 'USER'
 FastStartFailoverLagLimit = '30'
 CommunicationTimeout = '180'
 ObserverReconnect = '0'
 FastStartFailoverAutoReinstate = 'TRUE'
 FastStartFailoverPmyShutdown = 'TRUE'
 BystandersFollowRoleChange = 'ALL'
 ObserverOverride = 'FALSE'
 ExternalDestination1 = ''
 ExternalDestination2 = ''
 PrimaryLostWriteAction = 'CONTINUE'
 
 Fast-Start Failover: DISABLED
 
 Configuration Status:
 SUCCESS
 
 ********************************************************************************
 
 DGMGRL> show database verbose cdb1_stby
 
 Database - cdb1_stby
 
 Role: PRIMARY
 Intended State: TRANSPORT-ON
 Instance(s):
 cdb1
 
 Properties:
 DGConnectIdentifier = 'cdb1_stby'
 ObserverConnectIdentifier = ''
 LogXptMode = 'ASYNC'
 RedoRoutes = ''
 DelayMins = '0'
 Binding = 'optional'
 MaxFailure = '0'
 MaxConnections = '1'
 ReopenSecs = '300'
 NetTimeout = '30'
 RedoCompression = 'DISABLE'
 LogShipping = 'ON'
 PreferredApplyInstance = ''
 ApplyInstanceTimeout = '0'
 ApplyLagThreshold = '0'
 TransportLagThreshold = '0'
 TransportDisconnectedThreshold = '30'
 ApplyParallel = 'AUTO'
 StandbyFileManagement = 'AUTO'
 ArchiveLagTarget = '0'
 LogArchiveMaxProcesses = '4'
 LogArchiveMinSucceedDest = '1'
 DbFileNameConvert = ''
 LogFileNameConvert = ''
 FastStartFailoverTarget = 'cdb1'
 InconsistentProperties = '(monitor)'
 InconsistentLogXptProps = '(monitor)'
 SendQEntries = '(monitor)'
 LogXptStatus = '(monitor)'
 RecvQEntries = '(monitor)'
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb1_stby_DGMGRL)(INSTANCE_NAME=cdb1)(SERVER=DEDICATED)))'
 StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
 AlternateLocation = ''
 LogArchiveTrace = '0'
 LogArchiveFormat = '%t_%s_%r.dbf'
 TopWaitEvents = '(monitor)'
 
 Database Status:
 SUCCESS
 
 ********************************************************************************
 
 DGMGRL> show database verbose cdb1
 
 Database - cdb1
 
 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 0 seconds (computed 0 seconds ago)
 Average Apply Rate: 2.00 KByte/s
 Active Apply Rate: 0 Byte/s
 Maximum Apply Rate: 0 Byte/s
 Real Time Query: ON
 Instance(s):
 cdb1
 
 Properties:
 DGConnectIdentifier = 'cdb1'
 ObserverConnectIdentifier = ''
 LogXptMode = 'ASYNC'
 RedoRoutes = ''
 DelayMins = '0'
 Binding = 'optional'
 MaxFailure = '0'
 MaxConnections = '1'
 ReopenSecs = '300'
 NetTimeout = '30'
 RedoCompression = 'DISABLE'
 LogShipping = 'ON'
 PreferredApplyInstance = ''
 ApplyInstanceTimeout = '0'
 ApplyLagThreshold = '0'
 TransportLagThreshold = '0'
 TransportDisconnectedThreshold = '30'
 ApplyParallel = 'AUTO'
 StandbyFileManagement = 'AUTO'
 ArchiveLagTarget = '0'
 LogArchiveMaxProcesses = '4'
 LogArchiveMinSucceedDest = '1'
 DbFileNameConvert = ''
 LogFileNameConvert = ''
 FastStartFailoverTarget = 'cdb1_stby'
 InconsistentProperties = '(monitor)'
 InconsistentLogXptProps = '(monitor)'
 SendQEntries = '(monitor)'
 LogXptStatus = '(monitor)'
 RecvQEntries = '(monitor)'
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb1_DGMGRL)(INSTANCE_NAME=cdb1)(SERVER=DEDICATED)))'
 StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
 AlternateLocation = ''
 LogArchiveTrace = '0'
 LogArchiveFormat = '%t_%s_%r.dbf'
 TopWaitEvents = '(monitor)'
 
 Database Status:
 SUCCESS
 
 DGMGRL>

Validate Data Guard configuration:

DGMGRL> validate database verbose cdb1_stby
 
 Database Role: Primary database
 
 Ready for Switchover: Yes
 
 Capacity Information:
 Database Instances Threads
 cdb1_stby 1 1
 
 Temporary Tablespace File Information:
 cdb1_stby TEMP Files: 1
 
 Flashback Database Status:
 cdb1_stby: On
 
 Data file Online Move in Progress:
 cdb1_stby: No
 
 Transport-Related Information:
 Transport On: Yes
 
 Log Files Cleared:
 cdb1_stby Standby Redo Log Files: Cleared
 
 Automatic Diagnostic Repository Errors:
 Error cdb1_stby
 No logging operation NO
 Control file corruptions NO
 System data file missing NO
 System data file corrupted NO
 System data file offline NO
 User data file missing NO
 User data file corrupted NO
 User data file offline NO
 Block Corruptions found NO
 
 ********************************************************************************
 
 DGMGRL> validate database verbose cdb1
 
 Database Role: Physical standby database
 Primary Database: cdb1_stby
 
 Ready for Switchover: Yes
 Ready for Failover: Yes (Primary Running)
 
 Capacity Information:
 Database Instances Threads
 cdb1_stby 1 1
 cdb1 1 1
 
 Temporary Tablespace File Information:
 cdb1_stby TEMP Files: 3
 cdb1 TEMP Files: 3
 
 Flashback Database Status:
 cdb1_stby: On
 cdb1: On
 
 Data file Online Move in Progress:
 cdb1_stby: No
 cdb1: No
 
 Standby Apply-Related Information:
 Apply State: Running
 Apply Lag: 0 seconds (computed 1 second ago)
 Apply Delay: 0 minutes
 
 Transport-Related Information:
 Transport On: Yes
 Gap Status: No Gap
 Transport Lag: 0 seconds (computed 1 second ago)
 Transport Status: Success
 
 Log Files Cleared:
 cdb1_stby Standby Redo Log Files: Cleared
 cdb1 Online Redo Log Files: Cleared
 cdb1 Standby Redo Log Files: Available
 
 Current Log File Groups Configuration:
 Thread # Online Redo Log Groups Standby Redo Log Groups Status
 (cdb1_stby) (cdb1)
 1 3 4 Sufficient SRLs
 
 Future Log File Groups Configuration:
 Thread # Online Redo Log Groups Standby Redo Log Groups Status
 (cdb1) (cdb1_stby)
 1 3 4 Sufficient SRLs
 
 Current Configuration Log File Sizes:
 Thread # Smallest Online Redo Smallest Standby Redo
 Log File Size Log File Size
 (cdb1_stby) (cdb1)
 1 50 MBytes 50 MBytes
 
 Future Configuration Log File Sizes:
 Thread # Smallest Online Redo Smallest Standby Redo
 Log File Size Log File Size
 (cdb1) (cdb1_stby)
 1 50 MBytes 50 MBytes
 
 Apply-Related Property Settings:
 Property cdb1_stby Value cdb1 Value
 DelayMins 0 0
 ApplyParallel AUTO AUTO
 
 Transport-Related Property Settings:
 Property cdb1_stby Value cdb1 Value
 LogXptMode ASYNC ASYNC
 RedoRoutes
 Dependency
 DelayMins 0 0
 Binding optional optional
 MaxFailure 0 0
 MaxConnections 1 1
 ReopenSecs 300 300
 NetTimeout 30 30
 RedoCompression DISABLE DISABLE
 LogShipping ON ON
 
 Automatic Diagnostic Repository Errors:
 Error cdb1_stby cdb1
 No logging operation NO NO
 Control file corruptions NO NO
 SRL Group Unavailable NO NO
 System data file missing NO NO
 System data file corrupted NO NO
 System data file offline NO NO
 User data file missing NO NO
 User data file corrupted NO NO
 User data file offline NO NO
 Block Corruptions found NO NO
 
 DGMGRL>

Validate Data Guard connectivity from all hosts:

[oracle@ol7-121-dg2 sql]$ dgmgrl
 DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
 
 Copyright (c) 2000, 2013, Oracle. All rights reserved.
 
 Welcome to DGMGRL, type "help" for information.
 DGMGRL> connect sys@cdb1
 Password:
 Connected as SYSDBA.
 DGMGRL> connect sys@cdb1_stby
 Password:
 Connected as SYSDBA.
 DGMGRL> exit
 [oracle@ol7-121-dg2 sql]$
 
 ********************************************************************************
 
 [oracle@ol7-121-dg1 sql]$ dgmgrl
 DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
 
 Copyright (c) 2000, 2013, Oracle. All rights reserved.
 
 Welcome to DGMGRL, type "help" for information.
 DGMGRL> connect sys@cdb1
 Password:
 Connected as SYSDBA.
 DGMGRL> connect sys@cdb1_stby
 Password:
 Connected as SYSDBA.
 DGMGRL> exit
 [oracle@ol7-121-dg1 sql]$

Start Data Guard observer from standby:

Note: This is not a good practice for real-world scenarios. It is for testing purposes only.

oracle@ol7-121-dg1 sql]$ sqlplus / as sysdba
 
 SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 15 21:21:28 2020
 
 Copyright (c) 1982, 2014, Oracle. All rights reserved.
 
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
 OL7-121-DG1:(SYS@cdb1):PHYSICAL STANDBY> exit
 Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
 [oracle@ol7-121-dg1 sql]$ dgmgrl
 DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
 
 Copyright (c) 2000, 2013, Oracle. All rights reserved.
 
 Welcome to DGMGRL, type "help" for information.
 DGMGRL> connect sys@cdb1_stby
 Password:
 Connected as SYSDBA.
 DGMGRL> show configuration
 
 Configuration - my_dg_config
 
 Protection Mode: MaxPerformance
 Members:
 cdb1_stby - Primary database
 Warning: ORA-16819: fast-start failover observer not started
 
 cdb1 - (*) Physical standby database
 Warning: ORA-16819: fast-start failover observer not started
 
 Fast-Start Failover: ENABLED
 
 Configuration Status:
 WARNING (status updated 16 seconds ago)
 
 DGMGRL> start observer
 Observer started
 DGMGRL>

Shutdown primary host:

[oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba
 
 SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 15 21:26:51 2020
 
 Copyright (c) 1982, 2014, Oracle. All rights reserved.
 
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
 OL7-121-DG2:(SYS@cdb1):PRIMARY> exit
 Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 [oracle@ol7-121-dg2 sql]$ logout
 
 [vagrant@ol7-121-dg2 ~]$ logout
 Connection to 127.0.0.1 closed.
 
 resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
 $ vagrant halt
 ==> default: Attempting graceful shutdown of VM...
 
 resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
 $

Failover succeeded:

DGMGRL> start observer
 Observer started
 
 21:27:46.27 Wednesday, January 15, 2020 Initiating Fast-Start Failover to database "cdb1"... Performing failover NOW, please wait... Failover succeeded, new primary is "cdb1" 21:27:51.99 Wednesday, January 15, 2020

Review Data Guard configuration:

[oracle@ol7-121-dg1 ~]$ dgmgrl /
 DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
 
 Copyright (c) 2000, 2013, Oracle. All rights reserved.
 
 Welcome to DGMGRL, type "help" for information.
 Connected as SYSDG.
 DGMGRL> show configuration
 
 Configuration - my_dg_config
 
 Protection Mode: MaxPerformance
 Members:
 cdb1 - Primary database
 Warning: ORA-16829: fast-start failover configuration is lagging
 
 cdb1_stby - (*) Physical standby database (disabled)
 ORA-16661: the standby database needs to be reinstated
 
 Fast-Start Failover: ENABLED
 
 Configuration Status:
 WARNING (status updated 41 seconds ago)
 
 DGMGRL>

Start primary host:

resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
 $ vagrant status
 Current machine states:
 
 default poweroff (virtualbox)
 
 The VM is powered off. To restart the VM, simply run `vagrant up`
 
 resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
 $ vagrant up
 
 resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) 
 $ vagrant status
 Current machine states:
 
 default running (virtualbox)
 
 The VM is running. To stop this VM, you can run `vagrant halt` to
 shut it down forcefully, or you can run `vagrant suspend` to simply
 suspend the virtual machine. In either case, to restart it again,
 simply run `vagrant up`.
 
 resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
 $

Start listener:

[oracle@ol7-121-dg2 ~]$ lsnrctl start
 
 LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-JAN-2020 21:33:11
 
 Copyright (c) 1991, 2014, Oracle. All rights reserved.
 
 Starting /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait...
 
 TNSLSNR for Linux: Version 12.1.0.2.0 - Production
 System parameter file is /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
 Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-121-dg2/listener/alert/log.xml
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg2.localdomain)(PORT=1521)))
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg2)(PORT=1521)))
 STATUS of the LISTENER
 ------------------------
 Alias LISTENER
 Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
 Start Date 15-JAN-2020 21:33:12
 Uptime 0 days 0 hr. 0 min. 0 sec
 Trace Level off
 Security ON: Local OS Authentication
 SNMP OFF
 Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
 Listener Log File /u01/app/oracle/diag/tnslsnr/ol7-121-dg2/listener/alert/log.xml
 Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg2.localdomain)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 Services Summary...
 Service "cdb1_stby_DGMGRL" has 1 instance(s).
 Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
 The command completed successfully
 [oracle@ol7-121-dg2 ~]$

Startup mount database:

[oracle@ol7-121-dg2 sql]$ ps -ef|grep pmon
 oracle 17762 17567 0 21:34 pts/0 00:00:00 grep --color=auto pmon
 
 [oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba
 
 SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 15 21:34:09 2020
 
 Copyright (c) 1982, 2014, Oracle. All rights reserved.
 
 Connected to an idle instance.
 
 SYS@cdb1> startup mount;
 ORACLE instance started.
 
 Total System Global Area 1610612736 bytes
 Fixed Size 2924928 bytes
 Variable Size 520097408 bytes
 Database Buffers 1073741824 bytes
 Redo Buffers 13848576 bytes
 Database mounted.
 SYS@cdb1> exit
 Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 [oracle@ol7-121-dg2 sql]$

Review Data Guard configuration:

[oracle@ol7-121-dg2 sql]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
 
 Copyright (c) 2000, 2013, Oracle. All rights reserved.
 
 Welcome to DGMGRL, type "help" for information.
 Connected as SYSDG.
 DGMGRL> show configuration
 ORA-16795: the standby database needs to be re-created
 
 Configuration details cannot be determined by DGMGRL
 DGMGRL>

Review Observer:

DGMGRL> start observer
 Observer started
 
 21:27:46.27 Wednesday, January 15, 2020
 Initiating Fast-Start Failover to database "cdb1"...
 Performing failover NOW, please wait...
 Failover succeeded, new primary is "cdb1"
 21:27:51.99 Wednesday, January 15, 2020
 
 21:34:56.44 Wednesday, January 15, 2020 Initiating reinstatement for database "cdb1_stby"... Reinstating database "cdb1_stby", please wait... Reinstatement of database "cdb1_stby" succeeded 21:35:15.40 Wednesday, January 15, 2020

Review and Validate Data Guard configuration:

DGMGRL> show configuration
 
 Configuration - my_dg_config
 
 Protection Mode: MaxPerformance
 Members:
 cdb1 - Primary database
 cdb1_stby - (*) Physical standby database
 Warning: ORA-16829: fast-start failover configuration is lagging
 
 Fast-Start Failover: ENABLED
 
 Configuration Status:
 WARNING (status updated 54 seconds ago)
 
 DGMGRL> show configuration
 
 Configuration - my_dg_config
 
 Protection Mode: MaxPerformance
 Members:
 cdb1 - Primary database
 cdb1_stby - (*) Physical standby database
 
 Fast-Start Failover: ENABLED
 
 Configuration Status:
 SUCCESS (status updated 24 seconds ago)
 
 DGMGRL> validate database cdb1
 
 Database Role: Primary database
 
 Ready for Switchover: Yes
 
 DGMGRL> validate database cdb1_stby
 
 Database Role: Physical standby database
 Primary Database: cdb1
 
 Ready for Switchover: Yes
 Ready for Failover: Yes (Primary Running)
 
 DGMGRL>

It's a good idea to validate Data Guard configuration even when failover testing is not required to be prepared for any sort of disaster. If there is a need to test Data Guard failover, then hopefully the demo provided above will be of use.

Topics: Oracle Monitoring Technical Track Oracle Enterprise Manager (OEM)

No Comments Yet

Let us know what you think

What We Do

How to Test Data Guard Fast-Start Failover by Shutting Down Primary Server

Review Data Guard using sqlplus:

Review Data Guard configuration:

Validate Data Guard configuration:

Validate Data Guard connectivity from all hosts:

Start Data Guard observer from standby:

Shutdown primary host:

Failover succeeded:

Review Data Guard configuration:

Start primary host:

Start listener:

Startup mount database:

Review Data Guard configuration:

Review Observer:

Review and Validate Data Guard configuration:

No Comments Yet

Subscribe by email

What We Do

Share this

How to Test Data Guard Fast-Start Failover by Shutting Down Primary Server

Review Data Guard using sqlplus:

Review Data Guard configuration:

Validate Data Guard configuration:

Validate Data Guard connectivity from all hosts:

Start Data Guard observer from standby:

Shutdown primary host:

Failover succeeded:

Review Data Guard configuration:

Start primary host:

Start listener:

Startup mount database:

Review Data Guard configuration:

Review Observer:

Review and Validate Data Guard configuration:

Share this

No Comments Yet

Subscribe by email