The most common shell scripts used by eight DBAs to monitor Oracle databases

 

This article describes eight common shell scripts for monitoring data. First, I reviewed some common DBA Unix Commands and explained how to regularly execute DBA scripts through UNIX cron. There are also a lot of similar articles on the Internet, but they basically cannot run normally. After some time, you can simply use them.

I. At the same time, the article also introduced eight important scripts to monitor the Oracle database:
1. Check instance availability
2. Check listener availability
3. Check the error information in the Alert Log File.
4. Clear old log files before they are fully stored.
5. Analyze table and index for better performance
6. Check the usage of the table space.
7. Find invalid objects
8. Monitor users and transactions

2. Basic UNIX knowledge required by DBA
Basic Unix commands:
PS -- display process
Grep -- search for a text pattern in a file
Mailx -- read or send mail
Cat -- connect files or display them
Cut -- select the displayed Column
Awk-pattern matching language
DF -- display the remaining disk space

Here are some examples of how DBAs use these commands:

1. display available instances on the server:
$ PS-Ef | grep SMON
Oracle 22086 1 0 02:32:24? 0: 04 ora_smon_pprd10
Oracle 5215 28972 0 08:10:19 pts/4 grep SMON

2. display available listeners on the server:
$ PS-Ef grep listener grep-V grep
(The grep command should add the-I parameter, that is, grep-I listener. this parameter is used to ignore case sensitivity, because sometimes the listener is capitalized and the result will not be displayed)
$ PS-Ef | grep-I listener
Oracle 9655 1 0 Mar 12? 0: 01/data/APP/Oracle/9.2.0/bin/tnslsnr listener-inherit
Oracle 22610 1 0 02:45:02? 0: 02/data/APP/Oracle/10.2.0/bin/tnslsnr listener-inherit
Oracle 5268 28972 0 08:13:02 pts/4 grep-I listener

3. view the file system usage of the Oracle archive directory
$ DF-k | grep/Data
/Dev/MD/DSK/D50 104977675 88610542 15317357 86%/Data

4. count the number of rows in the alter. log file:
$ Cat alert_pprd10.log | WC-l
13124
$ More alert_pprd10.log | WC-l
13124

5. list all Oracle error messages in 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], []

6. crontab Basics
A crontab file contains six fields:
Minute 0-59
Hour 0-23
Day 1-31 of the month
Month 1-12
Day of the week 0-6, with 0 = Sunday

7. Unix commands or Shell scripts
To edit a crontab file, enter Crontab-e.
To view a crontab file, enter Crontab-l.
0 4 ** 5/dba/admin/analyze_table.ksh
30 3 ** 3, 6/dba/admin/hotbackup. ksh/dev/null 2> & 1
In the preceding example, the first line shows that the script for an analysis table runs at am every week. The second line shows a script for hot backup running at a.m. on every Wednesday and Saturday.

Iii. Common Shell scripts for monitoring databases
The following eight shell scripts cover 90% of DBA's daily monitoring work. You may need to modify UNIX environment variables.

1. Check the availability of Oracle instances
The oratab file lists all databases on the server.
$ Cat/var/opt/oracle/oratab
#

# This file is used by ORACLE utilities. It is created by root. sh
# And updated by the Database Configuration Assistant when creating
# A database.

# A colon, ':', is used as the field terminator. A new line terminates
# The entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ Oracle_sid: $ ORACLE_HOME: <n | Y>:
#
# The first and second fields are the system identifier and home
# Directory of the database respectively. The third filed indicates
# To the dbstart utility that the database shocould, "Y", or shocould not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ oracle_sid are not allowed.
#
#
# *:/Data/APP/Oracle/9.2.0: N
Trng:/data/APP/Oracle/9.2.0: Y
*:/Data/APP/Oracle/9.2.0: N
PPRD:/data/APP/Oracle/10.2.0: Y
Pprd10:/data/APP/Oracle/10.2.0: N

The following script checks all the Databases listed in the oratab file and finds the database status (start or close)
######################################## ###########################
# 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

Run 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 ckinstance. ksh

The following is an instance Availability Report:
$ Sh ckinstance. ksh
Wed May 13 12:51:20 PDT 2009
Oracle Database (s) status gambels:
Oracle instance-PPRD: Up
Oracle instance-pprd10: Up

2. Check the availability of the Oracle listener
The following script checks the Oracle listener. If the listener stops, the script restarts 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 = "tianlesoftware@vip.qq.com, tianlesoftware@hotmail.com"; 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' is down" $ dbalist
LSNRCTL start pprd10
Fi

3. Check alert logs (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/bin:/usr/CCS/bin:/usr/sbin: /usr/openwin/bin:/opt/bin :.; export path
Dbalist = "tianlesoftware@vip.qq.com, tianlesoftware@hotmail.com"; 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 old archive files
The following script clears 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/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

5. analysis table and index (for better performance)
The following shows how to transfer 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 input parameters to execute the script, enter:
$ Analyze_table.sh manager oradb1
The first part of the script generates an analyze. SQL file, which contains the statements used in the analysis table. The second part of the script analyzes 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 10 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

The following 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;

6. Use the checklist Space
The following script checks the use of tablespaces. If only 10% of the tablespace is 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/bin:/usr/CCS/bin:/usr/sbin: /usr/openwin/bin:/opt/bin :.; export path
Dbalist = "tianlesoftware@vip.qq.com, tianlesoftware@hotmail.com"; 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), '000000') "used (MB )",
To_char (F. free_space, '000000') "free (MB )",
To_char (T. total_space, '000000') "Total (MB )",
To_char (round (f. free_space/T. total_space) * 100), '000000') | '%' 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

Example of warning email output:
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. Search for invalid database objects
The following code finds invalid database objects:
######################################## #############################
# 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/bin:/usr/CCS/bin:/usr/sbin: /usr/openwin/bin:/opt/bin :.; export path
Dbalist = "tianlesoftware@vip.qq.com, tianlesoftware@hotmail.com"; export dbalist

Sqlplus-s '/As sysdba' <EOF
Set feed off
Set heading off
Column owner format A10
Column object_name 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 pprd10" $ 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. Monitor users and transactions (deadlocks, etc)
The following script sends a warning email 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/bin:/usr/CCS/bin:/usr/sbin: /usr/openwin/bin:/opt/bin :.; export path
Dbalist = "tianlesoftware@vip.qq.com, tianlesoftware@hotmail.com"; export dbalist

Sqlplus-s '/As sysdba' <EOF
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
EOF
If ['cat deadlock. Alert | WC-l'-GT 0]
Then
Mailx-s "deadlock alert for pprd10" $ dbalist <deadlock. Alert
Fi

Iv. 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
The above script can greatly reduce your work. You can use these to do more important work, such as performance adjustment.