Oracle Monitoring Scripts

Simple Command

1. Display the available instances on the server:
Ps-ef | grep Smon
2. Display the available listeners on the server:
Ps-ef | Grep-i Listener | Grep-v grep
3. View file system usage for the Oracle archive directory:
Df-h
4. Count the number of rows in the Alter.log file:
[Email protected] ~]# Find/-name alert*
5.CRONTAB
A crontab file contains six fields:
Minutes 0-59
Hours 0-23
Day of the month 1-31
Month 1-12
Day of the week 0-6, with 0 = Sunday
6.
To edit a Crontab file, type: CRONTAB-E
To view a Crontab file, enter: Crontab-l
0 4 * * 5/dba/admin/analyze_table.ksh
3 * * 3,6/dba/admin/hotbackup.ksh/dev/null 2>&1
In the example above, the first line shows the script for a parse table to run at 5 4:00am per week. The second line shows a script that performs a hot backup in each of the Wednesday and Saturday 3:00a.m.

Monitoring Scripts

The 8 shell scripts provided below cover 90% of the DBA's daily monitoring work, and you may also need to modify the UNIX environment variables.
1. Check availability of Oracle instances
all databases on the server are listed in the Oratab file
[[email protected] ~]# Find/-name oratab
/etc/oratab
/u01/app/oracle/product/11.2.0/db_1/install/oratab
[[email protected] ~]# cat/etc/oratab
The following script examines all the databases listed in the Oratab file and finds out the status of the database (start or close)
############################################################## #####
# # Ckinstance.ksh # #
###################################################################
ORATAB=/ Etc/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 command to confirm that the script can be executed:
$ chmod 744 Ckinstance.ksh
$ ls-l Ckinstance.ksh
-rwxr--r--1 Oracle DBA 657 Mar 5 22:59 Ckinstance.ksh

The following is a report of instance availability:
$ sh Ckinstance.ksh
Wed 12:51:20 PDT 2009
Oracle Database (s) Status gambels:
Oracle Instance-pprd:up
Oracle Instance-pprd10:up

2. Check the availability of Oracle listeners
Here is a similar script that examines the Oracle listener. If the listener is stopped, the script will restart the listener:
#####################################################################
# # cklsnr.sh # #
#####################################################################
#!/bin/ksh
Tns_admin=/var/opt/oracle; Export Tns_admin
Oracle_sid= PPRD10; Export Oracle_sid
Oraenv_ask=no; Export Oraenv_ask
Path= $PATH:/bin:/usr/local/bin; Export PATH
. Oraenv
dbalist= "[Email protected],[email protected]"; Export dbalist
Cd/var/opt/oracle
Rm-f lsnr.exist
Ps-ef | grep PPRD10 | grep-v grep > Lsnr.exist
If [-S lsnr.exist]
Then
Echo
Else
echo "Alert" | Mailx-s "Listener ' PPRD10 ' on ' hostname ' are down" $DBALIST
Lsnrctl Start PPRD10
Fi

3. Check alert log (ORA-XXXXX)
####################################################################
# # ckalertlog.sh # #
####################################################################
#!/bin/ksh

Editor=vi; export EDITOR
oracle_sid=pprd10; export oracle_sid
oracle_base=/data/app/oracle; export Oracle_ BASE
oracle_home= $ORACLE _base/10.2.0; 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= "[email protected],[email protected]"; Export dbalist

CD $ORACLE _base/admin/pprd10/bdump
If [-F Alert_pprd10.log]
Then
MV Alert_pprd10.log Alert_work.log
Touch Alert_pprd10.log
Cat Alert_work.log >> Alert_pprd10.hist
grep ora-alert_work.log > Alert.err
Fi
If [' Cat Alert.err | wc-l '-GT 0]
Then
Mailx-s "PPRD10 ORACLE ALERT ERRORS" $DBALIST < Alert.err
Fi
Rm-f Alert.err
Rm-f Alert_work.log

4. Clear the old archive file
The following script will empty the old archive file when the log file reaches 90% 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 $ $} ' 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

5. Analyze tables and indexes (for better performance)
Below I will show if the transfer parameters into 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 pass in the parameter to execute the script, enter:
$ analyze_table.sh Manager oradb1
The first part of the script produces a analyze.sql file that contains the statements used to parse the table. The second part of the script parses all the tables:
#################################################################
# # analyze_table.sh # #
#################################################################
Sqlplus-s '/as Sysdba ' <<eof
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 PERCENT; '
From Dba_segments
where Segment_type = ' TABLE '
and owner not in (' SYS ', ' SYSTEM ');
Spool off
Exit
Eof
Sqlplus-s '/as Sysdba ' <<eof
@./analyze_table.sql
Exit
Eof

Here is an example of Analyze.sql:
$ cat Analyze.sql
ANALYZE TABLE Hirwin. Janusage_summary ESTIMATE STATISTICS SAMPLE PERCENT;
ANALYZE TABLE Hirwin. Januser_profile ESTIMATE STATISTICS SAMPLE PERCENT;
ANALYZE TABLE Appssys. Hist_system_activity ESTIMATE STATISTICS SAMPLE PERCENT;
ANALYZE TABLE Htomeh. Quest_im_version ESTIMATE STATISTICS SAMPLE PERCENT;
ANALYZE TABLE Jstenzel. hist_sys_act_0615 ESTIMATE STATISTICS SAMPLE PERCENT;

6. Checking the use of table spaces
The following script detects the use of tablespace. If the tablespace has only 10% left, it will send a warning email.
#####################################################################
# # ck_tbsp.sh # #
#####################################################################
#!/bin/ksh

Editor=vi; Export EDITOR
ORACLE_SID=PPRD10; Export Oracle_sid
Oracle_base=/data/app/oracle; Export Oracle_base
Oracle_home= $ORACLE _base/10.2.0; 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= "[Email protected],[email protected]"; Export dbalist

Sqlplus-s '/as Sysdba ' <<eof
Set Feed off
Set Linesize 100
Set PageSize 200
Column "Used (MB)" format A10
Column "Free (MB)" format A10
Column "Total (MB)" format A10
Column Per_free format A10
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) < 80;
Spool off
Exit
Eof
If [' Cat tablespace.alert|wc-l '-GT 0]
Then
Cat Tablespace.alert > Tablespace.tmp
Mailx-s "Tablespace ALERT for PPRD10" $DBALIST < tablespace.tmp
Fi

An example of warning email output is as follows:
Tablespace_name Used (MB) free (MB) Total (MB) per_free
------------------------------ ---------- ---------- ---------- ----------
SYSTEM 519 401 920 44
Milldata 559 441 1,000 44
Sysaux 331 609 940 65
Millreports 146 254 400 64

7. Finding an invalid database object
The following finds an invalid database object:
#####################################################################
# #invalid_object_alert. SH
#####################################################################
#!/ Bin/ksh
Editor=vi; export EDITOR
oracle_sid=pprd10; export oracle_sid
oracle_base=/data/app/oracle; export Oracle_base
oracle_home= $ORACLE _base/10.2.0; 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= "[email protected],[email protected]"; Export dbalist

Sqlplus-s '/as Sysdba ' <<eof
Set feed off
Set heading off
column OWNER format A10
Column Object_nam E format A35
Column object_type format A10
Column STATUS format A10
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
EOF
If [' Cat Invalid_object.alert | wc-l '-GT 0] then
mailx-s "Invalid OBJECTS for PPRD Ten "$DBALIST < Invalid_object.alert
Fi

$ more Invalid_object.alert
Public all_wm_locked_tables synonym INVALID
Public all_wm_versioned_tables synonym INVALID
Public dba_wm_versioned_tables synonym INVALID
Public sdo_cart_text synonym INVALID
Public sdo_geometry synonym INVALID
Public sdo_regaggr synonym INVALID
Public sdo_regaggrset synonym INVALID
Public sdo_region synonym INVALID
Public sdo_regionset synonym INVALID
Public user_wm_locked_tables synonym INVALID
Public user_wm_versioned_tables synonym INVALID
Public wm_compress_batch_sizes synonym INVALID

8. Monitoring of users and transactions (deadlocks, etc.)
The following script sends a warning e-mail when a deadlock occurs:
###################################################################
# # deadlock_alert.sh # #
###################################################################
#!/bin/ksh

Editor=vi; export EDITOR
oracle_sid=pprd10; export oracle_sid
oracle_base=/data/app/oracle; export Oracle_ BASE
oracle_home= $ORACLE _base/10.2.0; 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= "[email protected],[email protected]"; Export dbalist

Sqlplus-s '/as Sysdba ' <<eof
Set feed off
set heading off
Spool Deadlock.alert
SELECT SID, DECODE (B LOCK, 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
EOF
If [' Cat Deadlock.alert | wc-l '-GT 0]
then
mailx-s "deadlock alert for PPRD10" $DBA LIST < 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/c klsnr.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_a lert.sh >/dev/null 2>&1
0,20,40 7-17 * * 1-5/dba/scripts/deadlock_alert.sh >/dev/null 2>&1
Pass Over the script, can greatly alleviate your work. You can use these to do more important work, such as performance tuning.