Oracle 19c Data guard


# standby_config.cfg


# Database and host details

PRIMARY_DB_NAME=chicago

PRIMARY_DB_UNIQUE_NAME=chicago

PRIMARY_HOST=primary_host

PRIMARY_LOG_PATH=/u01/app/oracle/databases/chicago/redo

PRIMARY_ARCHIVE_PATH=/u01/app/oracle/databases/chicago/arch


STANDBY_DB_NAME=boston

STANDBY_DB_UNIQUE_NAME=boston

STANDBY_HOST=standby_host

STANDBY_LOG_PATH=/u01/app/oracle/databases/boston/redo

STANDBY_ARCHIVE_PATH=/u01/app/oracle/databases/boston/arch


# Oracle Home paths

ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

ORACLE_SID=$STANDBY_DB_UNIQUE_NAME


# Network and TNS details

PRIMARY_TNS_ENTRY=chicago

STANDBY_TNS_ENTRY=boston

TNS_ADMIN=/u01/app/oracle/network/admin


# ASM storage locations

DATA_DISK_GROUP=+DATA

RECO_DISK_GROUP=+RECO



Sample 1


#!/bin/bash


# Load configuration file

source standby_config.cfg


# Function to log messages

log() {

  echo "$(date +%Y-%m-%dT%H:%M:%S) - $1"

}


# Step 1: Prepare the primary database

log "Preparing primary database..."


# Check if the database is in ARCHIVELOG mode

log "Checking if the database is in ARCHIVELOG mode..."

sqlplus -s / as sysdba <<EOF

SET HEADING OFF;

SET FEEDBACK OFF;

SELECT log_mode FROM v\$database;

EOF


# Enable force logging on the primary

log "Enabling FORCE LOGGING..."

sqlplus -s / as sysdba <<EOF

ALTER DATABASE FORCE LOGGING;

EOF


# Add standby redologs

log "Adding standby redo logs..."

sqlplus -s / as sysdba <<EOF

ALTER DATABASE ADD STANDBY LOGFILE '$PRIMARY_LOG_PATH/standby_redo01.log' SIZE 50M;

EOF


# Modify initialization parameters

log "Modifying primary database initialization parameters..."

sqlplus -s / as sysdba <<EOF

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=($PRIMARY_DB_UNIQUE_NAME, $STANDBY_DB_UNIQUE_NAME)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=$PRIMARY_LOG_PATH VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=$PRIMARY_DB_UNIQUE_NAME';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=$STANDBY_TNS_ENTRY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE) DB_UNIQUE_NAME=$STANDBY_DB_UNIQUE_NAME';

ALTER SYSTEM SET FAL_SERVER='$STANDBY_TNS_ENTRY';

ALTER SYSTEM SET FAL_CLIENT='$PRIMARY_TNS_ENTRY';

EOF


# Step 2: Check SQL*Net connectivity

log "Checking SQL*Net connectivity..."

tnsping $PRIMARY_TNS_ENTRY

tnsping $STANDBY_TNS_ENTRY


# Step 3: Create the standby database

log "Creating standby database..."


# Copy password file from primary to standby

log "Copying password file..."

cp $ORACLE_HOME/dbs/orapw$PRIMARY_DB_UNIQUE_NAME $ORACLE_HOME/dbs/orapw$STANDBY_DB_UNIQUE_NAME


# Create the necessary directories on standby

log "Creating directories for standby..."

ssh $STANDBY_HOST "mkdir -p $STANDBY_LOG_PATH $STANDBY_ARCHIVE_PATH"


# Create initialization parameter for standby

log "Creating standby initialization parameter..."

cat > $ORACLE_HOME/dbs/init${STANDBY_DB_UNIQUE_NAME}.ora <<EOF

db_name=$PRIMARY_DB_NAME

db_unique_name=$STANDBY_DB_UNIQUE_NAME

db_block_size=8192

control_files='$DATA_DISK_GROUP/$STANDBY_DB_UNIQUE_NAME/controlfile.ctl'

EOF


# Start standby instance

log "Starting standby instance..."

export ORACLE_SID=$STANDBY_DB_UNIQUE_NAME

sqlplus / as sysdba <<EOF

STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/init${STANDBY_DB_UNIQUE_NAME}.ora';

EOF


# Step 4: Perform RMAN DUPLICATE

log "Running RMAN DUPLICATE..."

rman target sys/password@$PRIMARY_TNS_ENTRY auxiliary sys/password@$STANDBY_TNS_ENTRY <<EOF

RUN {

  ALLOCATE CHANNEL prmy1 TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL stby TYPE DISK;

  DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE

    SPFILE

    PARAMETER_VALUE_CONVERT '$PRIMARY_DB_UNIQUE_NAME','$STANDBY_DB_UNIQUE_NAME'

    SET db_unique_name='$STANDBY_DB_UNIQUE_NAME'

    SET db_file_name_convert='$PRIMARY_LOG_PATH','$STANDBY_LOG_PATH'

    SET log_file_name_convert='$PRIMARY_LOG_PATH','$STANDBY_LOG_PATH'

    SET control_files='$DATA_DISK_GROUP/$STANDBY_DB_UNIQUE_NAME/controlfile.ctl'

    SET fal_client='$STANDBY_TNS_ENTRY'

    SET fal_server='$PRIMARY_TNS_ENTRY'

    SET standby_file_management='AUTO';

}

EOF


# Step 5: Start managed recovery

log "Starting managed recovery process on standby..."

sqlplus / as sysdba <<EOF

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

EOF


# Step 6: Add TNS entries for primary and standby

log "Adding TNS entries to tnsnames.ora..."

cat >> $TNS_ADMIN/tnsnames.ora <<EOF


$PRIMARY_TNS_ENTRY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = $PRIMARY_HOST)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = $PRIMARY_DB_NAME)

    )

  )


$STANDBY_TNS_ENTRY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = $STANDBY_HOST)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = $STANDBY_DB_NAME)

    )

  )

EOF


log "Standby setup completed successfully."

********************-----


Sample 2


 #!/bin/bash


# Load configuration file

source standby_config.cfg


# Function to log messages

log() {

  echo "$(date +%Y-%m-%dT%H:%M:%S) - $1"

}


# Step 1: Prepare the primary database

log "Preparing primary database..."


# Check if the database is in ARCHIVELOG mode

log "Checking if the database is in ARCHIVELOG mode..."

sqlplus -s / as sysdba <<EOF

SET HEADING OFF;

SET FEEDBACK OFF;

SELECT log_mode FROM v\$database;

EOF


# Enable force logging on the primary

log "Enabling FORCE LOGGING..."

sqlplus -s / as sysdba <<EOF

ALTER DATABASE FORCE LOGGING;

EOF


# Add standby redo logs

log "Adding standby redo logs..."

sqlplus -s / as sysdba <<EOF

ALTER DATABASE ADD STANDBY LOGFILE '$PRIMARY_LOG_PATH/standby_redo01.log' SIZE 50M;

EOF


# Modify initialization parameters

log "Modifying primary database initialization parameters..."

sqlplus -s / as sysdba <<EOF

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=($PRIMARY_DB_UNIQUE_NAME, $STANDBY_DB_UNIQUE_NAME)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=$PRIMARY_ARCHIVE_PATH VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=$PRIMARY_DB_UNIQUE_NAME';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=$STANDBY_TNS_ENTRY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE) DB_UNIQUE_NAME=$STANDBY_DB_UNIQUE_NAME';

ALTER SYSTEM SET FAL_SERVER='$STANDBY_TNS_ENTRY';

ALTER SYSTEM SET FAL_CLIENT='$PRIMARY_TNS_ENTRY';

EOF


# Step 2: Check SQL*Net connectivity

log "Checking SQL*Net connectivity..."

tnsping $PRIMARY_TNS_ENTRY

tnsping $STANDBY_TNS_ENTRY


# Step 3: Create the standby database

log "Creating standby database..."


# Copy password file from primary to standby

log "Copying password file..."

cp $ORACLE_HOME/dbs/orapw$PRIMARY_DB_UNIQUE_NAME $ORACLE_HOME/dbs/orapw$STANDBY_DB_UNIQUE_NAME


# Create necessary directories on standby

log "Creating directories for standby..."

ssh $STANDBY_HOST "mkdir -p $STANDBY_LOG_PATH $STANDBY_ARCHIVE_PATH"


# Create initialization parameter for standby

log "Creating standby initialization parameter..."

cat > $ORACLE_HOME/dbs/init${STANDBY_DB_UNIQUE_NAME}.ora <<EOF

db_name=$PRIMARY_DB_NAME

db_unique_name=$STANDBY_DB_UNIQUE_NAME

db_block_size=8192

control_files='$DATA_DISK_GROUP/$STANDBY_DB_UNIQUE_NAME/controlfile.ctl'

EOF


# Step 4: Start standby instance

log "Starting standby instance..."

export ORACLE_SID=$STANDBY_DB_UNIQUE_NAME

sqlplus / as sysdba <<EOF

STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/init${STANDBY_DB_UNIQUE_NAME}.ora';

EOF


# Step 5: Perform RMAN DUPLICATE

log "Running RMAN DUPLICATE..."

rman target sys/password@$PRIMARY_TNS_ENTRY auxiliary sys/password@$STANDBY_TNS_ENTRY <<EOF

RUN {

  ALLOCATE CHANNEL prmy1 TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL stby TYPE DISK;

  DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE

    SPFILE

    PARAMETER_VALUE_CONVERT '$PRIMARY_DB_UNIQUE_NAME','$STANDBY_DB_UNIQUE_NAME'

    SET db_unique_name='$STANDBY_DB_UNIQUE_NAME'

    SET db_file_name_convert='$PRIMARY_LOG_PATH','$STANDBY_LOG_PATH'

    SET log_file_name_convert='$PRIMARY_LOG_PATH','$STANDBY_LOG_PATH'

    SET control_files='$DATA_DISK_GROUP/$STANDBY_DB_UNIQUE_NAME/controlfile.ctl'

    SET fal_client='$STANDBY_TNS_ENTRY'

    SET fal_server='$PRIMARY_TNS_ENTRY'

    SET standby_file_management='AUTO';

}

EOF


# Step 6: Start managed recovery

log "Starting managed recovery process on standby..."

sqlplus / as sysdba <<EOF

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

EOF


# Step 7: Add TNS entries for primary and standby

log "Adding TNS entries to tnsnames.ora..."

cat >> $TNS_ADMIN/tnsnames.ora <<EOF


$PRIMARY_TNS_ENTRY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = $PRIMARY_HOST)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = $PRIMARY_DB_NAME)

    )

  )


$STANDBY_TNS_ENTRY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = $STANDBY_HOST)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = $STANDBY_DB_NAME)

    )

  )

EOF


# Step 8: Data Guard Broker Configuration (DGMGRL)

log "Configuring Data Guard Broker (DGMGRL)..."


dgmgrl sys/password <<EOF

CREATE CONFIGURATION 'DGConfig' AS PRIMARY DATABASE IS '$PRIMARY_DB_UNIQUE_NAME' CONNECT IDENTIFIER IS '$PRIMARY_TNS_ENTRY';

ADD DATABASE '$STANDBY_DB_UNIQUE_NAME' AS CONNECT IDENTIFIER IS '$STANDBY_TNS_ENTRY' MAINTAINED AS PHYSICAL;

ENABLE CONFIGURATION;

EOF


log "Data Guard Broker configuration completed."


# Step 9: RAC Configuration for Standby (if applicable)

log "Configuring RAC for standby database..."


srvctl add database -db $STANDBY_DB_UNIQUE_NAME -oraclehome $ORACLE_HOME -dbtype RAC -spfile $ORACLE_HOME/dbs/spfile${STANDBY_DB_UNIQUE_NAME}.ora

srvctl add instance -db $STANDBY_DB_UNIQUE_NAME -instance $STANDBY_DB_UNIQUE_NAME -node $STANDBY_HOST


log "RAC configuration for standby database completed."


log "Standby setup completed successfully."


********************

Sample 3


# Primary and Standby Database Details

PRIMARY_DB_SID=orcl

STANDBY_DB_SID=orcl_stby


# Hostnames

PRIMARY_HOST=primary_host

STANDBY_HOST=standby_host


# Shared Mount for PFILE and Password File Transfer

SHARED_MOUNT=/shared/mount


# Oracle Home

ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1


# ASM Directories and Password File Paths

ASM_PWD_FILE="+DATA/$STANDBY_DB_SID/orapw$STANDBY_DB_SID"

STANDBY_DATA_DIR="+DATA/$STANDBY_DB_SID"


# TNS Entries for Primary and Standby

PRIMARY_TNS_ENTRY=(

"PRIMARY_TNS ="

" (DESCRIPTION ="

" (ADDRESS = (PROTOCOL = TCP)(HOST = $PRIMARY_HOST)(PORT = 1521))"

" (CONNECT_DATA ="

" (SERVICE_NAME = $PRIMARY_DB_SID)"

" )"

" )"

)


STANDBY_TNS_ENTRY=(

"STANDBY_TNS ="

" (DESCRIPTION ="

" (ADDRESS = (PROTOCOL = TCP)(HOST = $STANDBY_HOST)(PORT = 1521))"

" (CONNECT_DATA ="

" (SERVICE_NAME = $STANDBY_DB_SID)"

" )"

" )"

)


*****

#!/bin/bash


# Load configuration file

source standby_config.cfg


# Function to log messages

log() {

  echo "$(date +%Y-%m-%dT%H:%M:%S) - $1"

}


# 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

}


# Step 1: Copy and modify PFILE for standby

log "Copying PFILE from shared mount and modifying it for standby..."

PFILE_PATH="$SHARED_MOUNT/init$PRIMARY_DB_SID.ora"

cp $PFILE_PATH $ORACLE_HOME/dbs/init$STANDBY_DB_SID.ora


# Modify PFILE for standby-specific configurations

sed -i "s/$PRIMARY_DB_SID/$STANDBY_DB_SID/g" $ORACLE_HOME/dbs/init$STANDBY_DB_SID.ora

sed -i "s#oradata/$PRIMARY_DB_SID#oradata/$STANDBY_DB_SID#g" $ORACLE_HOME/dbs/init$STANDBY_DB_SID.ora

sed -i "s#control_files=.*#control_files='$STANDBY_DATA_DIR/controlfile.ctl'#g" $ORACLE_HOME/dbs/init$STANDBY_DB_SID.ora


log "PFILE modified and ready for standby."


# Step 2: Create necessary ASM directories and copy password file using ASMCMD

log "Creating ASM directories and copying password file using ASMCMD..."

asmcmd mkdir $STANDBY_DATA_DIR

PASSWORD_FILE_PATH="$SHARED_MOUNT/orapw$PRIMARY_DB_SID"

asmcmd cp $PASSWORD_FILE_PATH $ASM_PWD_FILE


# Step 3: Start Standby in NOMOUNT mode

log "Starting standby database in NOMOUNT mode..."

export ORACLE_SID=$STANDBY_DB_SID

run_sql "STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/init$STANDBY_DB_SID.ora';"


# Step 4: RMAN Duplicate from Active Database

log "Starting RMAN duplicate from active primary database..."

rman target sys/oracle@$PRIMARY_DB_SID auxiliary / <<EOF

RUN {

    ALLOCATE CHANNEL prmy1 DEVICE TYPE DISK;

    ALLOCATE AUXILIARY CHANNEL stby1 DEVICE TYPE DISK;

    DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE 

    DORECOVER 

    NOFILENAMECHECK;

    RELEASE CHANNEL prmy1;

    RELEASE CHANNEL stby1;

}

EOF


# Step 5: Mount Standby and Start Redo Apply

log "Mounting standby database and starting redo apply..."

run_sql "ALTER DATABASE MOUNT STANDBY DATABASE;"

run_sql "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;"


# Step 6: Enable Active Data Guard (Optional)

log "Enabling Active Data Guard (Optional)..."

run_sql "ALTER DATABASE OPEN READ ONLY;"


# Step 7: Add TNS entries to tnsnames.ora

log "Adding TNS entries to tnsnames.ora..."

TNSNAMES_PATH="$ORACLE_HOME/network/admin/tnsnames.ora"


# Append Primary TNS Entry

{

  echo "${PRIMARY_TNS_ENTRY[@]}"

} >> $TNSNAMES_PATH


# Append Standby TNS Entry

{

  echo "${STANDBY_TNS_ENTRY[@]}"

} >> $TNSNAMES_PATH


log "TNS entries added to tnsnames.ora."


log "Standby database configuration completed successfully!"

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