DATAGUARD 实现随机启动数据库和自动开启recovery同步进程

目前使用的数据库为11.2.0.2及11.2.0.3的版本，主库和DG库数据库版本一致，在此版本中经常会出现600的错误，导致DG恢复进程挂起或者被kill掉，除了升级之外，可以使用脚本的方式自动检查mrp0的进程，发现进程停止，自动拉起recovery进程。在实际的使用过程中，可能会存在硬件故障，导致操作系统重启，那么亦可用此脚本实现数据库随机启动，DG自动开启恢复进程！
OS:REDHAT 6.5 64_BIT
DB:11.2.0.4
第一部分：随机启动脚本及自动恢复进程
1:创建oracle启动脚本
[root@test ~]# cd /etc/rc.d/init.d/
[root@test init.d]# vi oracle
#!/bin/bash
#
#################FUNCTION#############
#
# AutoStart Oracle and listener Using dbstart
# AutoStop Oracle and listener Using dbstop
#
#####################################
#
# Edited by LDY 2009-01-04
#
#chkconfig: 345 99 10
#description: script for the Oracle Instance, Listener 


source /home/oracle/.bash_profile
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
LOG=/var/log/oracle.log


case "$1" in
start)
echo "Starting Oracle Databases ... "
echo "-------------------------------------------------" >> $LOG 2>&1
date +" %T %a %D : Starting Oracle Databasee as part of system up." >> $LOG 2>&1
su - oracle -c "$ORACLE_HOME/bin/dbstart" >> $LOG 2>&1
echo "Done."
date +" %T %a %D : Finished." >> $LOG 2>&1
echo "-------------------------------------------------" >> $LOG 2>&1
touch /var/lock/subsys/oracle
echo "Recovering Oracle Databases ... "
su - oracle -c 'sqlplus -s / as sysdba <    whenever sqlerror exit sql.sqlcode;
   set echo off
   set heading off
   host echo "Database open mode is:" 
   select open_mode from v\$database;
   host echo "Recovering Oracle Databases......"
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
   host echo "Database open mode is:"
   select open_mode from v\$database;


   exit;
EOF
'
;;
stop)
echo "Stopping Oracle Databases ... "
echo "-------------------------------------------------" >> $LOG 2>&1
date +" %T %a %D : Stopping Oracle Databases as part of system down." >> $LOG 2>&1
su - oracle -c "$ORACLE_HOME/bin/dbshut" >> $LOG 2>&1
echo "Done."
date +" %T %a %D : Finished." >> $LOG 2>&1
echo "-------------------------------------------------" >> $LOG 2>&1
rm -f /var/lock/subsys/oracle


;;
restart)
$0 stop
$0 start


;;
*)
echo "Usage: oracle {start|stop|restart}"
exit 1
esac
此脚本加入了：数据库随机启动周后，自动开启DG恢复进程

2：权限及log文件创建
[root@test init.d]# ls -l ora*
-rwxr-x---  1 root root 1798 Dec 30 14:51 oracle
[root@test ~]# chmod 755 /etc/rc.d/init.d/oracle
[root@test log]# pwd
/var/log
[root@test log]# ls -l oracle*
-rw-r--r--  1 root root     0 Dec 29 16:45 oracle
-rw-r--r--  1 root root 26048 Dec 30 14:52 oracle.log

3：创建服务链接

[root@test ~]# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc2.d/S99oracle
[root@test ~]# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc3.d/S99oracle
[root@test ~]# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc5.d/S99oracle 
[root@test ~]# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc0.d/K01oracle # stop
[root@test ~]# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc6.d/K01oracle # restart

4：修改dbstart/dbshut脚本

[oracle@test ~]$  vi $ORACLE_HOME/bin/dbstart
# Set this to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=/u01/app/oracle/product/11.2.0/db_1
[oracle@test ~]$  vi  $ORACLE_HOME/bin/dbshut
# Set this to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=/u01/app/oracle/product/11.2.0/db_1

5：测试启动关闭

[root@test ~]# sercice oracle start
[root@test ~]# sercice oracle stop
[root@test ~]# sercice oracle restart

6：添加系统服务

chkconfig --add oracle 
chkconfig --level 345 oracle11g on

7：reboot 测试

重启后，检查数据已正常启动，日志同步正常开启

第二部分  ORA-600导致的DG  recover进程挂起处理

1：创建脚本


[root@test ~] mkdir -p /home/oracle/auto_check
[root@test auto_check]# vi check_main_programe.sh 
#!/bin/bash
source ./config.cfg


./main_programe.sh


[root@test auto_check]# vi config.cfg 
#!/bin/bash


DB_NAME='test'
db_sid='test'


export ORACLE_SID=test
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK


[root@test auto_check]# vi main_programe.sh 
#!/bin/bash
. /home/oracle/.bash_profile
sh /home/oracle/auto_check/mrp0_status.sh
mpr0_status=$(cat /home/oracle/auto_check/mrp0_status.log|grep "MRP0")
if [ "$mpr0_status"x != "MRP0"x ];    
then
    sh /home/oracle/auto_check/recover_standby.sh
fi


[root@test auto_check]# vi mrp0_status.sh 
#!/bin/bash


cd /home/oracle


export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=test
$ORACLE_HOME/bin/sqlplus  -s / as sysdba >/home/oracle/auto_check/mrp0_status.log  2>&1 < select process from v\$managed_standby where process='MRP0';
exit;
ZZZZ!


[root@test auto_check]#vi recover_standby.sh 
#!/bin/bash
cd /home/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=test
$ORACLE_HOME/bin/sqlplus  -s / as sysdba >/home/oracle/auto_check/recover_standby.log  2>&1 < alter database recover managed standby database using current logfile disconnect from session;
exit;
ZZZZ!

2：修改脚本权限

[root@test auto_check]#pwd
/home/oracle/auto_check [root@test auto_check]#chown oracle:oinstall *
[root@test auto_check]#chmod 777 *
[root@test auto_check]# ls -l
-rwxrwxrwx 1 oracle oinstall  52 Dec 30 13:08 check_main_programe.sh
-rwxrwxrwx 1 oracle oinstall 117 Dec 30 13:02 config.cfg
-rwxrwxrwx 1 oracle oinstall 251 Dec 30 15:01 main_programe.sh
-rwxrwxrwx 1 oracle oinstall  25 Dec 30 15:38 mrp0_status.log
-rwxrwxrwx 1 oracle oinstall 280 Dec 30 15:02 mrp0_status.sh
-rwxrwxrwx 1 oracle oinstall  20 Dec 30 15:32 recover_standby.log
-rwxrwxrwx 1 oracle oinstall 316 Dec 30 15:06 recover_standby.sh

3：创建脚本调用（每分钟检查一次）

[root@test auto_check]crontab -e
*/1 * * *  *  su - oracle -c 'cd /home/oracle/auto_check && ./check_main_programe.sh'

4：重启crontab 服务

[root@test oracle]# /sbin/service crond restart
Stopping crond: [  OK  ]
Starting crond: [  OK  ]

5：crontab 日志检查

tail -f /var/log/cron

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14507784/viewspace-1385175/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14507784/viewspace-1385175/