Oracle DBA daily renewal (1)

Preface
This article describes DBA's daily responsibilities in monitoring Oracle databases and how to use shell scripts to perform repeated monitoring. This article first reviews some common DBA Unix Commands and explains how to regularly execute DBA scripts through UNIX cron. The article also introduces eight important scripts to monitor the Oracle database:
　　
Check instance availability
　　
Check listener availability
　　
Check the error message in the Alert Log File
　　
Old log files are cleared before the location where the log files are stored is full
　　
Analyze table and index for better performance
　　
Check the space usage
　　
Find invalid objects
　　
Monitor users and transactions
　　
Basic UNIX knowledge required by DBA
　　
Basic Unix Commands
　　
The following are some common 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:
　　
Display available instances on the server:
　　
$ PS-Ef | grep SMON
Oracle 21832 1 0 Feb 24? 1 array: 05 ora_smon_oradb1

Www.bitscn.com

Oracle 8array8 1 0 Feb 15? 0: 00 ora_smon_oradb2
Dliu 251 arrayarray 1array038 0 10:48:57 pts/6 grep SMON
Oracle 277array8 1 0 05:43:54? 0: 00 ora_smon_oradb3
Oracle 28781 1 0 Mar 03? 0: 01 ora_smon_oradb4,
　　
Display available listeners on the server:
　　
$ PS-Ef | grep listener | grep-V grep
(Note: The grep command should add the-I parameter, namely, grep-I listener. this parameter is used to ignore case sensitivity, because sometimes the listener is capitalized and the result will not be displayed)
Oracle 2387 array 1 0 Feb 24? 33: 36/8 .1.7/bin/tnslsnr listener_db1-inherit
Oracle 27array3array 1 0 05:44:02? 0: 00/8.1.7/bin/tnslsnr listener_db2-inherit
Oracle 23536 1 0 Feb 12? 4: 1 array/8.1.7/bin/tnslsnr listener_db3-inherit
Oracle 288array1 1 0 Mar 03? 0: 01/8.1.7/bin/tnslsnr listener_db4-inherit
　　
View the file system usage of the Oracle archive directory
　　
$ DF-k | grep oraarch
/Dev/VX/DSK/proddg/oraarch 71123array68 4754872 65850768 7%/u0array/oraarch
　　
Count the number of rows in the alter. log file:
　　
$ Cat alert. log | WC-l
2array84 China Network Management Alliance
　　
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], [258604array6], [25857716], []
　　
　　Crontab Basics
A crontab file contains six fields:
　　
Minute-to-5array
　　
Hour 0-23
　　
Day 1-31 of the month
　　
Month 1-12
　　
Day of the week 0-6, with 0 = Sunday
　　
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.
　　
　　Common shell scripts for monitoring databases
The following eight shell scripts cover the array0 % of DBA daily monitoring. You may need to modify the Unix environment variables.

China Network Management Alliance

　　
　　Check Availability of Oracle instances
The oratab file lists all databases on the 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 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 China Network Management Forum
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 22: 5 array ckinstance. ksh *
　　
The following 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 Availability of Oracle listeners
The following script checks the Oracle listener. If the listener stops, the script restarts the listener:
　　
######################################## ###############################
# Cklsnr. Sh ##
######################################## ###############################
#! /Bin/KSh www.bitscn.com
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
　　
　　Checking alert logs (ORA-XXXXX)
Some environment variables used by each script can be placed in a profile:
　　
######################################## ###############################
# Oracle. Profile ##
######################################## ###############################
Editor = VI; export editor oracle_base =/u01/APP/Oracle; Export
Oracle_base ORACLE_HOME = $ oracle_base/product/8.1.7; export www_bitscn_com
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 :/
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 all environment variables. If any Oracle error is found, the script also sends a warning email to the DBA.
　　
######################################## ############################
# 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

Www.bitscn.com

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
　　
　　Clear old archive files
The following script clears the old archive file when the log file reaches array0 % capacity:
　　
$ DF-k | grep Arch
Filesystem Kbytes used avail capacity mounted on
/Dev/VX/DSK/proddg/archive 71123array68 30210248 405array4232 43%/u08/archive <