Decommission v3

 #!/bin/bash


# Check if ORACLE_SID is provided

if [ -z "$1" ]; then

    echo "Error: ORACLE_SID not provided."

    echo "Usage: $0 <ORACLE_SID> <second_node_instance>"

    exit 1

fi


# Set environment variables

export ORACLE_SID=$1

export ORAENV_ASK=NO

. oraenv

second_node_instance="$2"

LOG_FILE="decommission_$ORACLE_SID.log"

ORACLE_HOME="<oracle_home>"


# Function to log messages

log() {

    echo "$(date +'%Y-%m-%d %H:%M:%S') - $1" | tee -a $LOG_FILE

}


# Function to execute SQL commands

run_sql() {

    echo "$1" | $ORACLE_HOME/bin/sqlplus -S "/ as sysdba"

}


# Function to execute DGMGRL commands

run_dgmgrl() {

    dgmgrl -silent <<EOF

connect /

$1

exit;

EOF

}


# Start decommissioning process

log "Decommissioning process started for ORACLE_SID: $ORACLE_SID"


# Retrieve the database unique name dynamically

db_unique_name=$(sqlplus -s / as sysdba <<EOF

set heading off

set feedback off

set pagesize 0

select db_unique_name from v\$database;

exit;

EOF

)

db_unique_name=$(echo $db_unique_name | xargs) # Trim spaces

log "Retrieved database unique name: $db_unique_name"


# Retrieve the SPFILE location dynamically from the database

spfile_location=$(sqlplus -s / as sysdba <<EOF

set heading off

set feedback off

set pagesize 0

select value from v\$parameter where name = 'spfile';

exit;

EOF

)

spfile_location=$(echo $spfile_location | xargs) # Trim spaces

log "Retrieved SPFILE location: $spfile_location"


# Stop all database instances to ensure only one node is used

log "Stopping all instances of the database: $db_unique_name"

srvctl stop database -d $db_unique_name 2>&1 | tee -a $LOG_FILE


# Start only the required instance to proceed with the PFILE and drop sequence

log "Starting instance: $ORACLE_SID for PFILE creation"

srvctl start instance -d $db_unique_name -i $ORACLE_SID 2>&1 | tee -a $LOG_FILE


# Create PFILE from SPFILE

log "Creating PFILE from SPFILE"

run_sql "CREATE PFILE='/tmp/init${ORACLE_SID}.ora' FROM SPFILE='${spfile_location}';" 2>&1 | tee -a $LOG_FILE


# Capturing and removing Data Guard configuration

log "Capturing Data Guard configuration using DGMGRL"

run_dgmgrl "show configuration;" 2>&1 | tee -a $LOG_FILE

run_dgmgrl "remove configuration;" 2>&1 | tee -a $LOG_FILE


# Stop the second instance explicitly to avoid conflicts

log "Stopping second instance: $second_node_instance"

srvctl stop instance -d $db_unique_name -i $second_node_instance 2>&1 | tee -a $LOG_FILE


# Execute database drop sequence

log "Executing database drop sequence"

run_sql "

ALTER SYSTEM SET cluster_database=FALSE SCOPE=SPFILE SID='*';

STARTUP MOUNT;

ALTER SYSTEM SET use_large_pages=TRUE SCOPE=SPFILE SID='*';

SHUTDOWN IMMEDIATE;

STARTUP MOUNT EXCLUSIVE RESTRICT;

DROP DATABASE;

" 2>&1 | tee -a $LOG_FILE


# Remove the database from SRVCTL

log "Removing the database from SRVCTL configuration"

srvctl remove database -d $db_unique_name -f 2>&1 | tee -a $LOG_FILE


# Clean up remaining files and directories

log "Cleaning up remaining files and directories"

rm -rf $ORACLE_HOME/dbs/init${ORACLE_SID}.ora 2>&1 | tee -a $LOG_FILE

rm -rf $ORACLE_BASE/diag/rdbms/$ORACLE_SID 2>&1 | tee -a $LOG_FILE

rm -rf /u01/app/oracle/admin/$ORACLE_SID/adump 2>&1 | tee -a $LOG_FILE


# Updating listener.ora and oratab to remove database entries

log "Updating listener.ora and oratab to remove database entries"

cp $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora.bkp.$(date +%F) 2>&1 | tee -a $LOG_FILE

cp $ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora.bkp.$(date +%F) 2>&1 | tee -a $LOG_FILE

sed -i "/$ORACLE_SID/d" $ORACLE_HOME/network/admin/listener.ora 2>&1 | tee -a $LOG_FILE

cp /etc/oratab /tmp/oratab.bkp.$(date +%F) 2>&1 | tee -a $LOG_FILE

sed "/$ORACLE_SID/d" /tmp/oratab.bkp.$(date +%F) > /tmp/oratab.new 2>&1 | tee -a $LOG_FILE

mv /tmp/oratab.new /etc/oratab 2>&1 | tee -a $LOG_FILE


# Remove ASM directories associated with the database

log "Removing ASM directories associated with the database"

asmcmd <<EOF 2>&1 | tee -a $LOG_FILE

cd +DATA

rm -rf $db_unique_name

cd +RECO

rm -rf $db_unique_name

EOF


log "Database $db_unique_name has been successfully decommissioned."

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