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

数据库日志是否报错信息

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会话数

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

数据库是否存在死锁

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主备库同步状态检测

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备库是否实时同步

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