Data Guard - Snapshot Standby Database配置

概述

有些情況下，為了實作系統的壓力測試或者real application testing(rat)或者其他讀寫操作測試，那麼可以臨時将實體standby資料庫轉換為snapshot standby資料庫然後進行測試，因為snapshot standby資料庫是獨立于主庫的，并且是可以進行讀寫操作(read-write)。測試過程中snapshot standby資料庫正常接收主庫的歸檔日志，保證主庫的資料安全，但是不會應用這些日志，當壓力測試結束後，可以非常簡單的再将snapshot standby轉換為實體standby資料庫，繼續同步主庫日志。

配置

1.實體standby配置閃回日志

sql> alter system set db_recovery_file_dest_size=500m;

system altered.

sql> alter system set db_recovery_file_dest='/u01/app/oracle/snapshot_standby';

2.實體standby停止應用日志

sql> alter database recover managed standby database cancel;

database altered.

3.實體standby轉換為snapshot standby，并且open snapshot standby

sql> alter database convert to snapshot standby;

sql> alter database open;   

ps:如果使用dg broker，可以用如下指令進行轉換

dgmgrl> convert database slave to snapshot standby;

檢查snapshot standby資料庫角色是snapshot standby，open模式是read write：

sql> select database_role,name,open_mode from v$database;

database_role    name      open_mode

---------------- --------- --------------------

snapshot standby fsdb      read write

4.對snapshot standby資料庫進行壓力測試或者real application testing(rat)或者其他讀寫操作。

5.測試結束後，再将snapshot standby轉換為physical standby，并且重新開始應用日志

sql> shutdown immediate;

database closed.

database dismounted.

oracle instance shut down.

sql> startup mount;

oracle instance started.

database mounted.

sql> alter database convert to physical standby;

ora-01507: database not mounted

sql>alter database recover managed standby database disconnect from session;

5.轉換為實體standby後，檢視備庫角色是physical standby，open模式是mounted

physical standby fsdb      mounted

6.檢查主庫和實體備庫日志是同步的

主庫日志：

sql> select ads.dest_id,max(sequence#) "current sequence",

           max(log_sequence) "last archived"

       from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads

       where ad.dest_id=al.dest_id

       and al.dest_id=ads.dest_id

       and al.resetlogs_change#=(select max(resetlogs_change#) from v$archived_log )

       group by ads.dest_id;

   dest_id current sequence last archived

---------- ---------------- -------------

     1              361           361

     2              361           362

--備庫日志

sql>    select al.thrd "thread", almax "last seq received", lhmax "last seq applied"

      from (select thread# thrd, max(sequence#) almax

          from v$archived_log

          where resetlogs_change#=(select resetlogs_change# from v$database)

          group by thread#) al,

         (select thread# thrd, max(sequence#) lhmax

          from v$log_history

          group by thread#) lh

     where al.thrd = lh.thrd;

    thread last seq received last seq applied

---------- ----------------- ----------------

         1               361              361

參考至：https://blogs.oracle.com/database4cn/entry/data_guard_snapshot_standby_database

              http://tech.ddvip.com/2013-08/1377883448201885.html

如有錯誤，歡迎指正

郵箱:[email protected]

作者：czmmiao  文章出處：http://czmmiao.iteye.com/blog/2124898