$ cat DROP_CDB.sh
#!/bin/bash
############################################################
# #
# Oracle RAC CDB Drop Utility Script #
# #
# Author : name #
# Version : 1.0 #
# Created : 2025-Nov #
# Purpose : Safely drop an Oracle 19c RAC CDB #
# with validation, logging, and cleanup #
# #
# Usage : ./DROP_CDB.sh #
# #
# Notes : #
# - Validates environment and Clusterware #
# - Checks and stops database if running #
# - Confirms PDB status #
# - Removes DB from Clusterware #
# - Drops DB via SQL*Plus #
# - Sends log summary via email #
# #
############################################################
#set -euo pipefail
# === CONFIGURATION ===
CDB_NAME="CDB_NAME"
ORACLE_HOME="HOME_PATH"
ORACLE_SID="SID" #Check in oratab make sure case sensitive
EMAIL_TO="Your_mail"
LOGFILE="/home/oracle/drop_${CDB_NAME}_$(date +%Y%m%d_%H%M%S).log"
EMCTL_LOC="AGENT_HOME/bin/emctl"
# === Trap to send email on exit ===
trap 'send_email' EXIT
# === FLAGS ===
SKIP_BACKUP=false
# Colors
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[33m'
NC='\033[0m'
# === FUNCTIONS ===
log() {
echo -e "$(date '+%F %T') - $1" | tee -a "$LOGFILE"
}
warn() {
log "${YELLOW} $*${NC}"
}
red() {
log "${RED} $*${NC}"
}
set_db_environment() {
log "------------------------------------------------"
log "Setting Oracle environment for CDB: $CDB_NAME"
export ORACLE_HOME="$ORACLE_HOME"
# export ORACLE_SID="${CDB_NAME%%_*}1" # Assumes SID is prefix + instance number
export ORACLE_SID=$ORACLE_SID
export PATH="$ORACLE_HOME/bin:$PATH"
export LD_LIBRARY_PATH="$ORACLE_HOME/lib"
export TNS_ADMIN="$ORACLE_HOME/network/admin"
log "ORACLE_HOME = $ORACLE_HOME"
log "ORACLE_SID = $ORACLE_SID"
#log "PATH = $PATH"
#log "LD_LIBRARY_PATH = $LD_LIBRARY_PATH"
#log "TNS_ADMIN = $TNS_ADMIN"
}
validate_env() {
log "------------------------------------------------"
log "Validating Oracle environment variables..."
log "ORACLE_HOME = $ORACLE_HOME"
log "ORACLE_SID = $ORACLE_SID"
if [ -z "$ORACLE_HOME" ] || [ ! -d "$ORACLE_HOME" ]; then
log "ERROR: ORACLE_HOME is not set correctly or directory does not exist. Aborting."
exit 1
fi
if [ -z "$ORACLE_SID" ]; then
log "ERROR: ORACLE_SID is not set. Aborting."
exit 1
fi
# Check SID in /etc/oratab
if grep -q "$ORACLE_SID:" /etc/oratab; then
log "SID $ORACLE_SID found in /etc/oratab."
else
warn "SID $ORACLE_SID not found in /etc/oratab."
log "Check ORACLE_SID value in script and oratab, current ORATAB has below value"
warn "`cat /etc/oratab | grep -i $ORACLE_SID ` "
read -p "Continue anyway? (Y/N): " CONTINUE
[[ "$CONTINUE" =~ ^[Yy]$ ]] || exit 1
fi
# Check ORACLE_HOME match
EXPECTED_HOME=$(grep -i "${ORACLE_SID}:" /etc/oratab | cut -d: -f2)
if [ "$EXPECTED_HOME" != "$ORACLE_HOME" ]; then
warn "ORACLE_HOME mismatch: expected $EXPECTED_HOME, using $ORACLE_HOME"
read -p "Continue anyway? (Y/N): " CONTINUE
[[ "$CONTINUE" =~ ^[Yy]$ ]] || exit 1
else
log "ORACLE_HOME matches /etc/oratab entry."
fi
CDB_PREFIX="${CDB_NAME%%_*}"
if [[ "$ORACLE_SID^^" != "${CDB_PREFIX^^}"* ]]; then
warn "WARNING: ORACLE_SID ($ORACLE_SID) does not match expected CDB name prefix ($CDB_PREFIX)."
read -p "Continue anyway? (Y/N): " ENV_CONFIRM
case "$ENV_CONFIRM" in
[Yy]) log "User chose to continue despite SID mismatch." ;;
*) log "Aborting due to SID mismatch."; exit 1 ;;
esac
else
log "ORACLE_SID matches expected CDB prefix ($CDB_PREFIX)."
fi
}
confirm_backup_exists() {
log "------------------------------------------------"
echo -e "\n️ Have you verified a full RMAN backup exists for $CDB_NAME?"
read -p "Type Y to confirm or N to review options: " BACKUP_CONFIRM
case "$BACKUP_CONFIRM" in
[Yy])
log "User confirmed backup exists."
;;
[Nn])
echo -e "\n❓ You chose not to confirm backup. What would you like to do?"
echo " 1. Abort the script"
echo " 2. Skip backup check and continue"
read -p "Enter 1 or 2: " CHOICE
case "$CHOICE" in
1)
log "User aborted due to unconfirmed backup."
exit 1
;;
2)
log "User chose to skip backup confirmation and continue."
SKIP_BACKUP=true
;;
*)
log "Invalid input. Aborting."
exit 1
;;
esac
;;
*)
log "Invalid input. Aborting."
exit 1
;;
esac
}
check_blackout_status() {
log "------------------------------------------------"
log "Checking if $CDB_NAME is discovered by OEM agent..."
if ${EMCTL_LOC} config agent listtargets | grep -iq "$CDB_NAME"; then
log "$CDB_NAME is in OEM."
BLACKOUT_STATUS=$(${EMCTL_LOC} status blackout -target_name "$CDB_NAME" -target_type oracle_database 2>/dev/null)
if [ $? -eq 0 ] && echo "$BLACKOUT_STATUS" | grep -q "Blackoutname"; then
# At least one blackout exists on this target
BLACKOUT_NAME=$(echo "$BLACKOUT_STATUS" | grep Blackoutname | head -1 | awk '{print $3}')
REASON=$(echo "$BLACKOUT_STATUS" | grep Reason | head -1 | cut -d: -f2- | sed 's/^ *//')
warn " $CDB_NAME - Database is in OEM blackout!"
warn " Blackout Name : $BLACKOUT_NAME"
warn " Reason : $REASON"
#echo " Full details:"
#echo " $BLACKOUT_STATUS"
else
warn "⚠️ $CDB_NAME is NOT in OEM blackout."
echo "This may trigger alerts or incidents in OEM during the drop." | tee -a "$LOGFILE"
echo "Choose an option:" | tee -a "$LOGFILE"
echo " 1. Abort the script and schedule blackout manually" | tee -a "$LOGFILE"
echo " 2. Continue with drop anyway" | tee -a "$LOGFILE"
read -p "Enter 1 or 2: " CHOICE
case "$CHOICE" in
1)
log "User aborted drop script to schedule blackout manually."
exit 0
;;
2)
log "User chose to continue without blackout."
;;
*)
log "Invalid input. Aborting."
exit 1
;;
esac
fi
# echo "Please check blackout status manually in OEM Console or via EMCLI on the OMS server." | tee -a "$LOGFILE"
else
log "$CDB_NAME is not listed in agent targets. Skipping blackout check."
fi
}
check_db_role() {
log "------------------------------------------------"
log "Checking Data Guard role..."
DG_ROLE=$(sqlplus -s / as sysdba <<EOF
set heading off timing off feedback off
select database_role from v\$database;
exit;
EOF
)
log "Database Role = ${DG_ROLE} "
if [[ "$DG_ROLE" == "PHYSICAL STANDBY" ]]; then
red "${RED}This is a PHYSICAL STANDBY. Cannot drop standby CDB."
exit 1
fi
if [[ "$DG_ROLE" == "PRIMARY" ]]; then
warn "This is a PRIMARY database. Ensure standby is healthy."
fi
}
check_cluster_resources() {
log "Checking Clusterware resources and their status for $CDB_NAME..."
echo -e "\n--------------------------------------------------" | tee -a "$LOGFILE"
echo "### Clusterware Resources and Status for $CDB_NAME ###" | tee -a "$LOGFILE"
echo "--------------------------------------------------" | tee -a "$LOGFILE"
crsctl status resource | grep -i "$CDB_NAME" | while read -r line; do
RESOURCE=$(echo "$line" | awk '{print $1}')
STATUS=$(crsctl status resource "$RESOURCE" | grep -i "STATE=" | awk -F= '{print $2}' | xargs)
echo "Resource: $RESOURCE | Status: $STATUS" | tee -a "$LOGFILE"
done
echo "--------------------------------------------------" | tee -a "$LOGFILE"
}
validate_clusterware() {
log "------------------------------------------------"
log "Checking Clusterware status..."
# Check for CRS daemons (works on RAC or VM)
if ps -ef | grep -E 'crsd.bin|ocssd.bin|evmd.bin' | grep -v grep >/dev/null; then
log "Clusterware daemons are running."
else
log "Clusterware daemons not found — assuming single-instance or CRS is down."
fi
}
validate_database_exists() {
log "------------------------------------------------"
log "Validating database existence..."
srvctl config database -d "$CDB_NAME" >> "$LOGFILE" 2>&1
if [ $? -ne 0 ]; then
log "ERROR: Database '$CDB_NAME' not found in Clusterware. Aborting."
exit 1
fi
}
check_database_running() {
log "------------------------------------------------"
log "Checking database status..."
export ORACLE_HOME ORACLE_SID PATH=$ORACLE_HOME/bin:$PATH
STATUS=$(srvctl status database -d "$CDB_NAME" 2>>"$LOGFILE")
#echo "$STATUS" >> "$LOGFILE"
red "$STATUS"
RUNNING_COUNT=$(echo "$STATUS" | grep -i "Database is running" | wc -l)
if [ "$RUNNING_COUNT" -eq 0 ]; then
log "✅ Database is NOT running on any node."
DB_RUNNING=false
VALID_FOR_PDBS=false
else
log "⚠️ Database is running on $RUNNING_COUNT node(s)."
DB_RUNNING=true
VALID_FOR_PDBS=true
log "Checking for active sessions..."
ACTIVE_SESSIONS=$(${ORACLE_HOME}/bin/sqlplus -s / as sysdba <<EOF
set heading off feedback off timing off
select count(*) from v\$session where status='ACTIVE' and username is not null and type='USER';
exit;
EOF
)
SESSION_COUNT=$(echo "$ACTIVE_SESSIONS" | tr -d '[:space:]')
log "Active user sessions: $ACTIVE_SESSIONS"
if [[ $SESSION_COUNT -gt 0 ]]; then
warn "There are active sessions. Consider disconnecting users."
echo "Details:"
${ORACLE_HOME}/bin/sqlplus -s / as sysdba <<EOF | tee -a "$LOGFILE"
SET LINESIZE 200
set heading on feedback off timing off
COL USERNAME FORMAT A20
COL MACHINE FORMAT A30
COL PROGRAM FORMAT A30
SELECT SID, SERIAL#, USERNAME, STATUS, MACHINE, PROGRAM FROM v\$session WHERE type = 'USER' AND status = 'ACTIVE' and username is not null;
EOF
read -p "Continue anyway? (y/N): " sess_confirm
[[ "$sess_confirm" =~ ^[Yy]$ ]] || exit 1
fi
check_db_role
fi
}
stop_database_if_running() {
if [ "$DB_RUNNING" = true ]; then
log "------------------------------------------------"
warn "\n⚠️ Database is running on one or more nodes."
read -p "Do you want to stop the database before proceeding? (Y/N): " STOP_CONFIRM
case "$STOP_CONFIRM" in
[Yy])
log "User confirmed to stop the database."
log "Stopping database on all nodes..."
srvctl stop database -d "$CDB_NAME" >> "$LOGFILE" 2>&1
VALID_FOR_PDBS=true
;;
[Nn])
log "User choose NOT to stop the database."
warn "❌ Without stopping the CDB and keeping in Restricted Mode, drop is not possible. Aborting."
exit 1
#VALID_FOR_PDBS=false
;;
*)
log "Invalid input. Aborting."
exit 1
;;
esac
else
warn "Database is already stopped — skipping stop prompt."
VALID_FOR_PDBS=false
fi
}
confirm_database_stopped() {
log "------------------------------------------------"
log "Confirming database status after stop attempt..."
#STATUS=$(srvctl status database -d "$CDB_NAME" 2>>"$LOGFILE")
STATUS=$(srvctl status database -d "$CDB_NAME")
#warn "$STATUS" | tee -a "$LOGFILE"
warn "$STATUS"
RUNNING_COUNT=$(echo "$STATUS" | grep -i "Database is running" | wc -l)
if [ "$RUNNING_COUNT" -eq 0 ]; then
log "✅ Database is confirmed to be stopped on all nodes."
else
log "❌ WARNING: Database is still running on $RUNNING_COUNT node(s)."
echo "$STATUS" | tee -a "$LOGFILE"
read -p "Database still appears to be running. Do you want to continue anyway? (Y/N): " CONTINUE_ANYWAY
case "$CONTINUE_ANYWAY" in
[Yy]) log "User chose to continue despite database still running." ;;
*) log "Aborting due to running database."; exit 1 ;;
esac
fi
}
validate_pdbs() {
log "Checking PDBs and their status..."
export ORACLE_HOME ORACLE_SID PATH=$ORACLE_HOME/bin:$PATH
echo -e "\n--------------------------------------------------" | tee -a "$LOGFILE"
echo "### SQL*Plus Output: PDB Status ###" | tee -a "$LOGFILE"
echo "--------------------------------------------------" | tee -a "$LOGFILE"
sqlplus -s / as sysdba <<EOF | tee -a "$LOGFILE"
SET LINESIZE 200 PAGESIZE 100 timing off feedback off
COLUMN NAME FORMAT A30
COLUMN OPEN_MODE FORMAT A20
SELECT NAME, OPEN_MODE FROM V\$PDBS;
EOF
echo "--------------------------------------------------" | tee -a "$LOGFILE"
}
confirm_drop() {
warn "\n⚠️ WARNING: You are about to DROP the RAC CDB '$CDB_NAME'. This action is irreversible."
read -p "Do you want to continue? (Y/N): " CONFIRM
case "$CONFIRM" in
[Yy]) log "User confirmed drop operation." ;;
[Nn]) log "User aborted drop operation."; exit 0 ;;
*) log "Invalid input. Aborting."; exit 1 ;;
esac
}
remove_from_clusterware() {
log "------------------------------------------------"
log "Removing database from Clusterware..."
srvctl remove database -d "$CDB_NAME" -f >> "$LOGFILE" 2>&1
}
drop_database_sql() {
log "------------------------------------------------"
log "Dropping $CDB_NAME database using SQL*Plus..."
export ORACLE_HOME ORACLE_SID PATH=$ORACLE_HOME/bin:$PATH
echo -e "\n--------------------------------------------------" | tee -a "$LOGFILE"
echo "### SQL*Plus Output: DROP DATABASE ###" | tee -a "$LOGFILE"
echo "--------------------------------------------------" | tee -a "$LOGFILE"
sqlplus -s / as sysdba <<EOF | tee -a "$LOGFILE"
set timing on feedback on
STARTUP MOUNT EXCLUSIVE RESTRICT;
PROMPT "Setting cluster_database=FALSE in SPFILE..."
ALTER SYSTEM SET cluster_database=FALSE SCOPE=SPFILE;
shutdown immediate
STARTUP MOUNT EXCLUSIVE RESTRICT;
select name,open_mode from v\$database;
DROP DATABASE;
EOF
if [[ $? -eq 0 ]]; then
warn "CDB $CDB_NAME dropped successfully."
remove_from_clusterware
check_cluster_resources
comment_oratab
generate_service_removal_statements
else
warn "DROP DATABASE command failed."
fi
echo "--------------------------------------------------" | tee -a "$LOGFILE"
}
generate_service_removal_statements() {
log "------------------------------------------------"
log "Generating service removal commands..."
SERVICES=$(srvctl config service -d "$CDB_NAME" 2>/dev/null | grep "^Service name:" | awk -F: '{print $2}' | xargs)
if [ -z "$SERVICES" ]; then
warn "No services found."
else
echo "Generated commands:" | tee -a "$LOGFILE"
for SERVICE in $SERVICES; do
#echo "srvctl remove service -d $CDB_NAME -s $SERVICE" | tee -a "$LOGFILE"
warn "srvctl remove service -d $CDB_NAME -s $SERVICE"
done
fi
}
comment_oratab() {
log "Commenting out $CDB_NAME from /etc/oratab..."
for ENTRY in "$CDB_NAME" "$ORACLE_SID"; do
if grep -q "^$ENTRY:" /etc/oratab; then
sed -i "s/^$ENTRY:/#$ENTRY:/" /etc/oratab
echo "=> Commented out entry for $ENTRY:"
grep "^#$ENTRY:" /etc/oratab
fi
done
}
send_email() {
EXIT_CODE=$?
SUBJECT="CDB $CDB_NAME Drop script output Report (Exit Code: $EXIT_CODE)"
echo "$(date '+%F %T') - Sending email to $EMAIL_TO with exit code $EXIT_CODE..." >> "$LOGFILE"
BODY=$(cat "$LOGFILE")
#echo "$BODY" | mailx -s "$SUBJECT" "$EMAIL_TO"
cat "${LOGFILE}" | mail -s "$SUBJECT" "$EMAIL_TO"
echo -e "\n Check the Full Logfile here : $LOGFILE " | tee -a "$LOGFILE"
}
############################
# ===[ MAIN EXECUTION ]=== #
############################
log "=== Starting RAC CDB Drop Script for $CDB_NAME ==="
set_db_environment
validate_env
check_blackout_status
validate_clusterware
confirm_backup_exists
check_cluster_resources ## Before DROP
validate_database_exists
check_database_running
# Only check PDBs if CDB is running
if [ "$VALID_FOR_PDBS" = true ]; then
validate_pdbs
fi
stop_database_if_running
confirm_database_stopped
confirm_drop
drop_database_sql
#check_cluster_resources # After DROP
log "=== Drop operation completed ==="
echo -e "\n==================================================" | tee -a "$LOGFILE"
echo "### FINAL NOTES: Post-Drop Cleanup Checklist ###" | tee -a "$LOGFILE"
echo "==================================================" | tee -a "$LOGFILE"
echo "- Remove database entry from Oracle Enterprise Manager (OEM)." | tee -a "$LOGFILE"
echo "- ️ Review and remove or uncomment any backup jobs from CRON or RMAN scripts." | tee -a "$LOGFILE"
echo "- Update CMDB (Configuration Management Database) to reflect database decommission." | tee -a "$LOGFILE"
echo "- Clean up any related ASM disk groups or file system directories if applicable." | tee -a "$LOGFILE"
echo "- Revoke access or credentials tied to this CDB from monitoring or automation tools." | tee -a "$LOGFILE"
echo "==================================================" | tee -a "$LOGFILE"
#!/bin/bash
############################################################
# #
# Oracle RAC CDB Drop Utility Script #
# #
# Author : name #
# Version : 1.0 #
# Created : 2025-Nov #
# Purpose : Safely drop an Oracle 19c RAC CDB #
# with validation, logging, and cleanup #
# #
# Usage : ./DROP_CDB.sh #
# #
# Notes : #
# - Validates environment and Clusterware #
# - Checks and stops database if running #
# - Confirms PDB status #
# - Removes DB from Clusterware #
# - Drops DB via SQL*Plus #
# - Sends log summary via email #
# #
############################################################
#set -euo pipefail
# === CONFIGURATION ===
CDB_NAME="CDB_NAME"
ORACLE_HOME="HOME_PATH"
ORACLE_SID="SID" #Check in oratab make sure case sensitive
EMAIL_TO="Your_mail"
LOGFILE="/home/oracle/drop_${CDB_NAME}_$(date +%Y%m%d_%H%M%S).log"
EMCTL_LOC="AGENT_HOME/bin/emctl"
# === Trap to send email on exit ===
trap 'send_email' EXIT
# === FLAGS ===
SKIP_BACKUP=false
# Colors
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[33m'
NC='\033[0m'
# === FUNCTIONS ===
log() {
echo -e "$(date '+%F %T') - $1" | tee -a "$LOGFILE"
}
warn() {
log "${YELLOW} $*${NC}"
}
red() {
log "${RED} $*${NC}"
}
set_db_environment() {
log "------------------------------------------------"
log "Setting Oracle environment for CDB: $CDB_NAME"
export ORACLE_HOME="$ORACLE_HOME"
# export ORACLE_SID="${CDB_NAME%%_*}1" # Assumes SID is prefix + instance number
export ORACLE_SID=$ORACLE_SID
export PATH="$ORACLE_HOME/bin:$PATH"
export LD_LIBRARY_PATH="$ORACLE_HOME/lib"
export TNS_ADMIN="$ORACLE_HOME/network/admin"
log "ORACLE_HOME = $ORACLE_HOME"
log "ORACLE_SID = $ORACLE_SID"
#log "PATH = $PATH"
#log "LD_LIBRARY_PATH = $LD_LIBRARY_PATH"
#log "TNS_ADMIN = $TNS_ADMIN"
}
validate_env() {
log "------------------------------------------------"
log "Validating Oracle environment variables..."
log "ORACLE_HOME = $ORACLE_HOME"
log "ORACLE_SID = $ORACLE_SID"
if [ -z "$ORACLE_HOME" ] || [ ! -d "$ORACLE_HOME" ]; then
log "ERROR: ORACLE_HOME is not set correctly or directory does not exist. Aborting."
exit 1
fi
if [ -z "$ORACLE_SID" ]; then
log "ERROR: ORACLE_SID is not set. Aborting."
exit 1
fi
# Check SID in /etc/oratab
if grep -q "$ORACLE_SID:" /etc/oratab; then
log "SID $ORACLE_SID found in /etc/oratab."
else
warn "SID $ORACLE_SID not found in /etc/oratab."
log "Check ORACLE_SID value in script and oratab, current ORATAB has below value"
warn "`cat /etc/oratab | grep -i $ORACLE_SID ` "
read -p "Continue anyway? (Y/N): " CONTINUE
[[ "$CONTINUE" =~ ^[Yy]$ ]] || exit 1
fi
# Check ORACLE_HOME match
EXPECTED_HOME=$(grep -i "${ORACLE_SID}:" /etc/oratab | cut -d: -f2)
if [ "$EXPECTED_HOME" != "$ORACLE_HOME" ]; then
warn "ORACLE_HOME mismatch: expected $EXPECTED_HOME, using $ORACLE_HOME"
read -p "Continue anyway? (Y/N): " CONTINUE
[[ "$CONTINUE" =~ ^[Yy]$ ]] || exit 1
else
log "ORACLE_HOME matches /etc/oratab entry."
fi
CDB_PREFIX="${CDB_NAME%%_*}"
if [[ "$ORACLE_SID^^" != "${CDB_PREFIX^^}"* ]]; then
warn "WARNING: ORACLE_SID ($ORACLE_SID) does not match expected CDB name prefix ($CDB_PREFIX)."
read -p "Continue anyway? (Y/N): " ENV_CONFIRM
case "$ENV_CONFIRM" in
[Yy]) log "User chose to continue despite SID mismatch." ;;
*) log "Aborting due to SID mismatch."; exit 1 ;;
esac
else
log "ORACLE_SID matches expected CDB prefix ($CDB_PREFIX)."
fi
}
confirm_backup_exists() {
log "------------------------------------------------"
echo -e "\n️ Have you verified a full RMAN backup exists for $CDB_NAME?"
read -p "Type Y to confirm or N to review options: " BACKUP_CONFIRM
case "$BACKUP_CONFIRM" in
[Yy])
log "User confirmed backup exists."
;;
[Nn])
echo -e "\n❓ You chose not to confirm backup. What would you like to do?"
echo " 1. Abort the script"
echo " 2. Skip backup check and continue"
read -p "Enter 1 or 2: " CHOICE
case "$CHOICE" in
1)
log "User aborted due to unconfirmed backup."
exit 1
;;
2)
log "User chose to skip backup confirmation and continue."
SKIP_BACKUP=true
;;
*)
log "Invalid input. Aborting."
exit 1
;;
esac
;;
*)
log "Invalid input. Aborting."
exit 1
;;
esac
}
check_blackout_status() {
log "------------------------------------------------"
log "Checking if $CDB_NAME is discovered by OEM agent..."
if ${EMCTL_LOC} config agent listtargets | grep -iq "$CDB_NAME"; then
log "$CDB_NAME is in OEM."
BLACKOUT_STATUS=$(${EMCTL_LOC} status blackout -target_name "$CDB_NAME" -target_type oracle_database 2>/dev/null)
if [ $? -eq 0 ] && echo "$BLACKOUT_STATUS" | grep -q "Blackoutname"; then
# At least one blackout exists on this target
BLACKOUT_NAME=$(echo "$BLACKOUT_STATUS" | grep Blackoutname | head -1 | awk '{print $3}')
REASON=$(echo "$BLACKOUT_STATUS" | grep Reason | head -1 | cut -d: -f2- | sed 's/^ *//')
warn " $CDB_NAME - Database is in OEM blackout!"
warn " Blackout Name : $BLACKOUT_NAME"
warn " Reason : $REASON"
#echo " Full details:"
#echo " $BLACKOUT_STATUS"
else
warn "⚠️ $CDB_NAME is NOT in OEM blackout."
echo "This may trigger alerts or incidents in OEM during the drop." | tee -a "$LOGFILE"
echo "Choose an option:" | tee -a "$LOGFILE"
echo " 1. Abort the script and schedule blackout manually" | tee -a "$LOGFILE"
echo " 2. Continue with drop anyway" | tee -a "$LOGFILE"
read -p "Enter 1 or 2: " CHOICE
case "$CHOICE" in
1)
log "User aborted drop script to schedule blackout manually."
exit 0
;;
2)
log "User chose to continue without blackout."
;;
*)
log "Invalid input. Aborting."
exit 1
;;
esac
fi
# echo "Please check blackout status manually in OEM Console or via EMCLI on the OMS server." | tee -a "$LOGFILE"
else
log "$CDB_NAME is not listed in agent targets. Skipping blackout check."
fi
}
check_db_role() {
log "------------------------------------------------"
log "Checking Data Guard role..."
DG_ROLE=$(sqlplus -s / as sysdba <<EOF
set heading off timing off feedback off
select database_role from v\$database;
exit;
EOF
)
log "Database Role = ${DG_ROLE} "
if [[ "$DG_ROLE" == "PHYSICAL STANDBY" ]]; then
red "${RED}This is a PHYSICAL STANDBY. Cannot drop standby CDB."
exit 1
fi
if [[ "$DG_ROLE" == "PRIMARY" ]]; then
warn "This is a PRIMARY database. Ensure standby is healthy."
fi
}
check_cluster_resources() {
log "Checking Clusterware resources and their status for $CDB_NAME..."
echo -e "\n--------------------------------------------------" | tee -a "$LOGFILE"
echo "### Clusterware Resources and Status for $CDB_NAME ###" | tee -a "$LOGFILE"
echo "--------------------------------------------------" | tee -a "$LOGFILE"
crsctl status resource | grep -i "$CDB_NAME" | while read -r line; do
RESOURCE=$(echo "$line" | awk '{print $1}')
STATUS=$(crsctl status resource "$RESOURCE" | grep -i "STATE=" | awk -F= '{print $2}' | xargs)
echo "Resource: $RESOURCE | Status: $STATUS" | tee -a "$LOGFILE"
done
echo "--------------------------------------------------" | tee -a "$LOGFILE"
}
validate_clusterware() {
log "------------------------------------------------"
log "Checking Clusterware status..."
# Check for CRS daemons (works on RAC or VM)
if ps -ef | grep -E 'crsd.bin|ocssd.bin|evmd.bin' | grep -v grep >/dev/null; then
log "Clusterware daemons are running."
else
log "Clusterware daemons not found — assuming single-instance or CRS is down."
fi
}
validate_database_exists() {
log "------------------------------------------------"
log "Validating database existence..."
srvctl config database -d "$CDB_NAME" >> "$LOGFILE" 2>&1
if [ $? -ne 0 ]; then
log "ERROR: Database '$CDB_NAME' not found in Clusterware. Aborting."
exit 1
fi
}
check_database_running() {
log "------------------------------------------------"
log "Checking database status..."
export ORACLE_HOME ORACLE_SID PATH=$ORACLE_HOME/bin:$PATH
STATUS=$(srvctl status database -d "$CDB_NAME" 2>>"$LOGFILE")
#echo "$STATUS" >> "$LOGFILE"
red "$STATUS"
RUNNING_COUNT=$(echo "$STATUS" | grep -i "Database is running" | wc -l)
if [ "$RUNNING_COUNT" -eq 0 ]; then
log "✅ Database is NOT running on any node."
DB_RUNNING=false
VALID_FOR_PDBS=false
else
log "⚠️ Database is running on $RUNNING_COUNT node(s)."
DB_RUNNING=true
VALID_FOR_PDBS=true
log "Checking for active sessions..."
ACTIVE_SESSIONS=$(${ORACLE_HOME}/bin/sqlplus -s / as sysdba <<EOF
set heading off feedback off timing off
select count(*) from v\$session where status='ACTIVE' and username is not null and type='USER';
exit;
EOF
)
SESSION_COUNT=$(echo "$ACTIVE_SESSIONS" | tr -d '[:space:]')
log "Active user sessions: $ACTIVE_SESSIONS"
if [[ $SESSION_COUNT -gt 0 ]]; then
warn "There are active sessions. Consider disconnecting users."
echo "Details:"
${ORACLE_HOME}/bin/sqlplus -s / as sysdba <<EOF | tee -a "$LOGFILE"
SET LINESIZE 200
set heading on feedback off timing off
COL USERNAME FORMAT A20
COL MACHINE FORMAT A30
COL PROGRAM FORMAT A30
SELECT SID, SERIAL#, USERNAME, STATUS, MACHINE, PROGRAM FROM v\$session WHERE type = 'USER' AND status = 'ACTIVE' and username is not null;
EOF
read -p "Continue anyway? (y/N): " sess_confirm
[[ "$sess_confirm" =~ ^[Yy]$ ]] || exit 1
fi
check_db_role
fi
}
stop_database_if_running() {
if [ "$DB_RUNNING" = true ]; then
log "------------------------------------------------"
warn "\n⚠️ Database is running on one or more nodes."
read -p "Do you want to stop the database before proceeding? (Y/N): " STOP_CONFIRM
case "$STOP_CONFIRM" in
[Yy])
log "User confirmed to stop the database."
log "Stopping database on all nodes..."
srvctl stop database -d "$CDB_NAME" >> "$LOGFILE" 2>&1
VALID_FOR_PDBS=true
;;
[Nn])
log "User choose NOT to stop the database."
warn "❌ Without stopping the CDB and keeping in Restricted Mode, drop is not possible. Aborting."
exit 1
#VALID_FOR_PDBS=false
;;
*)
log "Invalid input. Aborting."
exit 1
;;
esac
else
warn "Database is already stopped — skipping stop prompt."
VALID_FOR_PDBS=false
fi
}
confirm_database_stopped() {
log "------------------------------------------------"
log "Confirming database status after stop attempt..."
#STATUS=$(srvctl status database -d "$CDB_NAME" 2>>"$LOGFILE")
STATUS=$(srvctl status database -d "$CDB_NAME")
#warn "$STATUS" | tee -a "$LOGFILE"
warn "$STATUS"
RUNNING_COUNT=$(echo "$STATUS" | grep -i "Database is running" | wc -l)
if [ "$RUNNING_COUNT" -eq 0 ]; then
log "✅ Database is confirmed to be stopped on all nodes."
else
log "❌ WARNING: Database is still running on $RUNNING_COUNT node(s)."
echo "$STATUS" | tee -a "$LOGFILE"
read -p "Database still appears to be running. Do you want to continue anyway? (Y/N): " CONTINUE_ANYWAY
case "$CONTINUE_ANYWAY" in
[Yy]) log "User chose to continue despite database still running." ;;
*) log "Aborting due to running database."; exit 1 ;;
esac
fi
}
validate_pdbs() {
log "Checking PDBs and their status..."
export ORACLE_HOME ORACLE_SID PATH=$ORACLE_HOME/bin:$PATH
echo -e "\n--------------------------------------------------" | tee -a "$LOGFILE"
echo "### SQL*Plus Output: PDB Status ###" | tee -a "$LOGFILE"
echo "--------------------------------------------------" | tee -a "$LOGFILE"
sqlplus -s / as sysdba <<EOF | tee -a "$LOGFILE"
SET LINESIZE 200 PAGESIZE 100 timing off feedback off
COLUMN NAME FORMAT A30
COLUMN OPEN_MODE FORMAT A20
SELECT NAME, OPEN_MODE FROM V\$PDBS;
EOF
echo "--------------------------------------------------" | tee -a "$LOGFILE"
}
confirm_drop() {
warn "\n⚠️ WARNING: You are about to DROP the RAC CDB '$CDB_NAME'. This action is irreversible."
read -p "Do you want to continue? (Y/N): " CONFIRM
case "$CONFIRM" in
[Yy]) log "User confirmed drop operation." ;;
[Nn]) log "User aborted drop operation."; exit 0 ;;
*) log "Invalid input. Aborting."; exit 1 ;;
esac
}
remove_from_clusterware() {
log "------------------------------------------------"
log "Removing database from Clusterware..."
srvctl remove database -d "$CDB_NAME" -f >> "$LOGFILE" 2>&1
}
drop_database_sql() {
log "------------------------------------------------"
log "Dropping $CDB_NAME database using SQL*Plus..."
export ORACLE_HOME ORACLE_SID PATH=$ORACLE_HOME/bin:$PATH
echo -e "\n--------------------------------------------------" | tee -a "$LOGFILE"
echo "### SQL*Plus Output: DROP DATABASE ###" | tee -a "$LOGFILE"
echo "--------------------------------------------------" | tee -a "$LOGFILE"
sqlplus -s / as sysdba <<EOF | tee -a "$LOGFILE"
set timing on feedback on
STARTUP MOUNT EXCLUSIVE RESTRICT;
PROMPT "Setting cluster_database=FALSE in SPFILE..."
ALTER SYSTEM SET cluster_database=FALSE SCOPE=SPFILE;
shutdown immediate
STARTUP MOUNT EXCLUSIVE RESTRICT;
select name,open_mode from v\$database;
DROP DATABASE;
EOF
if [[ $? -eq 0 ]]; then
warn "CDB $CDB_NAME dropped successfully."
remove_from_clusterware
check_cluster_resources
comment_oratab
generate_service_removal_statements
else
warn "DROP DATABASE command failed."
fi
echo "--------------------------------------------------" | tee -a "$LOGFILE"
}
generate_service_removal_statements() {
log "------------------------------------------------"
log "Generating service removal commands..."
SERVICES=$(srvctl config service -d "$CDB_NAME" 2>/dev/null | grep "^Service name:" | awk -F: '{print $2}' | xargs)
if [ -z "$SERVICES" ]; then
warn "No services found."
else
echo "Generated commands:" | tee -a "$LOGFILE"
for SERVICE in $SERVICES; do
#echo "srvctl remove service -d $CDB_NAME -s $SERVICE" | tee -a "$LOGFILE"
warn "srvctl remove service -d $CDB_NAME -s $SERVICE"
done
fi
}
comment_oratab() {
log "Commenting out $CDB_NAME from /etc/oratab..."
for ENTRY in "$CDB_NAME" "$ORACLE_SID"; do
if grep -q "^$ENTRY:" /etc/oratab; then
sed -i "s/^$ENTRY:/#$ENTRY:/" /etc/oratab
echo "=> Commented out entry for $ENTRY:"
grep "^#$ENTRY:" /etc/oratab
fi
done
}
send_email() {
EXIT_CODE=$?
SUBJECT="CDB $CDB_NAME Drop script output Report (Exit Code: $EXIT_CODE)"
echo "$(date '+%F %T') - Sending email to $EMAIL_TO with exit code $EXIT_CODE..." >> "$LOGFILE"
BODY=$(cat "$LOGFILE")
#echo "$BODY" | mailx -s "$SUBJECT" "$EMAIL_TO"
cat "${LOGFILE}" | mail -s "$SUBJECT" "$EMAIL_TO"
echo -e "\n Check the Full Logfile here : $LOGFILE " | tee -a "$LOGFILE"
}
############################
# ===[ MAIN EXECUTION ]=== #
############################
log "=== Starting RAC CDB Drop Script for $CDB_NAME ==="
set_db_environment
validate_env
check_blackout_status
validate_clusterware
confirm_backup_exists
check_cluster_resources ## Before DROP
validate_database_exists
check_database_running
# Only check PDBs if CDB is running
if [ "$VALID_FOR_PDBS" = true ]; then
validate_pdbs
fi
stop_database_if_running
confirm_database_stopped
confirm_drop
drop_database_sql
#check_cluster_resources # After DROP
log "=== Drop operation completed ==="
echo -e "\n==================================================" | tee -a "$LOGFILE"
echo "### FINAL NOTES: Post-Drop Cleanup Checklist ###" | tee -a "$LOGFILE"
echo "==================================================" | tee -a "$LOGFILE"
echo "- Remove database entry from Oracle Enterprise Manager (OEM)." | tee -a "$LOGFILE"
echo "- ️ Review and remove or uncomment any backup jobs from CRON or RMAN scripts." | tee -a "$LOGFILE"
echo "- Update CMDB (Configuration Management Database) to reflect database decommission." | tee -a "$LOGFILE"
echo "- Clean up any related ASM disk groups or file system directories if applicable." | tee -a "$LOGFILE"
echo "- Revoke access or credentials tied to this CDB from monitoring or automation tools." | tee -a "$LOGFILE"
echo "==================================================" | tee -a "$LOGFILE"
0 comments:
Post a Comment