11g Dataguard中的snapshot standby特性

11g中的ADG特性本身已經非常有特色，促使很多對於10g中不太靈便的備庫升級到11g，對於DBA是一大福利，那麼還有一個福利就是snapshot standby了。
在平時的資料更新操作中，DBA可以做好sql稽核，如果對於複雜的，繁多的變更，如果有些變更有一定的依賴，資料變化情況比較大，評估有難度，很多問題單純在測試環境還發現不了，到了生產就是事兒。如果你飽受這種困擾，snapshot standby就是一個不錯的選擇。你可以讓原本只讀的備庫可讀可寫，然後寫寫畫畫一番之後迴歸到上一次的一個臨界點，繼續應用歸檔日誌。
這種操作基本上沒有依賴，非常純粹，易操作。
我們來舉個例子看看。
下面的資料庫是一主一備的架構。
DGMGRL> show configuration;
Configuration - testmob_dg
  Protection Mode: MaxPerformance
  Databases:
    testmob   - Primary database
    s2testmob - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
我們來把備庫置為snapshot standby，命令非常簡單，在dg broker裡面可以使用如下的方式，在sqlplus中也是一個命令就可以搞定，前提是取消日誌應用。
dg broker修改為snapshot standby的日誌如下：
DGMGRL> convert database s2testmob to snapshot standby;
Converting database "s2testmob" to a Snapshot Standby database, please wait...
Database "s2testmob" converted successfully
切換完成之後再次檢視，就會發現s2testmob變為了snapshot standby
[oracle@teststd ~]$ dgmgrl /
DGMGRL> show configuration;
Configuration - testmob_dg
  Protection Mode: MaxPerformance
  Databases:
    testmob   - Primary database
    s2testmob - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
在備庫中會有下面的一些相關日誌資訊。
Fri Mar 25 22:27:12 2016
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Fri Mar 25 22:27:12 2016
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /U01/app/oracle/diag/rdbms/s2testmob/testmob/trace/testmob_pr00_12884.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 50484782
Fri Mar 25 22:27:12 2016
MRP0: Background Media Recovery process shutdown (testmob)
Managed Standby Recovery Canceled (testmob)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
alter database convert to snapshot standby
Starting background process RVWR
Fri Mar 25 22:27:13 2016
RVWR started with pid=32, OS id=28987
Allocated 15937344 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/25/2016 22:27:13
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 50484782
Resetting resetlogs activation ID 2146999722 (0x7ff89daa)
Online log /U01/app/oracle/oradata/testmob/redo01.log: Thread 1 Group 1 was previously cleared
Online log /U01/app/oracle/oradata/testmob/redo02.log: Thread 1 Group 2 was previously cleared
Online log /U01/app/oracle/oradata/testmob/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 50484780
Fri Mar 25 22:27:15 2016
Setting recovery target incarnation to 3
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete
這個時候檢視閃回區中，會發現有閃回資料庫的日誌資訊。
[oracle@teststd flashback]$ ll
total 102416
-rw-r----- 1 oracle oinstall 52436992 Mar 25 22:27 o1_mf_chblp1l1_.flb
-rw-r----- 1 oracle oinstall 52436992 Mar 25 22:27 o1_mf_chblp3nb_.flb
[oracle@teststd flashback]$ pwd
/U01/app/oracle/fast_recovery_area/S2testmob/flashback
這個時候檢視備庫的資訊，發現flashback_on的屬性已經悄然發生改變。
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------
RESTORE POINT ONLY
資料庫的角色和狀態也發生了相應的變化。
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE                    OPEN_MODE
-------------------------------- ----------------------------------------
SNAPSHOT STANDBY                 READ WRITE
這個時候我們在備庫裡面就可讀可寫，我們建立一個使用者，新建一個表。
SQL> create user  jeanron identified by jeanron;
User created.
SQL> grant dba to jeanron;
Grant succeeded.
SQL> create table jeanron.test as select *from cat;
Table created.

如果在主庫端切換日誌，在備庫也能夠看到RFS依舊可以正常接收歸檔，但是MRP肯定是還執行不了，也就意味著只會接收歸檔，但是日誌還無法應用。
主庫切換日誌後，檢視備庫的日誌資訊如下：
Fri Mar 25 22:36:04 2016
Archived Log entry 43 added for thread 1 sequence 1734 rlc 846934189 ID 0x7ff89daa dest 2:
RFS[3]: Selected log 4 for thread 1 sequence 1735 dbid 2146967210 branch 846934189
寫也寫了，這種測試評估還是很有說服力的。完成之後我們就可以切換為備庫狀態即可。
DGMGRL> convert database s2testmob to physical standby;
Converting database "s2testmob" to a Physical Standby database, please wait...
Operation requires shutdown of instance "testmob" on database "s2testmob"
Shutting down instance "testmob"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
        shut down instance "testmob" of database "s2testmob"
        start up and mount instance "testmob" of database "s2testmob"
在備庫端還是需要重啟一下備庫
SQL> shutdown immediate
SQL> startup mount 
SQL> select database_role from v$database;
DATABASE_ROLE
--------------------------------
SNAPSHOT STANDBY
可以直接使用一條命令即可完成切換，切換時間極短。
SQL> alter database convert to physical standby;
Database altered.
檢視備庫的日誌，發現閃回恢復回悄然完成，然後會自動刪除閃回日誌。
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (testmob)
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
Deleted Oracle managed file /U01/app/oracle/fast_recovery_area/S2testmob/flashback/o1_mf_chblp1l1_.flb
Deleted Oracle managed file /U01/app/oracle/fast_recovery_area/S2testmob/flashback/o1_mf_chblp3nb_.flb
Guaranteed restore point  dropped
Clearing standby activation ID 2207276870 (0x83905f46)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
...
Fri Mar 25 22:45:03 2016
ARCH shutting downARCH shutting down
ARC2: Archival stoppedARC1: Archival stopped
ARC3: Archival stopped
ARC0: Archival stopped
Completed: alter database convert to physical standby

這個時候備庫是在nomount狀態
SQL> select database_role,open_mode from v$database;
select database_role,open_mode from v$database
                                    *
ERROR at line 1:
ORA-01507: database not mounted
重新mount就報錯了，需要重啟一下。
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted

SQL> shutdown immediate
SQL> startup mount
以為使用sql命令手工修改，需要在dg broker裡面同步一下。
DGMGRL> show configuration;
Configuration - testmob_dg
  Protection Mode: MaxPerformance
  Databases:
    testmob   - Primary database
    s2testmob - Snapshot standby database
      Error: ORA-16810: multiple errors or warnings detected for the database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR

DGMGRL> convert database s2testmob to physical standby;
Converting database "s2testmob" to a Physical Standby database, please wait...
Operation requires shutdown of instance "testmob" on database "s2testmob"
Shutting down instance "testmob"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish the convert command:
        shut down instance "testmob" of database "s2testmob"
        start up and mount instance "testmob" of database "s2testmob"
其實這個時候備庫還沒有正式開啟日誌應用，重新啟用一下即可。
DGMGRL> show configuration;
Configuration - testmob_dg
  Protection Mode: MaxPerformance
  Databases:
    testmob   - Primary database
    s2testmob - Physical standby database
      Error: ORA-16766: Redo Apply is stopped
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL> edit database s2testmob set state='ONLINE';
Succeeded.

DGMGRL> show configuration;
Configuration - testmob_dg
  Protection Mode: MaxPerformance
  Databases:
    testmob   - Primary database
    s2testmob - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
整個過程完成之後，就跟什麼都沒有發生一樣，一切又恢復了平靜。