Data Guard - Snapshot Standby Database配置

概述

--------

一般情況下，物理standby資料庫處於mount狀態接收和應用主庫的REDO日誌，物理standby資料庫不能對外提供訪問。如果需要只讀訪問，那麼可以臨時以read-only的方式open物理備庫，或者配置ACTIVE DATA GUARD，那麼物理standby資料庫可以進行只讀(read-only)訪問（比如報表業務查詢），但是物理standby資料庫不能進行讀寫操作(read-write)。

有些情況下，為了實現系統的壓力測試或者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';

System altered.

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;

Database altered.

SQL> alter database open;

Database altered.

檢查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;

Database altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Database mounted.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

5.轉換為物理standby後，檢視備庫角色是PHYSICAL STANDBY，open模式是MOUNTED

SQL> select DATABASE_ROLE,name,OPEN_MODE from v$database;

DATABASE_ROLE NAME OPEN_MODE

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

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

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

group by thread#) lh

where al.thrd = lh.thrd;

Thread Last Seq Received Last Seq Applied

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

1 361 361

Data Guard - Snapshot Standby Database配置

Data Guard - Snapshot Standby Database配置

相關文章