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.

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.

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, the backup level is 0.
#===================================================== ==========================================

#! /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