dataguard switchover的自动化脚本实现 (r5笔记第48天)

data guard的主要功能就是作为备库来同步主库的数据变化，一般使用中物理standby使用的比较多。data guard显示威力的一个场景就是swithover了，即主备切换。这种切换方式执行时间很短，能够在一些灾难场景中极大的提高系统的可用性和稳定性。自己在本地的环境中搭建了一套data guard的环境，开始比较生疏，切换中碰到了不少的问题，最后搭建完成，把切换中的一些细节信息都总结起来，整理成了一个初步的脚本。能够很方便的实现swithover这个脚本适用于物理standby，在本地环境中反复测试，切换了十多次，还算是比较稳定的。在脚本中也对需要切换的实例进行了基本的校验，保证不会出现低级错误。比如主库切为主库，备库切为备库等等。当然对于一些更加细节的信息没有做过滤，比如对于归档gap的判定等。PRI_DB=`sqlplus -s sys/oracle@$1 as sysdba <<EOFset feedback offset pages 0select database_role from v\\$database;EOF`echo $PRI_DBif [[ $PRI_DB = 'PHYSICAL STANDBY' ]]then echo 'PRIMARY DB INSTANCE IS NOT '$1 ',PLEASE CHECK AGAIN'exitfiPRI_DB=$1#echo $PRI_DBSTD_DB=`sqlplus -s sys/oracle@$2 as sysdba <<EOFset feedback offset pages 0select database_role from v\\$database;EOF`if [[ $STD_DB = 'PRIMARY' ]]then echo 'STANDBY DB INSTANCE IS NOT '$2 ',PLEASE CHECK AGAIN'exitfiSTD_DB=$2#export ORACLE_SID=$STD_DBsqlplus -s sys/oracle@$PRI_DB as sysdba <<EOFbreak on db_nameset pages 50set linesize 100promptprompt Primary Instanceprompt ~~~~~~~~~~~~~~~~select d.dbid dbid , d.name db_name , i.instance_number inst_num , i.instance_name inst_name , d.database_role from v\$database d, v\$instance i;EOF#export ORACLE_SID=$STD_DBsqlplus -s sys/oracle@$STD_DB as sysdba <<EOFbreak on db_nameset pages 50set linesize 100promptprompt Standby Instanceprompt ~~~~~~~~~~~~~~~~select d.dbid dbid , d.name db_name , i.instance_number inst_num , i.instance_name inst_name , d.database_role from v\$database d, v\$instance i;EOFsqlplus sys/oracle@$STD_DB as sysdba <<EOFprompt recover managed standby database cancel;recover managed standby database cancel;EOF#export ORACLE_SID=$PRI_DBsqlplus sys/oracle@$PRI_DB as sysdba <<EOFprompt Alter database commit to switchover to physical standby with session shutdown;Alter database commit to switchover to physical standby with session shutdown;EOFsqlplus sys/oracle@$PRI_DB as sysdba <<EOFprompt shutdown immediate;shutdown immediate;EOFsqlplus sys/oracle@$PRI_DB as sysdba <<EOFprompt startup mountstartup mountprompt recover managed standby database disconnect from session;recover managed standby database disconnect from session;EOF#export ORACLE_SID=$STD_DBsqlplus sys/oracle@$STD_DB as sysdba <<EOFSelect name,switchover_status from v\$database;prompt alter database recover managed standby database finish force;alter database recover managed standby database finish force;select name,switchover_status from v\$database;prompt alter database commit to switchover to primary;alter database commit to switchover to primary;select name,database_role from v\$database;select instance_name,status from v\$instance;prompt alter database open;alter database open;EOF切换的日志如下，限于篇幅，适当做了整理。Primary Instance~~~~~~~~~~~~~~~~ DBID DB_NAME INST_NUM INST_NAME DATABASE_ROLE---------- --------- ---------- ---------------- ----------------1028247664 TEST11G 1 TEST11G PRIMARYStandby Instance~~~~~~~~~~~~~~~~ DBID DB_NAME INST_NUM INST_NAME DATABASE_ROLE---------- --------- ---------- ---------------- ----------------1028247664 TEST11G 1 DG11G PHYSICAL STANDBYConnected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsidle> recover managed standby database cancelidle> Media recovery complete.idle> Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionssys@TEST11G> Alter database commit to switchover to physical standby with session shutdownsys@TEST11G> Database altered.sys@TEST11G> idle> shutdown immediateidle> ORA-01507: database not mountedORACLE instance shut down.Connected to an idle instance.idle> startup mountidle> ORACLE instance started.Total System Global Area 435224576 bytesFixed Size 1337044 bytesVariable Size 272632108 bytesDatabase Buffers 155189248 bytesRedo Buffers 6066176 bytesDatabase mounted.idle> recover managed standby database disconnect from sessionidle> Media recovery complete.NAME SWITCHOVER_STATUS--------- --------------------TEST11G SWITCHOVER LATENTidle> alter database recover managed standby database finish forceidle> Database altered.NAME SWITCHOVER_STATUS--------- --------------------TEST11G TO PRIMARYidle> alter database commit to switchover to primaryidle> Database altered.NAME DATABASE_ROLE--------- ----------------TEST11G PRIMARYidle> INSTANCE_NAME STATUS---------------- ------------DG11G MOUNTEDidle> alter database openidle> Database altered.