Deletes Applied Archives older than Specified N hours on STANDBY DATABASE

# ##############################################
# This Script deletes Applied Archives older than Specified N hours on STANDBY DATABASE
# This script will run by default against ALL running STANDBY DATABASES.
# Please read the following instructions on how to use this script:
# – You can set MAIL_LIST variable to your E-mail to receive an email alert if archives
# are not applied.
# e.g. MAIL_LIST=”ram.kumar@dba.com”
# – You can specify the candidate archives for deletion older than N hours by setting
# LAST_N_HOURS variable to the number of hours.
# e.g. Deleting applied archives in older than 24 hours
# LAST_N_HOURS=24
# – You can EXCLUDE any instance from having the script to run against by passing INSTANCE_NAME
# to EXL_DB variable.
# e.g. excluding orcl from archive deletion:
# EXL_DB=”\-MGMTDB|ASM|orcl”
# – You can use FORCE option when deleting the archives from RMAN console: [Y|N]
# e.g. FORCE_DELETION=Y
# – You can decide to CROSSCHECK the archivelogs after the archivelogs deletion: [Y|N]
# e.g. VALIDATE_ARCHIVES=Y
# ################################################

# ##################################
# VARIABLES: [To be ALTERED By User] …………………………………
# ##################################

SCRIPT_NAME=”delete_standby_archives.sh”
SRV_NAME=`uname -n`
MAIL_LIST=”youremail@yourcompany.com”

# #############################################################################
# Define the number of HOURS where ARCHIVES older than N Hours will be deleted: [Default 8 HOURS]
# #############################################################################
LAST_N_HOURS=8
export LAST_N_HOURS

# #############################################################################
# Do you want to CROSSCHECK the ARCHIVE LOGS after the deletion?: [Y|N] [Default YES]
# #############################################################################
VALIDATE_ARCHIVES=Y

# #############################################################################
# Do you want to FORCEFULLY DELETE the ARCHIVE LOGS?: [Y|N] [Default NO]
# #############################################################################
FORCE_DELETION=N

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe “|” as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB=”\-MGMTDB|ASM” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# #########################
# Setting ORACLE_SID:
# #########################
for ORACLE_SID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )
do
export ORACLE_SID

# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $1}’|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ‘:’|tail -1`

# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi

# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk ‘{print $1}’`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk ‘{print $NF}’|sed -e ‘s/\/dbs//g’`
export ORACLE_HOME
#echo “ORACLE_SID is ${ORACLE_SID}”
#echo “ORACLE_HOME from PWDX is ${ORACLE_HOME}”

# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME
fi
#echo “ORACLE_HOME from oratab is ${ORACLE_HOME}”
fi

# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e ‘s/ORACLE_HOME=//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from environment is ${ORACLE_HOME}”
fi

# ATTEMPT4: If ORACLE_HOME is not found in the environment search user’s profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h ‘ORACLE_HOME=\/’ $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_HOME
#echo “ORACLE_HOME from User Profile is ${ORACLE_HOME}”
fi

# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e ‘s/\/bin\/orapipe//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from orapipe search is ${ORACLE_HOME}”
fi

# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo “Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly”
echo “e.g.”
echo “export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1”
exit
fi

# Neutralize login.sql file:
# #########################
# Existance of login.sql file under current working directory eliminates many functions during the execution of this script:

if [ -f ./login.sql ]
then
mv ./login.sql ./login.sql_NeutralizedBy${SCRIPT_NAME}
fi

if [ -f ${USR_ORA_HOME}/login.sql ]
then
mv ${USR_ORA_HOME}/login.sql ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME}
fi

# #########################
# Variables:
# #########################
export PATH=$PATH:${ORACLE_HOME}/bin

# #########################
# LOG FILE:
# #########################
export LOG_DIR=`pwd`

if [ ! -d ${LOG_DIR} ]
then
export LOG_DIR=/tmp
fi
LOG_FILE=${LOG_DIR}/DELETE_ARCHIVES.log

# #########################
# Getting DB_NAME:
# #########################
VAL1=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
SELECT name from v\$database
exit;
EOF
)
# Getting DB_NAME in Uppercase & Lowercase:
DB_NAME_UPPER=`echo $VAL1| perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’`
DB_NAME_LOWER=$( echo “$DB_NAME_UPPER” | tr -s ‘[:upper:]’ ‘[:lower:]’ )
export DB_NAME_UPPER
export DB_NAME_LOWER

# DB_NAME is Uppercase or Lowercase?:

if [ -d $ORACLE_HOME/diagnostics/${DB_NAME_LOWER} ]
then
DB_NAME=$DB_NAME_LOWER
else
DB_NAME=$DB_NAME_UPPER
fi

# ###########################
# CHECKING DB ROLE: [STANDBY]
# ###########################
VAL12=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
select COUNT(*) from v\$database where DATABASE_ROLE=’PHYSICAL STANDBY’;
exit;
EOF
)

DB_ROLE=`echo $VAL12| perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’`

# If the database is a standby DB, proceed with the rest of script:

if [ ${DB_ROLE} -gt 0 ]
then
# Delete archives only when they are applied:
VAL31=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
–select count(*) from v\$archived_log where completion_time between sysdate-2 and sysdate-$LAST_N_HOURS/24 and APPLIED = ‘NO’;
–select count(*) from v\$archived_log where name is not null and completion_time between sysdate and sysdate-$LAST_N_HOURS/24 and FAL=’NO’ and APPLIED = ‘NO’;
select count(*) from v\$archived_log where name is not null and completion_time between sysdate-(${LAST_N_HOURS}+1)/24 and sysdate-({$LAST_N_HOURS})/24 and FAL=’NO’ and APPLIED = ‘NO’;
EOF
)
NO_APPL_ARC=`echo ${VAL31}|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`
export NO_APPL_ARC

#echo “NOT_APPLIED_ARCHIVES=$NO_APPL_ARC”

if [ ${NO_APPL_ARC} -gt 0 ]
then
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
spool ${LOG_FILE}
PROMPT
PROMPT ————————————————————————————————

PROMPT SCRIPT TERMINATED! There are archivelogs in the last ${LAST_N_HOURS} Hours are NOT yet APPLIED.
PROMPT MAKE SURE THAT ALL ARCHIVES ARE APPLIED BEFORE DELETING ARCHIVELOGS.
PROMPT ————————————————————————————————

PROMPT THE FOLLOWING ARCHIVES ARE NOT YET APPLIED TO THE STANDBY DB:
PROMPT ————————————————————-

set pages 2000
set linesize 199
col name for a120
select name,to_char(completion_time,’HH24:MI:SS DD-MON-YYYY’) completion_time,applied from v\$archived_log
where name is not null and completion_time <= sysdate-${LAST_N_HOURS}/24 and FAL=’NO’ and APPLIED = ‘NO’
order by completion_time asc;

PROMPT
spool off
EOF

mail -s “ALERT: ARCHIVES IN THE LAST [${LAST_N_HOURS}] HOURS ARE NOT APPLIED ON STANDBY DB [${DB_NAME}] ” ${MAIL_LIST} < ${LOG_FILE}

else

echo “”
echo “^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^”
echo “ALL Archives in the last ${LAST_N_HOURS} Hours were APPLIED successfully.”
echo “^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^”
echo “”
VAL35=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
PROMPT
–select count(*) from v\$archived_log where name is not null and completion_time between sysdate-2 and sysdate-${LAST_N_HOURS}/24 and APPLIED = ‘YES’;
select count(*) from v\$archived_log where name is not null and completion_time <= sysdate-${LAST_N_HOURS}/24 and APPLIED = ‘YES’;
EOF
)
CAND_DEL_ARC=`echo ${VAL35}|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`
export CAND_DEL_ARC
if [ ${CAND_DEL_ARC} -gt 0 ]
then
echo “CHECKING CANDIDATE ARCHIVES FOR DELETION …”
sleep 1
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
PROMPT THE FOLLOWING CANDIDATE ARCHIVES WILL BE DELETED:
PROMPT ————————————————-

col name for a120
select name from v\$archived_log where name is not null and FAL=’NO’ and completion_time <= sysdate-${LAST_N_HOURS}/24;
EOF

# CHECK CROSSCHECK OPTION:

case ${VALIDATE_ARCHIVES} in
y|Y|yes|YES|Yes) CROSSCHECK_ARCHIVELOGS=”change archivelog all crosscheck;”; export CROSSCHECK_ARCHIVELOGS;;
*) CROSSCHECK_ARCHIVELOGS=””; export CROSSCHECK_ARCHIVELOGS;;
esac

# CHECK FORCE DELETION OPTION:

case ${FORCE_DELETION} in
y|Y|yes|YES|Yes) FORCE_OPTION=”force”; export FORCE_OPTION;;
*) FORCE_OPTION=””; export FORCE_OPTION;;
esac

# START CANDIDATE ARCHIVES DELETION FROM RMAN CONSOLE:
export NLS_DATE_FORMAT=”DD-MON-YY HH24:MI:SS”
${ORACLE_HOME}/bin/rman target / <<EOF
delete noprompt ${FORCE_OPTION} archivelog all completed before ‘sysdate-${LAST_N_HOURS}/24′;
${CROSSCHECK_ARCHIVELOGS}
EOF
echo “”
echo “ALL ARCHIVES OLDER THAN ${LAST_N_HOURS} HOURS WERE DELETED SUCCESSFULLY.”
echo “vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv”
echo “”
else
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
PROMPT
PROMPT AVAILABLE ARCHIVES STATUS IN THE LAST ${LAST_N_HOURS} HOURS:
PROMPT ———————————————–

set pages 2000 linesize 199
col name for a120
select name,to_char(completion_time,’DD-MON-YYYY HH24:MI:SS’) completion_time,applied from v\$archived_log
where name is not null and completion_time >= sysdate-${LAST_N_HOURS}/24
order by completion_time asc;
EOF
echo “”
echo “NO CANDIDATE ARCHIVES ARE ELIGIBLE FOR DELETION IN THE LAST ${LAST_N_HOURS} HOURS !”
echo “vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv”
echo “”
fi
fi
fi
if [ ${DB_ROLE} -eq 0 ]
then
echo “Database ${DB_NAME} is NOT a STANDBY DB”
echo “This script is designed to run against STANDBY DBs ONLY!”
echo “SCRIPT TERMINATED!”
fi
done

# De-Neutralize login.sql file:
# ############################
# If login.sql was renamed during the execution of the script revert it back to its original name:
if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
then
mv ./login.sql_NeutralizedBy${SCRIPT_NAME} ./login.sql
fi

if [ -f ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME} ]
then
mv ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME} ${USR_ORA_HOME}/login.sql
fi

# #############
# END OF SCRIPT
# #############

Backup & Gather Statistics On SCHEMA or TABLE.

# ##############################################
# Backup & Gather Statistics On SCHEMA|TABLE.
# To be run by ORACLE user
# ##############################################

# ###########
# Description:
# ###########
echo
echo “=======================================================”
echo “This script Gather & Backup Statistics on SCHEMA|TABLE.”
echo “=======================================================”
echo
sleep 1

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe “|” as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB=”\-MGMTDB|ASM” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# ###########################
# Listing Available Databases:
# ###########################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
then
echo No Database Running !
exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
then
export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
then
echo
echo “Select the ORACLE_SID:[Enter the number]”
echo ———————
select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )
do
if [ -z “${REPLY##[0-9]*}” ]
then
export ORACLE_SID=$DB_ID
echo Selected Instance:
echo $DB_ID
break
else
export ORACLE_SID=${REPLY}
break
fi
done

fi
# Exit if the user selected a Non Listed Number:
if [ -z “${ORACLE_SID}” ]
then
echo “You’ve Entered An INVALID ORACLE_SID”
exit
fi

# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v “\-MGMTDB”|awk ‘{print $1}’|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ‘:’|tail -1`

# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi

# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk ‘{print $1}’`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk ‘{print $NF}’|sed -e ‘s/\/dbs//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from PWDX is ${ORACLE_HOME}”

# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME
fi
#echo “ORACLE_HOME from oratab is ${ORACLE_HOME}”
fi

# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e ‘s/ORACLE_HOME=//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from environment is ${ORACLE_HOME}”
fi

# ATTEMPT4: If ORACLE_HOME is not found in the environment search user’s profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h ‘ORACLE_HOME=\/’ $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_HOME
#echo “ORACLE_HOME from User Profile is ${ORACLE_HOME}”
fi

# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e ‘s/\/bin\/orapipe//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from orapipe search is ${ORACLE_HOME}”
fi

# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo “Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly”
echo “e.g.”
echo “export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1”
exit
fi

# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
CURR_USER=`whoami`
if [ ${ORA_USER} != ${CURR_USER} ]; then
echo “”
echo “You’re Running This Sctipt with User: \”${CURR_USER}\” !!!”
echo “Please Run This Script With The Right OS User: \”${ORA_USER}\””
echo “Script Terminated!”
exit
fi

# ########################
# Getting ORACLE_BASE:
# ########################
# Get ORACLE_BASE from user’s profile if not set:

if [ -z “${ORACLE_BASE}” ]
then
ORACLE_BASE=`grep ‘ORACLE_BASE=\/’ $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_BASE
fi

# #########################
# SQLPLUS Section:
# #########################
# PROMPT FOR VARIABLES:
# ####################

if [ ! -d ${USR_ORA_HOME} ]
then
export USR_ORA_HOME=/tmp
fi

GATHERSTATSSCRIPT=${USR_ORA_HOME}/gather_stats_script_DBA_BUNDLE.sql
GATHERSTATSSCRIPTRUNNER=${USR_ORA_HOME}/gather_stats_script_DBA_BUNDLE.sh
GATHERSTATSSPOOL=${USR_ORA_HOME}/gather_stats_script_DBA_BUNDLE.log
STATS_TABLE=BACKUP_STATS
STATS_OWNER=SYS
STATS_TBS=SYSTEM

VAL33=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT STATUS FROM V\$INSTANCE;
EOF
)
VAL44=`echo $VAL33| awk ‘{print $NF}’`
case ${VAL44} in
“OPEN”) echo ;;
*) echo;echo “ERROR: INSTANCE [${ORACLE_SID}] IS IN STATUS: ${VAL44} !”
echo; echo “PLEASE OPEN THE INSTANCE [${ORACLE_SID}] AND RE-RUN THIS SCRIPT.”;echo; exit ;;
esac

echo “Enter the SCHEMA NAME/TABLE OWNER:”
echo “==================================”
while read SCHEMA_NAME
do
if [ -z ${SCHEMA_NAME} ]
then
echo
echo “Enter the SCHEMA NAME/TABLE OWNER:”
echo “==================================”
else
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME=upper(‘${SCHEMA_NAME}’);
EOF
)
VAL22=`echo $VAL11| awk ‘{print $NF}’`
if [ ${VAL22} -eq 0 ]
then
echo
echo “ERROR: USER [${SCHEMA_NAME}] IS NOT EXIST ON DATABASE [${ORACLE_SID}] !”
echo
echo “Enter the SCHEMA NAME:”
echo “=====================”
else
break
fi
fi
done

echo
echo “Enter the TABLE NAME: [BLANK VALUE MEANS GATHER THE WHOLE [${SCHEMA_NAME}] SCHEMA STATISTICS]”
echo “====================”
while read TABLE_NAME
do
if [ -z ${TABLE_NAME} ]
then
echo
echo “Confirm GATHERING STATISTICS ON WHOLE [${SCHEMA_NAME}] SCHEMA? [Y|N] [Y]”
echo “====================================================”
while read ANS
do
case $ANS in
“”|y|Y|yes|YES|Yes) echo “GATHERING STATISTICS ON SCHEMA [${SCHEMA_NAME}] …”
echo
echo “GATHER HISTOGRAMS ALONG WITH STATISTICS? [Y|N] [N]”
echo “=======================================”
while read ANS1
do
case $ANS1 in
y|Y|yes|YES|Yes) HISTO=”FOR ALL COLUMNS SIZE SKEWONLY”;HISTOMSG=”(+HISTOGRAMS)”; break ;;
“”|n|N|no|NO|No) HISTO=”FOR ALL COLUMNS SIZE 1”; break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

# Check The Existence of BACKUP STATS TABLE:
VAL1=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER=upper(‘${STATS_OWNER}’) AND TABLE_NAME=upper(‘${STATS_TABLE}’);
EOF
)
VAL2=`echo $VAL1| awk ‘{print $NF}’`
if [ ${VAL2} -gt 0 ]
then
echo
echo “STATISTICS BACKUP TABLE [${STATS_OWNER}.${STATS_TABLE}] IS ALREADY EXISTS.”
else
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
SET LINESIZE 157
SET PAGESIZE 5000
SET HEADING OFF
SET VERIFY OFF
PROMPT CREATING STATS TABLE [Holds original statistics]…
BEGIN
dbms_stats.create_stat_table (
ownname => upper(‘${STATS_OWNER}’),
tblspace => upper(‘${STATS_TBS}’),
stattab => upper(‘${STATS_TABLE}’));
END;
/
PROMPT
EOF
fi

echo “spool ${GATHERSTATSSPOOL}” > ${GATHERSTATSSCRIPT}
echo “PROMPT BACKING UP CURRENT STATISTICS OF SCHEMA [${SCHEMA_NAME}] …” >>${GATHERSTATSSCRIPT}
echo “BEGIN” >>${GATHERSTATSSCRIPT}
echo “DBMS_STATS.EXPORT_SCHEMA_STATS (” >>${GATHERSTATSSCRIPT}
echo “ownname => upper(‘${SCHEMA_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “statown => upper(‘${STATS_OWNER}’),” >>${GATHERSTATSSCRIPT}
echo “stattab => upper(‘${STATS_TABLE}’));” >>${GATHERSTATSSCRIPT}
echo “END;” >>${GATHERSTATSSCRIPT}
echo “/” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT GATHERING STATISTICS ${HISTOMSG} ON SCHEMA [${SCHEMA_NAME}] …” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT Feel free to exist this session any time as this script is running in the background :-)” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT To check the progress:” >>${GATHERSTATSSCRIPT}
echo “PROMPT tail -f ${GATHERSTATSSPOOL}” >>${GATHERSTATSSCRIPT}
echo “BEGIN” >>${GATHERSTATSSCRIPT}
echo “DBMS_STATS.GATHER_SCHEMA_STATS (” >>${GATHERSTATSSCRIPT}
echo “ownname => upper(‘${SCHEMA_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “METHOD_OPT => ‘${HISTO}’,” >>${GATHERSTATSSCRIPT}
echo “DEGREE => DBMS_STATS.AUTO_DEGREE,” >>${GATHERSTATSSCRIPT}
echo “estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);” >>${GATHERSTATSSCRIPT}
echo “END;” >>${GATHERSTATSSCRIPT}
echo “/” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT (IN CASE THE NEW STATISTICS ARE PERFORMING BAD, RESTORE BACK THE ORIGINAL STATISTICS USING THE FOLLOWING SQL COMMAND):” >>${GATHERSTATSSCRIPT}
echo “PROMPT >>>>” >>${GATHERSTATSSCRIPT}
echo “PROMPT EXEC DBMS_STATS.IMPORT_SCHEMA_STATS (ownname => upper(‘${SCHEMA_NAME}’), statown => upper(‘${STATS_OWNER}’), stattab => upper(‘${STATS_TABLE}’));;”>>${GATHERSTATSSCRIPT}
echo “PROMPT >>>>” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT SCRIPT COMPLETED!” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “spool off” >>${GATHERSTATSSCRIPT}
echo “exit” >>${GATHERSTATSSCRIPT}

echo “#!/bin/bash” > ${GATHERSTATSSCRIPTRUNNER}
echo “nohup ${ORACLE_HOME}/bin/sqlplus \”/ as sysdba\” @${GATHERSTATSSCRIPT} | tee ${GATHERSTATSSPOOL} 2>&1 &” >>${GATHERSTATSSCRIPTRUNNER}
chmod 740 ${GATHERSTATSSCRIPTRUNNER}
echo “”
source ${GATHERSTATSSCRIPTRUNNER}

exit 1 ;;
n|N|no|NO|No) echo; echo “Enter the TABLE NAME:”;echo “====================”;break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done
else
# Check The Existence of ENTERED TABLE:
VAL1=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER=upper(‘${SCHEMA_NAME}’) AND TABLE_NAME=upper(‘${TABLE_NAME}’);
EOF
)
VAL2=`echo $VAL1| awk ‘{print $NF}’`
if [ ${VAL2} -eq 0 ]
then
echo
echo “ERROR: TABLE [${SCHEMA_NAME}.${TABLE_NAME}] IS NOT EXIST !”
echo;echo “Enter the TABLE NAME: [BLANK VALUE MEANS GATHER THE WHOLE SCHEMA [${SCHEMA_NAME}] STATISTICS]”
echo “====================”
else
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
SET LINESIZE 157
SET PAGESIZE 5000
SELECT TABLE_NAME,to_char(LAST_ANALYZED, ‘DD-MON-YYYY HH24:MI:SS’)LAST_STATISTICS_DATE FROM DBA_TABLES WHERE TABLE_NAME=upper(‘${TABLE_NAME}’);
EOF
break
fi
fi
done

echo
echo “GATHER HISTOGRAMS ALONG WITH STATISTICS? [Y|N] [N]”
echo “=======================================”
while read ANS1
do
case $ANS1 in
y|Y|yes|YES|Yes) HISTO=”FOR ALL COLUMNS SIZE SKEWONLY”; HISTOMSG=”(+HISTOGRAMS)”;break ;;
“”|n|N|no|NO|No) HISTO=”FOR ALL COLUMNS SIZE 1”; break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

echo
echo “GATHER STATISTICS ON ALL TABLE’s INDEXES? [Y|N] [Y]”
echo “=========================================”
while read ANS2
do
case $ANS2 in
“”|y|Y|yes|YES|Yes) CASCD=”TRUE”;CASCMSG=”AND ITS INDEXES”; break ;;
n|N|no|NO|No) CASCD=”FALSE”; break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

# Execution of SQL Statement:
# ##########################

VAL1=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER=upper(‘${STATS_OWNER}’) AND TABLE_NAME=upper(‘${STATS_TABLE}’);
EOF
)
VAL2=`echo $VAL1| awk ‘{print $NF}’`
if [ ${VAL2} -gt 0 ]
then
echo
echo “BACKUP STATS TABLE [${STATS_OWNER}.${STATS_TABLE}] IS ALREADY EXISTS.”
else
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
SET LINESIZE 157
SET PAGESIZE 5000
SET HEADING OFF
SET VERIFY OFF
PROMPT CREATING BACKUP STATS TABLE …
BEGIN
dbms_stats.create_stat_table (
ownname => upper(‘${STATS_OWNER}’),
tblspace => upper(‘${STATS_TBS}’),
stattab => upper(‘${STATS_TABLE}’));
END;
/
PROMPT
EOF
fi

echo “SET LINESIZE 157” > ${GATHERSTATSSCRIPT}
echo “SET PAGESIZE 5000” >>${GATHERSTATSSCRIPT}
echo “SET HEADING OFF” >>${GATHERSTATSSCRIPT}
echo “spool ${GATHERSTATSSPOOL}” >>${GATHERSTATSSCRIPT}
echo “PROMPT BACKING UP CURRENT STATISTICS OF TABLE [${SCHEMA_NAME}.${TABLE_NAME}] …” >>${GATHERSTATSSCRIPT}
echo “BEGIN” >>${GATHERSTATSSCRIPT}
echo “DBMS_STATS.EXPORT_TABLE_STATS (” >>${GATHERSTATSSCRIPT}
echo “ownname => upper(‘${SCHEMA_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “tabname => upper(‘${TABLE_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “statown => upper(‘${STATS_OWNER}’),” >>${GATHERSTATSSCRIPT}
echo “stattab => upper(‘${STATS_TABLE}’));” >>${GATHERSTATSSCRIPT}
echo “END;” >>${GATHERSTATSSCRIPT}
echo “/” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT GATHERING STATISTICS ${HISTOMSG} FOR TABLE [${SCHEMA_NAME}.${TABLE_NAME}] ${CASCMSG} …” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT Feel free to exist this session any time as this script is running in the background :-)” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT To check the progress:” >>${GATHERSTATSSCRIPT}
echo “PROMPT tail -f ${GATHERSTATSSPOOL}” >>${GATHERSTATSSCRIPT}
echo “BEGIN” >>${GATHERSTATSSCRIPT}
echo “DBMS_STATS.GATHER_TABLE_STATS (” >>${GATHERSTATSSCRIPT}
echo “ownname => upper(‘${SCHEMA_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “tabname => upper(‘${TABLE_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “cascade => ${CASCD},” >>${GATHERSTATSSCRIPT}
echo “METHOD_OPT => ‘${HISTO}’,” >>${GATHERSTATSSCRIPT}
echo “DEGREE => DBMS_STATS.AUTO_DEGREE,” >>${GATHERSTATSSCRIPT}
echo “estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);” >>${GATHERSTATSSCRIPT}
echo “END;” >>${GATHERSTATSSCRIPT}
echo “/” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT => IN CASE THE NEW STATISTICS ARE PERFORMING BAD, RESTORE BACK THE ORIGINAL STATISTICS USING THE FOLLOWING SQL COMMAND:” >>${GATHERSTATSSCRIPT}
echo “PROMPT >>>>” >>${GATHERSTATSSCRIPT}
echo “PROMPT EXEC DBMS_STATS.IMPORT_TABLE_STATS (ownname => upper(‘${SCHEMA_NAME}’), tabname => upper(‘${TABLE_NAME}’), statown => upper(‘${STATS_OWNER}’), stattab => upper(‘${STATS_TABLE}’));;” >>${GATHERSTATSSCRIPT}
echo “PROMPT >>>>” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT SCRIPT COMPLETED!” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “spool off” >>${GATHERSTATSSCRIPT}
echo “exit” >>${GATHERSTATSSCRIPT}

echo “#!/bin/bash” > ${GATHERSTATSSCRIPTRUNNER}
echo “nohup ${ORACLE_HOME}/bin/sqlplus \”/ as sysdba\” @${GATHERSTATSSCRIPT} | tee ${GATHERSTATSSPOOL} 2>&1 &” >>${GATHERSTATSSCRIPTRUNNER}
chmod 740 ${GATHERSTATSSCRIPTRUNNER}
echo “”
source ${GATHERSTATSSCRIPTRUNNER}

# #############
# END OF SCRIPT
# #############

To check invalid objects

#################################################
# Script to check invalid objects
#################################################

#############
# Description:
#############
echo
echo “=====================================================”
echo “This script Checks ALL INVALID OBJECTS on a database.”
echo “=====================================================”
echo
sleep 1

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe “|” as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB=”\-MGMTDB|ASM” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# ###########################
# Listing Available Databases:
# ###########################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
then
echo No Database Running !
exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
then
export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
then
echo
echo “Select the ORACLE_SID:[Enter the number]”
echo ———————
select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )
do
if [ -z “${REPLY##[0-9]*}” ]
then
export ORACLE_SID=$DB_ID
echo Selected Instance:
echo $DB_ID
break
else
export ORACLE_SID=${REPLY}
break
fi
done

fi
# Exit if the user selected a Non Listed Number:
if [ -z “${ORACLE_SID}” ]
then
echo “You’ve Entered An INVALID ORACLE_SID”
exit
fi

# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v “\-MGMTDB”|awk ‘{print $1}’|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ‘:’|tail -1`

# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi

# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk ‘{print $1}’`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk ‘{print $NF}’|sed -e ‘s/\/dbs//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from PWDX is ${ORACLE_HOME}”

# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME
fi
#echo “ORACLE_HOME from oratab is ${ORACLE_HOME}”
fi

# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e ‘s/ORACLE_HOME=//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from environment is ${ORACLE_HOME}”
fi

# ATTEMPT4: If ORACLE_HOME is not found in the environment search user’s profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h ‘ORACLE_HOME=\/’ $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_HOME
#echo “ORACLE_HOME from User Profile is ${ORACLE_HOME}”
fi

# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e ‘s/\/bin\/orapipe//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from orapipe search is ${ORACLE_HOME}”
fi

# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo “Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly”
echo “e.g.”
echo “export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1”
exit
fi

##########################################
# Exit if the user is not the Oracle Owner:
##########################################
CURR_USER=`whoami`
if [ ${ORA_USER} != ${CURR_USER} ]; then
echo “”
echo “You’re Running This Sctipt with User: \”${CURR_USER}\” !!!”
echo “Please Run This Script With The Right OS User: \”${ORA_USER}\””
echo “Script Terminated!”
exit
fi

####################
# VARIABLES:
####################
LOC1=${USR_ORA_HOME}
cd ${LOC1}

#################################
# SQLPLUS: Check Invalid Objects
#################################
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ <<EOF
set pages 0
SET LINESIZE 190
SET PAGESIZE 5000
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
spool FIX_INVALID_OBJ.sql
select ‘alter package ‘||owner||’.’||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type like ‘%PACKAGE%’ union
select ‘alter type ‘||owner||’.’||object_name||’ compile specification;’ from dba_objects where status <> ‘VALID’ and object_type like ‘%TYPE%’union
select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type not in (‘PACKAGE’,’PACKAGE BODY’,’SYNONYM’,’TYPE’,’TYPE BODY’) union
select ‘alter public synonym ‘||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type =’SYNONYM’;
spool off
select ‘Invalid Objects#: ‘||count(distinct (owner||object_name)) a from dba_objects where status <> ‘VALID’;
EOF

# Check if the fix script is exist:
if [ -f ${LOC1}/FIX_INVALID_OBJ.sql ]
then
echo;echo “Do you want to FIX THOSE INVALID Objects? [Y|N] [Y]”
echo “========================================”
while read ANS
do
case $ANS in
“”|y|Y|yes|YES|Yes) echo;echo “START COMPILING INVALID OBJECTS …”;sleep 1
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set heading off
@FIX_INVALID_OBJ.sql
@FIX_INVALID_OBJ.sql
PROMPT
PROMPT List of remaining INVALID Objects …
PROMPT ———————————

PROMPT
select ‘alter PACKAGE ‘||owner||’.’||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type like ‘%PACKAGE%’ union
select ‘alter TYPE ‘||owner||’.’||object_name||’ compile specification;’ from dba_objects where status <> ‘VALID’ and object_type like ‘%TYPE%’union
select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type not in (‘PACKAGE’,’PACKAGE BODY’,’SYNONYM’,’TYPE’,’TYPE BODY’) union
select ‘alter public synonym ‘||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type =’SYNONYM’;
select ‘Invalid Objects#: ‘||count(distinct (owner||object_name)) a from dba_objects where status <> ‘VALID’;
EOF
break ;;
n|N|no|NO|No) echo;echo “Later To FIX the invalid objects:”
echo “run script: ${LOC1}/FIX_INVALID_OBJ.sql”
echo “OR run: @?/rdbms/admin/utlrp.sql”
echo;exit;break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done
fi
# #############
# END OF SCRIPT
# #############

EXPORT DATABASE or SCHEMA or TABLE.

# #############################################
# EXPORT DATABASE | SCHEMA | TABLE.
# To be run by ORACLE user
# ##############################################

# ###########
# Description:
# ###########
echo
echo “==============================================”
echo “This script EXPORTS DATABASE | SCHEMA | TABLE.”
echo “==============================================”
echo
sleep 1

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe “|” as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB=”\-MGMTDB|ASM” #Excluded INSTANCES [Will not get reported offline].

# ###########################
# Listing Available Instances:
# ###########################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
then
echo No Database Running !
exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
then
export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
then
echo
echo “Select the Instance You Want To Run this Script Against:[Enter the number]”
echo “——————————————————-”
select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )
do
if [ -z “${REPLY##[0-9]*}” ]
then
export ORACLE_SID=$DB_ID
echo Selected Instance:
echo
echo “********”
echo $DB_ID
echo “********”
echo
break
else
export ORACLE_SID=${REPLY}
break
fi
done

fi
# Exit if the user selected a Non Listed Number:
if [ -z “${ORACLE_SID}” ]
then
echo “You’ve Entered An INVALID ORACLE_SID”
exit
fi

# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $1}’|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ‘:’|tail -1`

## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME
PARALLEL_DEGREE=`cat /proc/cpuinfo| grep processor|wc -l`
if [ “${PARALLEL_DEGREE##[0-9]*}” ]
then
PARALLEL_DEGREE=1
fi

## If OS is SUN:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME
PARALLEL_DEGREE=`kstat cpu_info|grep core_id|sort -u|wc -l`
if [ -z “${PARALLEL_DEGREE##[0-9]*}” ]
then
PARALLEL_DEGREE=1
fi
fi

PARALLEL_DEGREE=1

## If oratab is not exist, or ORACLE_SID not added to oratab, find ORACLE_HOME in user’s profile:
if [ -z “${ORACLE_HOME}” ]
then
ORACLE_HOME=`grep -h ‘ORACLE_HOME=\/’ $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_HOME
fi

# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
CURR_USER=`whoami`
if [ ${ORA_USER} != ${CURR_USER} ]; then
echo “”
echo “You’re Running This Sctipt with User: \”${CURR_USER}\” !!!”
echo “Please Run This Script With The Right OS User: \”${ORA_USER}\””
echo “Script Terminated!”
exit
fi

# ########################
# Getting ORACLE_BASE:
# ########################
# Get ORACLE_BASE from user’s profile if not set:

if [ -z “${ORACLE_BASE}” ]
then
ORACLE_BASE=`grep -h ‘ORACLE_BASE=\/’ $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_BASE
fi

# #########################
# EXPORT Section:
# #########################
# PROMPT FOR VARIABLES:
# ####################
DUMPDATE=`date +%m-%d-%y`
#PASSHALF=`echo $((RANDOM % 999+7000))`
PASSHALF=`date ‘+%s’`

# If expdp version is 10g don’t use REUSE_DUMPFILES parameter in the script:
VERSION=`strings ${ORACLE_HOME}/bin/expdp|grep Release|awk ‘{print $3}’`

case ${VERSION} in
10g) REUSE_DUMP=”;;
*) REUSE_DUMP=’REUSE_DUMPFILES=Y’;;
# *) REUSE_DUMP=’REUSE_DUMPFILES=Y COMPRESSION=ALL’;;
esac

VAL33=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT STATUS FROM V\$INSTANCE;
EOF
)
VAL44=`echo $VAL33| awk ‘{print $NF}’`
case ${VAL44} in
“OPEN”) echo ;;
*) echo;echo “ERROR: INSTANCE [$ORACLE_SID] IS IN STATUS: ${VAL44} !”
echo; echo “PLEASE OPEN THE INSTANCE [$ORACLE_SID] AND RE-RUN THIS SCRIPT.”;echo; exit ;;
esac

echo “WHERE TO SAVE THE EXPORT FILE [DUMPFILE]? [ENTER THE DIRECOTRY PATH]”
echo “========================================”
while read LOC1
do
if [ ! -d ${LOC1} ]; then
echo
echo “ERROR: THE LOCATION YOU HAVE PROVIDED IS NOT EXIST OR WRITABLE !”
echo
echo “Please Enter the location where you want to save the EXPORT FILE [DUMPFILE]: [ENTER THE DIRECTORY PATH]”
echo “==========================================================================”
elif [ -z ${LOC1} ]; then
echo
echo “ERROR: THE LOCATION YOU HAVE PROVIDED IS NOT EXIST OR WRITABLE !”
echo
echo “Please Enter the location where you want to save the EXPORT FILE [DUMPFILE]: [ENTER THE DIRECTORY PATH]”
echo “==========================================================================”
else
break
fi
done

# #######################
# EXPORT DATABASE SECTION:
# #######################
echo
echo “Do you want to EXPORT FULL DATABASE? [Y|N] [Y] [N TO EXPORT SCHEMA|TABLE]”
echo “===================================”
while read ANS
do
case $ANS in
“”|y|Y|yes|YES|Yes) echo;echo “EXPORT FULL DATABASE MODE …”;sleep 1
echo;echo “WHICH EXPORT UTILITY YOU WANT TO USE: [1) DATAPUMP [EXPDP]]”
echo “====================================”
echo “1) DATAPUMP [EXPDP]”
echo “2) LEGACY EXPORT [EXP]”
while read EXP_TOOL
do
case $EXP_TOOL in
“”|”1″|”DATAPUMP”|”datapump”|”DATAPUMP [EXPDP]”|”[EXPDP]”|”EXPDP”|”expdp”)
cd ${LOC1}
SPOOLFILE2=AFTER_IMPORT_DATABASE_${ORACLE_SID}.sql
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT CREATE USER DBA_EXP [EXPORTER USER] (WILL BE DROPPED AFTER THE EXPORT) …
CREATE USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
ALTER USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
GRANT CREATE SESSION TO DBA_EXP;
GRANT DBA TO DBA_EXP;
— The following privileges to workaround Bug 6392040:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO DBA_EXP;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO DBA_EXP;
PROMPT
PROMPT CREATING DIRECTORY EXPORT_FILES_DBA_BUNDLE POINTING TO $LOC1 …
CREATE OR REPLACE DIRECTORY EXPORT_FILES_DBA_BUNDLE AS ‘$LOC1’;
PROMPT
PROMPT CREATING AFTER DATABASE IMPORT SCRIPT …
PROMPT
SET PAGES 0 TERMOUT OFF LINESIZE 157 ECHO OFF FEEDBACK OFF
SPOOL $SPOOLFILE2
SELECT ‘PROMPT ‘ FROM DUAL;
SELECT ‘PROMPT COMPILING DATABASE INVALID OBJECTS …’ FROM DUAL;
SELECT ‘@?/rdbms/admin/utlrp’ FROM DUAL;
SELECT ‘@?/rdbms/admin/utlrp’ FROM DUAL;
SELECT ‘PROMPT ‘ FROM DUAL;
SELECT ‘PROMPT THE FOLLOWING TRIGGERS ARE OWNED BY SYS SCHEMA AND MAY NOT BE EXIST AFTER THE IMPORT’ FROM DUAL;
SELECT ‘PROMPT YOU MAY CONSIDER CREATING THE NON EXIST TRIGGERS IF YOU NEED SO:’ FROM DUAL;
SELECT ‘PROMPT ***************************************************************’ FROM DUAL;
SELECT ‘PROMPT ‘||TRIGGER_TYPE||’ TRIGGER: ‘||TRIGGER_NAME FROM DBA_TRIGGERS WHERE OWNER=UPPER(‘SYS’) ORDER BY 1;
SELECT ‘PROMPT ‘ FROM DUAL;
SELECT ‘PROMPT ARE THESE DIRECTORIES POINTING TO THE RIGHT PATHS? ‘ FROM DUAL;
SELECT ‘PROMPT ************************************************** ‘ FROM DUAL;
COL DIRECTORY FOR A50
COL DIRECTORY_PATH FOR A100
SELECT ‘PROMPT ‘||OWNER||’.’||DIRECTORY_NAME||’: ‘||DIRECTORY_PATH FROM DBA_DIRECTORIES;
SELECT ‘PROMPT ‘ FROM DUAL;
SPOOL OFF
EOF
echo
echo “EXPORTING DATABASE $ORACLE_SID [USING DATAPUMP] …”
sleep 1
${ORACLE_HOME}/bin/expdp DBA_EXP/”bundle_${PASSHALF}” ${REUSE_DUMP} FULL=y PARALLEL=${PARALLEL_DEGREE} DIRECTORY=EXPORT_FILES_DBA_BUNDLE DUMPFILE=FULL_EXPORT_${ORACLE_SID}_${DUMPDATE}.dmp LOGFILE=FULL_EXPORT_${ORACLE_SID}_${DUMPDATE}.log

## Export METADATA ONLY: <using Legacy EXP because it’s more reliable than EXPDP in exporting DDLs>
#echo;echo “CREATING A FILE CONTAINS ALL CREATION [DDL] STATEMENT OF ALL USERS|OBJECTS …”;sleep 1
#${ORACLE_HOME}/bin/exp DBA_EXP/”bundle_${PASSHALF}” FULL=y ROWS=N STATISTICS=NONE FILE=${LOC1}/${ORACLE_SID}_METADATA_${DUMPDATE}.dmp log=${LOC1}/${ORACLE_SID}_METADATA_${DUMPDATE}.log

## Getting READABLE export script: [DUMP REFINING]
#/usr/bin/strings ${LOC1}/${ORACLE_SID}_METADATA_${DUMPDATE}.dmp > ${LOC1}/${ORACLE_SID}_METADATA_REFINED_${DUMPDATE}.trc

# Dropping user DBA_EXP:
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT
PROMPT DROPPING THE EXPORTER USER DBA_EXP (SAFELY) …
DROP USER DBA_EXP;
EOF

echo
echo “*****************”
echo “IMPORT GUIDELINES:”
echo “*****************”
echo “Later, AFTER YOU IMPORT THE DUMPFILE, IT’S RECOMMENDED TO RUN THIS SQL SCRIPT: ${LOC1}/$SPOOLFILE2″
echo ” => IT INCLUDES (HINT FOR TRIGGERS OWNED BY SYS) WHICH WILL NOT BE CREATED DURING THE IMPORT PROCESS + COMPILING INVALID OBJECTS.”
echo
echo “*************************”
echo “EXPORT DUMP FILE LOCATION:”
echo “*************************”
#echo “MAIN EXPORT FILE (DATA+METADATA):”
#echo “——————————–”
echo “${LOC1}/FULL_EXPORT_${ORACLE_SID}_${DUMPDATE}.dmp”
#echo
#echo “EXTRA FILES:”
#echo “———–”
#echo “METADATA ONLY DUMP FILE <IMPORTABLE with [legacy exp utility]>: ${LOC1}/${ORACLE_SID}_METADATA_${DUMPDATE}.dmp”
#echo “DDL Script FILE <READABLE | Cannot be Imported>: ${LOC1}/${ORACLE_SID}_METADATA_REFINED_${DUMPDATE}.trc”
#echo “*****************************************************************”
echo; exit ;;
“2”|”LEGACY EXPORT”|”LEGACY”|”EXPORT”|”LEGACY EXPORT [EXP]”|”EXP”|”[EXP]”|”exp”|”legacy export”|”legacy”|”export”)
echo
echo “EXPORTING DATABASE $ORACLE_SID [USING LEGACY EXP] …”
sleep 1
cd ${LOC1}
SPOOLFILE2=AFTER_IMPORT_DATABASE_${ORACLE_SID}.sql
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT CREATE USER DBA_EXP [EXPORTER USER] (WILL BE DROPPED AFTER THE EXPORT) …
CREATE USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
ALTER USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
GRANT CREATE SESSION TO DBA_EXP;
GRANT EXP_FULL_DATABASE TO DBA_EXP;
— The following privileges to workaround Bug 6392040:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO DBA_EXP;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO DBA_EXP;
PROMPT
PROMPT CREATING AFTER DATABASE IMPORT SCRIPT …
PROMPT
SET PAGES 0 TERMOUT OFF LINESIZE 157 ECHO OFF FEEDBACK OFF
SPOOL $SPOOLFILE2
SELECT ‘PROMPT COMPILING DATABASE INVALID OBJECTS …’ FROM DUAL;
SELECT ‘@?/rdbms/admin/utlrp’ FROM DUAL;
SELECT ‘@?/rdbms/admin/utlrp’ FROM DUAL;
SELECT ‘PROMPT ‘ FROM DUAL;
SELECT ‘PROMPT THE FOLLOWING TRIGGERS ARE OWNED BY SYS SCHEMA AND MAY NOT BE EXIST AFTER THE IMPORT’ FROM DUAL;
SELECT ‘PROMPT YOU MAY CONSIDER CREATING THE NON EXIST TRIGGERS IF YOU NEED SO:’ FROM DUAL;
SELECT ‘PROMPT ***************************************************************’ FROM DUAL;
SELECT ‘PROMPT ‘||TRIGGER_TYPE||’ TRIGGER: ‘||TRIGGER_NAME FROM DBA_TRIGGERS WHERE OWNER=UPPER(‘SYS’) ORDER BY 1;
SELECT ‘PROMPT ARE THESE DIRECTORIES POINTING TO THE RIGHT PATHS? ‘ FROM DUAL;
COL DIRECTORY FOR A50
COL DIRECTORY_PATH FOR A100
SELECT ‘PROMPT ‘||OWNER||’.’||DIRECTORY_NAME||’: ‘||DIRECTORY_PATH FROM DBA_DIRECTORIES;
SPOOL OFF
EOF

${ORACLE_HOME}/bin/exp DBA_EXP/”bundle_${PASSHALF}” FULL=y DIRECT=y CONSISTENT=y STATISTICS=NONE FEEDBACK=1000 FILE=${LOC1}/FULL_EXPORT_${ORACLE_SID}_${DUMPDATE}.dmp log=${LOC1}/FULL_EXPORT_${ORACLE_SID}_${DUMPDATE}.log

## Export METADATA ONLY: <using Legacy EXP because it’s more reliable than EXPDP in exporting DDLs>
#echo
#echo “CREATING A FILE CONTAINS ALL CREATION [DDL] STATEMENT OF ALL USERS|OBJECTS …”
#sleep 1
#${ORACLE_HOME}/bin/exp DBA_EXP/”bundle_${PASSHALF}” FULL=y ROWS=N STATISTICS=NONE FILE=${LOC1}/${ORACLE_SID}_METADATA_${DUMPDATE}.dmp log=${LOC1}/${ORACLE_SID}_METADATA_${DUMPDATE}.log
## Removing Extra Bad characters: [DUMP REFINING]
#/usr/bin/strings ${LOC1}/${ORACLE_SID}_METADATA_${DUMPDATE}.dmp > ${LOC1}/${ORACLE_SID}_METADATA_REFINED_${DUMPDATE}.trc

# DRPOPPING USER DBA_EXP:
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT
PROMPT DROPPING THE EXPORTER USER DBA_EXP (SAFELY) …
DROP USER DBA_EXP;
EOF

echo
echo “*****************”
echo “IMPORT GUIDELINES:”
echo “*****************”
echo “Later, AFTER IMPORTING THE DUMPFILE IT’S RECOMMENDED TO RUN THIS SQL SCRIPT: ${LOC1}/$SPOOLFILE2″
echo ” => IT INCLUDES (HINTS FOR TRIGGERS OWNED BY SYS) WHICH WILL NOT BE CREATED BY THE IMPORT PROCESS + COMPILING INVALID OBJECTS.”
echo
echo
echo “*************************”
echo “EXPORT DUMP FILE LOCATION:”
echo “*************************”
#echo “MAIN EXPORT FILE (DATA+METADATA):”
#echo “——————————–”
echo “${LOC1}/FULL_EXPORT_${ORACLE_SID}_${DUMPDATE}.dmp”
#echo
#echo “EXTRA FILES:”
#echo “———–”
#echo “METADATA ONLY DUMP FILE <IMPORTABLE with [legacy exp utility]>: ${LOC1}/${ORACLE_SID}_METADATA_${DUMPDATE}.dmp”
#echo “DDL Script FILE <READABLE | Cannot be Imported>: ${LOC1}/${ORACLE_SID}_METADATA_REFINED_${DUMPDATE}.trc”
#echo “*****************************************************************”
echo; exit ;;
*) echo “Please Enter a VALID Answer [1|2] [1]”; echo “=================================” ;;
esac
done
;;
n|N|no|NO|No) echo; echo “EXPORT SCHEMA MODE …”;echo;sleep 1; break ;;
*) echo “Please enter a VALID answer [Y|N]”; echo “=================================” ;;
esac
done

# #####################
# EXPORT SCHEMA SECTION:
# #####################

echo “Do you want to EXPORT a SCHEMA? [Y|N] [Y] [N If you want to EXPORT TABLE]”
echo “==============================”
while read ANS2
do
case $ANS2 in
“”|y|Y|yes|YES|Yes)
echo; echo “Please Enter the SCHEMA NAME:”
echo “============================”
while read SCHEMA_NAME
do
if [ -z ${SCHEMA_NAME} ]
then
echo
echo “Enter the SCHEMA NAME:”
echo “=====================”
else
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME=upper(‘$SCHEMA_NAME’);
EOF
)
VAL22=`echo $VAL11| awk ‘{print $NF}’`
if [ ${VAL22} -eq 0 ]
then
echo
echo “ERROR: USER [${SCHEMA_NAME}] IS NOT EXIST ON DATABASE [$ORACLE_SID] !”
echo; echo “Enter the SCHEMA NAME:”
echo “=====================”
else
break
fi
fi
done

echo;echo “WHICH EXPORT UTILITY YOU WANT TO USE: [1) DATAPUMP [EXPDP]]”
echo “====================================”
echo “1) DATAPUMP [EXPDP]”
echo “2) LEGACY EXPORT [EXP]”
while read EXP_TOOL
do
case $EXP_TOOL in
“”|”1″|”DATAPUMP”|”datapump”|”DATAPUMP [EXPDP]”|”[EXPDP]”|”EXPDP”|”expdp”)
cd ${LOC1}
SPOOLFILE1=BEFORE_IMPORT_SCHEMA_$SCHEMA_NAME.sql
SPOOLFILE2=AFTER_IMPORT_SCHEMA_$SCHEMA_NAME.sql

${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT CREATE USER DBA_EXP [EXPORTER USER] (WILL BE DROPPED AFTER THE EXPORT) …
CREATE USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
ALTER USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
GRANT CREATE SESSION TO DBA_EXP;
GRANT DBA TO DBA_EXP;
— The following privileges to workaround Bug 6392040:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO DBA_EXP;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO DBA_EXP;
PROMPT
PROMPT CREATING DIRECTORY EXPORT_FILES_DBA_BUNDLE POINTING TO $LOC1 …
CREATE OR REPLACE DIRECTORY EXPORT_FILES_DBA_BUNDLE AS ‘$LOC1’;
PROMPT
PROMPT CREATING BEFORE SCHEMA IMPORT SCRIPT …
PROMPT
SET PAGES 0 TERMOUT OFF LINESIZE 157 ECHO OFF FEEDBACK OFF
SPOOL $SPOOLFILE1
SELECT ‘CREATE USER ‘ || u.username ||’ IDENTIFIED ‘ ||’ BY VALUES ”’ || c.password || ”’ DEFAULT TABLESPACE ‘ || u.default_tablespace ||’ TEMPORARY TABLESPACE ‘ || u.temporary_tablespace ||’ PROFILE ‘ || u.profile || case when account_status= ‘OPEN’ then ‘;’ else ‘ Account LOCK;’ end “–Creation Statement”
FROM dba_users u,user$ c where u.username=c.name and u.username=upper(‘$SCHEMA_NAME’)
UNION
SELECT ‘CREATE ROLE ‘||GRANTED_ROLE||’;’ FROM DBA_ROLE_PRIVS WHERE GRANTEE=UPPER(‘$SCHEMA_NAME’)
UNION
select ‘GRANT ‘||GRANTED_ROLE||’ TO ‘||GRANTEE|| case when ADMIN_OPTION=’YES’ then ‘ WITH ADMIN OPTION;’ else ‘;’ end “Granted Roles”
from dba_role_privs where grantee= upper(‘$SCHEMA_NAME’)
UNION
select ‘GRANT ‘||PRIVILEGE||’ TO ‘||GRANTEE|| case when ADMIN_OPTION=’YES’ then ‘ WITH ADMIN OPTION;’ else ‘;’ end “Granted System Privileges”
from dba_sys_privs where grantee= upper(‘$SCHEMA_NAME’)
UNION
select ‘GRANT ‘||PRIVILEGE||’ ON ‘||OWNER||’.’||TABLE_NAME||’ TO ‘||GRANTEE||case when GRANTABLE=’YES’ then ‘ WITH GRANT OPTION;’ else ‘;’ end “Granted Object Privileges” from DBA_TAB_PRIVS where GRANTEE=upper(‘$SCHEMA_NAME’);
SPOOL OFF
PROMPT CREATING AFTER SCHEMA IMPORT SCRIPT …
PROMPT
SPOOL $SPOOLFILE2
select ‘GRANT ‘||PRIVILEGE||’ ON ‘||OWNER||’.’||TABLE_NAME||’ TO ‘||GRANTEE||case when GRANTABLE=’YES’ then ‘ WITH GRANT OPTION;’ else ‘;’ end “Granted Object Privileges” from DBA_TAB_PRIVS where OWNER=upper(‘$SCHEMA_NAME’)
UNION
SELECT ‘CREATE PUBLIC SYNONYM ‘||SYNONYM_NAME||’ FOR ‘||TABLE_OWNER||’.’||TABLE_NAME||’;’ FROM DBA_SYNONYMS WHERE TABLE_OWNER=UPPER(‘$SCHEMA_NAME’) AND OWNER=UPPER(‘PUBLIC’);
PROMPT
SELECT ‘PROMPT COMPILING DATABASE INVALID OBJECTS …’ FROM DUAL;
SELECT ‘@?/rdbms/admin/utlrp’ FROM DUAL;
SELECT ‘@?/rdbms/admin/utlrp’ FROM DUAL;
SELECT ‘PROMPT ‘ FROM DUAL;
SELECT ‘PROMPT THE FOLLOWING TRIGGERS ARE OWNED BY OTHER USERS BUT ARE DEPENDING ON SCHEMA $SCHEMA_NAME OBJECTS’ FROM DUAL;
SELECT ‘PROMPT YOU MAY CONSIDER TO CREATE THEM AFTER THE SCHEMA IMPORT IF YOU NEED SO:’ FROM DUAL;
SELECT ‘PROMPT **********************************************************************’ FROM DUAL;
SELECT ‘PROMPT ‘||TRIGGER_TYPE||’ TRIGGER: ‘||OWNER||’.’||TRIGGER_NAME||’ =>ON TABLE: ‘||TABLE_OWNER||’.’||TABLE_NAME FROM DBA_TRIGGERS WHERE TABLE_OWNER=UPPER(‘$SCHEMA_NAME’) AND OWNER <> UPPER(‘$SCHEMA_NAME’) ORDER BY 1;
SPOOL OFF
EOF
echo
echo “EXPORTING SCHEMA ${SCHEMA_NAME} [USING DATAPUMP] …”
sleep 1
${ORACLE_HOME}/bin/expdp DBA_EXP/”bundle_${PASSHALF}” ${REUSE_DUMP} SCHEMAS=${SCHEMA_NAME} PARALLEL=${PARALLEL_DEGREE} DIRECTORY=EXPORT_FILES_DBA_BUNDLE DUMPFILE=EXPORT_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp LOGFILE=EXPORT_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.log

## Export METADATA ONLY: <using Legacy EXP because it’s more reliable than EXPDP in exporting DDLs>
#echo
#echo “CREATING A FILE CONTAINS ALL CREATION [DDL] STATEMENTS OF ALL OBJECTS …”
#sleep 1
#${ORACLE_HOME}/bin/exp DBA_EXP/”bundle_${PASSHALF}” OWNER=${SCHEMA_NAME} ROWS=N STATISTICS=NONE FILE=${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_${DUMPDATE}.dmp log=${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_${DUMPDATE}.log

## Removing Extra Bad characters: [DUMP REFINING]
#/usr/bin/strings ${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_${DUMPDATE}.dmp > ${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_REFINED_${DUMPDATE}.trc

# DRPOPPING USER DBA_EXP:
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT
PROMPT DROPPING THE EXPORTER USER DBA_EXP (SAFELY) …
DROP USER DBA_EXP;
EOF

echo
echo “*****************”
echo “IMPORT GUIDELINES:”
echo “*****************”
echo “BEFORE IMPORTING THE DUMPFILE IT’S RECOMMENDED TO RUN THIS SQL SCRIPT: ${LOC1}/$SPOOLFILE1″
echo ” => IT INCLUDES (USER|ROLES|GRANTED PRIVILEGES CREATION STATEMENTS), WHICH WILL NOT BE CREATED DURING THE IMPORT PROCESS.”
echo
echo “Later, AFTER IMPORTING THE DUMPFILE IT’S RECOMMENDED TO RUN THIS SQL SCRIPT: ${LOC1}/$SPOOLFILE2″
echo ” => IT INCLUDES (PUBLIC SYNONYMS DDLS|PRIVILEGES GRANTED TO OTHERS|HINTS FOR TRIGGERS OWNED BY OTHERS AND DEPENDANT ON $SCHEMA_NAME OBJECTS)”
echo ” + COMPILING INVALID OBJECTS, SUCH STUFF WILL NOT BE CARRIED OUT BY THE IMPORT PROCESS.”
echo
echo
echo “*************************”
echo “EXPORT DUMP FILE LOCATION:”
echo “*************************”
#echo “*******************************************”
echo “${LOC1}/EXPORT_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp”
#echo “SCHEMA EXPORT (DATA+METADATA) file Location: ${LOC1}/EXPORT_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp”
#echo “SCHEMA METADATA ONLY Script LOCATION <IMPORTABLE (Can be Imported using [exp utility]>: ${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_${DUMPDATE}.dmp”
#echo “SCHEMA METADATA ONLY Script LOCATION <READABLE (CANNOT be Imported)>: ${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_REFINED_${DUMPDATE}.trc”
#echo “*******************************************”
echo; exit ;;
“2”|”LEGACY EXPORT”|”LEGACY”|”EXPORT”|”LEGACY EXPORT [EXP]”|”EXP”|”[EXP]”|”exp”|”legacy export”|”legacy”|”export”)
cd ${LOC1}
SPOOLFILE1=BEFORE_IMPORT_SCHEMA_$SCHEMA_NAME.sql
SPOOLFILE2=AFTER_IMPORT_SCHEMA_$SCHEMA_NAME.sql

${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT CREATE USER DBA_EXP [EXPORTER USER] (WILL BE DROPPED AFTER THE EXPORT) …
CREATE USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
ALTER USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
GRANT CREATE SESSION TO DBA_EXP;
GRANT EXP_FULL_DATABASE TO DBA_EXP;
— The following privileges to workaround Bug 6392040:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO DBA_EXP;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO DBA_EXP;
PROMPT
PROMPT CREATING BEFORE SCHEMA IMPORT SCRIPT …
PROMPT
SET PAGES 0 TERMOUT OFF LINESIZE 157 ECHO OFF FEEDBACK OFF
SPOOL $SPOOLFILE1
SELECT ‘CREATE USER ‘ || u.username ||’ IDENTIFIED ‘ ||’ BY VALUES ”’ || c.password || ”’ DEFAULT TABLESPACE ‘ || u.default_tablespace ||’ TEMPORARY TABLESPACE ‘ || u.temporary_tablespace ||’ PROFILE ‘ || u.profile || case when account_status= ‘OPEN’ then ‘;’ else ‘ Account LOCK;’ end “–Creation Statement”
FROM dba_users u,user$ c where u.username=c.name and u.username=upper(‘$SCHEMA_NAME’)
UNION
SELECT ‘CREATE ROLE ‘||GRANTED_ROLE||’;’ FROM DBA_ROLE_PRIVS WHERE GRANTEE=UPPER(‘$SCHEMA_NAME’)
UNION
select ‘GRANT ‘||GRANTED_ROLE||’ TO ‘||GRANTEE|| case when ADMIN_OPTION=’YES’ then ‘ WITH ADMIN OPTION;’ else ‘;’ end “Granted Roles”
from dba_role_privs where grantee= upper(‘$SCHEMA_NAME’)
UNION
select ‘GRANT ‘||PRIVILEGE||’ TO ‘||GRANTEE|| case when ADMIN_OPTION=’YES’ then ‘ WITH ADMIN OPTION;’ else ‘;’ end “Granted System Privileges”
from dba_sys_privs where grantee= upper(‘$SCHEMA_NAME’)
UNION
select ‘GRANT ‘||PRIVILEGE||’ ON ‘||OWNER||’.’||TABLE_NAME||’ TO ‘||GRANTEE||case when GRANTABLE=’YES’ then ‘ WITH GRANT OPTION;’ else ‘;’ end “Granted Object Privileges” from DBA_TAB_PRIVS where GRANTEE=upper(‘$SCHEMA_NAME’);
SPOOL OFF
PROMPT CREATING AFTER SCHEMA IMPORT SCRIPT …
PROMPT
SPOOL $SPOOLFILE2
select ‘GRANT ‘||PRIVILEGE||’ ON ‘||OWNER||’.’||TABLE_NAME||’ TO ‘||GRANTEE||case when GRANTABLE=’YES’ then ‘ WITH GRANT OPTION;’ else ‘;’ end “Granted Object Privileges” from DBA_TAB_PRIVS where OWNER=upper(‘$SCHEMA_NAME’)
UNION
SELECT ‘CREATE PUBLIC SYNONYM ‘||SYNONYM_NAME||’ FOR ‘||TABLE_OWNER||’.’||TABLE_NAME||’;’ FROM DBA_SYNONYMS WHERE TABLE_OWNER=UPPER(‘$SCHEMA_NAME’) AND OWNER=UPPER(‘PUBLIC’);
PROMPT
SELECT ‘PROMPT COMPILING DATABASE INVALID OBJECTS …’ FROM DUAL;
SELECT ‘@?/rdbms/admin/utlrp’ FROM DUAL;
SELECT ‘@?/rdbms/admin/utlrp’ FROM DUAL;
SELECT ‘PROMPT ‘ FROM DUAL;
SELECT ‘PROMPT THE FOLLOWING TRIGGERS ARE OWNED BY OTHER USERS BUT ARE DEPENDING ON SCHEMA $SCHEMA_NAME OBJECTS’ FROM DUAL;
SELECT ‘PROMPT YOU MAY CONSIDER TO CREATE THEM AFTER THE SCHEMA IMPORT IF YOU NEED SO:’ FROM DUAL;
SELECT ‘PROMPT **********************************************************************’ FROM DUAL;
SELECT ‘PROMPT ‘||TRIGGER_TYPE||’ TRIGGER: ‘||OWNER||’.’||TRIGGER_NAME||’ =>ON TABLE: ‘||TABLE_OWNER||’.’||TABLE_NAME FROM DBA_TRIGGERS WHERE TABLE_OWNER=UPPER(‘$SCHEMA_NAME’) AND OWNER <> UPPER(‘$SCHEMA_NAME’) ORDER BY 1;
SPOOL OFF
EOF
echo
echo “EXPORTING SCHEMA ${SCHEMA_NAME} [USING LEGACY EXP] …”
sleep 1
${ORACLE_HOME}/bin/exp DBA_EXP/”bundle_${PASSHALF}” OWNER=${SCHEMA_NAME} DIRECT=y CONSISTENT=y STATISTICS=NONE FEEDBACK=1000 FILE=${LOC1}/EXPORT_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp log=${LOC1}/EXPORT_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.log

## Export METADATA ONLY: <using Legacy EXP because it’s more reliable than EXPDP in exporting DDLs>
#echo
#echo “CREATING A FILE CONTAINS ALL CREATION [DDL] STATEMENT OF ALL USERS|OBJECTS …”
#sleep 1
#${ORACLE_HOME}/bin/exp DBA_EXP/”bundle_${PASSHALF}” OWNER=${SCHEMA_NAME} ROWS=N STATISTICS=NONE FILE=${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_${DUMPDATE}.dmp log=${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_${DUMPDATE}.log

## Removing Extra Bad characters: [DUMP REFINING]
#/usr/bin/strings ${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_${DUMPDATE}.dmp > ${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_REFINED_${DUMPDATE}.trc

# DRPOPPING USER DBA_EXP:
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT
PROMPT DROPPING THE EXPORTER USER DBA_EXP (SAFELY) …
DROP USER DBA_EXP;
EOF

echo
echo
echo “*****************”
echo “IMPORT GUIDELINES:”
echo “*****************”
echo “BEFORE IMPORTING THE DUMPFILE IT’S RECOMMENDED TO RUN THIS SQL SCRIPT: ${LOC1}/$SPOOLFILE1″
echo ” => IT INCLUDES (USER|ROLES|GRANTED PRIVILEGES CREATION STATEMENTS), WHICH WILL NOT BE CREATED DURING THE IMPORT PROCESS.”
echo
echo “AFTER IMPORTING THE DUMPFILE IT’S RECOMMENDED TO RUN THIS SQL SCRIPT: ${LOC1}/$SPOOLFILE2″
echo ” => IT INCLUDES (PUBLIC SYNONYMS DDLS|PRIVILEGES GRANTED TO OTHERS|HINTS FOR TRIGGERS OWNED BY OTHERS AND DEPENDANT ON $SCHEMA_NAME OBJECTS)”
echo ” + COMPILING INVALID OBJECTS, SUCH STUFF WILL NOT BE CARRIED OUT BY THE IMPORT PROCESS.”
echo
echo
echo “*************************”
echo “EXPORT DUMP FILE LOCATION:”
echo “*************************”
#echo “*******************************************”
echo “${LOC1}/EXPORT_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp”
#echo “SCHEMA EXPORT (DATA+METADATA) file Location: ${LOC1}/EXPORT_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp”
#echo “SCHEMA METADATA ONLY Script LOCATION <IMPORTABLE (Can be Imported using [exp utility]>: ${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_${DUMPDATE}.dmp”
#echo “SCHEMA METADATA ONLY Script LOCATION <READABLE (CANNOT be Imported)>: ${LOC1}/${SCHEMA_NAME}_${ORACLE_SID}_METADATA_REFINED_${DUMPDATE}.trc”
#echo “*******************************************”
echo; exit ;;
esac
done
;;
n|N|no|NO|No) echo; echo “EXPORT TABLE MODE …”;echo;sleep 1; break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

######################
# EXPORT TABLE SECTION:
######################

echo “Please Enter the TABLE OWNER:”
echo “============================”
while read SCHEMA_NAME
do
if [ -z ${SCHEMA_NAME} ]
then
echo
echo “Enter the TABLE OWNER:”
echo “=====================”
else
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME=upper(‘$SCHEMA_NAME’);
EOF
)
VAL22=`echo $VAL11| awk ‘{print $NF}’`
if [ ${VAL22} -eq 0 ]
then
echo
echo “ERROR: USER [${SCHEMA_NAME}] IS NOT EXIST ON DATABASE [$ORACLE_SID] !”
echo; echo “Enter the SCHEMA NAME:”
echo “=====================”
else
break
fi
fi
done

echo
echo “Please Enter the TABLE NAME:”
echo “===========================”
while read TABLE_NAME
do
if [ -z ${TABLE_NAME} ]
then
echo
echo “Enter the TABLE NAME:”
echo “====================”
else
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER=upper(‘$SCHEMA_NAME’) AND TABLE_NAME=upper(‘$TABLE_NAME’);
EOF
)
VAL22=`echo $VAL11| awk ‘{print $NF}’`
if [ ${VAL22} -eq 0 ]
then
echo
echo “ERROR: TABLE $TABLE_NAME IS NOT EXIST ON SCHEMA [$SCHEMA_NAME] !”
echo; echo “Enter the TABLE NAME:”
echo “====================”
else
break
fi
fi
done

echo
echo “WHICH EXPORT UTILITY YOU WANT TO USE: [1) DATAPUMP [EXPDP]]”
echo “====================================”
echo “1) DATAPUMP [EXPDP]”
echo “2) LEGACY EXPORT [EXP]”
while read EXP_TOOL
do
case $EXP_TOOL in
“”|”1″|”DATAPUMP”|”datapump”|”DATAPUMP [EXPDP]”|”[EXPDP]”|”EXPDP”|”expdp”)

cd ${LOC1}
SPOOLFILE2=AFTER_IMPORT_TABLE_${SCHEMA_NAME}.${TABLE_NAME}.sql

${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT CREATE USER DBA_EXP [EXPORTER USER] (WILL BE DROPPED AFTER THE EXPORT) …
CREATE USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
ALTER USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
GRANT CREATE SESSION TO DBA_EXP;
GRANT DBA TO DBA_EXP;
— The following privileges to workaround Bug 6392040:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO DBA_EXP;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO DBA_EXP;
PROMPT CREATING DIRECTORY EXPORT_FILES_DBA_BUNDLE POINTING TO $LOC1 …
CREATE OR REPLACE DIRECTORY EXPORT_FILES_DBA_BUNDLE AS ‘$LOC1’;
PROMPT
PROMPT CREATING AFTER TABLE IMPORT SCRIPT …
PROMPT
SET PAGES 0 TERMOUT OFF LINESIZE 157 ECHO OFF FEEDBACK OFF
SPOOL $SPOOLFILE2
SELECT ‘CREATE SYNONYM ‘||OWNER||’.’||SYNONYM_NAME||’ FOR ‘||TABLE_OWNER||’.’||TABLE_NAME||’;’ FROM DBA_SYNONYMS
WHERE TABLE_OWNER=UPPER(‘$SCHEMA_NAME’) AND TABLE_NAME=UPPER(‘$TABLE_NAME’) AND OWNER <> UPPER(‘PUBLIC’)
UNION
SELECT ‘CREATE PUBLIC SYNONYM ‘||SYNONYM_NAME||’ FOR ‘||TABLE_OWNER||’.’||TABLE_NAME||’;’ FROM DBA_SYNONYMS
WHERE TABLE_OWNER=UPPER(‘$SCHEMA_NAME’) AND TABLE_NAME=UPPER(‘$TABLE_NAME’) AND OWNER=UPPER(‘PUBLIC’);
SPOOL OFF
EOF

echo
echo “EXPORTING TABLE [${SCHEMA_NAME}.${TABLE_NAME}] USING DATAPUMP …”
sleep 1
${ORACLE_HOME}/bin/expdp DBA_EXP/”bundle_${PASSHALF}” ${REUSE_DUMP} TABLES=${SCHEMA_NAME}.${TABLE_NAME} PARALLEL=${PARALLEL_DEGREE} DIRECTORY=EXPORT_FILES_DBA_BUNDLE DUMPFILE=EXPORT_${TABLE_NAME}_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp LOGFILE=EXPORT_${TABLE_NAME}_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.log

# DRPOPPING USER DBA_EXP:
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT
PROMPT DROPPING THE EXPORTER USER DBA_EXP (SAFELY) …
DROP USER DBA_EXP;
EOF

echo
echo “*****************”
echo “IMPORT GUIDELINES:”
echo “*****************”
echo “AFTER IMPORTING THE DUMPFILE IT’S RECOMMENDED TO RUN THIS SQL SCRIPT: ${LOC1}/$SPOOLFILE2″
echo ” => IT INCLUDES (PRIVATE & PUBLIC SYNONYMS DDLS) WHICH WILL NOT BE CREATED DURING THE IMPORT PROCESS.”
echo
echo
echo “*************************”
echo “EXPORT DUMP FILE LOCATION:”
echo “*************************”
echo “${LOC1}/EXPORT_${TABLE_NAME}_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp”
#echo “****************************************************”
#echo “TABLE [${SCHEMA_NAME}.${TABLE_NAME}] EXPORT file Location: ${LOC1}/EXPORT_${TABLE_NAME}_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp”
#echo “****************************************************”
echo; exit ;;
“2”|”LEGACY EXPORT”|”LEGACY”|”EXPORT”|”LEGACY EXPORT [EXP]”|”EXP”|”[EXP]”|”exp”|”legacy export”|”legacy”|”export”)

cd ${LOC1}
SPOOLFILE2=AFTER_IMPORT_TABLE_${SCHEMA_NAME}.${TABLE_NAME}.sql

${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT CREATE USER DBA_EXP [EXPORTER USER] (WILL BE DROPPED AFTER THE EXPORT) …
CREATE USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
ALTER USER DBA_EXP IDENTIFIED BY “bundle_$PASSHALF”;
GRANT CREATE SESSION TO DBA_EXP;
GRANT EXP_FULL_DATABASE TO DBA_EXP;
— The following privileges to workaround Bug 6392040:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO DBA_EXP;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO DBA_EXP;
PROMPT
PROMPT CREATING AFTER TABLE IMPORT SCRIPT …
PROMPT
SET PAGES 0 LINESIZE 157 ECHO OFF FEEDBACK OFF TERMOUT OFF
SPOOL $SPOOLFILE2
SELECT ‘CREATE SYNONYM ‘||OWNER||’.’||SYNONYM_NAME||’ FOR ‘||TABLE_OWNER||’.’||TABLE_NAME||’;’ FROM DBA_SYNONYMS
WHERE TABLE_OWNER=UPPER(‘$SCHEMA_NAME’) AND TABLE_NAME=UPPER(‘$TABLE_NAME’) AND OWNER <> UPPER(‘PUBLIC’)
UNION
SELECT ‘CREATE PUBLIC SYNONYM ‘||SYNONYM_NAME||’ FOR ‘||TABLE_OWNER||’.’||TABLE_NAME||’;’ FROM DBA_SYNONYMS
WHERE TABLE_OWNER=UPPER(‘$SCHEMA_NAME’) AND TABLE_NAME=UPPER(‘$TABLE_NAME’) AND OWNER=UPPER(‘PUBLIC’);
SPOOL OFF
EOF

echo
echo “EXPORTING TABLE [${SCHEMA_NAME}.${TABLE_NAME}] USING LEGACY EXP …”
sleep 1
${ORACLE_HOME}/bin/exp DBA_EXP/”bundle_${PASSHALF}” TABLES=${SCHEMA_NAME}.${TABLE_NAME} DIRECT=y CONSISTENT=y STATISTICS=NONE FEEDBACK=1000 FILE=${LOC1}/EXPORT_${TABLE_NAME}_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp log=${LOC1}/EXPORT_${TABLE_NAME}_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.log

# DRPOPPING USER DBA_EXP:
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
PROMPT
PROMPT DROPPING THE EXPORTER USER DBA_EXP (SAFELY) …
DROP USER DBA_EXP;
EOF

echo
echo “*****************”
echo “IMPORT GUIDELINES:”
echo “*****************”
echo “AFTER IMPORTING THE DUMPFILE IT’S RECOMMENDED TO RUN THIS SQL SCRIPT: ${LOC1}/$SPOOLFILE2″
echo ” => IT INCLUDES (PRIVATE & PUBLIC SYNONYMS DDLS) WHICH WILL NOT BE CREATED DURING THE IMPORT PROCESS.”
echo
echo
echo “*************************”
echo “EXPORT DUMP FILE LOCATION:”
echo “*************************”
echo “${LOC1}/EXPORT_${TABLE_NAME}_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp”
#echo “************************************************”
#echo “TABLE [${SCHEMA_NAME}.${TABLE_NAME}] EXPORT file Location: ${LOC1}/EXPORT_${TABLE_NAME}_${SCHEMA_NAME}_${ORACLE_SID}_${DUMPDATE}.dmp”
#echo “************************************************”
echo; exit ;;
esac
done
;;
n|N|no|NO|No) echo; echo “NO OPTIONS REMAINING !”;echo “SCRIPT TERMINATED.”;echo ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

# #############
# END OF SCRIPT
# #############

Kill long running queries for more than N hours

# ##############################################
# Kill queries running for more than N hours based on specific criteria.
# ###############################################

# #####################
# Environment Variables: [ORACLE_SID must be set by the user in case multiple instances running]
# #####################
export ORACLE_SID=

export SCRIPT_NAME=”kill_long_running_queries”
export SRV_NAME=`uname -n`
#export LNXVER=`cat /etc/redhat-release | grep -o ‘[0-9]’|head -1`
export LOGFILE=/tmp/${SCRIPT_NAME}.log
export TERMINATOR_SCRIPT=/tmp/KILL_LONG_QUERIES.sql

# Email Recipients:
# ################
MAIL_LIST=”youremail@yourcompany.com”
export MAIL_LIST

# #######################################
# SCRIPT OPTIONS:
# #######################################

# #################
# KILLING Criteria:
# #################

# Module Name: [Put “,” between each module name and keep each module name between single quote]
# e.g. export MODULE_NAME=”‘SQL Developer’,’Toad'”
export MODULE_NAME=”‘SQL Developer'”

# Duration [In hours and its fraction] when exceeded the query will get killed:
# e.g. To kill the queries that exceed 3 hours and 30 minutes export DURATION=”3.5″
export DURATION=”2.5″

# Report Only Semaphore: [The script will NOT KILL any query if it set to Y but will report them to the user]
# Y to report long sessions by email without killing them.
# N to Kill long sessions and report them after killing to the user. [Default]
export REPORT_ONLY=”N”

case ${REPORT_ONLY} in
Y|y|yes|Yes|YES) export HASH_SCRIPT=”–“;export REPORT_ONLY_MESSAGE=”PROMPT REPORT_ONLY Semaphore is set to Y, No Killing will happen”;;
*) export HASH_SCRIPT=””;export REPORT_ONLY_MESSAGE=””;;
esac

# ####################################################################
# Check if ORACLE_SID & MAIL_LIST variables is already set by the user:
# ####################################################################

export EXL_DB=”\-MGMTDB|ASM” # Instances to not be considered when running the script
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )

case ${ORACLE_SID} in “”)
# Exit if No DBs are running:
if [ ${INS_COUNT} -eq 0 ]
then
echo No Database Running !
exit
fi

# If there is ONLY one DB make it the default ORACLE_SID:
if [ ${INS_COUNT} -eq 1 ]
then
export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )

# If there is more than one DB ASK the user to set the ORACLE_SID manually:
elif [ ${INS_COUNT} -gt 1 ]
then
echo
echo
echo “*****”
echo “ERROR! You have to manually set ORACLE_SID to one of the following instances in the ‘Environment Variables’ Section!”
echo “*****”
ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g”
echo
echo “Script Terminated !”
echo
exit
fi
;;
esac

# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v “\-MGMTDB”|awk ‘{print $1}’|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ‘:’|tail -1`

# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi

# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk ‘{print $1}’`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk ‘{print $NF}’|sed -e ‘s/\/dbs//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from PWDX is ${ORACLE_HOME}”

# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME
fi
#echo “ORACLE_HOME from oratab is ${ORACLE_HOME}”
fi

# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e ‘s/ORACLE_HOME=//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from environment is ${ORACLE_HOME}”
fi

# ATTEMPT4: If ORACLE_HOME is not found in the environment search user’s profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h ‘ORACLE_HOME=\/’ $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_HOME
#echo “ORACLE_HOME from User Profile is ${ORACLE_HOME}”
fi

# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e ‘s/\/bin\/orapipe//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from orapipe search is ${ORACLE_HOME}”
fi

# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo “Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly”
echo “e.g.”
echo “export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1”
exit
fi

# Neutralize login.sql file:
# #########################
# Existance of login.sql file under current working directory eliminates many functions during the execution of this script:

if [ -f ./login.sql ]
then
mv ./login.sql ./login.sql_NeutralizedBy${SCRIPT_NAME}
fi

# ###########
# SCRIPT BODY:
# ###########

# Script Description:
echo “”
echo “This Script Kills the sessions running a query for more than ${DURATION} hours and connecting from ${MODULE_NAME} …”
sleep 1

# Flush the logfile:
cat /dev/null > ${LOGFILE}

# CHECKING RUNNING SESSIONS:
SESSIONS_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
select count(*) from V\$SESSION where
MODULE in (${MODULE_NAME})
and last_call_et > 60*60*${DURATION}
and status = ‘ACTIVE’
;
exit;
EOF
)

SESSIONS_COUNT=`echo ${SESSIONS_COUNT_RAW}|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`

# KILLING LONG RUNNING SESSIONS IF EXIST:
# ######################################

if [ ${SESSIONS_COUNT} -gt 0 ]
then
echo “Found ${SESSIONS_COUNT} Candidate sessions to be killed!”
KILL_SESSION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
spool ${LOGFILE} APPEND
set pages 0 feedback off
prompt
PROMPT *****

select ‘TIME: ‘||to_char(systimestamp, ‘dd-Mon-yy HH24:MI:SS’) from dual;
PROMPT *****

set linesize 170 pages 1000;
prompt
prompt Session Details: [To be killed]
prompt ***************

col inst for 99
col module for a27
col event for a28
col MACHINE for a27
col “ST|ACT_SINC|LOG_TIME” for a51
col “USER|OSUSER|SID,SER|MACHIN|MODUL” for a75
select substr(s.USERNAME||’|’||s.OSUSER||’|’||s.sid||’,’||s.serial#||’|’||substr(s.MACHINE,1,25)||’ | ‘||substr(s.MODULE,1,25),1,75)”USER|OS|SID,SER|MACHIN|MODUL”
,substr(s.status||’|’||LAST_CALL_ET||’|’||LOGON_TIME,1,50) “ST|ACT_SINC|LOG_TIME”
,s.SQL_ID CURR_SQL_ID
from v\$session s
where
MODULE in (${MODULE_NAME})
and last_call_et > 60*60*${DURATION}
and status = ‘ACTIVE’
;

spool off

— Kill SQL Script creation:
set pages 0 feedback off echo off
spool ${TERMINATOR_SCRIPT}

select ‘ALTER SYSTEM DISCONNECT SESSION ”’||sid||’,’||serial#||”’ IMMEDIATE;’
from V\$SESSION
where
MODULE in (${MODULE_NAME})
and last_call_et > 60*60*${DURATION}
and status = ‘ACTIVE’
;

spool off

— Run the Terminator Script to kill the sessions:
set pages 1000 feedback on echo on
spool ${LOGFILE} APPEND
PROMPT
PROMPT Running The Terminator Script:
PROMPT *****************************

${REPORT_ONLY_MESSAGE}
${HASH_SCRIPT}START ${TERMINATOR_SCRIPT}

spool off
exit;
EOF
)

sleep 10

CURRENT_LONG_SESS_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set linesize 170 pages 1000;
spool ${LOGFILE} APPEND
prompt
prompt CHECK For other long queries fulfill the killing criteria: [Still Running]
prompt *********************************************************

col inst for 99
col module for a27
col event for a28
col MACHINE for a27
col “ST|ACT_SINC|LOG_TIME” for a51
col “USER|SID,SER|MACHIN|MODUL” for a72
select substr(s.USERNAME||’|’||s.sid||’,’||s.serial#||’|’||substr(s.MACHINE,1,25)||’ | ‘||substr(s.MODULE,1,25),1,72)”USER|SID,SER|MACHIN|MODUL”
,substr(s.status||’|’||LAST_CALL_ET||’|’||LOGON_TIME,1,50) “ST|ACT_SINC|LOG_TIME”
,s.SQL_ID CURR_SQL_ID
from v\$session s
where
MODULE in (${MODULE_NAME})
and last_call_et > 60*60*${DURATION}
and status = ‘ACTIVE’
;

spool off
exit;
EOF
)

# EMAIL Notification with the killed session:
case ${MAIL_LIST} in
“youremail@yourcompany.com”);;
*)
/bin/mail -s “Info: Long Running QUERY KILLED on [${ORACLE_SID}]” ${MAIL_LIST} < ${LOGFILE};;
esac

else
echo “”
echo “Hooray! No Candidate Sessions were found based on the Killing Criteria.”
echo “”
fi

# De-Neutralize login.sql file:
# ############################
# If login.sql was renamed during the execution of the script revert it back to its original name:
if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
then
mv ./login.sql_NeutralizedBy${SCRIPT_NAME} ./login.sql
fi

# #############
# END OF SCRIPT
# #############

To unlock locked users

# #################################################
# Script to unlock locked users
# #################################################
SCRIPT_NAME=”lock_user”
# ###########
# Description:
# ###########
echo
echo “=================================”
echo “This script LOCKS database users.”
echo “=================================”
echo
sleep 1

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe “|” as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB=”\-MGMTDB|ASM” #Excluded INSTANCES [Will not get reported offline].

# ###########################
# Listing Available Databases:
# ###########################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
then
echo No Database Running !
exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
then
export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
then
echo
echo “Select the ORACLE_SID:[Enter the number]”
echo ———————
select DB_ID in $( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )
do
if [ -z “${REPLY##[0-9]*}” ]
then
export ORACLE_SID=$DB_ID
echo Selected Instance:
echo
echo “********”
echo $DB_ID
echo “********”
echo
break
else
export ORACLE_SID=${REPLY}
break
fi
done

fi
# Exit if the user selected a Non Listed Number:
if [ -z “${ORACLE_SID}” ]
then
echo “You’ve Entered An INVALID ORACLE_SID”
exit
fi

# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|grep -v ASM|grep -v “\-MGMTDB”|awk ‘{print $1}’|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ‘:’|tail -1`

# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi

# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk ‘{print $1}’`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk ‘{print $NF}’|sed -e ‘s/\/dbs//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from PWDX is ${ORACLE_HOME}”

# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME
fi
#echo “ORACLE_HOME from oratab is ${ORACLE_HOME}”
fi

# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e ‘s/ORACLE_HOME=//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from environment is ${ORACLE_HOME}”
fi

# ATTEMPT4: If ORACLE_HOME is not found in the environment search user’s profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h ‘ORACLE_HOME=\/’ $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_HOME
#echo “ORACLE_HOME from User Profile is ${ORACLE_HOME}”
fi

# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e ‘s/\/bin\/orapipe//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from orapipe search is ${ORACLE_HOME}”
fi

# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo “Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly”
echo “e.g.”
echo “export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1”
exit
fi

# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
CURR_USER=`whoami`
if [ ${ORA_USER} != ${CURR_USER} ]; then
echo “”
echo “You’re Running This Sctipt with User: \”${CURR_USER}\” !!!”
echo “Please Run This Script With The Right OS User: \”${ORA_USER}\””
echo “Script Terminated!”
exit
fi

# #############################
# SQLPLUS: Lock An Oracle User:
# #############################
# Variables
echo
echo “Please enter the USERID:”
echo “=======================”
while read USERNAME2
do
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME=upper(‘$USERNAME2’);
EOF
)
VAL22=`echo $VAL11| awk ‘{print $NF}’`
case ${VAL22} in
# If the provided value match an exist username in the DB:
1)
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME=upper(‘$USERNAME2’);
PROMPT
PROMPT Locking user [${USERNAME2}] …
PROMPT
EOF
VAL1=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
ALTER user $USERNAME2 ACCOUNT LOCK;
EOF
)
VAL2=`echo $VAL1| grep “User altered”`
if [ -z “${VAL2}” ]
then
echo “Failed to lock User \”${USERNAME2}\” !”
echo
exit
else
echo
echo User ${USERNAME2} locked Successfully.
echo
fi; break;;
# If no value provided or the value doesn’t match any user in the DB try to search for matching:
*) echo; echo “INFO: USER [${USERNAME2}] IS NOT EXIST ON DATABASE [$ORACLE_SID] !”
echo; echo “Searching …”; sleep 1
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set linesize 143
set pagesize 1000
set feedback off
set trim on
set echo off
col USERNAME for a30
col account_status for a23
select username,account_status,profile,LOCK_DATE,EXPIRY_DATE from dba_users where username like upper (‘%$USERNAME2%’);
EOF
echo; echo “Please Enter the FULL USERID:”
echo “=============================” ;;
esac
done

# #############
# END OF SCRIPT
# #############

To generate ORABEDUG report

# ################################################
# Script to generate ORADEBUG report.
# To be run on hanged instance.
# ###############################################

# ############
# Description:
# ############
echo
echo “================================================================”
echo “This script runs ORADEBUG to dump HANG analysis into trace file.”
echo “================================================================”
echo
sleep 1
echo -e “\033[33;5mORADEBUG utility should be run in SEVERE cases; where the instance is totally hanged as it may crash your instance!\033[0m”
sleep 3

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe “|” as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB=”\-MGMTDB|ASM” #Excluded INSTANCES [Will not get reported offline].

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe “|” as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB=”\-MGMTDB|ASM” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# ###########################
# Listing Available Databases:
# ###########################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
then
echo No Database Running !
exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
then
export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
then
echo
echo “Select the ORACLE_SID:[Enter the number]”
echo ———————
select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )
do
if [ -z “${REPLY##[0-9]*}” ]
then
export ORACLE_SID=$DB_ID
echo Selected Instance:
echo $DB_ID
break
else
export ORACLE_SID=${REPLY}
break
fi
done

fi
# Exit if the user selected a Non Listed Number:
if [ -z “${ORACLE_SID}” ]
then
echo “You’ve Entered An INVALID ORACLE_SID”
exit
fi

# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v “\-MGMTDB”|awk ‘{print $1}’|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ‘:’|tail -1`

# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi

# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk ‘{print $1}’`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk ‘{print $NF}’|sed -e ‘s/\/dbs//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from PWDX is ${ORACLE_HOME}”

# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME
fi
#echo “ORACLE_HOME from oratab is ${ORACLE_HOME}”
fi

# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e ‘s/ORACLE_HOME=//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from environment is ${ORACLE_HOME}”
fi

# ATTEMPT4: If ORACLE_HOME is not found in the environment search user’s profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h ‘ORACLE_HOME=\/’ $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_HOME
#echo “ORACLE_HOME from User Profile is ${ORACLE_HOME}”
fi

# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e ‘s/\/bin\/orapipe//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from orapipe search is ${ORACLE_HOME}”
fi

# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo “Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly”
echo “e.g.”
echo “export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1”
exit
fi

# ################################
# SQLPLUS: RUN ORADEBUG:
# ################################
echo
echo “Enter the HANG ANALYSIS level: [Enter one of these values [1,2,3,4,5,10]]”
echo “——————————”
echo “<The higher the level the more details will be captured in the log>”
while read ANALYZE_LEVEL
do
case ${ANALYZE_LEVEL} in
“1”|”2″|”3″|”4″|”5″|”10″) echo;echo “Hang Analysis Level is: ${ANALYZE_LEVEL}”;
break ;;
*) echo
echo “Invalid Hang Analysis Level, please enter a value between [1,2,3,4,5,10]:”
echo “————————————————————————“;;

esac
done

echo
echo “Select the option you want to run ORABEDUG with? [DB(1) <DEFAULT> or SYSTEMSTATE(2)]”
echo “================================================”
echo “[1] to run ORADEBUG against the DATABASE. [DEFAULT]”
echo “[2] to run ORADEBUG with SYSTEMSTATE option.”
echo “Enter [1 or 2]:”
while read ANS
do
case $ANS in
“”|”1″|”DB”|”db”) echo;echo “ORADEBUG will analyze DB hang now …”;sleep 1

${ORACLE_HOME}/bin/sqlplus -s ‘/nolog’ << EOF
set _prelim on
conn / as sysdba
oradebug setmypid
— perform cluster DB wide HANGANALYZE:
oradebug setinst all
— Set tracefile size unlimited:
oradebug unlimit
–oradebug tracefile_name
–oradebug -g def hanganalyze ${ANALYZE_LEVEL}
oradebug -g all hanganalyze ${ANALYZE_LEVEL}
–oradebug tracefile_name
–Flush any pending writes to the trace file and close it:
oradebug flush
oradebug close_trace

EOF
break;;
“2”|”SYSTEMSTATE”|”systemstate”|”SYSTEM”|”system”) echo; echo “ORADEBUG will analyze SYSTEMSTATE hang now …”;echo;sleep 1;

${ORACLE_HOME}/bin/sqlplus -s ‘/nolog’ << EOF
set _prelim on
conn / as sysdba
oradebug setmypid
— Set tracefile size unlimited:
oradebug unlimit
— Run ORADEBUG:
–oradebug dump crs ${ANALYZE_LEVEL}
oradebug -g all dump systemstate 266
oradebug tracefile_name
–Flush any pending writes to the trace file and close it:
oradebug flush
oradebug close_trace

EOF
break ;;
*) echo “Please enter a VALID answer [1|2|DB|CLUSTERWARE]”;
echo “================================================” ;;
esac

done

# #############
# END OF SCRIPT
# #############

Backup Controlfile & SPFILE & Generate AWR Report on ALL Running Databases

# ##############################################
# Backup Controlfile & SPFILE & Generate AWR Report on ALL Running Databases
# ################################################
SCRIPT_NAME=”backup_ctrl_spf_AWR”
SRV_NAME=`uname -n`

# Receive an Email notification if the backup location file system hit the threshold:
MAIL_LIST=”youremail@yourcompany.com”

# ###################
# SCRIPT CONTROLS:
# ###################

# Enable/Disable CONTROLFILE & SPFILE Backup: [Default ENABLED]
CTRLSPFFLAG=Y
export CTRLSPFFLAG

# Enable/Disable AWR Report Generation: [Default DISABLED]
AWRFLAG=N
export AWRFLAG

# #########################
# THRESHOLDS:
# #########################
# Don’t run the backup if the Filesystem where the backup will be located has reached the following threshold:

FSTHRESHOLD=95 # FILESYSTEM %USED THRESHOLD IF THE BACKUP LOCATION REACHED THE SCRIPT WILL TERMINATE TO AVOID FILLING THE FILESYSTEM.

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances dbalarm will IGNORE and will NOT run against:
# Use pipe “|” as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB=”\-MGMTDB|ASM”

# #########################
# Setting ORACLE_SID:
# #########################
# Exit with sending Alert mail if No DBs are running:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )
if [ $INS_COUNT -eq 0 ]
then
exit
fi

for ORACLE_SID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )
do
export ORACLE_SID

# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $1}’|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ‘:’|tail -1`

## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME
fi

## If oratab is not exist, or ORACLE_SID not added to oratab, find ORACLE_HOME in user’s profile:
if [ -z “${ORACLE_HOME}” ]
then
ORACLE_HOME=`grep -h ‘ORACLE_HOME=\/’ $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_HOME
fi

# #########################
# Variables:
# #########################
export PATH=$PATH:${ORACLE_HOME}/bin

# ####################
# BACKUP DIRECTORY:
# ####################

# CONTORLFILE & SPFILE backup directory location:
BKP_DIR=${USR_ORA_HOME}/backup_ctl_spfile
export BKP_DIR

if [ ! -d ${BKP_DIR} ]
then
mkdir -p ${BKP_DIR}
chown -R ${ORA_USER} ${BKP_DIR}
chmod -R go-rwx ${BKP_DIR}
fi

# Exit if the backup location is not accessible:
if [ ! -d ${BKP_DIR} ]
then
echo “Location Path \”${LOC1}\” is NOT EXIST!”
echo “Script Terminated!”
exit
fi

# AWR report directory location:
AWR_DIR=${BKP_DIR}/AWRs
export AWR_DIR

if [ ! -d ${AWR_DIR} ]
then
mkdir -p ${AWR_DIR}
chown -R ${ORA_USER} ${AWR_DIR}
chmod -R go-rwx ${AWR_DIR}
fi

# #######################################
# Checking The FILESYSTEM Available Size:
# #######################################

# Workaround df command output bug “`/root/.gvfs’: Permission denied”
if [ -f /etc/redhat-release ]
then
export DF=’df -hPx fuse.gvfs-fuse-daemon’
else
export DF=’df -h’
fi

cd ${BKP_DIR}
FSLOG=/tmp/backup_ctl_spf_filesystem_used.log
echo “Reported By Script: ${SCRIPT_NAME}” > ${FSLOG}
echo “” >> ${FSLOG}
#echo “Controfile, SPFILE and AWR BACKUP has failed because the filesystem that hold the backup has hit the identified threshold ${FSTHRESHOLD}%” >> ${FSLOG}
echo “” >> ${FSLOG}
echo “Filesystem Utilization Details” >> ${FSLOG}
echo “…………………………….” >> ${FSLOG}
${DF} . >> ${FSLOG}
${DF} .| grep -v “^Filesystem” |awk ‘{print substr($0, index($0, $2))}’| grep -v “/dev/mapper/”| grep -v “/dev/asm/”|awk ‘{print $(NF-1)” “$NF}’| while read OUTPUT
do
PRCUSED=`echo ${OUTPUT}|awk ‘{print $1}’|cut -d’%’ -f1`
FILESYS=`echo ${OUTPUT}|awk ‘{print $2}’`
# Terminate the script and send notification to the user incase the THRESHOLD REACHED:
if [ ${PRCUSED} -ge ${FSTHRESHOLD} ]
then
mail -s “WARNING: Controlfile/SPFILE/AWR Backup FAILED on Server [${SRV_NAME}] | Filesystem [${FILESYS}] has reached ${PRCUSED}% of USED space” ${MAIL_LIST} < ${FSLOG}
echo “Script Terminated! Please check the space on the backup location.”
fi
done

LAST_PCTUSED=`cat ${FSLOG}|awk ‘{print $5}’|cut -d’%’ -f1|tail -1`

if [ ${LAST_PCTUSED} -ge ${FSTHRESHOLD} ]
then
exit
fi

rm -f ${FSLOG}

# ########################
# Getting ORACLE_BASE:
# ########################

# Get ORACLE_BASE from user’s profile if it EMPTY:

if [ -z “${ORACLE_BASE}” ]
then
ORACLE_BASE=`grep -h ‘ORACLE_BASE=\/’ $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
fi

# #########################
# Getting DB_NAME:
# #########################
VAL1=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
SELECT name from v\$database
exit;
EOF
)
# Getting DB_NAME in Uppercase & Lowercase:
DB_NAME_UPPER=`echo $VAL1| perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’`
DB_NAME_LOWER=$( echo “$DB_NAME_UPPER” | tr -s ‘[:upper:]’ ‘[:lower:]’ )
export DB_NAME_UPPER
export DB_NAME_LOWER

# DB_NAME is Uppercase or Lowercase?:

if [ -d $ORACLE_HOME/diagnostics/${DB_NAME_LOWER} ]
then
DB_NAME=$DB_NAME_LOWER
else
DB_NAME=$DB_NAME_UPPER
fi

# ##################################
# Backup CONTROLFILE & SPFILE Script:
# ##################################
DATE_FORMAT=`date +”%d-This_Month-%Y”`
CONTROLFILE_BKP_NAME=${BKP_DIR}/CTRL_${DB_NAME}_${DATE_FORMAT}.trc
SPFILE_BKP_NAME=${BKP_DIR}/init${ORACLE_SID}_${DATE_FORMAT}.ora

# Check if the CONTROLFILE & SPFILE Backup flag is to Y:

case ${CTRLSPFFLAG} in
Y|y|YES|yes|Yes)

echo “Backing up CONTROLFILE & SPFILE on [${ORACLE_SID}] …”
VAL1=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF
PROMPT Taking Controlfile Trace Backup …
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘${CONTROLFILE_BKP_NAME}’ REUSE;
PROMPT Taking SPFILE Text Backup …
CREATE PFILE=’${SPFILE_BKP_NAME}’ FROM SPFILE;
— Controlfile Physical Backup:
–ALTER DATABASE BACKUP CONTROLFILE TO ‘${BKP_DIR}/CTRL_${DB_NAME}_Physical.bkp’ REUSE;
exit;
EOF
)

VAL2=$(${ORACLE_HOME}/bin/rman target / << EOF
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT ‘${BKP_DIR}/CONTROLFILE_%d_%I.bkp’ REUSE ;
EOF
)

# Retain the backup taken on the FIRST day of each month:
if [ ${DATE_FORMAT} = `date +”01-This_Month-%Y”` ]
then
DATE_FORMAT=`date +”%d-%m-%Y”`
mv ${CONTROLFILE_BKP_NAME} ${BKP_DIR}/CTRL_${DB_NAME}_${DATE_FORMAT}.trc
fi

esac
# ##################################
# AWR Report Generation Script:
# ##################################

# Define the AWR report period in days:
AWR_WINDOW=1

# Report Name:
LOGDATE=`date +%d-%m-%y`
REPORTNAME=${AWR_DIR}/AWR_${ORACLE_SID}_${LOGDATE}.html
export REPORTNAME

# Check if the AWR Report generation flag is to Y:

case ${AWRFLAG} in
Y|y|YES|yes|Yes)

echo “Generating AWR report on [${ORACLE_SID}] …”
VAL3=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
DEFINE num_days=${AWR_WINDOW};
DEFINE i_instance=${ORACLE_SID};
DEFINE inst_name=’${ORACLE_SID}’;
DEFINE report_type=’html’;
DEFINE report_name=’${REPORTNAME}’;
DEFINE begin_snap=0;
DEFINE end_snap=0;

column inst_name heading “Instance Name” new_value inst_name format A16;
SELECT UPPER(‘&i_instance’) inst_name FROM DUAL;

column begin_snap heading “Min SNAP ID” new_value begin_snap format 9999999999;
column end_snap heading “Max SNAP ID” new_value end_snap format 9999999999;

SELECT MIN(SNAP_ID) begin_snap FROM dba_hist_snapshot WHERE TRUNC(begin_interval_time) = TRUNC(SYSDATE-&num_days);
SELECT MAX(SNAP_ID) end_snap FROM dba_hist_snapshot WHERE TRUNC(begin_interval_time) = TRUNC(SYSDATE-&num_days);

–column report_name heading “AWR file name” new_value report_name format A30;
–SELECT ‘$REPORTNAME’ report_name FROM DUAL;

SELECT &num_days i num_days FROM DUAL;
SELECT ‘&report_type’ report_type FROM DUAL;
SELECT ‘$REPORTNAME’ report_name FROM DUAL;
SELECT &begin_snap begin_snap FROM DUAL;
SELECT &end_snap end_snap FROM DUAL;

@?/rdbms/admin/awrrpt.sql

undefine num_days;
undefine report_type;
undefine report_name;
undefine begin_snap;
undefine end_snap;
EOF
)

# Compress AWR reports:
gzip -f9 ${REPORTNAME}
esac

done

# #############
# END OF SCRIPT
# #############