Common monitoring scripts for Oracle Database Management-greatly simplifying O & M

SHELL scripts have been studied in recent days. To facilitate Oracle O & M and simplify management, it is very necessary to learn some SHELL scripts. Through books and some online materials, I have compiled some excellent scripts, which are all feasible after self-testing. Let's share them with you.

# Monitoring the Oracle listening status (chk_lsnr_stat.sh)
#===================================================== ========================================================== ========
# Monitor the Oracle listener status. When an exception is found, the listener is started and an email is sent to the Administrator. If the listener fails to be started, an email is sent.
# Notify the administrator.
#===================================================== ========================================================== ========
 
#! /Bin/bash
 
./Home/oracle/. bash_profile
 
Tempfile = $ ORACLE_BASE/admin/$ ORACLE_SID/tempfile. lis
 
Su-oracle-c "lsnrctl status">/dev/null
 
If [$? ! = '0']; then
Echo ""> $ tempfile
 
Echo "============================================== ==================== "> $ tempfile
 
Echo "'date + % D-% t'"> $ tempfile
 
Su-oracle-c "lsnrctl start" >>$ tempfile
 
If [$? = '0']; then

Cat $ tempfile | mail dba@163.com-s "The Listener Shutdown, and Restarted Success"

Else
 
Cat $ tempfile | mail dba@163.com-s "The Listener Shutdown, and Restarted Failed"
 
Fi
 
Fi
 
 
 
Bytes ----------------------------------------------------------------------------------------
 
 
# Monitoring the Oracle instance status (chk_inst_stat.sh)
#===================================================== ========================================================== ======
# Monitor whether the Oracle instance is enabled, whether the database is available when the instance is opened, and send an alarm email when the instance is closed or the database is unavailable
# Notify the Administrator (except for the special instance ASM)
#===================================================== ========================================================== ======
 
#! /Bin/bash
 
ORATAB =/etc/oratab
 
Tempfile =/home/oracle/tempfile. lis
 
Db = 'cat $ ORATAB | egrep-I ": Y |: N" | cut-d ":"-f 1 | grep-v "^ + "'
 
Pslist = "'ps-ef | grep pmon | grep-v grep '"
 
Mark = n
 
Dbstat = 'Su-oracle <EOF
 
Sqlplus-s/nolog
 
Conn/as sysdba
 
Set feedback off heading off pagesize 0
 
Select status from v \\$ instance;
 
Exit
 
EOF'
 
For db_name in $ db; do

Echo "$ pslist" | grep "ora_pmon _ $ db_name">/dev/null 2> & 1

If [$? = "0"]; then

If [$ dbstat! = "OPEN"]; then
 
Mark = y

Break

Fi

Else

Mark = y

Break

Fi

Done
 
If [$ mark! = 'N']; then
 
Echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>> '>>$ tempfile
Echo "SERVER: $ HOSTNAME" >>$ tempfile
Echo "'date + % D-% t'"> $ tempfile
Echo 'warn !!! Oracle Database Unavailable '> $ tempfile
Echo "Maybe The Following Reasons: The Instance or Database is not OPEN" >>$ tempfile

Echo | mail-s "Oracle Database Abnormal" dba@163.com <$ tempfile
Rm-f $ tempfile

Fi
 
Bytes ------------------------------------------------------------------------------------------
 
 
# Monitoring the archive directory space (chk_arc_space.sh)
#===================================================== ========================================================== ======
# Control the log directory space to 200 MB. When the capacity is greater than MB, pack and copy the oldest log to another directory and delete it.
# Until the log directory space is less than MB.
#===================================================== ========================================================== ======
 
#! /Bin/bash
 
ARC_DIR =/disk01/tbs03
 
BAK_DIR =/opt/arcbackup
 
Limit = 200
 
Capacity ()
 
{
 
Du-sh $ ARC_DIR | awk-F "" '{print $1}' | tr-d M
 
}
 
 
Oldlog ()
 
{
 
Ls-l | sort-k6 | sed '1d '| head-1 | awk-F ""' {print $9 }'

}
 
Cd $ ARC_DIR
 
If ['capacity '-gt $ limit]; then
 
Echo "" | mail-s "The Archivelog Directory is Over Than $ limit "\
Dba@163.com
 
Fi
 
While ['capacity '-gt $ limit]
 
Do

File = 'oldlog'

Tar-czf $ file. 'date when policyymm1_d1_hsf-m'.tar.gz $ file

Cp $ file. 'date when policyymm1_d1_hsf-m'.tar.gz $ BAK_DIR/

Rm-rf $ ARC_DIR/$ file. 'date when policyymm1_d1_h1_m'.tar.gz

Done
 
 
 
# Monitoring warning file error message (chk_alert_info.sh)
#===================================================== ========================================================== =
# Check whether the alert _ $ ORACLE_SID.log file contains the error message starting with ORA-at a fixed interval. If yes
# Notify the Administrator by email.
#===================================================== ========================================================== =
 
#! /Bin/bash
 
./Home/oracle/. bash_profile
 
Cd $ ORACLE_BASE/admin/$ ORACLE_SID/bdump/
 
Mv alert _ $ ORACLE_SID.log alert_temp.log
 
Touch alert _ $ ORACLE_SID.log
 
Cat alert_temp.log> alert. $ ORACLE_SID.hist
 
Grep ORA-alert_temp.log> alert. err
 
If ['cat alert. err | wc-l'-gt 0]; then
 
Mail-s "oracle alert error" dba@163.com <alert. err

Fi
 
Rm-rf alert. err
 
Rm-rf alert_temp.log
 
 
 
# Monitoring disk space utilization (chk_disk_space)
#===================================================== ==================================
# Determine the disk or partition space usage starting with/dev. If the disk space usage exceeds 90%, an email notification is sent.
# Administrator
#===================================================== ==================================
 
#! /Bin/bash
 
Limit = 90%
 
Tempfile = chk_disk_space.tmp
 
Mark = n
 
Diskusage ()
 
{
 
Df-h | grep-v Filesystem | sed '/\/dev \/mapper/N; s/\ n // '| grep "^/dev" | awk-F ""' {print $5 }'

}
 
For percent in 'diskusage'
 
Do
 
If [[$ percent> $ limit]; then

Mark = y

Break

Fi

Done
 
If [$ mark! = 'N']; then
 
Df-h> $ tempfile

Mail-s "Disk Usage Over than $ limit on 'hostname'" <$ tempfile
 
Rm-rf $ tempfile
Fi
 
 
 
 
# Monitor idle tablespace (chk_tbs_free.sh)
#===================================================== ========================================================== ===
# Monitor idle tablespace. When the idle tablespace is less than 20%, send an email to the Administrator.
#===================================================== ========================================================== ===
 
#! /Bin/bash
 
Su-oracle>/dev/null <EOF
Sqlplus-s/nolog
Conn/as sysdba
Set feedback off
Set heading off
Set verify off
Set pagesize 0
Set linesize 200
 
Spool tbsfree. alert
 
Select t. tablespace_name, f. free_space/t. total_space from
(Select tablespace_name, sum (bytes) total_space from
Dba_data_files group by tablespace_name) t,
(Select tablespace_name, sum (bytes) free_space from
Dba_free_space group by tablespace_name) f
Where t. tablespace_name = f. tablespace_name and f. free_space/t. total_space <0.20
/
 
Spool off
 
Exit
 
EOF
 
If ['cat tbsfree. alert | wc-l'-gt 0]; then
 
Cat tbsfree. alert | mail-s "No Free Space in Oracle db" dba@163.com

Rm-rf tbsfree. alert

Fi
 
 
# Full-database cold backup (full_cold_backup.sh)
#===================================================== ========================================================== =
# The backup script is automatically generated when the database is opened. Then shut down the database, and redo the log file to the control file, data file,
# Initialize the parameter file and password file for cold backup, and then open the database.
#===================================================== ========================================================== =
 
#! /Bin/bash
 
./Home/oracle/. bash_profile
 
Backup_dir =/disk01/backup/coldbak
 
Log_file =/disk01/backup/coldbak/cold_backup _ $ ORACLE_SID.log
 
Echo 'in in Cold Backup >>>>>>>>>>>>>>>>>>> '>>>$ log_file
 
Date> $ log_file
 
Su-oracle>/dev/null <EOF
 
Sqlplus-s/nolog
 
Conn/as sysdba
 
Set feedback off heading off pagesize 0 row 1000
 
Spool file_copy _ $ ORACLE_SID.sh
 
Select 'cp' | name | '$ backup_dir/' from v $ controlfile;
 
Select 'cp' | file_name | '$ backup_dir/' from dba_data_files;
 
Select 'cp' | member | '$ backup_dir/' from v $ logfile;
 
Spool off
 
Shutdown immediate
 
! Bash file_copy _ $ ORACLE_SID.sh
 
Startup
 
Exit
 
EOF
 
If [-e $ ORACLE_HOME/dbs/init $ ORACLE_SID.ora]; then
 
Cp $ ORACLE_HOME/dbs/init $ ORACLE_SID.ora $ backup_dir/

Fi
 
If [-e $ ORACLE_HOME/dbs/spfile $ ORACLE_SID.ora]; then
 
Cp $ ORACLE_HOME/dbs/spfile $ ORACLE_SID.ora $ backup_dir/

Fi
 
If [-e $ ORACLE_HOME/dbs/orapw $ ORACLE_SID]; then
 
Cp $ ORACLE_HOME/dbs/orapw $ ORACLE_SID $ backup_dir/

Fi
 
 
Echo 'cold Backup Finished >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> '> $ log_file
 
Date> $ log_file
 
 
# RMAN backup SHELL script (rman_backup.sh)
#===================================================== ==========================================
# Implement Incremental backup at the specified level. The backup level parameter is input by the user. If this parameter is not specified, backup at the backup level 0 is performed.
#===================================================== ==========================================
 
#! /Bin/bash
 
./Home/oracle/. bash_profile
 
If [$1]; then
 
Backup_level = $1

Else
 
Backup_level = 0

Fi
 
Backup_user = sys
 
Backup_user_pw = oracle
 
# Catalog_user = rman
 
# Catalog_user_pw = rman
 
Log_file =/home/oracle/rman_backup.log
 
Echo 'ining ining rman backup >>>>>>>>>>>>>>>>>>>>>>>>' >>> $ log_file
 
Date> $ log_file
 
Su-oracle> $ log_file <EOF
 
Rman target $ backup_user/$ backup_user_pw
# Catalog $ catalog_user/$ catalog_user_pw

Backup incremental level = $ backup_level database;
 
Quit;
 
EOF
 
Echo 'rman backup finished >>>>>>>>>>>>>>>>>>>>>>>' >>> $ log_file
 
Date> $ log_file
 
 
 
# SHELL script for logical backup (schema_exp.sh)
#===================================================== ========================================
# EXP back up the database schema object. You can input the username to be backed up as a parameter to the SHELL script.
#===================================================== ========================================
 
#! /Bin/bash
 
BAK_DIR =/disk01/backup/logical_bak/
 
Log_file =/disk01/backup/logical_bak/user_full_bak.log
 
Exp_par = "userid = system/oracle buffer = 10485760 owner = $1"
 
If [$2]; then
 
Exp_par = "$ exp_par file = $2"

Else
 
Exp_par = "$ exp_par file =" $ BAK_DIR/$1 _ 'date + % Y % m % d % H % M'. dmp ""

Fi
 
Echo "Begining User $1 Export -------------------"> $ log_file
 
Echo "Export with following parameters: $ exp_par" >>$ log_file
 
Date> $ log_file
 
Su-oracle-c "exp $ exp_par" >>$ log_file 2> & 1
 
Echo "Backup Finished ---------------------"> $ log_file
 
Date> $ log_file