Sunday, 29 July 2012

DBA Related Shell Scripts

--------------------------------------------------------------------- 
Script to remove the logs 
a) Remove archivelogs which are older than 3 days. 
b) Remove logs from db admin directory which are older than 14 days 
--------------------------------------------------------------------- 

#!/bin/ksh 

# Cleanup PROD archive logs more than 3 days old 
/usr/bin/find /u01/app/oracle/admin/PROD/arch/arch_prod*.arc -ctime +3 -exec rm {} \; 

# Cleanup trace and dump files over 14 days old 
for ORACLE_SID in `cat /etc/oratab|egrep ':N|:Y'|grep -v \*|cut -f1 -d':'` 
do 
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -d":" -f2` 
DBA=`echo $ORACLE_HOME | sed -e 's:/product/.*::g'`/admin 
/usr/bin/find $DBA/$ORACLE_SID/bdump -name \*.trc -mtime +14 -exec rm {} \;
/usr/bin/find $DBA/$ORACLE_SID/udump -name \*.trc -mtime +14 -exec rm {} \;
/usr/bin/find $ORACLE_HOME/rdbms/audit -name \*.aud -mtime +14 -exec rm {} \; 
done 

----------------------------------------------------------------------------------- 

Scripts by Daniel T. Liu 
---------------------------------------------------------------------------------- 

Introduction 
This article focuses on the DBA's daily responsibilities for monitoring Oracle databases and provides tips and techniques on how DBAs can turn their manual, reactive monitoring activities into a set of proactive shell scripts. The article first reviews some commonly used Unix commands by DBAs. It explains the Unix Cron jobs that are used as part of the scheduling mechanism to execute DBA scripts. The article covers eight important scripts for monitoring Oracle database: 

Check instance availability 

Check listener availability 

Check alert log files for error messages 

Clean up old log files before log destination gets filled 

Analyze tables and indexes for better performance 

Check tablespace usage 

Find out invalid objects 

Monitor users and transactions 

UNIX Basics for the DBA 
Basic UNIX Command 

The following is a list of commonly used Unix command: 

ps - Show process 

grep - Search files for text patterns 

mailx - Read or send mail 

cat - Join files or display them 

cut - Select columns for display 

awk - Pattern-matching language 

df - Show free disk space 

Here are some examples of how the DBA uses these commands: 


List available instances on a server: 

$ ps -ef | grep smon 
oracle 21832 1 0 Feb 24 ? 19:05 ora_smon_oradb1 
oracle 898 1 0 Feb 15 ? 0:00 ora_smon_oradb2 
dliu 25199 19038 0 10:48:57 pts/6 0:00 grep smon 
oracle 27798 1 0 05:43:54 ? 0:00 ora_smon_oradb3 
oracle 28781 1 0 Mar 03 ? 0:01 ora_smon_oradb4 


List available listeners on a server: 

$ ps -ef | grep listener | grep -v grep 
oracle 23879 1 0 Feb 24 ? 33:36 /8.1.7/bin/tnslsnr listener_db1 -inherit 
oracle 27939 1 0 05:44:02 ? 0:00 /8.1.7/bin/tnslsnr listener_db2 -inherit 
oracle 23536 1 0 Feb 12 ? 4:19 /8.1.7/bin/tnslsnr listener_db3 -inherit 
oracle 28891 1 0 Mar 03 ? 0:01 /8.1.7/bin/tnslsnr listener_db4 -inherit 


Find out file system usage for Oracle archive destination: 

$ df -k | grep oraarch 
/dev/vx/dsk/proddg/oraarch 71123968 4754872 65850768 7% /u09/oraarch 


List number of lines in the alert.log file: 

$ cat alert.log | wc -l 
2984 


List all Oracle error messages from the alert.log file: 

$ grep ORA- alert.log 
ORA-00600: internal error code, arguments: [kcrrrfswda.1], [], [], [], [], [] 
ORA-00600: internal error code, arguments: [1881], [25860496], [25857716], [] 

CRONTAB Basics 
A crontab file is comprised of six fields: 

Minute 0-59 
Hour 0-23 
Day of month 1-31 
Month 1 - 12 
Day of Week 0 - 6, with 0 = Sunday 
Unix Command or Shell Scripts 


To edit a crontab file, type: 

Crontab -e 


To view a crontab file, type: 

Crontab -l 
0 4 * * 5 /dba/admin/analyze_table.ksh 
30 3 * * 3,6 /dba/admin/hotbackup.ksh /dev/null 2>&1 


In the example above, the first entry shows that a script to analyze a table runs every Friday at 4:00 a.m. The second entry shows that a script to perform a hot backup runs every Wednesday and Saturday at 3:00 a.m. 

Top DBA Shell Scripts for Monitoring the Database 
The eight shell scripts provided below cover 90 percent of a DBA's daily monitoring activities. You will need to modify the UNIX environment variables as appropriate. 

Check Oracle Instance Availability 

The oratab file lists all the databases on a server: 

$ cat /var/opt/oracle/oratab 
################################################################### 
## /var/opt/oracle/oratab ## 
################################################################### 
oradb1:/u01/app/oracle/product/8.1.7:Y 
oradb2:/u01/app/oracle/product/8.1.7:Y 
oradb3:/u01/app/oracle/product/8.1.7:N 
oradb4:/u01/app/oracle/product/8.1.7:Y 


The following script checks all the databases listed in the oratab file, and finds out the status (up or down) of databases: 

################################################################### 
## ckinstance.ksh ## 
################################################################### 
ORATAB=/var/opt/oracle/oratab 
echo "`date` " 
echo "Oracle Database(s) Status `hostname` :\n" 


db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "\#" | grep -v "\*"` 
pslist="`ps -ef | grep pmon`" 
for i in $db ; do 
echo "$pslist" | grep "ora_pmon_$i" > /dev/null 2>$1 
if (( $? )); then 
echo "Oracle Instance - $i: Down" 
else 
echo "Oracle Instance - $i: Up" 
fi 
done 


Use the following to make sure the script is executable: 

$ chmod 744 ckinstance.ksh 
$ ls -l ckinstance.ksh 
-rwxr--r-- 1 oracle dba 657 Mar 5 22:59 ckinstance.ksh* 

Here is an instance availability report: 

$ ckinstance.ksh 
Mon Mar 4 10:44:12 PST 2002 

Oracle Database(s) Status for DBHOST server: 
Oracle Instance - oradb1: Up 
Oracle Instance - oradb2: Up 
Oracle Instance - oradb3: Down 
Oracle Instance - oradb4: Up 

Check Oracle Listener's Availability 
A similar script checks for the Oracle listener. If the listener is down, the script will restart the listener: 

####################################################################### 
## cklsnr.sh ## 
####################################################################### 
#!/bin/ksh 
DBALIST="primary.dba@company.com,another.dba@company.com";export DBALIST 
cd /var/opt/oracle 
rm -f lsnr.exist 
ps -ef | grep mylsnr | grep -v grep > lsnr.exist 
if [ -s lsnr.exist ] 
then 
echo 
else 
echo "Alert" | mailx -s "Listener 'mylsnr' on `hostname` is down" $DBALIST 
TNS_ADMIN=/var/opt/oracle; export TNS_ADMIN 
ORACLE_SID=db1; export ORACLE_SID 
ORAENV_ASK=NO; export ORAENV_ASK 
PATH=$PATH:/bin:/usr/local/bin; export PATH 
. oraenv 
LD_LIBRARY_PATH=${ORACLE_HOME}/lib;export LD_LIBRARY_PATH 
lsnrctl start mylsnr 
fi 

Check Alert Logs (ORA-XXXXX) 
Some of the environment variables used by each script can be put into one profile: 

####################################################################### 
## oracle.profile ## 
####################################################################### 
EDITOR=vi;export EDITOR ORACLE_BASE=/u01/app/oracle; export 
ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/8.1.7; export 
ORACLE_HOME LD_LIBRARY_PATH=$ORACLE_HOME/lib; export 
LD_LIBRARY_PATH TNS_ADMIN=/var/opt/oracle;export 
TNS_ADMIN NLS_LANG=american; export 
NLS_LANG NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export 
NLS_DATE_FORMAT ORATAB=/var/opt/oracle/oratab;export 
ORATAB PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/ 
sbin:/usr/openwin/bin:/opt/bin:.; export 
PATH DBALIST="primary.dba@company.com,another.dba@company.com";export 
DBALIST 

The following script first calls oracle.profile to set up all the environment variables. The script also sends the DBA a warning e-mail if it finds any Oracle errors: 

#################################################################### 
## ckalertlog.sh ## 
#################################################################### 
#!/bin/ksh 
. /etc/oracle.profile 
for SID in `cat $ORACLE_HOME/sidlist` 
do 
cd $ORACLE_BASE/admin/$SID/bdump 
if [ -f alert_${SID}.log ] 
then 
mv alert_${SID}.log alert_work.log 
touch alert_${SID}.log 
cat alert_work.log >> alert_${SID}.hist 
grep ORA- alert_work.log > alert.err 
fi 
if [ `cat alert.err|wc -l` -gt 0 ] 
then 
mailx -s "${SID} ORACLE ALERT ERRORS" $DBALIST < alert.err 
fi 
rm -f alert.err 
rm -f alert_work.log 
done 

Clean Up Old Archived Logs 
The following script cleans up old archive logs if the log file system reaches 90-percent capacity: 

$ df -k | grep arch 
Filesystem kbytes used avail capacity Mounted on 
/dev/vx/dsk/proddg/archive 71123968 30210248 40594232 43% /u08/archive 


####################################################################### 
## clean_arch.ksh ## 
####################################################################### 
#!/bin/ksh 
df -k | grep arch > dfk.result 
archive_filesystem=`awk -F" " '{ print $6 }' dfk.result` 
archive_capacity=`awk -F" " '{ print $5 }' dfk.result` 



if [[ $archive_capacity > 90% ] ] 
then 
echo "Filesystem ${archive_filesystem} is ${archive_capacity} filled" 
# try one of the following option depend on your need 
find $archive_filesystem -type f -mtime +2 -exec rm -r {} \; 
tar 
rman 
fi 

Analyze Tables and Indexes (for Better Performance) 
Below, I have shown an example on how to pass parameters to a script: 

#################################################################### 
## analyze_table.sh ## 
#################################################################### 
#!/bin/ksh # 
input parameter: 1: password # 2: SID if (($#<1)) then echo "Please enter 
'oracle' 
user password as the first parameter !" exit 0 fi if (($#<2)) then echo 
"Please enter 
instance name as the second parameter!" exit 0 fi 

To execute the script with parameters, type: 

$ analyze_table.sh manager oradb1 


The first part of script generates a file analyze.sql, which contains the syntax for analyzing table. The second part of script analyzes all the tables: 

##################################################################### 
## analyze_table.sh ## 
##################################################################### 
sqlplus -s <oracle/$1@$2 
set heading off 
set feed off 
set pagesize 200 
set linesize 100 
spool analyze_table.sql 
select 'ANALYZE TABLE ' || owner || '.' || segment_name || 
' ESTIMATE STATISTICS SAMPLE 10 PERCENT;' 
from dba_segments 
where segment_type = 'TABLE' 
and owner not in ('SYS', 'SYSTEM'); 
spool off 
exit 

sqlplus -s <oracle/$1@$2 
@./analyze_table.sql 
exit 



Here is an example of analyze.sql: 

$ cat analyze.sql 
ANALYZE TABLE HIRWIN.JANUSAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT; 
ANALYZE TABLE HIRWIN.JANUSER_PROFILE ESTIMATE STATISTICS SAMPLE 10 PERCENT; 
ANALYZE TABLE APPSSYS.HIST_SYSTEM_ACTIVITY ESTIMATE STATISTICS SAMPLE 10 PERCENT; 
ANALYZE TABLE HTOMEH.QUEST_IM_VERSION ESTIMATE STATISTICS SAMPLE 10 PERCENT; 
ANALYZE TABLE JSTENZEL.HIST_SYS_ACT_0615 ESTIMATE STATISTICS SAMPLE 10 PERCENT; 
ANALYZE TABLE JSTENZEL.HISTORY_SYSTEM_0614 ESTIMATE STATISTICS SAMPLE 10 PERCENT; 
ANALYZE TABLE JSTENZEL.CALC_SUMMARY3 ESTIMATE STATISTICS SAMPLE 10 PERCENT; 
ANALYZE TABLE IMON.QUEST_IM_LOCK_TREE ESTIMATE STATISTICS SAMPLE 10 PERCENT; 
ANALYZE TABLE APPSSYS.HIST_USAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT; 
ANALYZE TABLE PATROL.P$LOCKCONFLICTTX ESTIMATE STATISTICS SAMPLE 10 PERCENT; 

Check Tablespace Usage 
This scripts checks for tablespace usage. If tablespace is 10 percent free, it will send an alert e-mail. 

##################################################################### 
## ck_tbsp.sh ## 
##################################################################### 
#!/bin/ksh 
sqlplus -s <oracle/$1@$2 
set feed off 
set linesize 100 
set pagesize 200 
spool tablespace.alert 
SELECT F.TABLESPACE_NAME, 
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)", 
TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)", 
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)", 
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE 
FROM ( 
SELECT TABLESPACE_NAME, 
ROUND (SUM (BLOCKS*(SELECT VALUE/1024 
FROM V\$PARAMETER 
WHERE NAME = 'db_block_size')/1024) 
) FREE_SPACE 
FROM DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME 
) F, 

SELECT TABLESPACE_NAME, 
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE 
FROM DBA_DATA_FILES 
GROUP BY TABLESPACE_NAME 
) T 
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME 
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10; 
spool off 
exit 

if [ `cat tablespace.alert|wc -l` -gt 0 ] 
then 
cat tablespace.alert -l tablespace.alert > tablespace.tmp 
mailx -s "TABLESPACE ALERT for ${2}" $DBALIST < tablespace.tmp 
fi 


An example of the alert mail output is as follows: 

TABLESPACE_NAME USED (MB) FREE (MB) TOTAL (MB) PER_FREE 
------------------- --------- ----------- ------------------- ------------------ 
SYSTEM 2,047 203 2,250 9 % 
STBS01 302 25 327 8 % 
STBS02 241 11 252 4 % 
STBS03 233 19 252 8 % 

Find Out Invalid Database Objects 
The following finds out invalid database objects: 

##################################################################### 
## invalid_object_alert.sh ## 
##################################################################### 
#!/bin/ksh 
. /etc/oracle.profile 
sqlplus -s <oracle/$1@$2 
set feed off 
set heading off 
column object_name format a30 
spool invalid_object.alert 
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS 
FROM DBA_OBJECTS 
WHERE STATUS = 'INVALID' 
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME; 
spool off 
exit 

if [ `cat invalid_object.alert|wc -l` -gt 0 ] 
then 
mailx -s "INVALID OBJECTS for ${2}" $DBALIST < invalid_object.alert 
fi 
$ cat invalid_object.alert 


OWNER OBJECT_NAME OBJECT_TYPE STATUS 
---------------------------------------------------------------------- 
HTOMEH DBMS_SHARED_POOL PACKAGE BODY INVALID 
HTOMEH X_$KCBFWAIT VIEW INVALID 
IMON IW_MON PACKAGE INVALID 
IMON IW_MON PACKAGE BODY INVALID 
IMON IW_ARCHIVED_LOG VIEW INVALID 
IMON IW_FILESTAT VIEW INVALID 
IMON IW_SQL_FULL_TEXT VIEW INVALID 
IMON IW_SYSTEM_EVENT1 VIEW INVALID 
IMON IW_SYSTEM_EVENT_CAT VIEW INVALID 
LBAILEY CHECK_TABLESPACE_USAGE PROCEDURE INVALID 
PATROL P$AUTO_EXTEND_TBSP VIEW INVALID 
SYS DBMS_CRYPTO_TOOLKIT PACKAGE INVALID 
SYS DBMS_CRYPTO_TOOLKIT PACKAGE BODY INVALID 
SYS UPGRADE_SYSTEM_TYPES_TO_816 PROCEDURE INVALID 
SYS AQ$_DEQUEUE_HISTORY_T TYPE INVALID 
SYS HS_CLASS_CAPS VIEW INVALID 
SYS HS_CLASS_DD VIEW INVALID 

Monitor Users and Transactions (Dead Locks, et al) 
This script sends out an alert e-mail if dead lock occurs: 

################################################################### 
## deadlock_alert.sh ## 
################################################################### 
#!/bin/ksh 
. /etc/oracle.profile 
sqlplus -s <oracle/$1@$2 
set feed off 
set heading off 
spool deadlock.alert 
SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER, 
DECODE(REQUEST, 0, 'NO','YES' ) WAITER 
FROM V$LOCK 
WHERE REQUEST > 0 OR BLOCK > 0 
ORDER BY block DESC; 
spool off 
exit 

if [ `cat deadlock.alert|wc -l` -gt 0 ] 
then 
mailx -s "DEADLOCK ALERT for ${2}" $DBALIST < deadlock.alert 
fi 

Conclusion 

0,20,40 7-17 * * 1-5 /dba/scripts/ckinstance.sh > /dev/null 2>&1 
0,20,40 7-17 * * 1-5 /dba/scripts/cklsnr.sh > /dev/null 2>&1 
0,20,40 7-17 * * 1-5 /dba/scripts/ckalertlog.sh > /dev/null 2>&1 
30 * * * 0-6 /dba/scripts/clean_arch.sh > /dev/null 2>&1 
* 5 * * 1,3 /dba/scripts/analyze_table.sh > /dev/null 2>&1 
* 5 * * 0-6 /dba/scripts/ck_tbsp.sh > /dev/null 2>&1 
* 5 * * 0-6 /dba/scripts/invalid_object_alert.sh > /dev/null 2>&1 
0,20,40 7-17 * * 1-5 /dba/scripts/deadlock_alert.sh > /dev/null 2>&1 

Now my DBA friends, you can have more uninterrupted sleep at night. You may also have time for more important things such as performance tuning. 

References 
Unix in a Nutshell, O'Reilly & Associates, Inc.; 
“Using Oracle9i Application Server to Build Your Web-Based Database Monitoring Tool,” Daniel T. Liu; Select Magazine - November 2001 Volume 8, No. 1; 
“Net8: A Step-by-Step Setup of Oracle Names Server,” Daniel T. Liu; Oracle Open World 2000, Paper#271. 
I would also like to acknowledge the assistance of Johnny Wedekind of ADP, Ann Collins, Larry Bailey, Husam Tomeh and Archana Sharma of FARES. 

-- 

Daniel Liu is a senior Oracle Database Administrator at First American Real Estate Solutions in Anaheim, CA, and co-author of Oracle Database 10g New Features. His expertise includes Oracle database administration, performance tuning, Oracle networking, and Oracle Application Server. As an Oracle Certified Professional, he taught Oracle certified DBA classes and IOUG University Seminar. Daniel has published articles with DBAzine, Oracle Internals, and SELECT Journal. Daniel holds a Master of Science degree in computer science from Northern Illinois University. 

----------------------------------------------------------------------------- 
----------------------------------------------------------------------------- 

cpu Usage:- 

#!/bin/sh 

ALERTNAME="/opt/MonitoringScript/cpumem.txt" 
Date=`date +'%Y%m%d-%H:%M'` 
host=`hostname` 
Usage=`prstat -Z 1 3|grep -i $host |tail -1|awk '{print "MEMORY:",$5,"CPU:",$7}'` 
echo " $Usage $Date ">> alertlog 

##cat alertlog|tail -1|mailx -s "CPU and MEMORY USAGE `hostname`" -c "user@company.com" "user@company.com" 
cat alertlog|tail -1|mailx -s "CPU and MEMORY USAGE `hostname`" "user@company.com" 



Disk usage:- 

#!/bin/ksh 

LOGNAME="/opt/MonitoringScript/alert.txt" 
host_name=`hostname` 
for mou in /u01 /u05 /u02 
do 
disk_space=`df -k $mou | grep $mou | tr -s " " | cut -d" " -f5 | tr -d "%"` 

if [ $disk_space -ge 75 ] 
then 
echo " $mou on $host_name - ( $disk_space"%" ) - `date '+%d%m%Y - %H:%M'`\n" >> $LOGNAME 
fi 

done 

if [ -f $LOGNAME ] 
then 
cat $LOGNAME | mailx -s "TADDS DISK ALERT" "username@company.com" 
rm $LOGNAME 
fi 


Long running queries :- 

/dboracle/orabase/product/10.2.0.3/bin/sqlplus -s user/pass@db << !END > $path 2>&1 
set termout off; 
set serveroutput on; 
select sql_text, round((elapsed_time/executions)/1000000,2) "Time_Taken in secs",sql_id, child_number from 
v\$sql where executions > 0; 
exit; 
sql_end 

cat $path | mailx -s 'Alert: TEST' "user@company.com" 
EOF 

Tablesapce usage script:- 
#!/bin/sh 
###This script is used to find the Free tablespace less than 40% and e-mail to ASG group e-mail id 
################################################ 

path="/var/TADDSTABLESPACE.log" 

echo "================================================================================== " > $path 

ORACLE_BASE=/dboracle/orabase/product 
export ORACLE_BASE 

ORACLE_HOME=/dboracle/orabase/product/10.2.0.3 
export ORACLE_HOME 

ORACLE_SID=tadds 
export ORACLE_SID 

TNS_ADMIN=/dborafiles/orabase/admin/netadmin 
export TNS_ADMIN 


/dboracle/orabase/product/10.2.0.3/bin/sqlplus -s user/pas@db << !END >> $path 2>&1 

show user 
select tablespace_name, PCT_FREE 
from (SELECT sysdate, Total.tablespace_name Tablespace_Name, 
Dt.EXTENT_MANAGEMENT, 
total_space total_Size_MB, 
round(nvl(total_space-free_space,0),2) used_MB, 
round(nvl(free_space,0),2) free_MB, 
round(nvl(total_space-free_space,0)/total_space*100,2) pct_used, 
round(nvl(free_space,0)/total_space*100,2) pct_free, 
round(Maxsize,2) Maxsize_MB 
FROM 
(select tablespace_name, 
sum(bytes/1024/1024) Free_Space 
from sys.dba_free_space 
group by tablespace_name 
) Free, 
(select TABLESPACE_NAME, sum(MAXBYTES)/1024/1024 Maxsize, 
sum(bytes)/1024/1024 Total_space 
From dba_data_files 
group by tablespace_name ) Total, 
(select TABLESPACE_NAME, 
EXTENT_MANAGEMENT 
from dba_tablespaces 
order by TABLESPACE_NAME) dt 
WHERE Free.Tablespace_name(+) = Total.tablespace_name 
and total.Tablespace_name = dt. TABLESPACE_NAME 
ORDER BY Total.tablespace_name,dt.tablespace_name) WHERE PCT_FREE < 40 
ORDER BY PCT_FREE; 
exit 
!END 

cd 

counter=`cat $path | sed '/^$/d'| wc -l` 
echo "count="$counter 
if [ $counter -gt 0 ] 
then 
cat $path | mailx -s 'Alert: tablespace free PCT' "user@compnay.com" 
rm $path 
fi 

1 comment:

  1. Oracle Database is an object-relational database management system. Your article covers eight important scripts for monitoring Oracle database. I would like to share this in coursework writing service.

    ReplyDelete