【DataGuard】Oracle 11g DataGuard 新特性之 Snapshot Standby Database

海星星hktk發表於2014-08-24
OracleDatabase
Oracle 11g DataGuard 新特性之 Snapshot Standby Database



Oracle 11g DataGuard 的Snapshot Standby資料庫功能，可以讓物理備庫從只讀狀態 切換到可讀寫狀態。
在snapshot standby狀態下，備庫可以進行讀寫操作，同時備庫還會正常接收主庫的歸檔和日誌資訊，但不會應用這些redo日誌。
當備庫讀寫狀態下任務完成後，還可以從Snapshot Standby資料庫角色切換回Physical standby備庫角色，恢復與主庫資料同步。
切換回物理備庫後，在Snapshot standby狀態下寫入備庫的資料將不再存在。

【實驗環境】
Red Hat Enterprise Linux Server release 5.4
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

【DG主庫、物理備庫結構資訊】




【實驗過程】

主庫 Primary database：綠色
物理備庫 Physical standby database:黃色


1、切換前準備

 1.1、檢視當前備庫模式

sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;

 

1.2、主備庫檢視測試表資訊






1.3、確認備庫閃回區已設定

未設定閃回區報錯



設定閃回區後檢視

sys@DGDB>show parameter db_recovery

 

1.4、備庫取消日誌應用

在日誌應用狀態轉換報錯


備庫取消日誌應用

sys@DGDB>alter database recover managed standby database cancel;

sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;


 

2、physical standby 切換為 snapshot standby

2.1、備庫轉換為snapshot standby


sys@DGDB>alter database convert to snapshot standby;   -- 轉化成 snapshot standby

檢視當前備庫狀態 snapshot standby
sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;

 

轉換過程中告警日誌

Sun Aug 24 01:18:07 2014

alter database convert to snapshot standby

Starting background process RVWR

Sun Aug 24 01:18:07 2014

RVWR started with pid=27, OS id=17153

Allocated 3981120 bytes in shared pool for flashback generation buffer

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_08/24/2014 01:18:07

krsv_proc_kill: Killing 3 processes (all RFS)

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

CLOSE: all sessions shutdown successfully.

Sun Aug 24 01:18:10 2014

SMON: disabling cache recovery

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

RESETLOGS after incomplete recovery UNTIL CHANGE 1216097

Resetting resetlogs activation ID 2198976699 (0x8311b8bb)

Online log /u02/oradata/sh/redo01.log: Thread 1 Group 1 was previously cleared

Online log /u02/oradata/sh/redo02.log: Thread 1 Group 2 was previously cleared

Online log /u02/oradata/sh/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 1216095

Sun Aug 24 01:18:11 2014

Setting recovery target incarnation to 3

CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby

Completed: alter database convert to snapshot standby

 

2.2、snapshot standby開庫，可讀寫


sys@DGDB>alter database open;

sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;


 

2.3、對測試表進行插入操作

現在可以 讀寫操作 standby database

sys@DGDB>insert into lvxinghao.test select * from scott.emp;

sys@DGDB>select count(*) from lvxinghao.test;


 

3、snapshot standby 切回 日誌應用狀態

3.1、備庫重啟到mount狀態

idle>startup mount force;

idle>select database_role, open_mode, protection_mode, protection_level from v$database;



3.2、備庫由snapshot standby 切換為 physical standby

idle>alter database convert to physical standby;



 

切換過程中跟蹤日誌

Sun Aug 24 01:26:09 2014

alter database convert to physical standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (SH)

Flashback Restore Start

Flashback Restore Complete

Drop guaranteed restore point

Stopping background process RVWR

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/DGDB/SHANGHAI/flashback/o1_mf_9zlxdhmc_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/DGDB/SHANGHAI/flashback/o1_mf_9zlxdl5k_.flb

Guaranteed restore point  dropped

Clearing standby activation ID 2202357444 (0x83454ec4)

The primary database controlfile was created using the

'MAXLOGFILES 16' clause.

There is space for up to 13 standby redo logfiles

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;

Shutting down archive processes

Archiving is disabled

Sun Aug 24 01:26:09 2014

ARCH shutting down

ARC3: Archival stopped

Sun Aug 24 01:26:09 2014

ARCH shutting down

ARC2: Archival stopped

Sun Aug 24 01:26:09 2014

ARCH shutting down

ARC1: Archival stopped

Sun Aug 24 01:26:09 2014

ARCH shutting down

ARC0: Archival stopped

Completed: alter database convert to physical standby

 
切換完成後例項處於started狀態

sys@DGDB>select instance_name,status from v$instance;



3.3、重啟備庫到mount狀態，檢視當前備庫角色


sys@DGDB>startup mount force;

sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;



3.4、備庫開庫，檢視測試表

sys@DGDB>alter database open;


sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;


 

檢視測試表 條數不再是24條，而是恢復到切換前與主庫相同的10條

 

3.5、備庫上Real-time Apply 方式應用日誌


sys@DGDB>alter database recover managed standby database using current logfile disconnect;


 

 
【實驗總結】

1、切換Snapshot standby前，物理備庫需設定快速回復區db_recovery_file_dest。
2、物理備庫切換為Snapshot standby狀態前，需取消備庫的日誌應用。

3、物理備庫切換為Snapshot standby時，會建立一個restore point，當需要從snapshot standby狀態切回原來的狀態時，會回到這個restore point的狀態，期間在snapshot standby 的備庫上寫入的資料將不再存在。


呂星昊
2014.8.24

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29475508/viewspace-1256816/，如需轉載，請註明出處，否則將追究法律責任。

相關文章