Skip to main content

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

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
Labels:

Comments

Post a Comment

Popular posts from this blog

WebLogic migration to OCI using WDT tool

WebLogic migration to OCI using WDT tool Oracle WebLogic Deploy Tool (WDT) is an open-source project designed to simplify and streamline the management of Oracle WebLogic Server domains. With WDT, you can export configuration and application files from one WebLogic Server domain and import them into another, making it a highly effective tool for tasks like migrating on-premises WebLogic configurations to Oracle Cloud. This blog outlines a detailed step-by-step process for using WDT to migrate WebLogic resources and configurations. Supported WLS versions Why Use WDT for Migration? When moving Oracle WebLogic resources from an on-premises environment to Oracle Cloud (or another WebLogic Server), WDT provides an efficient and reliable approach to: Discover and export domain configurations and application binaries. Create reusable models and archives for deployment in a target domain. Key Pre-Requisites Source System: An Oracle WebLogic Server with pre-configured resources such as: Applica...
Post a Comment
Read more

How to Validate TDE Wallet Password in Oracle Database

How to Validate TDE Wallet Password in Oracle Database Validating the Transparent Data Encryption (TDE) wallet password is crucial, especially when ensuring that the password is correct without using the OPEN or CLOSE commands in the database. This blog post explains a straightforward method to validate the TDE password using the mkstore utility. Steps to Validate TDE Wallet Password Follow these steps to validate the TDE wallet password: Step 1: Copy the Keystore/Wallet File Navigate to your existing TDE wallet directory. Copy only the ewallet.p12 file to a new directory. If a cwallet.sso file exists, do not copy it . The absence of cwallet.sso ensures that the wallet does not use auto-login, forcing the utility to prompt for the password. Step 2: Validate Using mkstore Use the mkstore utility to check the contents of the wallet file. The mkstore utility will prompt you for the TDE wallet password, allowing you to validate its correctness. Command Syntax To display the conten...
Post a Comment
Read more

Rename a PDB in Oracle Database Multitenant Architecture in TDE and Non TDE Environment

Rename a PDB in Oracle Database Multitenant Architecture I am sharing a step-by-step guide to help you rename a PDB. This approach uses SQL commands. Without TDE or encryption Wallet Initial Check Check the Current Database Name and Open Mode: SQL > SELECT NAME, OPEN_MODE FROM V$DATABASE; NAME OPEN_MODE --------- -------------------- BEECDB READ WRITE List Current PDBs: SQL > SHOW PDBS; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 FUAT READ WRITE NO We need to RENAME FUAT to BEE  Steps to Rename the PDB Step 1: Export ORACLE_SID Set the Oracle SID to the Container Database (CDB): export ORACLE_SID=BEECDB Step 2: Verify Target PDB Name Availability If the target PDB name is different from the current PDB name, ensure no service exists with the target PDB name. Run SQL to Check Exi...
Post a Comment
Read more