Oracle Physical Standby Database setup
The following steps can be used to setup a Oracle Physical Standby Database managed by Oracle Data Guard Broker. The software versions are: Oracle GI (Oracle Restart) and DB 19.25 and RHEL 8.1. You need to setup both nodes as described here. Afterwards patch it with RU 19.25 as described here. Delete the orcl database on the second node (see here).
Table of Contents
For the following steps open 2 terminals per node. One as the grid user and one as the oracle user. Then run the following commands in one terminal and follow the instructions. This will setup the environment variables:
echo -n 'Enter the primary hostname (e.g. lin1): ' && read pnode && \
echo -n 'Enter the standby hostname (e.g. lin2): ' && read snode && \
echo 'run the following commands in the other 3 terminals: ' && \
echo -e 'export pnode='$pnode'\nexport snode='$snode
Prepare the primary node of the
as the oracle user run on 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
. ~/ora19.env
sqlplus -S / 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 changeme;' 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 -S / as sysdba
srvctl add database -db orcl_$pnode -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_$pnode -a "DATA,FRA"
srvctl start database -d orcl_$pnode
Setup the network configuration
run this as the oracle user on both nodes to adjust the tnsnames.ora file:
echo "
orcl_$pnode=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=$pnode)(PORT=1521))
)
(CONNECT_DATA=(SID=orcl)
)
)
orcl_$snode=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=$snode)(PORT=1521))
)
(CONNECT_DATA=(SID=orcl)
)
)
" >> /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
as the oracle user run on the standby node:
echo "
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = $snode.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
as the grid user run these commands on the primary and the standby node:
# delete the block between a line starting with SID_LIST_LISTENER and an empty line
# there must be an empty line after the SID_LIST_LISTENER= block
sed -i '/^SID_LIST_LISTENER.*$/,/^$/d' /u01/app/19.0.0/grid/network/admin/listener.ora
# replace the block between the line starting with LISTENER and the empty line with a new text block
# there must be an empty line after the LISTENER= block
curhost=`hostname|awk -F. {'print $1'}`
sed -i "/^LISTENER.*$/,/^$/c\
LISTENER=\\
(DESCRIPTION_LIST=\\
(DESCRIPTION=\\
(ADDRESS=(PROTOCOL=TCP)(HOST=$curhost)(PORT=1521))\\
(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))\\
)\\
)\\
\\
SID_LIST_LISTENER=\\
(SID_LIST=\\
(SID_DESC=\\
(GLOBAL_DBNAME=orcl\_$curhost\_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')\\
)\\
)\\
" /u01/app/19.0.0/grid/network/admin/listener.ora
# restart the listener:
srvctl stop listener && srvctl start listener
Setup the standby node and duplicate the database
as the oracle user on the standby node run:
# 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
. ~/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=changeme 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 -S / 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_$snode' nofilenamecheck;" | rman target sys/changeme@orcl_$pnode auxiliary sys/changeme@orcl_$snode
# create a spfile in ASM
echo "create spfile='+DATA/ORCL_${snode^^}/PARAMETERFILE/spfileorcl.ora' from memory;" | sqlplus -S / as sysdba
echo "spfile='+DATA/ORCL_${snode^^}/PARAMETERFILE/spfileorcl.ora'" > $ORACLE_HOME/dbs/initorcl.ora
rm $ORACLE_HOME/dbs/spfileorcl.ora
# enable flashback logging and shutdown the database
sqlplus -S / 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_$snode -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_$snode -a "DATA,FRA"
srvctl modify database -db orcl_$snode -startoption mount
srvctl start database -db orcl_$snode
Create and enable the Data Guard Broker configuration
run as the oracle user on the primary and standby node:
# start the Data Guard Broker
echo "alter system set dg_broker_start=true;" | sqlplus -S / as sysdba
run as the oracle user on the primary node:
# create and enable the dg configuration
dgmgrl sys/changeme@orcl_$pnode << EOF
create configuration dgconf as primary database is orcl_$pnode connect identifier is orcl_$pnode;
add database orcl_$snode as connect identifier is orcl_$snode;
rem connect identifier is a tnsnames alias
enable configuration
enable database orcl_$snode
host sleep 20
show configuration verbose
quit
EOF
You now have a running Oracle Physical Standby Database. Congratulations! You might take a look at Administration of an Oracle Standby Database now.
Leave a Reply