Category Data Guard

Data Guard, Linux, Oracle

Administration of a Oracle Standby Database

by jb

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

Data Guard, Linux

Setup a Oracle Physical Standby Database

by jb

The following steps can be used to setup a Oracle Physical Standby Database managed by Data Guard Broker. The software versions used are: Oracle 19.25 + ASM (RAC One Node) and RHEL 8.1. On the first node a database (orcl) is already running, on the second node only the database software is installed and an ASM instance with configured diskgroups (DATA and FRA) is running.

Prepare the primary node

# prepare and source environment files containing ORACLE_HOME and ORACLE_SID
# ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 ORACLE_SID=orcl
source ~/ora19.env
sqlplus / as sysdba << EOF
-- enable archivelog mode on primary
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
-- enable force logging
alter database force logging;
-- enable flashback database
alter database flashback on;
EOF

sqlplus -S / as sysdba << EOF
set head off
-- create the standby redo logfiles (same size as the normal redo logfiles, 1 group more than the number of the normal redo log groups)
spool /tmp/crsblf1.sql
select 'alter database add standby logfile thread '||thread#||' group '||(group#+10)||' size '||bytes||';' from v\$log;
spool off
@/tmp/crsblf1.sql
spool /tmp/crsblf2.sql
select 'alter database add standby logfile thread '||thread#||' group '||(group#+1)||' size '||bytes||';' from v\$standby_log where group#=(select max(group#) from v\$standby_log);
spool off
@/tmp/crsblf2.sql
-- enable automatic standby file management
alter system set standby_file_management=auto;
EOF

# set the db_unique_name of the primary db to a unique value (s.a. Oracle Doc 1604421.1)
# show current config
srvctl config database -d orcl
sqlplus -S / as sysdba << EOF
set head off lines 200
spool /tmp/s1.sql
select 'host echo "spfile='''||value||'''" >> $ORACLE_HOME/dbs/initorcl.ora' from v\$parameter where name='spfile';
select 'startup' from dual;
select 'alter system set db_unique_name='''||lower(name)||'_'||regexp_substr(host_name, '[^.]*')||''' scope=spfile;' from v\$instance, v\$database;
select 'alter user sys identified by oracle;' from dual;
select 'shutdown immediate' from dual;
spool off
EOF

srvctl stop database -d orcl
srvctl remove database -noprompt -d orcl
echo @/tmp/s1 | sqlplus / as sysdba
srvctl add database -db orcl_lin -dbname orcl -instance orcl -oraclehome $ORACLE_HOME -spfile `cat $ORACLE_HOME/dbs/initorcl.ora|awk -F= {'print $2'}|sed s/\'//g`
srvctl modify database -d orcl_lin -a "DATA,FRA"
srvctl start database -d orcl_lin

Setup the network configuration

# add the following lines to the primary and standby tnsnames.ora:
echo "
orcl_lin=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCP)(HOST=lin)(PORT=1521))
    )
    (CONNECT_DATA=(SID=orcl)
    )
  )

orcl_lin2=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCP)(HOST=lin2)(PORT=1521))
    )
    (CONNECT_DATA=(SID=orcl)
    )
  )
" >> /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora

# add the following entries to the standby side
echo "
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = lin2.fritz.box)(PORT = 1521))

" >> /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
echo "
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
" >> /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

# adjust the listener.ora on the primary side:
vi /u01/app/19.0.0/grid/network/admin/listener.ora

LISTENER=
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=lin)(PORT=1521))
      (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orcl_lin_DGMGRL.fritz.box)  # set to db_unique_name_DGMGRL.db_domain
      (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME=orcl)
      (ENVS='TNS_ADMIN=/u01/app/19.0.0/grid/network/admin')
    )
  )
# adjust the listener.ora on the standby side:
vi /u01/app/19.0.0/grid/network/admin/listener.ora

LISTENER=
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=lin2)(PORT=1521))
      (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orcl_lin2_DGMGRL.fritz.box)  # set to db_unique_name_DGMGRL.db_domain
      (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME=orcl)
      (ENVS='TNS_ADMIN=/u01/app/19.0.0/grid/network/admin')
    )
  )
# restart the listeners on both nodes:
srvctl stop listener && srvctl start listener

Setup the standby node and duplicate the database

# prepare and source environment files containing ORACLE_HOME and ORACLE_SID
# ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 ORACLE_SID=orcl
source ~/ora19.env
# create directories
mkdir -p /u01/app/oracle/admin/orcl/adump
# create password file (same sys pw as primary side)
orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle format=12 entries=10
# create a temporary pfile
echo "*.db_name='orcl'" > /tmp/pfile
# start the standby db in nomount mode
echo 'startup nomount pfile=/tmp/pfile' | sqlplus / as sysdba
# duplicate the standby database from the primary database via rman
echo "duplicate target database for standby from active database dorecover spfile set db_unique_name='orcl_lin2' nofilenamecheck;" | rman target sys/oracle@orcl_lin auxiliary sys/oracle@orcl_lin2
# create a spfile in ASM
echo "create spfile='+DATA/ORCL_LIN2/PARAMETERFILE/spfileorcl.ora' from memory;" | sqlplus / as sysdba
echo "spfile='+DATA/ORCL_LIN2/PARAMETERFILE/spfileorcl.ora'" > $ORACLE_HOME/dbs/initorcl.ora
rm $ORACLE_HOME/dbs/spfileorcl.ora
# enable flashback logging and shutdown the database
sqlplus / as sysdba << EOF
alter database archivelog;
alter database flashback on;
shutdown immediate
EOF
# add the db to the Oracle RAC config
srvctl add database -db orcl_lin2 -dbname orcl -instance orcl -oraclehome $ORACLE_HOME -spfile `cat $ORACLE_HOME/dbs/initorcl.ora|awk -F= {'print $2'}|sed s/\'//g`
srvctl modify database -d orcl_lin2 -a "DATA,FRA"
srvctl modify database -db orcl_lin2 -startoption mount
srvctl start database -db orcl_lin2

Create and enable the Data Guard Broker configuration

# start the Data Guard Broker on the primary and on the standby node
echo "alter system set dg_broker_start=true;" | sqlplus / as sysdba

# create and enable the dg configuration on the primary node
dgmgrl sys/oracle@orcl_lin << EOF
create configuration dgconf as primary database is orcl_lin connect identifier is orcl_lin;
add database orcl_lin2 as connect identifier is orcl_lin2;
rem connect identifier is a tnsnames alias
enable configuration
enable database orcl_lin2
host sleep 20
show configuration verbose
quit
EOF