This post covers some basic tasks regarding Oracle Standby Databases that can pop up from time to time. The tasks are based on a already existing primary/standby database configuration with Oracle Data Guard Broker. The steps used to setup such a configuration are described here.
Check the health of the Data Guard configuration
Display the overall status
show configurations displays the status of the Data Guard configuration. It should return a Configuration Status: SUCCESS. Be aware that the command does not run any checks. It only outputs the result of the last check which by default could be 60 seconds ‘old’.
show configuration
[oracle@lin ~]$ dgmgrl sys/oracle
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Dec 26 12:58:57 2024
Version 19.25.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "orcl_lin"
Connected as SYSDBA.
DGMGRL> show configuration
Configuration - dgconf
Protection Mode: MaxPerformance
Members:
orcl_lin - Primary database
orcl_lin2 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 45 seconds ago)
DGMGRL>
If you would like to get a ‘fresh’ status of the configuration (by actually running the checks) do this instead:
show configuration verbose
DGMGRL> show configuration verbose
Configuration - dgconf
Protection Mode: MaxPerformance
Members:
orcl_lin - Primary database
orcl_lin2 - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverLagGraceTime = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'orcl_CFG'
FastStartFailoverLagType = 'APPLY'
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
DGMGRL>
Display the database status
show database orcl_lin
DGMGRL> show database orcl_lin
Database - orcl_lin
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL>
Display the transport / apply lag
show configuration lag
DGMGRL> show configuration lag
Configuration - dgconf
Protection Mode: MaxPerformance
Members:
orcl_lin - Primary database
orcl_lin2 - Physical standby database
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 15 seconds ago)
DGMGRL>
Perform a database switchover
A switchover transitions the roles of the primary database and standby database. This means the primary will become a standby and the standby database will become the primary database. To perform a switchover both databases need to be able to communicate with each other.
dgmgrl sys/oracle << EOF
set time on
switchover to orcl_lin2
host sleep 30
show configuration verbose
EOF
[oracle@lin ~]$ dgmgrl sys/oracle
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Dec 26 13:33:47 2024
Version 19.25.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "orcl_lin"
Connected as SYSDBA.
DGMGRL> set time on
13:33:51 DGMGRL> show configuration
Configuration - dgconf
Protection Mode: MaxPerformance
Members:
orcl_lin - Primary database
orcl_lin2 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 22 seconds ago)
13:33:52 DGMGRL> switchover to orcl_lin2
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl_lin2"
Connecting ...
Connected to "orcl_lin2"
Connected as SYSDBA.
New primary database "orcl_lin2" is opening...
Oracle Clusterware is restarting database "orcl_lin" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "orcl_lin"
Switchover succeeded, new primary is "orcl_lin2"
13:35:20 DGMGRL> show configuration verbose
Configuration - dgconf
Protection Mode: MaxPerformance
Members:
orcl_lin2 - Primary database
orcl_lin - 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'
ConfigurationWideServiceName = 'orcl_CFG'
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
13:35:52 DGMGRL>
Perform a database failover
A failover can be done when a primary database fails or has become unreachable. Make sure to connect to the current standby database:
dgmgrl sys/oracle << EOF
set time on
failover to orcl_lin2
show configuration verbose
EOF
[oracle@lin2 ~]$ dgmgrl sys/oracle
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Dec 26 13:49:43 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "orcl_lin2"
Connected as SYSDBA.
DGMGRL> set time on
13:49:47 DGMGRL> failover to orcl_lin2
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl_lin2"
13:51:03 DGMGRL> show configuration verbose
Configuration - dgconf
Protection Mode: MaxPerformance
Members:
orcl_lin2 - Primary database
orcl_lin - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
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'
ConfigurationWideServiceName = 'orcl_CFG'
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
13:51:14 DGMGRL>
As we can see after the failover the old primary database is disabled and needs to be reinstated to be part of the Data Guard configuration again.
Reinstate a database after a failover
To reinstate a database after a failover event follow these steps.
on the failed database run:
sqlplus / as sysdba << EOF
shutdown immediate
startup mount
EOF
on the current primary database run:
dgmgrl sys/oracle << EOF
set time on
show configuration verbose
reinstate database orcl_lin
host sleep 20
show configuration verbose
EOF
Configure the DB client to connect to the current primary node
This is a simple example for a client tnsnames.ora that clients can use to connect to the current primary node of the Data Guard setup. First we create a new pdb:
sqlplus -S / as sysdba << EOF
create pluggable database pdb1 admin user pdb1admin identified by oracle;
alter pluggable database pdb1 open;
alter pluggable database pdb1 save state;
alter session set container=pdb1;
grant select on v_\$instance to pdb1admin;
EOF
Now we have a service that will be active on the current primary node:
lsnrctl services
...
Service "pdb1.fritz.box" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4164 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[oracle@lin ~]$
...
Next we add the alias pdb1 to the tnsnames.ora of a client (can be on the server machine or on a separate client machine):
echo "
pdb1=
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=lin.fritz.box)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=lin2.fritz.box)(PORT=1521)
))
(CONNECT_DATA=(SERVICE_NAME=pdb1.fritz.box)))
" >> /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
Using this alias we connect to the first node where the service pdb1.fritz.box is available:
echo "select host_name from v\$instance;"|sqlplus -S pdb1admin/oracle@pdb1
HOST_NAME
----------------------------------------------------------------
lin.fritz.box
After performing a switchover or failover we can see that we are connecting to the new primary node now. In case of a failover make sure that the previous primary db is really unavailable (e.g. shutdown).
echo "select host_name from v\$instance;"|sqlplus -S pdb1admin/oracle@pdb1
HOST_NAME
----------------------------------------------------------------
lin2.fritz.box
