dataguard switchover的自動化指令碼實現

data guard的主要功能就是作為備庫來同步主庫的資料變化，一般使用中物理standby使用的比較多。data guard顯示威力的一個場景就是swithover了，即主備切換。這種切換方式執行時間很短，能夠在一些災難場景中極大的提高系統的可用性和穩定性。
自己在本地的環境中搭建了一套data guard的環境，開始比較生疏，切換中碰到了不少的問題，最後搭建完成，把切換中的一些細節資訊都總結起來，整理成了一個初步的指令碼。能夠很方便的實現swith over

這個指令碼適用於物理standby，在本地環境中反覆測試，切換了十多次，還算是比較穩定的。
在指令碼中也對需要切換的例項進行了基本的校驗，保證不會出現低階錯誤。比如主庫切為主庫，備庫切為備庫等等。
當然對於一些更加細節的資訊沒有做過濾，比如對於歸檔gap的判定等。

PRI_DB=`sqlplus -s sys/oracle@$1 as sysdba < set feedback off
set pages 0
select database_role from v\\$database;
EOF`
echo $PRI_DB

if [[ $PRI_DB = 'PHYSICAL STANDBY' ]]
then  echo 'PRIMARY DB INSTANCE IS NOT '$1 ',PLEASE CHECK AGAIN'
exit
fi

PRI_DB=$1
#echo $PRI_DB

STD_DB=`sqlplus -s sys/oracle@$2 as sysdba < set feedback off
set pages 0
select database_role from v\\$database;
EOF`

if [[ $STD_DB = 'PRIMARY' ]]
then  echo 'STANDBY DB INSTANCE IS NOT '$2 ',PLEASE CHECK AGAIN'
exit
fi

STD_DB=$2

#export ORACLE_SID=$STD_DB
sqlplus -s sys/oracle@$PRI_DB as sysdba < break on db_name
set pages 50
set linesize 100
prompt
prompt Primary Instance
prompt ~~~~~~~~~~~~~~~~
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_DB
sqlplus -s sys/oracle@$STD_DB as sysdba < break on db_name
set pages 50
set linesize 100
prompt
prompt Standby Instance
prompt ~~~~~~~~~~~~~~~~
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

sqlplus  sys/oracle@$STD_DB as sysdba < prompt recover managed standby database cancel;
recover managed standby database cancel;
EOF

#export ORACLE_SID=$PRI_DB
sqlplus  sys/oracle@$PRI_DB as sysdba < prompt Alter database commit to switchover to physical standby with session shutdown;
Alter database commit to switchover to physical standby with session shutdown;
EOF

sqlplus  sys/oracle@$PRI_DB as sysdba < prompt shutdown immediate;
shutdown immediate;
EOF

sqlplus  sys/oracle@$PRI_DB as sysdba < prompt startup mount
startup mount
prompt recover managed standby database disconnect from session;
recover managed standby database disconnect from session;
EOF

#export ORACLE_SID=$STD_DB
sqlplus  sys/oracle@$STD_DB as sysdba < Select 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          PRIMARY

Standby Instance
~~~~~~~~~~~~~~~~
      DBID DB_NAME     INST_NUM INST_NAME        DATABASE_ROLE
---------- --------- ---------- ---------------- ----------------
1028247664 TEST11G            1 DG11G            PHYSICAL STANDBY

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
idle> recover managed standby database cancel
idle> Media recovery complete.
idle> 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@TEST11G> Alter database commit to switchover to physical standby with session shutdown
sys@TEST11G> 
Database altered.
sys@TEST11G> 
idle> shutdown immediate
idle> ORA-01507: database not mounted
ORACLE instance shut down.
Connected to an idle instance.
idle> startup mount
idle> ORACLE instance started.
Total System Global Area  435224576 bytes
Fixed Size                  1337044 bytes
Variable Size             272632108 bytes
Database Buffers          155189248 bytes
Redo Buffers                6066176 bytes
Database mounted.
idle> recover managed standby database disconnect from session
idle> Media recovery complete.
NAME      SWITCHOVER_STATUS
--------- --------------------
TEST11G   SWITCHOVER LATENT
idle> alter database recover managed standby database finish force
idle> 
Database altered.
NAME      SWITCHOVER_STATUS
--------- --------------------
TEST11G   TO PRIMARY

idle> alter database commit to switchover to primary
idle> 
Database altered.
NAME      DATABASE_ROLE
--------- ----------------
TEST11G   PRIMARY
idle> 
INSTANCE_NAME    STATUS
---------------- ------------
DG11G            MOUNTED
idle> alter database open
idle> 
Database altered.