Administration of a Oracle Standby Database
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.
Table of Contents
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 verboseDGMGRL> 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_linDGMGRL> 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 lagDGMGRL> 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.boxAfter 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