Month: May 2014

# Iterates a command across all running DB instances
#
. `dirname “${BASH_SOURCE[0]}”`/ora_funcs.sh

RUN_CMDS=Y

usage() {
echo “Usage: $0 -c command string [-n ] [-e exclude type] [-l list file]”
echo ”  -c command string – the string of commands to run once the DB environment is set”
echo ”  -n – no changes, only create files with commands to run, so you can edit and run manually”
echo ”  -e exclude type – exclude these types of databases: clonedb standby”
echo ”  -l list file – name of text file that lists the databases to process, 1 per line”
exit 1
}

# Handle parameters
while getopts “:c:e:l:n” opt; do
case $opt in
n)
RUN_CMDS=N
;;
c)
CMD_STRING=$OPTARG
;;
l)
LIST_FILE=$OPTARG
;;
e)
EXCLUDE_TYPE=$OPTARG
echo “Exclude: $EXCLUDE_TYPE”
;;
\?)
echo “Invalid option: -$OPTARG” >&2
usage
;;
: )
echo “Option -$OPTARG requires an argument.” >&2
usage
;;
esac
done

REPORT_NAME=RunForAllDatabases-`date “+%y%m%d%H%M”`.sh
:>${REPORT_NAME}
# Create the file listing all the databases to operate on
if [ -z ${LIST_FILE} ] ; then
LIST_FILE=/tmp/Run4All-listing.txt
ps -ef|grep ora_\\pmon|cut -d_ -f3-|grep -v ASM > $LIST_FILE
fi
#echo “List file: $LIST_FILE”

for ORACLE_SID in `cat $LIST_FILE`
do
if [ “$EXCLUDE_TYPE” = “clonedb” ] ; then
is_clonedb ${ORACLE_SID} && continue
else
test_connect ${ORACLE_SID} || continue
fi

if [ “$EXCLUDE_TYPE” = “standby” -a “${DB_ROLE}” = “PHYSICAL STANDBY” ] ; then
continue
fi

echo “export ORACLE_SID=${ORACLE_SID};export ORACLE_HOME=${ORACLE_HOME};${CMD_STRING}” >>${REPORT_NAME}
done

echo “Commands written to ${REPORT_NAME}”

if [ “${RUN_CMDS}” = ‘Y’ ] ; then
sh ${REPORT_NAME}
fi

# cleanup
if [ -r “/tmp/Run4All-listing.txt” ] ; then
rm /tmp/Run4All-listing.txt
fi

declare -f dbname
declare -f is_clonedb
declare -f oe
declare -f pathmunge
declare -f test_connect

#!/bin/bash
pathmunge () {
if ! echo $PATH | /bin/egrep -q “(^|:)$1($|:)” ; then
if [ “$2” = “after” ] ; then
PATH=$PATH:$1
else
PATH=$1:$PATH
fi
fi
}

# Sets the Oracle environment, if possible
oe ()
{
# if no argument, try the existing value of ORACLE_SID
TMP_ORACLE_SID=${1:-$ORACLE_SID}
if [ -z “${TMP_ORACLE_SID}” ] ; then
echo “No SID”
return 1
fi
# Set the SID
ORACLE_SID=`ps -ef|grep ora_\\\\pmon|sed -e “s/.*pmon_//”|grep ${TMP_ORACLE_SID}|sort -u`
# check that only one SID was found
if [ `echo $ORACLE_SID|wc -w` -gt 1 ] ; then
echo “Too many matching SID’s (${ORACLE_SID})”
# Just take the first one
ORACLE_SID=`echo $ORACLE_SID|cut -d” ” -f1`
echo “Setting ORACLE_SID to $ORACLE_SID”
fi
if [ -z “${ORACLE_SID}” ] ; then
echo “SID not found”
return 1
fi
ps -e -o command,pid | grep ora_\\\\pmon|grep ${ORACLE_SID}|read cmd pid
#Find ORACLE_HOME from process id
if [ -r /proc/$pid/exe ] ; then
TMP_ORACLE_HOME=$(ls -l /proc/$pid/exe | awk -F’> ‘ ‘{print $2}’ | sed ‘s/\/[^\/]*\/[^\/]*$//’)
else
# Get the OH from oratab if the process files are not readable
TMP_ORACLE_HOME=`grep “${TMP_ORACLE_SID}:” /etc/oratab|cut -d: -f2`
# If it wasn’t found, strip off the last char to work on RAC
if [ -z “${TMP_ORACLE_HOME}” ]; then
INST=`expr substr ${TMP_ORACLE_SID} ${#TMP_ORACLE_SID} 1`
TMP_ORACLE_HOME=`grep “${TMP_ORACLE_SID%$INST}:” /etc/oratab|cut -d: -f2`
fi
fi

# If we found an OH and it’s valid, set the rel OH, otherwise leave it unchanged and hope for the best
if [ -d “${TMP_ORACLE_HOME}” ] ; then
ORACLE_HOME=${TMP_ORACLE_HOME}
fi

# Check that the ORACLE_SID we finally decided on has a running db
ps -ef|grep ora_\\pmon_${ORACLE_SID} >/dev/null
if [ $? = ‘0’ ] ; then
# Everything works! exit with success
echo “${ORACLE_SID}”
pathmunge $ORACLE_HOME/bin
export ORACLE_SID ORACLE_HOME PATH
return 0
else
echo “SID not found”
return 1
fi
}

test_connect ()
{
if [ $# -gt 0 ] ; then
oe $1
fi

# Test the connection
export DB_ROLE=`sqlplus -s / as sysdba <<!
set pages 0
select DATABASE_ROLE from v\\$database;
exit
!`
echo $DB_ROLE | grep ORA- && return 1
return 0
}

is_clonedb ()
{
if [ $# -gt 0 ] ; then
oe $1
fi

# Test the connection
(sqlplus -s / as sysdba <<!
set pages 0
select VALUE from v\$parameter where NAME = ‘clonedb’;
exit
!
) | grep TRUE >/dev/null && return 0
return 1
}

dbname ()
{
if [ $# -gt 0 ] ; then
oe $1
fi

# Get the real database name
export DBNAME=`sqlplus -s / as sysdba <<!
set pages 0
select NAME from v\\$database;
exit
!`
return 0
}

Oracle now has 2 different ways of scheduling jobs. We sometimes want to disable jobs for a period in order to do maintenance of some kind, and then re-enable them. Care must be taken that we do not re-enable jobs that were not enabled before we started, so the correct approach is to generate the re-enable scripts before we disable anything.

The following shell script creates the disable and enable SQL scripts for you, and will handle both types of job queues. You can run the SQL “as is” or edit them to leave some jobs running.

 #Generate SQL to maintain Oracle jobs – both DBA_SCHEDULER_JOBS and DBA_JOBS

CRED=${CRED:-/}

usage() {
echo “Usage: $0 [-i] [-c] [-d database name]”
echo ”  -i – interactive (allows you to run the SQL that is generated”
echo ”  -c – check or count, only counts the number of jobs that are enabled”
echo ”  -d database name – defaults to $ORACLE_SID”
echo ” ”
echo “This script creates 2 files, containing commands to disable and enable the currently enabled jobs.”
echo “It handles both DBMS_JOBS and DBMS_SCHEDULER_JOBS”
echo “The .sql files contain the database name and a timestamp to avoid overwriting existing files”
echo “Run the disable SQL before the maintenance starts, and the enable SQL after it completes, to restore operations as they were”
echo “If you are running this from a remote host (using TNS), you will need to set the CRED env var to a user/passwd with SYSDBA established in the orapwd files”
exit 1
}

# Handle parameters
while getopts “:d:ic” opt; do
case $opt in
d)
TWO_TASK=$OPTARG
DB_NAME=$OPTARG
;;
c)
COUNT_ONLY=Y
;;
i)
INTERACTIVE=Y
;;
\?)
echo “Invalid option: -$OPTARG” >&2
usage
;;
: )
echo “Option -$OPTARG requires an argument.” >&2
usage
;;
esac
done

DB_NAME=${DB_NAME:-${ORACLE_SID}}
if [ “${COUNT_ONLY}” = “Y” ] ; then
echo “${DB_NAME}”
(sqlplus -s ${CRED} as sysdba <<!
set pages 0
set feed off
set head off
set lines 130
SELECT ‘DBMS_SCHEDULER enabled: ‘||count(*) FROM dba_scheduler_jobs WHERE enabled = ‘TRUE’;
SELECT ‘DBMS_JOBS enabled: ‘||count(*) FROM dba_jobs WHERE broken = ‘N’;
exit
!
)
exit 0
fi

DISABLE_SQL=JobDisable_${DB_NAME}-`date “+%y%m%d%H%M”`.sql
DISABLE_LOG=JobDisable_${DB_NAME}-`date “+%y%m%d%H%M”`.log
ENABLE_SQL=JobEnable_${DB_NAME}-`date “+%y%m%d%H%M”`.sql
ENABLE_LOG=JobEnable_${DB_NAME}-`date “+%y%m%d%H%M”`.log

echo “Generating SQL to disable and enable Oracle jobs”
echo
(sqlplus -s ${CRED} as sysdba <<!
set pages 0
set feed off
set head off
set lines 130
spool ${DISABLE_SQL}
prompt set sqlprompt “_CONNECT_IDENTIFIER> “;
prompt set feed off;
prompt set echo on;
prompt spool ${DISABLE_LOG};
SELECT ‘exec sys.dbms_scheduler.disable(”’||owner||’.’||job_name||”’)’ disable_cmd FROM dba_scheduler_jobs WHERE enabled = ‘TRUE’;
SELECT ‘exec sys.dbms_ijob.broken(‘||job||’, TRUE)’ FROM dba_jobs WHERE broken = ‘N’;
prompt SELECT ‘exec sys.dbms_scheduler.stop_job(”’||owner||’.’||job_name||”’, TRUE)’ stop_cmd FROM dba_scheduler_jobs WHERE enabled = ‘TRUE’;;
prompt SELECT ‘exec sys.dbms_scheduler.disable(”’||owner||’.’||job_name||”’)’ disable_cmd FROM dba_scheduler_jobs WHERE enabled = ‘TRUE’;
spool ${ENABLE_SQL}
prompt set sqlprompt “_CONNECT_IDENTIFIER> “;
prompt set feed off;
prompt set echo on;
prompt spool ${ENABLE_LOG};
SELECT ‘exec sys.dbms_scheduler.enable(”’||owner||’.’||job_name||”’)’ enable_cmd FROM dba_scheduler_jobs WHERE enabled = ‘TRUE’;
SELECT ‘exec sys.dbms_ijob.broken(‘||job||’, FALSE)’ FROM dba_jobs WHERE broken = ‘N’;
spool off
SELECT ‘DBMS_SCHEDULER enabled: ‘||count(*) FROM dba_scheduler_jobs WHERE enabled = ‘TRUE’;
SELECT ‘DBMS_JOBS enabled: ‘||count(*) FROM dba_jobs WHERE broken = ‘N’;
exit
!
)

echo
echo “job disable commands for ${DB_NAME} are in ${DISABLE_SQL}”
echo “job enable commands for ${DB_NAME} are in ${ENABLE_SQL}”

if [ “${INTERACTIVE}” = “Y” ] ; then
echo “Do you want to run the Disable SQL in ${DB_NAME} ?”
read ans
if [ “$ans” = “Y” ] ; then
echo sqlplus ${CRED} as sysdba @${DISABLE_SQL}
echo Output spooled to ${DISABLE_LOG}
fi
echo “Do you want to run the Enable SQL in ${DB_NAME} ?”
read ans
if [ “$ans” = “Y” ] ; then
echo sqlplus ${CRED} as sysdba @${ENABLE_SQL}
echo Output spooled to ${ENABLE_LOG}
fi
fi

The following shell script will gather table stats, and optionally, various system-wide stats. Copy the contents into a file and make it executable as the oracle owner account.

By default it runs with OS authentication as SYSDBA, you can alter that behavior by setting an env variable before running the script. I do not recommend adding passwords into this script for obvious security reasons.

You can show the usage by just passing an invalid command line parameter, for example ./GatherStats.sh -?

# Gather table stats – table list filtered by last time and (optionally) size (based on the number of records)
#
# File $Id: GatherStats.sh  $
# Modified $Author: dheltzel $
# Date $Date:  $
# Revision $Revision: $

CRED=${CRED:-/}

usage() {
echo “Usage: $0 [-t days] [-r num_recs] [-d database name] [-s] [-f filter_string]”
echo ”  -t days – only select tables that have not had stats gathered in at least this many days (default 7)”
echo ”  -r num_recs – only select tables with fewer than X thousand records (default is unlimited)”
echo ”  -d database name – defaults to $ORACLE_SID”
echo ”  -s – also gather system stats (takes a long time)”
echo ”  -f filter_string – arbitrary filter for the select query (ex. \”AND s.owner=’DBADMIN’\”)”
exit 1
}

# Handle parameters
while getopts “:d:t:r:sf:” opt; do
case $opt in
d)
TWO_TASK=$OPTARG
DB_NAME=$OPTARG
;;
t)
AGE_DAYS=$OPTARG
;;
r)
NUM_RECS=”AND num_rows < $OPTARG * 1000″
;;
s)
SYS_STATS=Y
;;
f)
OPT_FILTER=$OPTARG
;;
\?)
echo “Invalid option: -$OPTARG” >&2
usage
;;
: )
echo “Option -$OPTARG requires an argument.” >&2
usage
;;
esac
done

DB_NAME=${DB_NAME:-${ORACLE_SID}}
AGE_DAYS=${AGE_DAYS:-7}
REPORT_NAME=GatherStats_${DB_NAME}-`date “+%y%m%d%H%M”`.sql
LOG_NAME=GatherStats_${DB_NAME}-`date “+%y%m%d%H%M”`.log
#echo “${AGE_DAYS}”
#echo “${NUM_RECS}”
#echo “${REPORT_NAME}”
#echo “${OPT_FILTER}”
echo “Gathering stats for tables in ${DB_NAME} with stats older that ${AGE_DAYS} days . . .”

(sqlplus -s ${CRED} as sysdba <<!
set pages 0
set feed off
set head off
set lines 130
prompt set sqlprompt “_CONNECT_IDENTIFIER> “;
prompt set feed off;
prompt set echo on;
–prompt spool ${LOG_NAME}
SELECT ‘exec DBMS_STATS.GATHER_TABLE_STATS(”’||t.owner||”’,”’||t.table_name||”’);’ FROM dba_tables t
JOIN dba_tab_statistics s ON (s.OWNER = t.OWNER AND s.TABLE_NAME = t.TABLE_NAME)
WHERE t.owner NOT LIKE ‘%SYS%’ AND t.owner NOT IN (‘XDB’) AND t.temporary = ‘N’ AND t.secondary = ‘N’ AND t.segment_created = ‘YES’
AND s.stattype_locked IS NULL AND (t.last_analyzed < SYSDATE –  ${AGE_DAYS} OR t.last_analyzed IS NULL) AND t.table_name NOT LIKE ‘SYS%’ ${NUM_RECS} ${OPT_FILTER}
ORDER BY t.num_rows;
exit
!
) > ${REPORT_NAME}

# Add commands to gather system stats if requested
if [ -n “${SYS_STATS}” ] ; then
echo “exec DBMS_STATS.GATHER_SYSTEM_STATS” >>${REPORT_NAME}
echo “exec DBMS_STATS.GATHER_DICTIONARY_STATS” >>${REPORT_NAME}
echo “exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS” >>${REPORT_NAME}
fi
echo “exit” >>${REPORT_NAME}

sqlplus ${CRED} as sysdba @${REPORT_NAME}