Thinking Out Loud

January 15, 2020

Data Guard Fast-Start Failover Test – Shutdown Primary Host

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 10:37 pm

Note: Primary Database: cdb1_stby is because failover was previously performed.

This also demonstrate 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 good practice for real world and only 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>

2 Comments »

  1. […] Data Guard Fast-Start Failover Test […]

    Pingback by Disable fast_start failover | Thinking Out Loud — January 16, 2020 @ 12:54 am | Reply

  2. […] Data Guard Fast-Start Failover Test – Shutdown Primary Host […]

    Pingback by Data Guard Fast-Start Failover Test – Shutdown Standby Host | Thinking Out Loud — January 17, 2020 @ 9:41 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

%d bloggers like this: