脚本执行前提：保证执行脚本的用户设置了正确的 ORACLE_SID 环境变量。
#定义检查数据库状态函数
#!/bin/sh
function chk_db_status() {
local vl_db_status
vl_db_status=`ps -ef|grep ora_pmon_$ORACLE_SID|grep -v grep|wc -l`
echo $vl_db_status
}
#定义检查数据库角色的函数
function chk_db_role() {
local vl_db_role
vl_db_role=`sqlplus -S / as sysdba <<!
set head off
set feedback off
set trimout on
set pages 0
select database_role from v\\$database;
exit;
!
`
vl_db_role=`echo $vl_db_role|tr -d ' '`
echo $vl_db_role
}
#定义重启MRP进程函数
function restart_mrp_proc() {
sqlplus -S / as sysdba <<!
set head off
set feedback off
set trimout on
set pages 0
alter database recover managed standby database using current logfile disconnect from session;
exit;
!
}
#定义检查mrp进程数函数
function chk_mrp_nums() {
local vl_mrp_srv
vl_mrp_srv=`sqlplus -S / as sysdba <<!
set head off
set feedback off
set trimout on
set pages 0
select count(*) from gv\\$managed_standby where process like 'MRP%';
exit;
!
`
vl_mrp_srv=`echo $vl_mrp_srv|tr -d ' '`
echo $vl_mrp_srv
}
### Get Current User's profile
function get_current_user_profile() {
source ~/.bash_profile
}
### Get Current DateTime
function get_current_datetime() {
local vl_current_datetime
vl_current_datetime=`date '+%Y-%m-%d %H:%M:%S'`
echo $vl_current_datetime
}
#### main ####
## Get Current User's profile
get_current_user_profile
### Check Database Status
v_current_datetime=$(get_current_datetime)
v_db_status=$(chk_db_status)
if [ $v_db_status = 0 ]
then
echo ${v_current_datetime}" Please Check Database Status ! "
exit 1
fi
注释：如果状态检查失败退出脚本并且输出1
### Check Database Role
v_current_datetime=$(get_current_datetime)
v_db_role=$(chk_db_role)
if [ $v_db_role != "PHYSICALSTANDBY" ]
then
echo ${v_current_datetime}" Please Running this script in the Standby Database ! "
exit 2
fi
注释：如果状态检查不是备库退出脚本并输出2
### Check MRP Process Numbers, if MRP Process Numbers is 0 then Restart MRP Process
v_current_datetime=$(get_current_datetime)
v_mrp_result=$(chk_mrp_nums)
if [ $v_mrp_result = 0 ]
then
echo ${v_current_datetime}" Numbers of Current Running MRP process : "$v_mrp_result
echo ${v_current_datetime}" Restarting MRP process, Waiting ..."
echo ""
restart_mrp_proc
else
echo ${v_current_datetime}" MRP process is Running ! "
echo ${v_current_datetime}" Numbers of Current Running MRP process : "$v_mrp_result
exit 0
fi
注释：重启mrp进程
###检查mrp是否启动成功
v_current_datetime=$(get_current_datetime)
v_mrp_result=$(chk_mrp_nums)
if [ $v_mrp_result = 0 ]
then
echo ${v_current_datetime}" MRP process Restarting Failed ! "
exit 3
else
echo ${v_current_datetime}" MRP process Restarting Succesful ! "
echo ${v_current_datetime}" Numbers of Current Running MRP process : "$v_mrp_result
exit 0
fi
以下为定时任务和实验验证
二、定时任务设置（ crontab -l ）：
实现每 5 分钟执行一次监控并重启 MRP 服务脚本。
RAC 第一节点：
0,10,20,30,40,50 * * * * sh /oracle/app/oracle/script/mrp_check.sh > /oracle/app/oracle/script/mrp_check.log 2>&1
RAC 第二节点：
5,15,25,35,45,55 * * * * sh /oracle/app/oracle/script/mrp_check.sh > /oracle/app/oracle/script/mrp_check.log 2>&1
三、测试用例：
1、主库端测试：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
oracle@ora19c:/oracle/app/oracle/scripts>sh chk_mrp2.sh
2023-02-23 10:39:18 Please Check Database Status !
oracle@ora19c:/oracle/app/oracle/scripts>
SQL> startup
ORACLE instance started.
Total System Global Area 1593832616 bytes
Fixed Size 9135272 bytes
Variable Size 1325400064 bytes
Database Buffers 251658240 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> select database_role from v$database;
DATABASE_ROLE
------------------------------------------------
PRIMARY
SQL>
oracle@ora19c:/oracle/app/oracle/scripts>sh chk_mrp2.sh
2023-02-23 10:39:51 Please Running this script in the Standby Database !
oracle@ora19c:/oracle/app/oracle/scripts>
2、备库端测试：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
oracle@ora19cdg:/oracle/app/oracle/scripts>sh chk_mrp2.sh
2023-02-23 10:40:50 Please Check Database Status !
oracle@ora19cdg:/oracle/app/oracle/scripts>
SQL> startup
ORACLE instance started.
Total System Global Area 1593832616 bytes
Fixed Size 9135272 bytes
Variable Size 1258291200 bytes
Database Buffers 318767104 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> select database_role from v$database;
DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY
SQL>
oracle@ora19cdg:/oracle/app/oracle/scripts>sh chk_mrp2.sh
2023-02-23 10:41:15 Numbers of Current Running MRP process : 0
2023-02-23 10:41:15 Restarting MRP process, Waiting ...
2023-02-23 10:41:15 MRP process Restarting Succesful !
2023-02-23 10:41:15 Numbers of Current Running MRP process : 1
oracle@ora19cdg:/oracle/app/oracle/scripts>sh chk_mrp2.sh
2023-02-23 10:41:24 MRP process is Running !
2023-02-23 10:41:24 Numbers of Current Running MRP process : 1
oracle@ora19cdg:/oracle/app/oracle/scripts>
3、测试定时任务执行：
## 一分钟执行一次监控并重启 MRP 服务脚本
## */1 * * * * sh /oracle/app/oracle/scripts/chk_mrp2.sh >> /oracle/app/oracle/scripts/chk_mrp2.log 2>&1
oracle@ora19cdg:/oracle/app/oracle/scripts>id
uid=1614(oracle) gid=1613(oinstall) groups=1613(oinstall),1614(dba),1615(oper)
oracle@ora19cdg:/oracle/app/oracle/scripts>crontab -e
no crontab for oracle - using an empty one
crontab: installing new crontab
oracle@ora19cdg:/oracle/app/oracle/scripts>crontab -l
*/1 * * * * sh /oracle/app/oracle/scripts/chk_mrp2.sh >> /oracle/app/oracle/scripts/chk_mrp2.log 2>&1
oracle@ora19cdg:/oracle/app/oracle/scripts>tail -f /oracle/app/oracle/scripts/chk_mrp2.log
2023-02-23 14:39:02 MRP process is Running !
2023-02-23 14:39:02 Numbers of Current Running MRP process : 1
2023-02-23 14:40:01 MRP process is Running !
2023-02-23 14:40:01 Numbers of Current Running MRP process : 1
2023-02-23 14:41:01 Please Check Database Status !
2023-02-23 14:42:02 Numbers of Current Running MRP process : 0
2023-02-23 14:42:02 Restarting MRP process, Waiting ...
2023-02-23 14:42:08 MRP process Restarting Succesful !
2023-02-23 14:42:08 Numbers of Current Running MRP process : 1
2023-02-23 14:43:02 MRP process is Running !
2023-02-23 14:43:02 Numbers of Current Running MRP process : 1
2023-02-23 14:44:01 MRP process is Running !
2023-02-23 14:44:01 Numbers of Current Running MRP process : 1
2023-02-23 14:45:02 MRP process is Running !
2023-02-23 14:45:02 Numbers of Current Running MRP process : 1
备注：定时任务执行期间测试了在备库端关闭数据库，启动数据库但没有启动 MRP 服务的两个场景。
注意：在执行 crontab 定时任务时建议将 .bash_profile 设置的 stty erase ^H 注释掉否则会有如下报错：
oracle@ora19cdg:/oracle/app/oracle/scripts>tail -f /oracle/app/oracle/scripts/chk_mrp2.log
stty: standard input: Inappropriate ioctl for device
2023-02-22_17:05:02 : MRP process is Running !
2023-02-22_17:05:02 : Numbers of Current Running MRP process : 1
stty: standard input: Inappropriate ioctl for device
2023-02-22_17:06:01 : MRP process is Running !
2023-02-22_17:06:01 : Numbers of Current Running MRP process : 1