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