SHELL中执行Oracle SQL语句查询性能视图

数据库日志是否报错信息

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

vi check_log.sh #!/bin/bash # Created : 2019.10.10 # Updated : # Author : # Description :alert log     loadsql="SELECT count(1) FROM v\$diag_alert_ext WHERE originating_timestamp > ( sysdate - interval '1' HOUR ) and ( message_text LIKE '%error%' OR message_text LIKE '%ORA-%' OR message_text LIKE '%terminating the instance%' OR message_text LIKE '%CRS-%');"   loadsql="$loadsql" result=`sqlplus -s /nolog <<EOF set echo off feedback off heading off underline off; conn / as sysdba; $loadsql exit; EOF`   echo $result

数据库session会话数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

vi check_session.sh #!/bin/bash # Created : 2019.10.10 # Updated : # Author : # Description :session   loadsql="select count(*) from v\$session where status ='ACTIVE';"   loadsql="$loadsql" result=`sqlplus -s /nolog <<EOF set echo off feedback off heading off underline off; conn / as sysdba; $loadsql exit; EOF`   echo $result

数据库是否存在死锁

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

vi check_deadlock.sh #!/bin/bash # Created : 2019.10.10 # Updated : # Author : # Description :dead lock loadsql="select count(*) from v\$session where status ='ACTIVE';"   loadsql="$loadsql" result=`sqlplus -s /nolog <<EOF set echo off feedback off heading off underline off; conn / as sysdba; $loadsql exit; EOF`   echo $result

Oracle DG主备库同步状态检测

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

vi check_sync.sh #!/bin/bash # Created : 2019.10.10 # Updated : # Author : # Description :master_standby_sync loadsql="select status from v\$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';"   loadsql="$loadsql" result=`sqlplus -s /nolog <<EOF set echo off feedback off heading off underline off; conn / as sysdba; $loadsql exit; EOF`   echo $result

Oracle DG备库是否实时同步

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

vi check_real_time.sh #!/bin/bash # Created : 2019.10.10 # Updated : # Author : # Description :real-time sync loadsql="select recovery_mode from v\$archive_dest_status where dest_id=2;"   loadsql="$loadsql" result=`sqlplus -s /nolog <<EOF set echo off feedback off heading off underline off; conn / as sysdba; $loadsql exit; EOF` echo $result

#SQL> select recovery_mode from v$archive_dest_status where dest_id=2;

#RECOVERY_MODE
#-----------------------
#MANAGED REAL TIME APPLY

 

若使用root用户部署，需要导出环境变量

export ORACLE_SID=orcl1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib