Standby database creation

 #!/bin/bash


# Define variables (set these appropriately for your environment)

ORACLE_HOME="/u01/app/oracle/product/19.3.0/dbhome_1"

ORACLE_SID="pmjks"  # Standby SID for example

ORACLE_USER="oracle"

ORACLE_GROUP="dba"

STAGE_DIR="/u01/stage"

LOG_DIR="/tmp/tstgrade/${ORACLE_SID}"

DB_CREATEFILE_PATH="/u02/oradata"

DB_RECOFILE_DEST="/u03/fast_recovery_area"

TNS_PATH="/u01/app/oracle/network/admin"

PR_HOST="ora-x1"   # Primary hostname

SB_HOST="ora-x2"   # Standby hostname

LSNR_NAME="LISTENER"

DB_UNQ_NAME="mydb" # Database unique name

ROOT_USER="root"

ANSIBLE_DATE_TIME=$(date -Iseconds)


# Create log file directory

mkdir -p $LOG_DIR

chown $ORACLE_USER:$ORACLE_GROUP $LOG_DIR


# Create required directories on standby database server

echo "Creating required directories on standby database server..."

mkdir -p $STAGE_DIR $LOG_DIR $DB_CREATEFILE_PATH/$ORACLE_SID $DB_RECOFILE_DEST/$ORACLE_SID $DB_RECOFILE_DEST/$ORACLE_SID/pdbseed

chown -R $ORACLE_USER:$ORACLE_GROUP $STAGE_DIR $LOG_DIR $DB_CREATEFILE_PATH/$ORACLE_SID $DB_RECOFILE_DEST/$ORACLE_SID


# Create required directories on primary database server

if [ "$(hostname)" == "$PR_HOST" ]; then

  echo "Creating required directories on primary database server..."

  mkdir -p $STAGE_DIR $LOG_DIR $DB_RECOFILE_DEST/$ORACLE_SID

  chown -R $ORACLE_USER:$ORACLE_GROUP $STAGE_DIR $LOG_DIR $DB_RECOFILE_DEST/$ORACLE_SID

fi


# Copy required scripts to primary database server

if [ "$(hostname)" == "$PR_HOST" ]; then

  echo "Copying required scripts to primary database server..."

  cp /path/to/chkexistdg.sql /path/to/gensbredologs.sql /path/to/postcrpr.sql /path/to/dg_status.sql /path/to/tns_upd.sh /path/to/add_staticlsnr.sh $STAGE_DIR

  chown $ORACLE_USER:$ORACLE_GROUP $STAGE_DIR/*

  chmod 0755 $STAGE_DIR/*

fi


# Copy required scripts to standby database server

if [ "$(hostname)" == "$SB_HOST" ]; then

  echo "Copying required scripts to standby database server..."

  cp /path/to/postcrsb.sql /path/to/tns_upd.sh /path/to/dg_status.sql /path/to/add_staticlsnr.sh $STAGE_DIR

  chown $ORACLE_USER:$ORACLE_GROUP $STAGE_DIR/*

  chmod 0755 $STAGE_DIR/*

fi


# Template for post-sql on standby database

if [ "$(hostname)" == "$SB_HOST" ]; then

  echo "Setting up postcrsb.sql on standby database..."

  cp /path/to/postcrsb.sh.j2 $STAGE_DIR/postcrsb.sql

  chown $ORACLE_USER:$ORACLE_GROUP $STAGE_DIR/postcrsb.sql

  chmod 0775 $STAGE_DIR/postcrsb.sql

fi


# Template for post-sql on primary database

if [ "$(hostname)" == "$PR_HOST" ]; then

  echo "Setting up postcrpr.sql on primary database..."

  cp /path/to/postcrpr.sh.j2 $STAGE_DIR/postcrpr.sql

  chown $ORACLE_USER:$ORACLE_GROUP $STAGE_DIR/postcrpr.sql

  chmod 0775 $STAGE_DIR/postcrpr.sql

fi


# Configure silent listener on standby host

if [ "$(hostname)" == "$SB_HOST" ]; then

  echo "Configuring silent listener on standby host..."

  cp /path/to/listener.rsp.19c.j2 $STAGE_DIR/silent-lsnr19c-config.rsp

  chown $ORACLE_USER:$ORACLE_GROUP $STAGE_DIR/silent-lsnr19c-config.rsp

  chmod 0775 $STAGE_DIR/silent-lsnr19c-config.rsp

  $ORACLE_HOME/bin/netca -silent -responseFile $STAGE_DIR/silent-lsnr19c-config.rsp

fi


# Execute TNS update for primary and standby databases

echo "Executing TNS update for primary and standby databases..."

$STAGE_DIR/tns_upd.sh $ORACLE_SID $PR_HOST $PORT $DB_UNQ_NAME $TNS_PATH

$STAGE_DIR/tns_upd.sh $ORACLE_SID $SB_HOST $PORT $DB_UNQ_NAME $TNS_PATH


# Add static listener for primary and standby

echo "Adding static listener for primary and standby databases..."

$STAGE_DIR/add_staticlsnr.sh ${ORACLE_SID}_DGMGRL $ORACLE_SID $LSNR_NAME $TNS_PATH


# Reload listener on primary database server

if [ "$(hostname)" == "$PR_HOST" ]; then

  echo "Reloading listener on primary database server..."

  $ORACLE_HOME/bin/lsnrctl reload $LSNR_NAME

fi


# Setup force logging on primary

if [ "$(hostname)" == "$PR_HOST" ]; then

  echo "Setting up force logging on primary database..."

  sqlplus / as sysdba <<EOF

  alter database force logging;

EOF

fi


# Ensure archiving is set to Fast Recovery Area on primary

if [ "$(hostname)" == "$PR_HOST" ]; then

  echo "Setting archive log destination on primary..."

  sqlplus / as sysdba <<EOF

  alter system set log_archive_dest_1='location=$DB_RECOFILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=$DB_UNQ_NAME';

EOF

fi


# Execute standby redo logs script on primary

if [ "$(hostname)" == "$PR_HOST" ]; then

  echo "Executing standby redo logs script on primary database..."

  sqlplus / as sysdba <<EOF

  @$STAGE_DIR/gensbredologs.sql

EOF

fi


# Final status message

echo "This step completed the pre-configuration task for the physical standby database on $(hostname) at $ANSIBLE_DATE_TIME."

Comments

Post a Comment

Popular posts from this blog

SQL Loader V3

Create the Configuration File (config.cfg):Create a file named config.cfg with the following content. Adjust the values to match your environment:[database] username=your_username password=your_password database=your_database [files] csv_file=/path/to/your/file.csv log_file=/path/to/your/log_file.log inserted_rows_file=/path/to/your/inserted_rows.log updated_rows_file=/path/to/your/updated_rows.log ********** #!/bin/bash # Load configuration source config.cfg # Variables CSV_FILE=$csv_file USERNAME=$username PASSWORD=$password DATABASE=$database MASTER_TABLE="master_table" CHILD_TABLE="child_table" LOG_FILE=$log_file INSERTED_ROWS_FILE=$inserted_rows_file UPDATED_ROWS_FILE=$updated_rows_file # Logging function log() {   echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE } log "Starting data load process." # SQL Loader Control File for child table CONTROL_FILE="control_file.ctl" # Create control file for child table cat ...
Read more

Db Unix

-- Insert new records into master table INSERT INTO $master_table (     parent,     parent_sys_class_name,     child_sys_class_name,     child_u_cmdb_ci_used_for,     parent_correlation_id,     type,     child_correlation_id,     child,     modified_date ) SELECT     c.parent,     c.parent_sys_class_name,     c.child_sys_class_name,     c.child_u_cmdb_ci_used_for,     c.parent_correlation_id,     c.type,     c.child_correlation_id,     c.child,     c.modified_date FROM $child_table c LEFT JOIN $master_table m ON c.child_correlation_id = m.child_correlation_id WHERE m.child_correlation_id IS NULL; -- Log inserted rows SPOOL $inserted_rows_file SELECT     c.parent,     c.parent_sys_class_name,     c.child_sys_class_name,     c.child_u_cmdb_ci_used_for,     c.pare...
Read more

Standby redo log add

********* #!/bin/bash # Function to run SQL*Plus commands run_sql() {     sqlplus -s / as sysdba <<EOF     SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF     $1     EXIT EOF } # Get the max group, max thread, and max size from the v$log and v$logfile views redo_info=$(run_sql "SELECT MAX(l.group#), MAX(l.thread#), MAX(l.bytes/1024/1024)  FROM v\$log l, v\$logfile f  WHERE f.group# = l.group#;") # Extract the max group, max thread, and max size values max_group=$(echo "$redo_info" | awk '{print $1}') max_thread=$(echo "$redo_info" | awk '{print $2}') max_size=$(echo "$redo_info" | awk '{print $3}') # Get the number of redo groups per thread groups_per_thread=$(run_sql "SELECT COUNT(DISTINCT group#) FROM v\$log WHERE thread#=1;") # Get the recovery file destination reco_dest=$(run_sql "SHOW PARAMETER db_recovery_file_dest") reco_dest=$(echo "$reco_dest" | awk 'NR==2 {print $3}' |...
Read more