Showing posts with label Script. Show all posts
Showing posts with label Script. Show all posts

Thursday, December 11, 2025

DROP CDB Shell script

 $ 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;     --Uncomment this to DROP a DB
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"
 

Sunday, August 17, 2025

Convert to SNAPSHOT Standby Shell script

########################################################################### #!/bin/bash # #Purpose : This Script will put Phy Standby to SNAPSHOT Stanby #Usage : ./convert_standby_broker.sh [to_snapshot|to_physical] #

#Author : Chandra B ########################################################################### #Version - Initial Draft set -e #Configuration ORACLE_SID="db_sid" PRIMARY_DG_NAME="your_primary_dg_name" STANDBY_DG_NAME="db_name" DB_USER="sys" DB_PASSWORD="your_sys_password" ##DG_BROKER_CONFIG="/path/to/dg_broker_config_files" # Adjust to your DGMGRL config path EMAIL_TO="TO_EMAIL" EMAIL_SUBJECT="Oracle Snapshot Standby Conversion Alert" EMAIL_FROM="TO_EMAIL" if [[ -f /home/oracle/CB/Shell_convert_snapshot_standby.log ]]; then rm /home/oracle/CB/Shell_convert_snapshot_standby.log fi LOG_FILE="/home/oracle/CB/Shell_convert_snapshot_standby.log" #Set Oracle DB environment export ORACLE_HOME=/u01/app/rdbms/product/19c export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export ORACLE_SID #Function to log messages log_message() { echo -e "\n$(date '+%Y-%m-%d %H:%M:%S'): 1" |tee −a "{LOG_FILE}" } #Function to send email send_email() { local message="$1" log_message "Email sent: ${message}" cat ${LOG_FILE} | mailx -s "${EMAIL_SUBJECT}" -r "${EMAIL_FROM}" "${EMAIL_TO}" } #Function to check for errors and exit check_error() { if [ $1 -ne 0 ]; then log_message "Error: $2" send_email "Error during snapshot standby conversion: $2" exit 1 fi } check_prompt() { while true do (1) prompt and read command line argument read -p "Do you want to proceed ...?? " answer (2) handle the input we were given case $answer in [yY]* ) echo -e "\nEntered YES, Continuing script........\n" break;; [nN]* ) echo -e "\nEntered NO ........" echo -e "\nexiting script ........\n" exit;; ) echo "just enter Y or N, please.";; esac done }

run_sql() { local sql="$1" sqlplus -s "/ as sysdba" <<-EOSQL SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF TIMING OFF $sql EXIT; EOSQL } #Pre-conversion checks log_message "Starting pre-conversion checks..." #Check if database is a physical standby check_standby_role() { local role STANDBY_ROLE=$(run_sql "select database_role from v$database;") echo "$STANDBY_ROLE" } STANDBY_ROLE=$(echo "${STANDBY_ROLE}" | tr -d '[:space:]') echo ${STANDBY_ROLE} if [ "${STANDBY_ROLE}" != "PHYSICALSTANDBY" ]; then log_message "Error: Database is not a physical standby (Role: ${STANDBY_ROLE})" send_email "Conversion failed: Database is not a physical standby (Role: ${STANDBY_ROLE})" exit 1 fi log_message "Check passed: Database is a physical standby." #Check if Data Guard Broker is enabled BROKER_STATUS=$(dgmgrl -silent / "show configuration;" | grep -A 1 "Configuration Status" |grep -v "Configuration Status" | awk '{print $1}') if [ "${BROKER_STATUS}" != "SUCCESS" ]; then log_message "Error: Data Guard Broker configuration is not healthy (Status: ${BROKER_STATUS})" send_email "Conversion failed: Data Guard Broker configuration is not healthy (Status: ${BROKER_STATUS})" exit 1 fi log_message "Check passed: Data Guard Broker is healthy." #Check if flashback database is enabled (required for snapshot standby) flashback_status () { SET HEADING OFF Timing off FEEDBACK OFF; FLASHBACK_STATUS=$(sqlplus -s / as sysdba <<EOF SELECT FLASHBACK_ON FROM V$DATABASE; EXIT; EOF )

FLASHBACK_STATUS=$(echo "${FLASHBACK_STATUS}" | tr -d '[:space:]') if [ "${FLASHBACK_STATUS}" != "YES" ]; then log_message "Error: Flashback database is not enabled" send_email "Conversion failed: Flashback database is not enabled on the standby" exit 1 fi

log_message "Check passed: Flashback database is enabled." } flashback_status(); #Check if standby is in sync with primary LAG_SECONDS=$(sqlplus -s / as sysdba <<EOF SET HEADING OFF Timing off FEEDBACK OFF; SELECT VALUE FROM V$DATAGUARD_STATS WHERE NAME = 'apply lag'; EXIT; EOF ) LAG_SECONDS=$(echo "${LAG_SECONDS}" | tr -d '[:space:]') if [ -z "${LAG_SECONDS}" ] || [ "${LAG_SECONDS}" -gt 300 ]; then log_message "Error: Standby is not in sync with primary (Lag: ${LAG_SECONDS} seconds)" send_email "Conversion failed: Standby is not in sync with primary (Lag: ${LAG_SECONDS} seconds)" exit 1 fi log_message "Check passed: Standby is in sync with primary (Lag: ${LAG_SECONDS} seconds)." #Perform conversion to snapshot standby using DGMGRL check_prompt; convert_to_snapshot() { log_message "Starting conversion to snapshot standby..." ###---CCONVERT DATABASE ${STANDBY_DG_NAME} TO SNAPSHOT STANDBYYYYYYY; dgmgrl / < /tmp/dgmgrl_convert.log 2>&1 --##CONNECT "/ as sysdba"@${STANDBY_DG_NAME} show configuration; EXIT; EOF check_error $? "Failed to convert database to snapshot standby. Check /tmp/dgmgrl_convert.log for details." log_message "---[ Completed Conversion, Below is the Output ]--- " log_message "cat /tmp/dgmgrl_convert.log" #cat /tmp/dgmgrl_convert.log | tee -a ${LOG_FILE} } # Verify conversion SNAPSHOT_STATUS=$(sqlplus -s / as sysdba <<EOF SET HEADING OFF Timing off FEEDBACK OFF; SELECT DATABASE_ROLE FROM V$DATABASE; EXIT; EOF ) SNAPSHOT_STATUS=$(echo "${SNAPSHOT_STATUS}" | tr -d '[:space:]') if [ "${SNAPSHOT_STATUS}" != "SNAPSHOT_STANDBY" ]; then log_message "Error: Conversion failed. Database role is ${SNAPSHOT_STATUS}, expected SNAPSHOT_STANDBY" send_email "Conversion failed: Database role is ${SNAPSHOT_STATUS}, expected SNAPSHOT_STANDBY" exit 1 fi log_message "Conversion successful: Database is now a snapshot standby." # Verify read-write access sqlplus -s / as sysdba < /tmp/test_rw.log 2>&1 CREATE TABLE test_snapshot (id NUMBER); INSERT INTO test_snapshot VALUES (1); COMMIT; DROP TABLE test_snapshot; EXIT; EOF check_error $? "Failed to verify read-write access on snapshot standby. Check /tmp/test_rw.log for details." log_message "Check passed: Snapshot standby is read-write." # Send success email send_email "Successfully converted ${STANDBY_DG_NAME} to snapshot standby." log_message "Snapshot standby conversion completed successfully."

# Main script logic

main() { case "$1" in to_snapshot) convert_to_snapshot ;; to_physical) revert_to_physical ;; *) log "Usage: $0 [to_snapshot|to_physical]" exit 1 ;; esac } main $1 cat "${LOG_FILE}" exit 0 ### -- Revert Back NOT working yet

Monday, November 4, 2024

pre-post check script

 #######################################################
#!/bin/ksh
# set the DB env and run
# Script to check PRE & Post checks Manually
#
# CB - <Date>   - version 1 - Initial Draft
#set -x

echo -e "\n*************************************"
echo  "DATE = `date`"
echo  "HOSTNAME = " `hostname`
echo -e "*************************************\n"

if [ "$1" = "" ]; then
  echo -e "\n please Provide pre or post "
  echo -e "Syntax: $0 <pre>|<post> \n"
  exit 1
elif [[ "${chk_type}" == "pre" || "${chk_type}" == "post" ]]; then
  echo -e "\n Entered ${chk_type}   ..........  \n"
else
  echo -e "\n Please enter either " PRE " or " POST " Only. \n"
  exit 1
fi

chk_type=`echo $1 | tr "[:upper:]" "[:lower:]"`
export logfile=/home/oracle/CB/${chk_type}_shell_logfile.log
export home=<ORACLE_HOME>
export oem_agent_home=<Agent_home>
export psu_location=<Patch_path>/<patch#>

#exit 1
precheck(){
echo -e "\n   -----[ Gather OS Info ]------\n"
date
uname -a
cat /etc/oratab
ps -ef |grep pmon
ps -ef |grep tns
crsctl stat res -t
df -h
echo -e "\n   -----[ OEM Agent Info ]------\n"
${oem_agent_home}/bin/emctl status agent
${oem_agent_home}/bin/emctl status blackout <name>
}


home_check () {
echo -e "\n     -----[ Gather PATCH Info ]------\n"
$home/OPatch/opatch version
$home/OPatch/opatch lsinventory
 if [[ "$chk_type" == "pre" ]]; then
  echo -e "\n Checking patch Conflicts ........."
  cd ${psu_location}
  ${home}/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
 fi
}

db_check () {
echo -e "\n   -----[ Gather DB Info ]------\n"
crsctl stat res -t | grep ".db$" |tr "." " " | awk -F" " '{print $2}'| while read LINE2
do
 # case $LINE2 in
 #   *)
echo -e "\n Running for : $LINE2  "
srvctl status database -d $LINE2 -v
srvctl status service -d $LINE2 -v
echo "-------------------------"
#esac
done
}

home_check | tee -a ${logfile}
precheck | tee ${logfile}
db_check  | tee -a ${logfile}

sql_info (){
cat /etc/oratab | grep 112|tr ":" " "  | awk -F" " '{print $1}' |egrep -v ''\#'|ASM' | while read LINE3
do
export ORACLE_SID=${LINE3}
export ORACLE_HOME=${home}
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:$PATH
#echo "$LINE3"
echo -e "\n    --------[ Running for database : $LINE3 ]---------"
$ORACLE_HOME/bin/sqlplus -s / as sysdba << eof
set pages 999 lines 280 feedback off
col COMMENTS for a33
col pdb_name format a15
col dp_action format a15
col dp_status format a20
col dp_action_time format a20
col dp_description format a30
select comments, action, to_char(action_time,'DD/MM/RR HH24:MI:SS') action_date, version  from sys.registry\$history  order by action_date;
 select name,action_time dp_action_time,id dp_patch_id,action dp_action,comments dp_description from v\$database,sys.registry\$history  where action_time=(select max(action_time)   from sys.registry\$history);
eof
done
}

sql_info

cat  ${logfile} | mailx -s "`hostname` : precheck info " Your_email

 

Wednesday, December 28, 2022

Goldengate 19c Switchover shell script

 #######################################################
#!/bin/ksh
#  Purpose          : This Script will do Actual GG Failover to another DC
#  NOTE            : Always SHOULD run from OLD Primary
#  Hard Coded   : Target server, Target DB
#  ASSUMING   : SSH works from Source to Target servers
#                          :  DB Switchover already completed
#  AUTHOR       : CB
########################################################
# Changes :
# CB - Aug 2018   - version 1 - Initial Draft
 
echo -e "\n*************************************"
echo  "DATE = `date`"
echo  "HOSTNAME = " `hostname`
echo -e "*************************************\n"

if [ "$2" = "" ]; then
  echo " "
  echo -e "Syntax: $0 <DB name>  <New Prod Server (node 1 ONLY)> \n"
  exit 1
fi

DBTGT=`echo $1 | tr "[:upper:]" "[:lower:]"`
export GG_HOME=/goldengate/home
export GG_DBA_DIR=/goldengate/SCRITPS/
TRG_DB=<TARGET_DB_NAME>
TARGET_SERVER=`$GG_DBA_DIR/GET_HOST.sh $TRG_DB`
SWITCH_TO=`echo $2 | tr "[:upper:]" "[:lower:]"`
#DBTAIL=`echo $DBTGT |awk '{print substr($0,3)}'`
#echo "DBTAIL :" $DBTAIL
#echo "DBTGT : " $DBTGT
SOURCE_SERVER=`hostname`
TODAY=`date '+%b%d%y'`
#END_MOD="\e[0m"
# Checking logic for NODE1 input because my mgr always runs on node1
if [[ "${SWITCH_TO:0:6}" == "server_name" || "${SWITCH_TO:0:6}" == "server_name" ]]; then
echo -e "\n Starting Switchover of  ${DBTGT} to ${SWITCH_TO}  ..........  \n"
else
echo -e "\n Entered Server name is NOT a PROD NODE 1 RAC server \n"
exit 1
fi

ORAENV_ASK=NO ; export ORAENV_ASK; export ORACLE_SID=$DBTGT
. oraenv $ORACLE_SID
 
check_prompt_3()
{
while true
do
  # (1) prompt and read command line argument
  read -p "Do you want to proceed ...?? " answer3
  # (2) handle the input we were given
  case $answer3 in
   [yY]* )
           echo -e "\nEntered YES, Continuing script........\n"
           break;;
   [nN]* ) echo -e "\nEntered NO ........"
           echo -e "\nexiting script ........\n"
           exit;;
   * )     echo "Dude, just enter Y or N, please.";;
  esac
done
}
GG_INFO () {
$GG_HOME/ggsci -s <<EOF
info all
exit
EOF
}
GG_PROCESS () {
$GG_HOME/ggsci -s <<EOF
info ${PROCESS_NAME}
exit
EOF
}
STOP_EXTRACT () {
$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/spool_EXT.log
info all
exit
EOF
cat ${GG_DBA_DIR}/spool_EXT.log | egrep 'EXTRACT' | grep -i E$DBTGT| tr ":" " " | while read LINE
do
  case $LINE in
    *)
    PROCESS_TYPE=`echo $LINE | awk -F" " '{print $1}'`
    PROCESS_STATUS=`echo $LINE | awk -F" " '{print $2}'`
    PROCESS_NAME=`echo $LINE | awk -F" " '{print $3}'`
if [ "$PROCESS_TYPE" != "MANAGER" ]
  then
    if [ "$PROCESS_STATUS" == "RUNNING" ]
       then
#Making sure Only Extracts should be STOPPED NOT PUMP
PUMP_CHAR=`echo $PROCESS_NAME |awk '{print substr($0,1,1)}'`
#echo -e "\nPUMP_CHAR = " $PUMP_CHAR
      if [[ "$PUMP_CHAR" != "P" ]]; then
        echo -e "\n  *******************************************"
        echo -e "  Executing for $PROCESS_TYPE :  $PROCESS_NAME "
        echo -e "  ********************************************\n"
        echo -e "STOPPING EXTRACT $PROCESS_NAME ........\n"
$GG_HOME/ggsci <<EOF
STOP $PROCESS_NAME
sh sleep 15
info $PROCESS_NAME
sh echo -e "\ninfo ${PROCESS_NAME} SHOWCH"
info ${PROCESS_NAME} SHOWCH
EOF
       fi
  else
     echo -e "\n   *******************************************"
     echo  "    EXTRACT ${PROCESS_NAME} NOT RUNNING......."
     echo -e "   *******************************************\n"
     GG_PROCESS;
    fi
if [[ -f ${GG_DBA_DIR}/EXT_SHOWCH.txt ]]; then
rm ${GG_DBA_DIR}/EXT_SHOWCH.txt
fi
touch ${GG_DBA_DIR}/EXT_SHOWCH.txt
$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/EXT_SHOWCH.txt
info ${PROCESS_NAME} SHOWCH
exit
EOF
EXT_SEQ_NO=`grep -i "Sequence #" ${GG_DBA_DIR}/EXT_SHOWCH.txt|awk -F" " '{print $3}'`
EXT_RBA_NO=`grep -i "RBA" ${GG_DBA_DIR}/EXT_SHOWCH.txt|awk -F" " '{print $2}'`
   echo -e "\n\n          *************************************************"
   echo -e "           Extract STOPPED at SEQ No. =" ${EXT_SEQ_NO} ", RBA No. ="  ${EXT_RBA_NO}
   echo -e "          **************************************************\n"
if [[ -f ${GG_DBA_DIR}/EXT_SHOWCH.txt ]]; then
rm ${GG_DBA_DIR}/EXT_SHOWCH.txt
fi
 fi
esac
done
if [[ -f ${GG_DBA_DIR}/spool_EXT.log ]]; then
rm ${GG_DBA_DIR}/spool_EXT.log
fi
}


STOP_PUMP () {
$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/spool_PUMP.log
info all
exit
EOF
#DB_TYPE=$DBTGT
DB_TYPE=`echo ${DBTGT} |awk '{print substr($0,1,2)}'`
echo -e "\nDB_TYPE = " $DB_TYPE
cat ${GG_DBA_DIR}/spool_PUMP.log | egrep 'EXTRACT' | grep -i P${DBTGT}| tr ":" " " | while read LINE2
do
  case $LINE2 in
    *)
    PROCESS_TYPE=`echo $LINE2 | awk -F" " '{print $1}'`
    PROCESS_STATUS=`echo $LINE2 | awk -F" " '{print $2}'`
    PROCESS_NAME=`echo $LINE2 | awk -F" " '{print $3}'`
if [ "$PROCESS_TYPE" != "MANAGER" ]
  then
    if [ "$PROCESS_STATUS" == "RUNNING" ]
       then
#Making sure Only Extracts should be STOPPED NOT PUMP
PUMP_CHAR=`echo $PROCESS_NAME |awk '{print substr($0,1,1)}'`
#echo -e "\nPUMP_CHAR = " $PUMP_CHAR
      if [[ "$PUMP_CHAR" == "P" ]]; then
echo -e "\nChecking .......SEND ${PROCESS_NAME} LOGEND"
send_pump_fn ()
{
$GG_HOME/ggsci <<EOF
SEND P${DB_TYPE}* LOGEND
EXIT
EOF
}
if [[ -f ${GG_DBA_DIR}/READ_SEND_PUMP ]]; then
rm ${GG_DBA_DIR}/READ_SEND_PUMP
fi
if [[ -f ${GG_DBA_DIR}/send_pump.tmp ]]; then
rm ${GG_DBA_DIR}/send_pump.tmp
fi
touch ${GG_DBA_DIR}/READ_SEND_PUMP
touch ${GG_DBA_DIR}/send_pump.tmp
until [ -s ${GG_DBA_DIR}/READ_SEND_PUMP ]
  do
      send_pump_fn > ${GG_DBA_DIR}/send_pump.tmp
      grep -i YES ${GG_DBA_DIR}/send_pump.tmp > ${GG_DBA_DIR}/READ_SEND_PUMP
         sleep 5
      echo -e "\nWaiting for Pump to get .......YES"
  done
echo -e "\n ******    SEND Pump STATUS ==>  YES and it processed all trail records  ******"
echo -e "\n  ---------- [ SEND PUMP Output  ] ------------"
echo -e "  *********************************************\n"
cat ${GG_DBA_DIR}/send_pump.tmp
echo -e "\n  ********************************************\n"
echo -e "\nSTOPPING PUMP $PROCESS_NAME Process ............\n"
$GG_HOME/ggsci <<EOF
SEND $PROCESS_NAME STATUS
STOP $PROCESS_NAME
sh sleep 5
info $PROCESS_NAME
EOF
fi
   else
   echo -e "\n  ****************************"
   echo -e "  PUMP $PROCESS_NAME NOT running"
   echo -e "  *****************************\n"
    fi
fi
esac
done
if [[ -f ${GG_DBA_DIR}/spool_PUMP.log ]]; then
rm ${GG_DBA_DIR}/spool_PUMP.log
fi
}
###   MAIN    ####
GG_INFO;
STOP_EXTRACT;
GG_INFO;
#check_prompt_3
STOP_PUMP;
GG_INFO;
#check_prompt_3
$GG_HOME/ggsci <<EOF > EXT_TRAIL
info EXTTRAIL *${DBTGT}*
EXIT
EOF

EXT_TRAIL_PATH=`grep -i "SRC" EXT_TRAIL |awk -F" " '{print $3}' `
echo "   EXT_TRAIL_PATH=" $EXT_TRAIL_PATH
PUMP_TRAIL_PATH=`grep -i TRG EXT_TRAIL |awk -F" " '{print $3}'`
echo "   PUMP_TRAIL_PATH =" $PUMP_TRAIL_PATH
OLD_REP_SEQNO=`grep  "Seqno:" EXT_TRAIL |awk -F" " '{print $2}' |tail -1`
if [[ -f ${GG_DBA_DIR}/READ_SEND_PUMP ]]; then
rm ${GG_DBA_DIR}/READ_SEND_PUMP
fi
if [[ -f ${GG_DBA_DIR}/send_pump.tmp ]]; then
rm ${GG_DBA_DIR}/send_pump.tmp
fi
if [[ -f ${GG_DBA_DIR}/EXT_TRAIL ]]; then
rm ${GG_DBA_DIR}/EXT_TRAIL
fi
echo -e "\nChecking manager........\n"
$GG_HOME/ggsci <<EOF
sh echo "info mgr"
info Mgr
EXIT
EOF
echo -e "\n\n    ================[   Current SOURCE `hostname -s` Trail files  ]================== "
ls -lrth ${EXT_TRAIL_PATH}* | tail
## TARGET REPLICAT Activities
#echo -e "\n##### ---------------------------[ START NEW SSH ]------------------------########"
echo -e "###############################################################################################"
#echo -e "\n First :  ssh oracle@${TARGET_SERVER} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} TRG_DB=${TRG_DB} PUMP_TRAIL_PATH=${PUMP_TRAIL_PATH}  'bash -ls' <<'ENDSSH' \n"
#ssh oracle@${TARGET_SERVER} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} TRG_DB=${TRG_DB} PUMP_TRAIL_PATH=${PUMP_TRAIL_PATH}  'bash -ls' <<'ENDSSH'
echo -e "\n  ************************************"
echo -e "  Logged into Target server : `hostname -s`"
echo -e "  ************************************\n"
$GG_HOME/ggsci -s <<EOF
info all
exit
EOF
$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/spool_targetDB.log
info all
exit
EOF
cat ${GG_DBA_DIR}/spool_targetDB.log | egrep 'REPLICAT' | egrep -i $DB_TYPE| egrep -i ${DBTGT} | tr ":" " " | while read LINE
do
  case $LINE in
    *)
    PROCESS_TYPE=`echo $LINE | awk -F" " '{print $1}'`
    PROCESS_STATUS=`echo $LINE | awk -F" " '{print $2}'`
    PROCESS_NAME=`echo $LINE | awk -F" " '{print $3}'`
if [ "$PROCESS_TYPE" != "MANAGER" ]
  then
    if [ "$PROCESS_TYPE" == "REPLICAT" ]
      then
echo -e "\n  *******************************************"
echo -e "   Executing for $PROCESS_TYPE :  $PROCESS_NAME"
echo -e "  ********************************************\n"
        if [ "$PROCESS_STATUS" == "RUNNING" ]
          then
echo -e "DB_TYPE =" $DB_TYPE
echo  "DBTGT = " $DBTGT
#REP_PROCESS_NAME=R${DB_TYPE}*D2
echo "REP_PROCESS_NAME =" $PROCESS_NAME
#echo -e "\ninfo ${PROCESS_NAME}"
$GG_HOME/ggsci -s <<EOF
sh echo "info ${PROCESS_NAME}"
info ${PROCESS_NAME}
exit
EOF

send_replicat_fn ()
{
$GG_HOME/ggsci <<EOF
SEND ${PROCESS_NAME} LOGEND
EXIT
EOF
}
if [[ -f ${GG_DBA_DIR}/READ_SEND_REPLICAT ]]; then
rm ${GG_DBA_DIR}/READ_SEND_REPLICAT
fi
if [[ -f ${GG_DBA_DIR}/send_replicat.tmp ]]; then
rm ${GG_DBA_DIR}/send_replicat.tmp
fi
touch ${GG_DBA_DIR}/READ_SEND_REPLICAT
touch ${GG_DBA_DIR}/send_replicat.tmp
until [ -s ${GG_DBA_DIR}/READ_SEND_REPLICAT ]
  do
     send_replicat_fn > ${GG_DBA_DIR}/send_replicat.tmp
     grep -i YES ${GG_DBA_DIR}/send_replicat.tmp > ${GG_DBA_DIR}/READ_SEND_REPLICAT
     echo -e "\nWaiting for Replicat to get .......YES"
      sleep 5
  done
echo -e "\nSEND REPLICAT STATUS ==>  is YES and it processed all trail records"
echo -e "\n*********** [ SEND REPLICAT status OUTPUT ] *********"
  cat ${GG_DBA_DIR}/send_replicat.tmp
echo -e "\n********************************************\n"
echo -e "\n  STOPPING $PROCESS_NAME Process ............\n"
$GG_HOME/ggsci -s <<EOF
sh echo "SEND ${PROCESS_NAME} STATUS"
SEND ${PROCESS_NAME} STATUS
STOP ${PROCESS_NAME}
sh sleep 5
info ${PROCESS_NAME}
exit
EOF
     else
     echo -e "\n   ********************************"
     echo -e "    Replicat ${PROCESS_NAME} NOT RUNNING........"
     echo -e "   *********************************\n"
$GG_HOME/ggsci -s <<EOF
info ${PROCESS_NAME}
exit
EOF
        fi
fi
fi
ORAENV_ASK=NO ; export ORAENV_ASK; export ORACLE_SID=${TRG_DB}
. oraenv
GETPW=<password>
#echo "Password -> "${GETPW}
echo " ORACLE_SID = " $ORACLE_SID
echo " ORACLE_HOME = " $ORACLE_HOME
DB_SEQNO=`${ORACLE_HOME}/bin/sqlplus -s /nolog << EOF
connect system/$GETPW@${TRG_DB}
set verify off trimspool on feed off echo off termout off head off
select seqno from ggadmin.checkpoint_target where group_name='${PROCESS_NAME}';
exit
EOF
`
DB_RBANO=`${ORACLE_HOME}/bin/sqlplus -s /nolog << EOF
connect system/$GETPW@${TRG_DB}
set verify off trimspool on feed off echo off termout off head off
select RBA from ggadmin.checkpoint_target where group_name='${PROCESS_NAME}';
exit
EOF
`
     echo -e "\n      *************************************************************"
     echo -e "      Replicat ${PROCESS_NAME} Last Applied SEQ No. = " ${DB_SEQNO} ", RBA No. =" ${DB_RBANO}
     echo -e "      **************************************************************\n"
esac
done
echo -e "\n  -------------[  List of Trail Files on TARGET `hostname -s` Server ]-------------"
echo -e "ls -lrth ${PUMP_TRAIL_PATH}*| tail"
ls -lrth ${PUMP_TRAIL_PATH}*| tail
if [[ -f ${GG_DBA_DIR}/READ_SEND_REPLICAT ]]; then
rm ${GG_DBA_DIR}/READ_SEND_REPLICAT
fi
if [[ -f ${GG_DBA_DIR}/send_replicat.tmp ]]; then
rm ${GG_DBA_DIR}/send_replicat.tmp
fi
#ENDSSH
#if [ $? != 0 ]
#then
#echo -e "\n  *********************************************"
#    echo -e "\n  Found errors on Target SSH script .......Check the Error\n"
#    echo -e "  *******************************************\n"
#exit 1
#fi
echo -e "\n##### ---------------------------[ END SSH ]------------------------########"
###check_prompt_3;
#####################  NEW PRIMARY  (Was Standby)  #############
##### ---------------------------[ START NEW SSH ]------------------------########
## Checking just EXTRACT info and Cleaning OLD files
echo -e "\nSecond:  ssh ${SWITCH_TO} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} EXT_TRAIL_PATH=${EXT_TRAIL_PATH} TRG_DB=${TRG_DB} 'bash -ls' <<'ENDSSH' \n"
ssh ${SWITCH_TO} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} EXT_TRAIL_PATH=${EXT_TRAIL_PATH} TRG_DB=${TRG_DB}  'bash -ls' <<'ENDSSH'
sleep 5
echo -e "\n\n  ---------[ List of checkpoint files... ]--------"
ls -lcrth $GG_HOME/dirchk | tail
echo -e "\n  ---------[ List of param files... ]--------"
ls -lcrth $GG_HOME/dirprm | tail -5
echo -e "\n        ************************************"
echo -e "\n           Logged into `hostname -s` Server\n"
echo -e "         ************************************\n"
$GG_HOME/ggsci -s <<EOF
sh echo "Starting Manager ......."
info mgr
start mgr
sh sleep 5
info mgr
exit
EOF


SOURCE_SERVER=`hostname -s`
TODAY=`date '+%b%d%y'`
ORAENV_ASK=NO ; export ORAENV_ASK; export ORACLE_SID=$DBTGT
. dbaenv $ORACLE_SID
 
GG_INFO_DETAIL () {
$GG_HOME/ggsci -s <<EOF
sh echo "dblogin useridalias ggadmin_prod"
dblogin useridalias ggadmin_prod
info E${DBTGT^^}* DETAIL
exit
EOF
}
GG_INFO_DETAIL;
if [[ -f ${GG_DBA_DIR}/EXT_SHOWCH.txt ]]; then
rm ${GG_DBA_DIR}/EXT_SHOWCH.txt
fi
touch ${GG_DBA_DIR}/EXT_SHOWCH.txt
$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/EXT_SHOWCH.txt
info E${DBTGT^^}* SHOWCH
exit
EOF
EXT_SEQ_NO=`grep -i "Sequence #" ${GG_DBA_DIR}/EXT_SHOWCH.txt|awk -F" " '{print $3}'`
EXT_RBA_NO=`grep -i "RBA" ${GG_DBA_DIR}/EXT_SHOWCH.txt|awk -F" " '{print $2}'`
   echo -e "\n\n          *************************************************"
   echo -e "         OLD Extract CURRENT SEQ No. =" ${EXT_SEQ_NO} ", RBA No. ="  ${EXT_RBA_NO}
   echo -e "          **************************************************\n"
if [[ -f ${GG_DBA_DIR}/EXT_SHOWCH.txt ]]; then
rm ${GG_DBA_DIR}/EXT_SHOWCH.txt
fi
echo -e "\n ##################################"
echo -e " Cleaning OLD Files ............."
echo -e " ##################################\n"
echo -e " EXTRACT Cleanup ...........\n"
mkdir -p ${GG_HOME}/dirprm/Switchover_$TODAY
mkdir -p ${GG_HOME}/dirchk/Switchover_$TODAY
mkdir -p ${GG_HOME}/dirrpt/Switchover_$TODAY
echo -e "\nBacking up Existing param files "
echo "mv ${GG_HOME}/dirprm/e$DBTGT*.prm ${GG_HOME}/dirprm/p$DBTGT*.prm ${GG_HOME}/dirprm/Switchover_$TODAY"
mv ${GG_HOME}/dirprm/e$DBTGT*.prm ${GG_HOME}/dirprm/p$DBTGT*.prm ${GG_HOME}/dirprm/Switchover_$TODAY
ls -lrth ${GG_HOME}/dirprm | tail -5
echo -e "\nBacking up Existing DIRCHK directory "
echo "mv ${GG_HOME}/dirchk/*${DBTGT^^}*.cpb ${GG_HOME}/dirchk/*${DBTGT^^}*.cpe  ${GG_HOME}/dirchk/Switchover_$TODAY"
mv ${GG_HOME}/dirchk/*${DBTGT^^}*.cpb ${GG_HOME}/dirchk/*${DBTGT^^}*.cpe  ${GG_HOME}/dirchk/Switchover_$TODAY
ls -lrth ${GG_HOME}/dirchk
mv ${GG_HOME}/dirrpt/E*${DBTGT^^}* ${GG_HOME}/dirrpt/e*${DBTGT}* ${GG_HOME}/dirrpt/Switchover_$TODAY
mv ${GG_HOME}/dirrpt/P*${DBTGT^^}* ${GG_HOME}/dirrpt/p*${DBTGT}* ${GG_HOME}/dirrpt/Switchover_$TODAY
echo -e "\nBacking up Existing Trail Files...."
mkdir -p ${GG_HOME}/dirdat/SRC/${DBTGT}/${TRG_DB}/Switchover_$TODAY
echo "mv ${GG_HOME}/dirdat/SRC/${DBTGT}/${TRG_DB}/${DB_TYPE}* ${GG_HOME}/dirdat/SRC/${DBTGT}/${TRG_DB}/Switchover_$TODAY"
mv ${GG_HOME}/dirdat/SRC/${DBTGT}/${TRG_DB}/${DB_TYPE}* ${GG_HOME}/dirdat/SRC/${DBTGT}/${TRG_DB}/Switchover_$TODAY
#if [[ "$DO_EXT" == "Y" ]]; then
echo -e "\n REPLICAT Cleanup ...........\n"
#### Below are for Replicat
echo -e "\nBacking up Existing param files "
echo "mv ${GG_HOME}/dirprm/r$DBTGT*.prm ${GG_HOME}/dirprm/Switchover_$TODAY"
mv ${GG_HOME}/dirprm/r$DBTGT*.prm  ${GG_HOME}/dirprm/Switchover_$TODAY
ls -lrth ${GG_HOME}/dirprm | tail -5
echo -e "\nBacking up Existing DIRCHK directory "
echo "mv ${GG_HOME}/dirchk/*${DBTGT^^}*.cpr ${GG_HOME}/dirchk/Switchover_$TODAY"
mv ${GG_HOME}/dirchk/*${DBTGT^^}*.cpr ${GG_HOME}/dirchk/Switchover_$TODAY
ls -lrth ${GG_HOME}/dirchk
mv ${GG_HOME}/dirrpt/R*${DBTGT^^}* ${GG_HOME}/dirrpt/r*${DBTGT}* ${GG_HOME}/dirrpt/Switchover_$TODAY
echo -e "\nBacking up Existing Trail Files...."
mkdir -p ${GG_HOME}/dirdat/TRG/${DBTGT}/${TRG_DB}/Switchover_$TODAY
echo "mv ${GG_HOME}/dirdat/TRG/${DBTGT}/${TRG_DB}/${DB_TYPE^^}* ${GG_HOME}/dirdat/TRG/${DBTGT}/${TRG_DB}/Switchover_$TODAY"
mv ${GG_HOME}/dirdat/TRG/${DBTGT}/${TRG_DB}/${DB_TYPE^^}* ${GG_HOME}/dirdat/TRG/${DBTGT}/${TRG_DB}/Switchover_$TODAY
#fi

echo "ORACLE_SID=" $ORACLE_SID
$GG_HOME/ggsci -s <<EOF
info all
exit
EOF
echo -e "\n\n ---------[ List of Latest trail files on NEW PRIMARY ]--------- "
#echo -e "\nls -lrth ${EXT_TRAIL_PATH}* | tail"
ls -lrth ${EXT_TRAIL_PATH}* | tail
ENDSSH
##### ---------------------------[ END SSH ]------------------------########
###check_prompt_3;
echo -e "  ########################## SCP files to NEW PRIMARY : ${SWITCH_TO} ##############"
sleep 5
echo "scp -r ${GG_HOME}/dirchk/E${DBTGT^^}*  $GG_HOME/dirchk/P${DBTGT^^}* oracle@${SWITCH_TO}:$GG_HOME/dirchk "
scp -r ${GG_HOME}/dirchk/E${DBTGT^^}*.* $GG_HOME/dirchk/P${DBTGT^^}*.* oracle@${SWITCH_TO}:$GG_HOME/dirchk
scp -r ${GG_HOME}/dirprm/e${DBTGT}*.prm $GG_HOME/dirprm/p${DBTGT}*.prm oracle@${SWITCH_TO}:$GG_HOME/dirprm
echo "scp -r ${GG_HOME}/dirchk/R${DBTGT^^}*   oracle@${SWITCH_TO}:$GG_HOME/dirchk "
scp -r  ${GG_HOME}/dirchk/R${DBTGT^^}* oracle@${SWITCH_TO}:$GG_HOME/dirchk
scp -r ${GG_HOME}/dirprm/r${DBTGT}*.prm  oracle@${SWITCH_TO}:$GG_HOME/dirprm
if [ $? != 0 ]
then
    echo -e "\n    **********************************************"
    echo -e "\n     SCP to ${SWITCH_TO} Failed.......Check the Error\n"
    echo -e "     **********************************************\n"
  exit 1
else
echo -e "\n    *************************************"
    echo -e "    SCP to ${SWITCH_TO} ............SUCCESS"
    echo -e "   **************************************\n"
fi
###check_prompt_3;
##### ---------------------------[ START NEW SSH ]------------------------########
# EXTRACT Activities on NEW Primary
echo -e "\n3rd : ssh ${SWITCH_TO} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} TARGET_SERVER=${TARGET_SERVER} EXT_TRAIL_PATH=${EXT_TRAIL_PATH} EXT_SEQ_NO=${EXT_SEQ_NO} EXT_RBA_NO=${EXT_RBA_NO}  'bash -ls' <<'ENDSSH' \n"
ssh ${SWITCH_TO} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} TARGET_SERVER=${TARGET_SERVER} EXT_TRAIL_PATH=${EXT_TRAIL_PATH} EXT_SEQ_NO=${EXT_SEQ_NO} EXT_RBA_NO=${EXT_RBA_NO}  'bash -ls' <<'ENDSSH'
echo -e "\n  ************************************"
echo -e "\n  Logged into `hostname -s` Server\n"
echo -e "  ************************************\n"
ls -lcrth $GG_HOME/dirchk | tail
echo -e "\n  Updating RMTHOST in p${DBTGT}p paramfile ......."
RMT_HOST_NAME=`grep RMTHOST ${GG_HOME}/dirprm/p${DBTGT}*.prm  | awk '{print $2}'`
RMT_HOST_NAME=${RMT_HOST_NAME%%?}
echo -e "\nOLD RMTHOST = " $RMT_HOST_NAME
echo "s/${RMT_HOST_NAME}/${TARGET_SERVER}/g"
eval sed -i 's/${RMT_HOST_NAME}/${TARGET_SERVER}/g' ${GG_HOME}/dirprm/p${DBTGT}*.prm
cat ${GG_HOME}/dirprm/p${DBTGT}*.prm
RMT_HOST_NAME=`grep RMTHOST ${GG_HOME}/dirprm/p${DBTGT}*.prm  | awk '{print $2}'`
RMT_HOST_NAME=${RMT_HOST_NAME%%?}
#echo -e  "\nNEW RMTHOST = " $RMT_HOST_NAME
if [[  ${RMT_HOST_NAME} == ${TARGET_SERVER} ]]; then
 echo -e  "\nNEW RMTHOST = " $RMT_HOST_NAME
else
 echo -e  "\nNEW RMTHOST = " $RMT_HOST_NAME
 echo -e " WRONG HOSTNAME in pump param file ..... Modify to ${TARGET_SERVER} name and continue. "
 exit 1
fi
SOURCE_SERVER=`hostname -s`
$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/INFO_EXT
info all
exit
EOF
#INFO_EXT_VAR= `cat ${GG_DBA_DIR}/INFO_EXT | egrep 'EXTRACT' | grep -i E${DB_TYPE}|grep -i ${DBTGT}| tr ":" " "`
EXTRACT_NAME=`cat ${GG_DBA_DIR}/INFO_EXT | egrep 'EXTRACT' | grep -i E${DB_TYPE}|grep -i ${DBTGT}| tr ":" " " | awk -F" " '{print $3}'`
echo "EXTRACT_NAME =" $EXTRACT_NAME
if [[ -f ${GG_DBA_DIR}/EXT_SHOWCH_BEFORE.txt ]]; then
rm ${GG_DBA_DIR}/EXT_SHOWCH_BEFORE.txt
fi
touch ${GG_DBA_DIR}/EXT_SHOWCH_BEFORE.txt
$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/EXT_SHOWCH_BEFORE.txt
info ${EXTRACT_NAME} SHOWCH
exit
EOF
SEQ_NO_BEFORE=`grep -i "Sequence #" ${GG_DBA_DIR}/EXT_SHOWCH_BEFORE.txt|awk -F" " '{print $3}'`
RBA_NO_BEFORE=`grep -i "RBA" ${GG_DBA_DIR}/EXT_SHOWCH_BEFORE.txt|awk -F" " '{print $2}'`



ORAENV_ASK=NO ; export ORAENV_ASK; export ORACLE_SID=$DBTGT
. dbaenv $ORACLE_SID
 
echo "ORACLE_SID=" $ORACLE_SID
$GG_HOME/ggsci -s <<EOF
dblogin useridalias ggadmin_prod
info all
info ${EXTRACT_NAME} DETAIL
sh echo "ALTER EXTRACT ${EXTRACT_NAME} ETROLLOVER"
ALTER EXTRACT ${EXTRACT_NAME} ETROLLOVER
info ${EXTRACT_NAME} DETAIL
sh echo "start ${EXTRACT_NAME}"
start ${EXTRACT_NAME}
sh sleep 5
info ${EXTRACT_NAME} showch
sh sleep 5
send ${EXTRACT_NAME} status
info all
exit
EOF
echo -e "\n\n  ------------[ Extract Trail file after ALTER  ]----------"
echo "ls -lrth ${EXT_TRAIL_PATH}* | tail"
ls -lrth ${EXT_TRAIL_PATH}* | tail
if [[ -f ${GG_DBA_DIR}/EXT_SHOWCH.txt ]]; then
rm ${GG_DBA_DIR}/EXT_SHOWCH.txt
fi
touch ${GG_DBA_DIR}/EXT_SHOWCH.txt
$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/EXT_SHOWCH.txt
info ${EXTRACT_NAME} SHOWCH
exit
EOF
SEQ_NO=`grep -i "Sequence #" ${GG_DBA_DIR}/EXT_SHOWCH.txt|awk -F" " '{print $3}'`
echo -e "\n        ***************************************************"
echo -e "        EXTRACT ${EXTRACT_NAME} Latest SEQ no. After ETROLLOVER : " $SEQ_NO
echo -e "        ***************************************************\n"
ENDSSH
##### ---------------------------[ END 3rd SSH ]------------------------########
check_prompt_3;
##### ---------------------------[ START NEW SSH for PUMP ]------------------------########
#PUMP Activities
echo -e "\n -------[ Starting PUMP Activities ]-------- \n"
sleep 5
echo -e "\n4th : ssh ${SWITCH_TO} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} TARGET_SERVER=${TARGET_SERVER} EXT_TRAIL_PATH=${EXT_TRAIL_PATH} PUMP_TRAIL_PATH=${PUMP_TRAIL_PATH} 'bash -ls' <<'ENDSSH' \n"
ssh ${SWITCH_TO} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} TARGET_SERVER=${TARGET_SERVER} EXT_TRAIL_PATH=${EXT_TRAIL_PATH} PUMP_TRAIL_PATH=${PUMP_TRAIL_PATH} 'bash -ls' <<'ENDSSH'
echo -e "\n  ************************************"
echo -e "\n  Logged into `hostname -s` Server\n"
echo -e "  ************************************\n"

$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/INFO_EXT_PUMP
info all
exit
EOF
# USE "P" in below Grep for PUMP
EXTRACT_NAME=`cat ${GG_DBA_DIR}/INFO_EXT_PUMP | egrep 'EXTRACT' | grep -i P${DB_TYPE}|grep -i ${DBTGT}| tr ":" " " | awk -F" " '{print $3}'`
echo -e "\nEXTRACT_NAME =" $EXTRACT_NAME

SEQ_NO=`grep -i "Sequence #" ${GG_DBA_DIR}/EXT_SHOWCH.txt|awk -F" " '{print $3}'`
echo "Extract SEQ_NO : " ${SEQ_NO}
#EXTRACT_NAME=P${DB_TYPE}*D2
#EXT_EXTRACT_NAME=E${DB_TYPE}*D2
$GG_HOME/ggsci -s <<EOF
info ${EXTRACT_NAME}
sh echo "ALTER EXTRACT ${EXTRACT_NAME} extseqno ${SEQ_NO} , EXTRBA 00"
ALTER EXTRACT ${EXTRACT_NAME} extseqno ${SEQ_NO} , EXTRBA 00
info ${EXTRACT_NAME}
sh echo "ALTER EXTRACT ${EXTRACT_NAME} ETROLLOVER"
ALTER EXTRACT ${EXTRACT_NAME} ETROLLOVER
sh echo "start ${EXTRACT_NAME}"
start ${EXTRACT_NAME}
EOF
echo -e "\n "
echo -e "\n -------------[ Pump SOURCE Latest Trail file after alter PUMP  ]------------"
echo -e "\nls -lrth ${EXT_TRAIL_PATH}* | tail"
ls -lrth ${EXT_TRAIL_PATH}* | tail
echo -e "\n -------------[  List of new REPLICAT Trail file on ${TARGET_SERVER} After PUMP ALTER SEQ  ]------------"
echo -e "\nssh oracle@${TARGET_SERVER} ls -lrth ${PUMP_TRAIL_PATH}* | tail"
ssh oracle@${TARGET_SERVER} ls -lrth ${PUMP_TRAIL_PATH}* | tail
if [[ -f ${GG_DBA_DIR}/EXT_SHOWCH.txt ]]; then
rm ${GG_DBA_DIR}/EXT_SHOWCH.txt
fi
if [[ -f ${GG_DBA_DIR}/INFO_EXT_PUMP ]]; then
rm ${GG_DBA_DIR}/INFO_EXT_PUMP
fi
ENDSSH
echo -e "\n ##### ---------------------------[ END SSH ]------------------------########\n"
###check_prompt_3;
echo -e "\n ##### ---------------------------[ START NEW SSH for REPLICAT ]------------------------########"
#REPLICAT Activities
sleep 5
echo -e "\n 5th :  ssh ${TARGET_SERVER} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} TRG_DB=${TRG_DB} PUMP_TRAIL_PATH=${PUMP_TRAIL_PATH} OLD_REP_SEQNO=${OLD_REP_SEQNO} 'bash -ls' <<'ENDSSH' \n"
ssh ${TARGET_SERVER} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} TRG_DB=${TRG_DB} PUMP_TRAIL_PATH=${PUMP_TRAIL_PATH} OLD_REP_SEQNO=${OLD_REP_SEQNO} 'bash -ls' <<'ENDSSH'
echo -e "\n  ************************************"
echo -e "\n  Logged into `hostname -s` Server\n"
echo -e "  ************************************\n"
echo -e "\n   -----------[ List of latest REPLICAT trail Files on TARGET `hostname -s`  server ]---------"
ls -lrth  ${PUMP_TRAIL_PATH}* | tail

GG_INFO (){
$GG_HOME/ggsci -s <<EOF
info all
exit
EOF
}
GG_INFO;
$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/spool_targetDB.log
info all
exit
EOF
cat ${GG_DBA_DIR}/spool_targetDB.log | egrep 'REPLICAT' | egrep -i $DB_TYPE| egrep -i ${DBTGT}| tr ":" " " | while read LINE
do
  case $LINE in
    *)
    PROCESS_TYPE=`echo $LINE | awk -F" " '{print $1}'`
    PROCESS_STATUS=`echo $LINE | awk -F" " '{print $2}'`
    PROCESS_NAME=`echo $LINE | awk -F" " '{print $3}'`
if [ "$PROCESS_TYPE" != "MANAGER" ]
  then
    if [ "$PROCESS_TYPE" == "REPLICAT" ]
      then
#echo -e "DB_TYPE =" $DB_TYPE
#exit 1
echo -e "\n  *******************************************"
echo -e "   Executing for $PROCESS_TYPE :  $PROCESS_NAME"
echo -e "  ********************************************\n"

ORAENV_ASK=NO ; export ORAENV_ASK; export ORACLE_SID=${TRG_DB}
 
GETPW=<password>
#echo "Password -> "${GETPW}
echo " ORACLE_SID = " $ORACLE_SID
echo " ORACLE_HOME = " $ORACLE_HOME
DB_SEQNO=`$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect system/$GETPW@${TRG_DB}
set verify off trimspool on feed off echo off termout off head off
select seqno from ggadmin.checkpoint_target where group_name='${PROCESS_NAME}';
exit
EOF
`
DB_RBA=`$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect system/$GETPW@${TRG_DB}
set verify off trimspool on feed off echo off termout off head off
select rba from ggadmin.checkpoint_target where group_name='${PROCESS_NAME}';
exit
EOF
`
     echo -e "\n********************************************************"
     echo -e " Replicat ${PROCESS_NAME} Current OLD SEQ No. = " ${DB_SEQNO} , RBA No. = ${DB_RBA}
     echo -e "*********************************************************\n"
echo -e "OLD_REP_SEQNO =" ${OLD_REP_SEQNO}
echo -e "DB_SEQNO =" ${DB_SEQNO}
DB_SEQNO=`expr ${DB_SEQNO} + 1`
echo "        NEW REP DB_SEQNO need to set =  " ${DB_SEQNO}
if [[ "$DB_SEQNO" -eq 1 ]]; then
echo " **********************************************"
echo -e  "  ERROR ....... New REPLICAT Seq number is WRONG !!  \n"
echo -e " **********************************************\n"
exit 1
fi
$GG_HOME/ggsci -s <<EOF
sh echo "info ${PROCESS_NAME}"
info ${PROCESS_NAME}
sh echo "Alter REPLICAT ${PROCESS_NAME} extseqno $DB_SEQNO  extrba 00"
Alter REPLICAT ${PROCESS_NAME} extseqno $DB_SEQNO  extrba 00
info ${PROCESS_NAME}
exit
EOF
fi
fi
 
     echo -e "\n  **************************************************"
     echo -e "   Replicat ${PROCESS_NAME} SEQ No. After ALTER = " ${DB_SEQNO}
     echo -e "  ***************************************************\n"
esac
done
GG_INFO;
## ENDSSH
##### ---------------------------[ END SSH ]------------------------########
### check_prompt_3;
##### ---------------------------[ START NEW SSH for REPLICAT ]------------------------########
## REPLICAT Activities
##echo -e "\n 6th :  ssh ${TARGET_SERVER} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} TRG_DB=${TRG_DB} PUMP_TRAIL_PATH=${PUMP_TRAIL_PATH}  'bash -ls' <<'ENDSSH' \n"
### ssh ${TARGET_SERVER} GG_HOME=${GG_HOME} GG_DBA_DIR=${GG_DBA_DIR} DB_TYPE=${DB_TYPE} DBTGT=${DBTGT} TRG_DB=${TRG_DB} PUMP_TRAIL_PATH=${PUMP_TRAIL_PATH} 'bash -ls' <<'ENDSSH'
## echo -e "\n  ************************************"
## echo -e "\n   Logged into `hostname -s` Server"
## echo -e "  ************************************\n"
echo -e "\n List of Latest trail files for ${DBTGT}"
ls -lrth ${PUMP_TRAIL_PATH}* | tail -5
#GG_INFO (){
#$GG_HOME/ggsci -s <<EOF
#info all
#exit
#EOF
#}
GG_INFO;
$GG_HOME/ggsci -s <<EOF > ${GG_DBA_DIR}/spool_targetDB.log
info all
exit
EOF
cat ${GG_DBA_DIR}/spool_targetDB.log | egrep 'REPLICAT' | egrep -i $DB_TYPE| egrep -i ${DBTGT}| tr ":" " " | while read LINE
do
  case $LINE in
    *)
    PROCESS_TYPE=`echo $LINE | awk -F" " '{print $1}'`
    PROCESS_STATUS=`echo $LINE | awk -F" " '{print $2}'`
    PROCESS_NAME=`echo $LINE | awk -F" " '{print $3}'`
if [ "$PROCESS_TYPE" != "MANAGER" ]
  then
    if [ "$PROCESS_TYPE" == "REPLICAT" ]
      then
#echo -e "DB_TYPE =" $DB_TYPE
#exit 1
echo -e "\n  *******************************************"
echo -e "  Executing for $PROCESS_TYPE :  $PROCESS_NAME"
echo -e "  ********************************************\n"
$GG_HOME/ggsci -s <<EOF
sh echo "info ${PROCESS_NAME}"
info ${PROCESS_NAME}
sh echo " START REPLICAT ${PROCESS_NAME}"
START REPLICAT ${PROCESS_NAME}
sh sleep 5
exit
EOF
GG_INFO;
fi
fi

$GG_HOME/ggsci -s <<EOF > REP_STATUS
sh echo " SEND REPLICAT ${PROCESS_NAME} STATUS"
SEND REPLICAT ${PROCESS_NAME} status
exit
EOF
DB_SEQNO=`grep -i "Sequence #" REP_STATUS |awk -F" " '{print $3}'`
     echo -e "\n  **************************************************"
     echo -e "   Replicat ${PROCESS_NAME} New CURRENT SEQ No. = " ${DB_SEQNO}
     echo -e "  ***************************************************\n"
esac
done
ENDSSH
##### ---------------------------[ END 6th SSH ]------------------------########
################################################
echo -e "\n       Shell Script COMPLETED : `date`"
exit 0

-- 

Saturday, November 18, 2017

Shell script to cleanup Goldengate PROD extracts in NON-PROD


PRODUCTION DB: Oracle on linux with Goldengate extracts ( ex: ETESTDBQ2 ) running
LINUX: RHEL6

The below script used for cleanup non-prod databases where goldengate is running when it is refreshed (FULL refresh like SNAP copy, rman clone, any physical clone) from PROD databases where goldengate also running, which means when you refresh non-prod with prod database then you will see goldengate prod extract name in non-prod which will repeat errors in alert log and restricts archivelog from deleting.

You’ll see something like below in non-prod with prod extract group names which needs cleanup

SQL> select capture_name, status, captured_scn, applied_scn, capture_type,STATUS_CHANGE_TIME from dba_capture;

CAPTURE_NAME
--------------------------------------------------------------------------------
STATUS   CAPTURED_SCN  APPLIED_SCN   CAPTURE_TY   STATUS_CHAN
------------- -----------------  -------------------    -------------------  --------------
OGG$CAP_ETESTDBQ2
ENABLED    3.5062E+11  3.5062E+11 LOCAL      13-NOV-2017


#!/bin/ksh
####################################################################################
# This script will do the POST refresh activities for Databases having Goldengate
# It will uses the directory defined in the EXPDPDR directory
#
# Syntax:  Script.sh <database_name>
#
# PARAMETERS:
#     Source dbname    : (required) - database name
#
# EX: GG_POST.sh mydev
#
# AUTHOR: Chandra
####################################################################################
echo -e "\n*************************************"
echo  "DATE = `date`"
echo  "HOSTNAME = " `hostname`
echo -e "*************************************\n"

if [ "$1" = "" ]; then
  echo " "
  echo "Syntax: $0 <DB name>"
  exit 1
fi

DBTGT=`echo $1 | tr "[:upper:]" "[:lower:]"`
DBTAIL=`echo $DBTGT |awk '{print substr($0,3)}'`
SOURCE_SERVER=`hostname`
#echo "DBTAIL :" $DBTAIL
#echo "DBTGT : " $DBTGT

if [[ "$DBTAIL" = "prod"]]; then
echo -e "\nThis script CANNOT be run on $DBTGT PRODUCTION databases \n"
exit 1
fi

ORAENV_ASK=NO ; export ORAENV_ASK; export ORACLE_SID=$DBTGT
#. oraenv $ORACLE_SID
if [[ "${SOURCE_SERVER:0:3}" = "rac" ]] || [[ "${SOURCE_SERVER:0:3}" = "RAC" ]]; then
. /opt/oracle/RAC/oraenv
else
. /usr/local/bin/oraenv
fi

echo "ORACLE_HOME=" $ORACLE_HOME

CAP_NAME=`sqlplus -s /nolog << EOF
connect / as sysdba
set verify off trimspool on feed off echo off termout off head off
col capture_name for a15
col STATE for a33
select capture_name from dba_capture where capture_name like '%PROD%';
exit
EOF
`

CAP_NEW_NAME=$CAP_NAME
echo -e "\n CAP_NEW_NAME = " $CAP_NEW_NAME
C_ENV=`echo $CAP_NAME |awk '{print substr($0,9,14)}'`
echo " CAP_NAME= $CAP_NAME"
echo " C_ENV" =$C_ENV

stop_cap () {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
exec DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG\$CAP_${C_ENV}');
EOF
}

drop_cap () {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
exec DBMS_CAPTURE_ADM.DROP_CAPTURE('OGG\$CAP_${C_ENV}');
EOF
}

CHECK_CAP () {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
set line 280 pages 999
col CAPTURE_NAME for a22
col STATE for a25
select capture_name, status, captured_scn, applied_scn, capture_type from dba_capture;
select extract_name,CAPTURE_NAME, sid, serial#, STATE from  V\$GOLDENGATE_CAPTURE;
EOF
}


if [[ "$C_ENV" == "ESTPRODP" ]] || [[ "$C_ENV" == "EDVPRODP" ]] ; then
echo -e "\n*****************************************************"
echo -e "Database ${1} has PROD "${CAP_NEW_NAME}" capture name "
echo -e "*************************************************** \n"
echo -e " BEFORE Dropping :"
CHECK_CAP ;
echo -e " \nStopping "${CAP_NEW_NAME}" Capture process "
stop_cap;
echo -e "\nDROPPING "${CAP_NEW_NAME}" Capture process "
drop_cap;
echo -e " AFTER Dropping :"
CHECK_CAP ;
else
echo -e "\n*********************************************************"
echo -e "NOT found PROD extract process name in $1 database "
echo -e "*********************************************************\n"
CHECK_CAP ;
fi

echo -e "\nDATE = `date`"


Another option is to login to ggsci and do unregister, check below link to unregister manually

Thursday, April 26, 2012

Compile INVALID objects


There are few different ways to compile invalid objects in a database, different people use different ways to compile (like utlrp.sql, alter command,etc)

This is the method i used recently in my environment.

 SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Check current invalid object counts:

SQL> select object_type,count(*) from dba_objects where status='INVALID' group by object_type;

SQL >  Spool run_invalid.sql

SQL> select 'ALTER ' || OBJECT_TYPE ||' '||owner||'.'||OBJECT_NAME || ' COMPILE;' from dba_objects where owner='SYSFM' and status = 'INVALID' ;

SQL>  Spool off;

SQL>  @run_invalid

Check again counts:

SQL> select object_type,count(*) from dba_objects where owner='MYUSER' and status='INVALID' group by object_type;



Auto Scroll Stop Scroll