Shell Script to create a Physical Standby Server for a 19c Oracle Database

In this post I am sharing a script which can use useful for automating the creation of a physical standby.




Note:

Both my server are OCI Compute and we will used opc key for connecting between opc user and then using oracle user for database.
Database is CDB-PDB and having 19c Database.

Script: Please change script accordingly for your needs.

#!/bin/bash
#set -x
# Database details and configurations
PRIMARY_DB_SID="EBSCDB"
STANDBY_DB_SID="SEBSCDB"
PRIMARY_DB_HOST="db.example.com"
STANDBY_DB_HOST="ebs-server2.subnet09191039.vcn09191039.oraclevcn.com"
DB_UNIQUE_NAME_PRIMARY="EBSCDB"
DB_UNIQUE_NAME_STANDBY="SEBSCDB"
PRIMARY_ORACLE_HOME="/u01/install/APPS/19.0.0"
PRIMARY_ORACLE_BASE="/u01/install/APPS"
STANDBY_ORACLE_HOME="/u01/install/APPS/19.0.0"
STANDBY_ORACLE_BASE="/u01/install/APPS"
SSH_KEY_PATH="/home/oracle/dr_Server_key.pem"
SYS_PASSWORD="manager#123"  # Replace with actual SYS password

# Export Primary Oracle environment
export ORACLE_HOME=$PRIMARY_ORACLE_HOME
export ORACLE_BASE=$PRIMARY_ORACLE_BASE
export ORACLE_SID=$PRIMARY_DB_SID

#Connect to Primary Database and Enable Archive Log Mode
echo "Checking archive log mode on primary database..."
#Connect to Primary Database and Enable Archive Log Mode and Force Logging
sqlplus / as sysdba << EOF
SET SERVEROUTPUT ON;

DECLARE
    v_log_mode VARCHAR2(20);
BEGIN
    SELECT log_mode INTO v_log_mode FROM v\$database;

    IF v_log_mode != 'ARCHIVELOG' THEN
        DBMS_OUTPUT.PUT_LINE('Database is not in ARCHIVELOG mode. Enabling...');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Database is already in ARCHIVELOG mode.');
    END IF;

END;
/
EXIT;
EOF

sqlplus / as sysdba << EOF
ALTER SYSTEM SET log_archive_config='DG_CONFIG=($DB_UNIQUE_NAME_PRIMARY,$DB_UNIQUE_NAME_STANDBY)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/install/APPS/data/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=$DB_UNIQUE_NAME_PRIMARY' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_2='SERVICE=$DB_UNIQUE_NAME_STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=$DB_UNIQUE_NAME_STANDBY' SCOPE=BOTH;
ALTER SYSTEM SET fal_server='$DB_UNIQUE_NAME_PRIMARY' SCOPE=BOTH;
ALTER SYSTEM SET fal_client='$DB_UNIQUE_NAME_STANDBY' SCOPE=BOTH;
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;
ALTER SYSTEM SET remote_login_passwordfile='EXCLUSIVE' SCOPE=spfile;
EXIT;
EOF
sqlplus / as sysdba << EOF
SET SERVEROUTPUT ON;

DECLARE
    v_largest_redo_size NUMBER;
    v_redo_log_count NUMBER;
BEGIN
    SELECT MAX(bytes) INTO v_largest_redo_size FROM v\$log;

    SELECT COUNT(*) INTO v_redo_log_count FROM v\$log;

    FOR i IN 1 .. (v_redo_log_count + 1) LOOP
        EXECUTE IMMEDIATE 'ALTER DATABASE ADD STANDBY LOGFILE ''' || '/u01/install/APPS/data/EBSCDB_stby_redo_log_group_' || i || '.log' ||  ''' SIZE ' || v_largest_redo_size || ' ';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Standby redo logs created successfully.');
END;
/

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE OPEN;
EXIT;
EOF

# Create the Oracle environment file on the primary server and copy to standby
cat << EOF > /tmp/oracle_env.sh
# Oracle Standby Environment Configuration
export ORACLE_BASE=${STANDBY_ORACLE_BASE}
export ORACLE_HOME=${STANDBY_ORACLE_HOME}
export ORACLE_SID=SEBSCDB
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
EOF

# Copy the environment file to the standby server
scp -i $SSH_KEY_PATH /tmp/oracle_env.sh opc@$STANDBY_DB_HOST:/tmp/oracle_env.sh
chmod 777 /tmp/oracle_env.sh
ssh -t -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST "sudo su - oracle -c 'mv /tmp/oracle_env.sh /home/oracle/oracle_env.sh'"

# Check for password file on primary and copy it if exists
echo "Step 1: Checking for password file on primary..."
if ssh -t -i $SSH_KEY_PATH opc@$PRIMARY_DB_HOST "sudo su - oracle -c 'test -f ${PRIMARY_ORACLE_HOME}/dbs/orapw${PRIMARY_DB_SID}'"; then
  echo "Password file exists. Copying to standby..."
  cp ${PRIMARY_ORACLE_HOME}/dbs/orapw${PRIMARY_DB_SID} /tmp/orapw${STANDBY_DB_SID}
  scp -i $SSH_KEY_PATH /tmp/orapw${STANDBY_DB_SID} opc@$STANDBY_DB_HOST:/tmp/orapw${STANDBY_DB_SID}
  ssh -t -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST "chmod 777 /tmp/orapw${STANDBY_DB_SID}"
  ssh -t -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST "sudo su - oracle -c 'cp /tmp/orapw${STANDBY_DB_SID} ${STANDBY_ORACLE_HOME}/dbs/orapw${STANDBY_DB_SID}'"
else
  echo "Password file does not exist on primary. Please create one."
  exit 1
fi


# Setup directories on standby if not existing
echo "Step 2: Creating necessary directories on standby..."
ssh -tt -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST << EOF
  sudo su - oracle -c "
    mkdir -p ${STANDBY_ORACLE_BASE}/admin/${STANDBY_DB_SID}/adump &&
    mkdir -p ${STANDBY_ORACLE_HOME}/dbs &&
    mkdir -p ${STANDBY_ORACLE_BASE}/data/${STANDBY_DB_SID}
  "
EOF

#Configure listener on standby
echo "Step 3: Configuring and starting listener on standby..."
ssh -tt -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST << EOF
  sudo su - oracle -c "source /home/oracle/oracle_env.sh &&
    echo '${STANDBY_DB_SID} =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ${STANDBY_DB_HOST})(PORT = 1521))
    )
  )

# Static registration for SEBSCDB standby database
SID_LIST_${STANDBY_DB_SID} =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ${STANDBY_DB_SID})
      (ORACLE_HOME = ${STANDBY_ORACLE_HOME})
      (SID_NAME = ${STANDBY_DB_SID})
  )
) ' > ${STANDBY_ORACLE_HOME}/network/admin/listener.ora &&
    ${STANDBY_ORACLE_HOME}/bin/lsnrctl start ${STANDBY_DB_SID}
  "
EOF

# Configure TNS entries on primary and standby
echo "Step 4: Configuring TNS entries on primary and standby..."
# TNS entry configuration for primary
cat <<EOF > ${ORACLE_HOME}/network/admin/tnsnames.ora
${DB_UNIQUE_NAME_PRIMARY} =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = $PRIMARY_DB_HOST)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ${DB_UNIQUE_NAME_PRIMARY})
    )
  )

${DB_UNIQUE_NAME_STANDBY} =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = $STANDBY_DB_HOST)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ${DB_UNIQUE_NAME_STANDBY})
    )
  )
EOF

# Copy TNS configuration to standby server
chmod 777 ${ORACLE_HOME}/network/admin/tnsnames.ora
scp -i $SSH_KEY_PATH ${ORACLE_HOME}/network/admin/tnsnames.ora opc@$STANDBY_DB_HOST:/tmp/tnsnames.ora
ssh -t -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST "chmod 777 /tmp/tnsnames.ora"
ssh -t -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST "sudo su - oracle -c 'cp /tmp/tnsnames.ora ${STANDBY_ORACLE_HOME}/network/admin/tnsnames.ora'"

#Prepare Primary for Standby Duplicate
#echo "Step 5: Configuring Primary database for standby setup..."
#rman target sys/$SYS_PASSWORD@$PRIMARY_DB_SID <<EOF
#CONFIGURE CONTROLFILE AUTOBACKUP ON;
#BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '${PRIMARY_ORACLE_BASE}/data/${PRIMARY_DB_SID}/standby_controlfile.bkp';
#EOF

#Startstandby in Nomount
ssh -tt -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST << EOF
  sudo su - oracle -c "source /home/oracle/oracle_env.sh &&
    echo 'DB_NAME=${STANDBY_DB_SID}
DB_UNIQUE_NAME=${DB_UNIQUE_NAME_STANDBY}' > ${STANDBY_ORACLE_HOME}/dbs/init${STANDBY_DB_SID}.ora
"
EOF

ssh -tt -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST << EOF
sudo su - oracle -c "source /home/oracle/oracle_env.sh && sqlplus / as sysdba <<SQL
STARTUP NOMOUNT ;
EXIT;
SQL
"
EOF


#Duplicate the database for standby
echo "Running RMAN DUPLICATE for standby..."
rman target sys/$SYS_PASSWORD@$PRIMARY_DB_SID auxiliary sys/$SYS_PASSWORD@$STANDBY_DB_SID <<EOF
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET DB_UNIQUE_NAME='${DB_UNIQUE_NAME_STANDBY}'
SET CONTROL_FILES='${STANDBY_ORACLE_BASE}/data/${STANDBY_DB_SID}/control01.ctl'
SET LOG_FILE_NAME_CONVERT='${PRIMARY_ORACLE_BASE}/data/${PRIMARY_DB_SID}','${STANDBY_ORACLE_BASE}/data/${STANDBY_DB_SID}'
SET DB_FILE_NAME_CONVERT='${PRIMARY_ORACLE_BASE}/data/${PRIMARY_DB_SID}','${STANDBY_ORACLE_BASE}/data/${STANDBY_DB_SID}'
SET fal_client='SEBSCDB'
SET fal_server='EBSCDB'
SET standby_file_management='AUTO'
SET log_archive_config='DG_CONFIG=($DB_UNIQUE_NAME_PRIMARY,$DB_UNIQUE_NAME_STANDBY)'
SET log_archive_dest_1='LOCATION=/u01/install/APPS/data/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=$DB_UNIQUE_NAME_STANDBY' 
SET log_archive_dest_2='SERVICE=EBSCDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=$DB_UNIQUE_NAME_PRIMARY'
NOFILENAMECHECK;
EOF


#Start Managed Recovery on Standby Database
echo "Starting managed recovery on standby..."
ssh -tt -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST << EOF
sudo su - oracle -c "
source /home/oracle/oracle_env.sh &&
sqlplus / as sysdba <<SQL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
EXIT;
SQL
"
EOF

# Verify Standby Database Setup
ssh -tt -i $SSH_KEY_PATH opc@$STANDBY_DB_HOST << EOF
sudo su - oracle -c "
source /home/oracle/oracle_env.sh &&
sqlplus / as sysdba <<SQL
SELECT open_mode, database_role FROM v\$database;
SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V\$MANAGED_STANDBY;
EXIT;
SQL
"
EOF
echo "Standby database setup completed successfully."






Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment