Oracle Database Experts

Here are the additional RPM requirement to upgrade from Oracle Database 10g to Oracle Database 11g. Using up2date, you can easily install or update these RPMs.

[root@rac01 rpm]# up2date elfutils-libelf-devel

Fetching Obsoletes list for channel: el4_i386_latest…

Fetching rpm headers…
########################################

Name Version Rel
———————————————————-
elfutils-libelf-devel 0.97.1 5 i386

Testing package set / solving RPM inter-dependencies…
########################################
elfutils-libelf-0.97.1-5.i3 ########################## Done.
elfutils-libelf-devel-0.97. ########################## Done.
elfutils-0.97.1-5.i386.rpm: ########################## Done.
Preparing ########################################### [100%]

Installing…
1:elfutils-libelf ########################################### [100%]
2:elfutils ########################################### [100%]
3:elfutils-libelf-devel ########################################### [100%]
The following packages were added to your selection to satisfy dependencies:

Name Version Release
————————————————————–
elfutils-libelf 0.97.1 5
elfutils 0.97.1 5

[root@rac01 rpm]# up2date libaio-devel

Fetching Obsoletes list for channel: el4_i386_latest…

Fetching rpm headers…
########################################

Name Version Rel
———————————————————-
libaio-devel 0.3.105 2 i386

Testing package set / solving RPM inter-dependencies…
########################################
libaio-devel-0.3.105-2.i386 ########################## Done.
Preparing ########################################### [100%]

Installing…
1:libaio-devel ########################################### [100%]
[root@rac01 rpm]# up2date unixODBC

Fetching Obsoletes list for channel: el4_i386_latest…

Fetching rpm headers…
########################################

Name Version Rel
———————————————————-
unixODBC 2.2.11 1.RHEL4.1 i386

Testing package set / solving RPM inter-dependencies…
########################################
unixODBC-2.2.11-1.RHEL4.1.i ########################## Done.
Preparing ########################################### [100%]

Installing…
1:unixODBC ########################################### [100%]
[root@rac01 rpm]# up2date unixODBC-devel

Fetching Obsoletes list for channel: el4_i386_latest…

Fetching rpm headers…
########################################

Name Version Rel
———————————————————-
unixODBC-devel 2.2.11 1.RHEL4.1 i386

Testing package set / solving RPM inter-dependencies…
########################################
unixODBC-devel-2.2.11-1.RHE ########################## Done.
Preparing ########################################### [100%]

Installing…
1:unixODBC-devel ########################################### [100%]

Technorati : Oracle Database 11g

If you are licensed to use Oracle Enterprise Linux and have setup up2date with OLN, you can use up2date with the “–install oracle-validated” option to automatically install all required packages by the Oracle database installer. The oracle-validated installation also creates the oracle OS user and an oinstall and dba groups.

up2date --install oracle-validated

Fetching Obsoletes list for channel: el4_i386_addons...

Fetching Obsoletes list for channel: el4_i386_oracle...

Fetching Obsoletes list for channel: el4_i386_latest...

Fetching rpm headers...
########################################

Name                                    Version        Rel
----------------------------------------------------------
oracle-validated                        1.0.0          3.el4             i386  

Testing package set / solving RPM inter-dependencies...
########################################
oracle-validated-1.0.0-3.el ########################## Done.
elfutils-libelf-devel-0.97. ########################## Done.
gcc-3.4.6-3.1.0.1.i386.rpm: ########################## Done.
gcc-c++-3.4.6-3.1.0.1.i386. ########################## Done.
glibc-devel-2.3.4-2.25.i386 ########################## Done.
glibc-headers-2.3.4-2.25.i3 ########################## Done.
glibc-kernheaders-2.4-9.1.9 ########################## Done.
libstdc++-devel-3.4.6-3.1.0 ########################## Done.
sysstat-5.0.5-11.rhel4.i386 ########################## Done.
Preparing              ########################################### [100%]

Installing...
   1:libstdc++-devel        ########################################### [100%]
   2:glibc-kernheaders      ########################################### [100%]
   3:glibc-headers          ########################################### [100%]
   4:glibc-devel            ########################################### [100%]
   5:gcc                    ########################################### [100%]
   6:gcc-c++                ########################################### [100%]
   7:sysstat                ########################################### [100%]
   8:elfutils-libelf-devel  ########################################### [100%]
   9:oracle-validated       ########################################### [100%]

.....

Posted by Charles Kim @ DBAExpert.com

Imagine a comprehensive monitoring script that will allow you to customize how many times an alert will be sent. Not only does it tell you the alert but it also informs you the incident number of the alert. Here’s an example of the subject of the message that you will receive:

Tablespace Threshold:DB:WARNING:rac01.dbaexpert.com:gc::Alert#: 5 for Tbsp_Free < 15

And here’s an example of the message detail:

TABLESPACE_NAME %_FREE
—————————— ——
DATA 14.8
SYSAUX 15.4

#!/bin/ksh
######################################################################################################
#
# TITLE……: tbspmon.ksh
# LOCATION…: /apps/oracle/general/sh
# PARAMETERS : PAGER Threshold and Database
#
# USAGE……: tbspmon.ksh $PAGER_THRESHOLD $DB
# OUTPUTS….: List of databases that have tablespaces that are about to run out of space
# DESCRIPTION
# FUNCTION: 1) Runs SQL code that saves info for tablespaces that have less free space than the
# specified have less free space than the specified pager threshold.
# 2) Sends appropriate e-mail and pager notifications, for tablespaces that have less free
# space than the specified e-mail threshold and pager threshold respectively.
# NOTES……: This function was written for the Korn shell and may have
# minor portability problems between different platforms.
# AUTHOR…..:
#
# Modification
#-Date———-Author & Description——————————————————————
#
#
######################################################################################################

export PAGER_THRESHOLD=$1
export DB=$2
FN=`echo $0 | sed s/\.*[/]//`
MAX_NUMBER_ALERTS=5
TMPDIR=/tmp

TBSP_CONFIG=$(echo $0 | sed s/\.*[/]// |awk -F”.” {‘print $1 “.exceptions”‘})

#———————————————
# Parameters from the ${0}.conf file
# And define error and log files
#———————————————
export LOGDIR=${TMPDIR}
export LOGFILE=${TMPDIR}/${DB}_tbsp_free_email_notif.alert
echo $LOGFILE

if [ -f "$LOGFILE" ]; then
rm $LOGFILE
fi

function show_debug_parameters
{
echo “Max Number of Alerts: $MAX_NUMBER_ALERTS”
echo “Log Directory: $LOGDIR”
echo $COUNTER
}

function exam_tablespace_threshold
{

print “TBSP CONFIG: $TBSP_CONFIG”
# — Generate Ignore List of database/tablespaces from being monitored
cat $SH/$TBSP_CONFIG |egrep -v “\^#|grep”| grep $DB |awk -F”:” {‘print $2′} |read IGNORE_LIST

[ "$IGNORE_LIST" = "" ] && export IGNORE_LIST=”‘SOME_BOGUS_TABLESPACE’”
print “IGNORE LIST: $IGNORE_LIST”

#export TNS_ADMIN=/var/opt/oracle/dba
export PW=$(cat $SH/.rodba.pw)
export CONNECT_STRING=rodba/${PW}@${DB}
echo “Connect String: $CONNECT_STRING”

sqlplus -s $CONNECT_STRING 2>&1 <<__ENDSQL
whenever sqlerror exit sql.sqlcode;

set pages 60
set lines 90
set verif off
set trims on

col pct_free for 999.9 hea ‘%_FREE’
define ORA_SID=’$DB’
define PAGE_THRESHOLD=$PAGER_THRESHOLD
define LOG_DIR=’$LOGDIR’

spool $LOGFILE
select tablespace_name, pct_free
from (
SELECT b.tablespace_name, b.tablespace_size_mb,
sum(nvl(fs.bytes,0))/1024/1024 free_size_mb,
(sum(nvl(fs.bytes,0))/1024/1024/b.tablespace_size_mb *100) pct_free
FROM dba_free_space fs,
(SELECT tablespace_name, sum(bytes)/1024/1024 tablespace_size_mb
FROM dba_data_files
GROUP BY tablespace_name
)b
where fs.tablespace_name = b.tablespace_name
and b.tablespace_name NOT IN (SELECT tablespace_name FROM dba_tablespaces WHERE status=’READ ONLY’)
and b.tablespace_name NOT IN (${IGNORE_LIST})
group by b.tablespace_name, b.tablespace_size_mb
) tbsp_pct_free_v
WHERE PCT_FREE < &PAGE_THRESHOLD
ORDER BY pct_free;
spool off

set pages 14
set lines 80
set verif on
__ENDSQL

grep “TABLESPACE_” $LOGFILE

if [ "$?" -eq 0 ]
then
(( EMAIL_COUNTER = ${COUNTER} + 1 ))

# —————————————————————
# Set ALERT_COUNTER to be passed into alert_notification.ksh
# —————————————————————
export ALERT_COUNTER=$EMAIL_COUNTER

print “Sending Alert ${FN}_PG `hostname` $DB Alert#: $EMAIL_COUNTER for Tbsp_Free”
$SH/alert_notification.ksh ${FN}_PG `hostname` $DB “” “Alert#: $EMAIL_COUNTER for Tbsp_Free < $PAGER_THRESHOLD” $LOGFILE

# Increment the counter by one and append it to the LOGFILE filename
(( COUNTER = ${COUNTER} + 1 ))
echo $COUNTER
mv ${LOGFILE} ${LOGFILE}.${COUNTER}

fi
}

#+++++++++++++++++++++++++++++++++++++++++++++++++++++
# MAIN LOGIC
#+++++++++++++++++++++++++++++++++++++++++++++++++++++

#——————————————–
# Count the number of error files (LOGFILE)
# in the /tmp directory
#——————————————–
# if [ -f ${LOGFILE}.* ]; then
export COUNTER=`ls ${LOGFILE}.* |wc -l`
echo “Counted x number of alert files in the $TMPDIR: $COUNTER”
# else
# COUNTER=0
# echo “Counter is set to 0 because there is no alert files”
# fi

show_debug_parameters;

#——————————————————
# If # of error files on disk is greater than
# $MAX_NUMBER_ALERTS, then skip to the next database
#——————————————————
echo “Current Counter: $COUNTER”
echo “Max number before stopping: $MAX_NUMBER_ALERTS”
if [ "${COUNTER}" -ge "$MAX_NUMBER_ALERTS" ]; then
echo “Exceeded Max number of Alerts: $MAX_NUMBER_ALERTS”
exit 1;
else
exam_tablespace_threshold;
fi

Technorati : Tablespace Monitoring Database Administration

#!/bin/ksh
# Script: oracle_cleanup.daily.ksh
# Purpose: remove unneeded files
# Note:
#—————————————————————
# Modifications
# Date By Change Made
# ———- ————– ——————————–
#
#—————————————————————
# This script runs weekly on an UNIX system and performs a cleanup on
# Oracle files and/or logs that accumulate on a UNIX system. In some cases,
# the files are truncated; in other cases they are simply removed.
#
# Any and all changes made to this script should be considered very
# carefully.

. $HOME/.ORACLE_BASE
export PATH=/usr/local/bin:/usr/sbin:$ORACLE_BASE/general/sh:$PATH

# ****************************************************************************
# * DEFINE FULL QUALIFIED UNIX SYSTEM COMMANDS *
# ****************************************************************************
CHMOD=/bin/chmod
CHOWN=/bin/chown
CP=/bin/cp
DATE=/bin/date
TOUCH=/bin/touch
FIND=/usr/bin/find
RM=/bin/rm
TAR=/bin/tar

# ****************************************************************************
# * DEFINE DIRECTORIES FOR SEARCHES *
# ****************************************************************************
if [ "$ORACLE_BASE" = "" ]; then
echo “ORACLE BASE Directory not defined”
exit 1
fi

TRACE_DIR=$ORACLE_BASE/admin
AUD_DIR=$ORACLE_BASE/admin

# ****************************************************************************
# * DEFINE NULL DEVICE FOR TRUNCATING FILES
# ****************************************************************************

NULL=/dev/null

# ****************************************************************************
# * DEFINE CURRENT DAY DATESTAMP (MMDDYY) TO USE IN LOG RENAMING *
# ****************************************************************************

DATESTAMP=`$DATE +%m%d%y`

# ****************************************************************************
# * FIND and delete *.trc files more than 5 days old. *
# ****************************************************************************

$FIND $TRACE_DIR -name ‘*.trc’ -type f -mtime +5 -exec $RM {} \;

# ****************************************************************************
# * FIND and delete *.aud files more than 5 days old. *
# ****************************************************************************

$FIND $AUD_DIR -name ‘*.aud’ -type f -mtime +5 -exec $RM {} \;

# ****************************************************************************
# * FIND and delete files owned by the dba group more than 5 days old. *
# ****************************************************************************

$FIND /tmp -group dba -type f -mtime +5 -exec rm -f {} \;
$FIND /tmp/dba -group dba -type f -mtime +5 -exec rm -f {} \;

# ****************************************************************************
# * FIND and delete all core files owned by oracle. *
# ****************************************************************************

$FIND $ORACLE_BASE -name ‘core’ -type f -mtime +3 -exec $RM {} \;

# ****************************************************************************
# * FIND and delete all nohup files owned by oracle. *
# ****************************************************************************

$FIND $ORACLE_BASE -name ‘nohup.out’ -type f -mtime +1 -exec $RM {} \;

# ****************************************************************************
# * Removing uncleared events
# ****************************************************************************
print “Clearing Events …”;ls -l /tmp/*.alert.*; rm /tmp/*.alert.*

exit 0

rdist versions 6.1 and greater can use ssh instead of rsh using the -P option:

-P <transport-path>
Set the path to the transport command to be used. This is normally rsh(1c) but can be any other program – such as
ssh(1) – which understands rsh(1c) command line syntax and which provides an appropriate connection to the remote
host. The transport-path may be a colon seperated list of possible pathnames. In thi case, the first component
of the path to exist is used. i.e. /usr/bin/ssh, /usr/ucb/rsh:/usr/bin/remsh, /usr/bsd/rsh.

Here is a sample rdist configuration file (rdist.conf) to synchronize the SQL and shell scripts to remote servers:

HOSTS = ( rac1.dbaexpert.com rac2.dbaexpert.com rac3.dbaexpert.com rac11.dbaexpert.com rac12.dbaexpert.com rac13.dbaexpert.com )
FILES = ( /u01/app/oracle/.profile /u01/app/oracle/.aliases /u01/app/oracle/.rhosts /u01/app/oracle/general/sql /u01/app/oracle/gen)
${FILES} -> ${HOSTS}
install -R;

# The -R option will remove files on the remote file system if the file is removed from the local file system

To manually synchronize the file systems for all of the 6 servers listed above, you would use rdist with the -f option and pass in the rdist.conf file.

Posted by: Charles Kim @ DBAExpert.com