How to Test Data Guard Fast-Start Failover by Shutting Down Primary Server
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:
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.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>
