How to Test Data Guard Fast-Start Failover by Shutting Down Standby Server
This post will demonstrate the procedure for testing Oracle Data Guard Fast-Start Failover by shutting down the server where the standby database is running from. The environment is a single instance database without any grid infrastructure components.
Review primary host and start observer:
[oracle@ol7-121-dg1 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 20:42:54 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):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-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. Connected as SYSDG. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 13 seconds ago) DGMGRL> enable fast_start failover Enabled. 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 12 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> show database cdb1 Database - cdb1 Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): cdb1 Database Error(s): ORA-16820: fast-start failover observer is no longer observing this database Database Status: ERROR DGMGRL> show database cdb1_stby Database - cdb1_stby 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 Real Time Query: ON Instance(s): cdb1 Database Error(s): ORA-16820: fast-start failover observer is no longer observing this database Database Status: ERROR DGMGRL> start observer [P001 01/17 20:46:01.38] Authentication failed. DGM-16979: Unable to log on to the primary or standby database as SYSDBA Failed. DGMGRL> connect sys@cdb1 Password: Connected as SYSDBA. DGMGRL> start observer Observer started
Restart standby host, listener, and database:
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 Bringing machine 'default' up with 'virtualbox' provider... ==================================================================== 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) $ vagrant ssh Last login: Fri Jan 17 20:11:35 2020 from 10.0.2.2 [vagrant@ol7-121-dg2 ~]$ sudo su - oracle Last login: Fri Jan 17 20:11:44 UTC 2020 on pts/0 [oracle@ol7-121-dg2 ~]$ . oraenv <<< cdb1 ORACLE_SID = [cdb1] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@ol7-121-dg2 ~]$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-JAN-2020 20:53:20 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 17-JAN-2020 20:53:22 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 ~]$ cd /sf_working/sql [oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 20:53:38 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> @stby.sql Session altered. *** v$database *** DB OPEN DATABASE REMOTE SWITCHOVER DATAGUARD PRIMARY_DB UNIQUE_NAME MODE ROLE ARCHIVE STATUS BROKER UNIQUE_NAME ----------- ---------------------- ------------------ ---------- --------------- ---------- --------------- cdb1_stby MOUNTED PHYSICAL STANDBY ENABLED NOT ALLOWED ENABLED cdb1 *** gv$archive_dest *** MOUNT THREAD# DEST_ID DESTINATION STATUS TARGET SCHEDULE PROCESS ID -------- -------- ------------------------- ------------ ---------------- -------- ---------- ----- 1 1 USE_DB_RECOVERY_FILE_DEST VALID LOCAL ACTIVE ARCH 0 1 32 USE_DB_RECOVERY_FILE_DEST VALID LOCAL ACTIVE RFS 0 *** gv$archive_dest_status *** DATABASE RECOVERY INST_ID DEST_ID STATUS MODE MODE GAP_STATUS ERROR -------- -------- ------------ --------------- ----------------------- --------------- -------------------------------------------------- 1 1 VALID MOUNTED-STANDBY IDLE NONE 1 32 VALID UNKNOWN IDLE NONE *** v$thread *** THREAD# CURRENT LOG SEQUENCE STATUS -------- -------------------- ------------ 1 26 OPEN *** gv$archived_log *** DEST_ID THREAD# APPLIED MAX_SEQ MAX_TIME DELTA_SEQ DETA_MIN -------- -------- --------- -------- -------------------- --------- -------- 1 1 NO 25 17-JAN-2020 20:53:53 2 41.68333 1 1 YES 23 17-JAN-2020 20:12:12 *** v$archive_gap *** no rows selected *** GAP can also be verified using RMAN from STANDBY *** RMAN1 ------------------------------------------------------------ list archivelog from sequence 24 thread 1; *** v$dataguard_stats *** NAME VALUE UNIT ------------------------- ------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval apply lag day(2) to second(0) interval *** gv$managed_standby *** no rows selected 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]$
Screen output from observer:
DGMGRL> start observer
Observer started
[W000 01/17 20:48:58.27] The primary database has requested a transition to the UNSYNC/LAGGING state.
[W000 01/17 20:48:58.28] Permission granted to the primary database to transition to UNSYNC/LAGGING state.
[W000 01/17 20:50:01.29] The primary database has been in UNSYNC/LAGGING state for 63 seconds.
[W000 01/17 20:51:04.31] The primary database has been in UNSYNC/LAGGING state for 126 seconds.
[W000 01/17 20:52:07.33] The primary database has been in UNSYNC/LAGGING state for 189 seconds.
[W000 01/17 20:53:10.36] The primary database has been in UNSYNC/LAGGING state for 252 seconds.
[W000 01/17 20:54:13.39] The primary database has been in UNSYNC/LAGGING state for 315 seconds.
[W000 01/17 20:54:16.39] The primary database returned to SYNC/NOT LAGGING state.
Validate 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 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 10 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> exit [oracle@ol7-121-dg2 sql]$
Open database read only:
This is required because the database is not registered to a cluster resource.It's a good idea and best practice to shut down a standby host to validate that the environment can restart without any issues.[oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 21:33:07 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):PHYSICAL STANDBY> alter database open read only; Database altered. OL7-121-DG2:(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-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 database cdb1_stby Database - cdb1_stby 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: 1.00 KByte/s Real Time Query: ON Instance(s): cdb1 Database Status: SUCCESS DGMGRL> exit [oracle@ol7-121-dg2 sql]$
