的 Guard不僅僅帶給我們的是Active Data Guard實時查詢特性，參見文章《【DataGuard】Oracle 11g物理Active Data Guard實時查詢（Real-time query）特性》（http://space.itpub.net/519536/viewspace-718742）。同時還帶來了另外一個驚喜，這便是資料庫功能，此項功能可將備庫置身於“可讀寫狀態”用於不方便在生產環境主庫中測試的內容，比如模擬上線測試等任務。當備庫讀寫狀態下任務完成後，可以非常輕鬆的完成Snapshot Standby資料庫角色切換回備庫角色，與主庫資料同步。在Snapshot Standby資料庫狀態下，備庫是可以接受主庫傳過來的日誌，但是不能夠將變化應用在備庫中。

1.停止Redo Apply
如果備庫正處於Redo Apply過程，需要先取消。
sys@ora11gdg> alter database recover managed standby database cancel;

Database altered.

2.檢視當前備庫狀態確保備庫處於MOUNTED狀態
sys@ora11gdg> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

此時備庫是備庫角色，執行模式是MOUNTED。

3.確保閃回恢復區已指定
友情提示：實現Snapshot Standby資料庫功能並不需要開啟主庫和備庫的閃回（ Database）功能，與是否開啟閃回資料庫無關。
sys@ora11gdg> show parameter db_recovery_file_dest

NAME                        TYPE         VALUE
--------------------------- ------------ ------------------------------------
db_recovery_file_dest       string       /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 3852M

確認主庫閃回功能並未開啟
sys@ora11g> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
NO

確認備庫閃回功能並未開啟
sys@ora11gdg> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
NO

4.調整備庫到Snapshot Standby資料庫狀態
只需要執行一條非常簡單的命令便可以將備庫調整到Snapshot Standby資料庫。
sys@ora11gdg> alter database convert to snapshot standby;

Database altered.

sys@ora11gdg> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

5.將備庫置於對外可讀寫狀態
sys@ora11gdg> alter database open;

Database altered.

sys@ora11gdg> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE

一套全新的可讀寫資料庫展現在我們面前。

6.分析切換過程中的日誌資訊
ora11g主庫alert日誌：
Mon Mar 19 18:46:28 2012
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact
Error 3135 for archive log file 2 to 'ora11gdg'
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 2 thread 1 sequence 50 (3135)
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact

ora11gdg備庫alert日誌：
Mon Mar 19 18:46:26 2012
alter database convert to snapshot standby
Starting background process RVWR
Mon Mar 19 18:46:26 2012
RVWR started with pid=26, OS id=8824
Allocated 3981204 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/19/2012 18:46:26
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 1472476
Resetting resetlogs activation ID 4174194338 (0xf8cd26a2)
Online log /u01/app/oracle/oradata/ora11gdg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/ora11gdg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/ora11gdg/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1472474
Mon Mar 19 18:46:29 2012
Setting recovery target incarnation to 5
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby

關鍵的一行提示資訊“Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/19/2012 18:46:26”，這裡給出了我們轉換成snapshot的時刻，便於後面的回切。

7.測試備庫處於Snapshot Standby資料庫對主庫日誌的接收
當主庫切換日誌時，備庫依然可以接收到日誌，只是並不應用
1）主庫切換日誌
sys@ora11g> alter system switch logfile;

System altered.

2）主庫記錄的alert日誌內容
ora11g主庫alert日誌：
Mon Mar 19 18:52:00 2012
Thread 1 cannot allocate new log, sequence 52
Private strand flush not complete
Current log# 3 seq# 51 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
Mon Mar 19 18:52:00 2012
ARC3: Standby redo logfile selected for thread 1 sequence 50 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 52 (LGWR switch)
Current log# 1 seq# 52 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
Mon Mar 19 18:52:03 2012
Archived Log entry 91 added for thread 1 sequence 51 ID 0xf8cd26a2 dest 1:
Mon Mar 19 18:52:03 2012
LNS: Standby redo logfile selected for thread 1 sequence 51 for destination LOG_ARCHIVE_DEST_2
LNS: Standby redo logfile selected for thread 1 sequence 52 for destination LOG_ARCHIVE_DEST_2

ora11gdg備庫alert日誌：
Mon Mar 19 18:52:00 2012
RFS[5]: Assigned to RFS process 9174
RFS[5]: Identified database type as 'snapshot standby': Client is ARCH pid 27296
Mon Mar 19 18:52:00 2012
RFS[6]: Assigned to RFS process 9176
RFS[6]: Identified database type as 'snapshot standby': Client is ARCH pid 27300
RFS[6]: Selected log 4 for thread 1 sequence 50 dbid -120744030 branch 778023141
Mon Mar 19 18:52:00 2012
Archived Log entry 47 added for thread 1 sequence 50 ID 0xf8cd26a2 dest 1:
Mon Mar 19 18:52:03 2012
RFS[7]: Assigned to RFS process 9180
RFS[7]: Identified database type as 'snapshot standby': Client is LGWR ASYNC pid 27302
RFS[7]: Selected log 4 for thread 1 sequence 51 dbid -120744030 branch 778023141
Mon Mar 19 18:52:04 2012
Archived Log entry 48 added for thread 1 sequence 51 ID 0xf8cd26a2 dest 1:
RFS[7]: Selected log 4 for thread 1 sequence 52 dbid -120744030 branch 778023141

3）檢視主庫和備庫歸檔目錄下的日誌檔案內容
（1）主庫歸檔日誌檔案
ora11g@secdb /home/oracle/arch/ora11g$ ls -ltr
total 879M
……省略其他……
-rw-r----- 1 oracle oinstall 1.1M Mar 19 18:51 1_50_778023141.arc
-rw-r----- 1 oracle oinstall 363K Mar 19 18:52 1_51_778023141.arc

（2）備庫歸檔日誌檔案
ora11g@secdb /home/oracle/arch/ora11gdg$ ls -ltr
total 847M
……省略其他……
-rw-r----- 1 oracle oinstall 1.1M Mar 19 18:52 1_50_778023141.arc
-rw-r----- 1 oracle oinstall 363K Mar 19 18:52 1_51_778023141.arc

可見，備庫已經接受到主庫發過來的日誌。

8.在Snapshot Standby資料建立使用者和表並初始化資料
sys@ora11gdg> create user ocmu identified by ocmu;

User created.

secooler@ora11gdg> grant  to ocmu;

Grant succeeded.

secooler@ora11gdg> conn ocmu/ocmu
Connected.
ocmu@ora11gdg> create  t (x varchar2(8));

Table created.

ocmu@ora11gdg> insert into t values ('Secooler');

1 row created.

ocmu@ora11gdg> commit;

Commit complete.

ocmu@ora11gdg> select * from t;

X
--------

結論，此時備庫是一個可任意修改和調整的狀態，也就是我們要的“READ WRITE”可讀寫狀態。
特別注意的是，原理上實現Snapshot Standby資料庫功能是基於閃回資料原理的，因此任何導致閃回資料庫無法回退的動作在這裡也要規避，否則Snapshot Standby資料庫將無法回到曾經的備庫恢復狀態。

9.恢復Snapshot Standby資料庫為Physical Standby資料庫
1）重啟備庫到MOUNTED狀態
ocmu@ora11gdg> conn / as sysdba
Connected.
sys@ora11gdg> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ora11gdg> startup mount
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             268438616 bytes
Database Buffers           37748736 bytes
Redo Buffers                6336512 bytes
Database mounted.

sys@ora11gdg> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

2）一條命令恢復原物理備庫身份
sys@ora11gdg> alter database convert to physical standby;

Database altered.

3）備庫的alert日誌清楚的記錄了這個切換的過程
Mon Mar 19 19:30:24 2012
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (ora11gdg)
Flashback Restore Start
Flashback Restore Complete
Stopping background process RVWR
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORA11GDG/flashback/o1_mf_7pg3n2jc_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORA11GDG/flashback/o1_mf_7pg52yst_.flb
Guaranteed restore point dropped
Clearing standby activation ID 4174523254 (0xf8d22b76)
The primary database controlfile was created using the
'MAXLOGFILES 30' clause.
There is space for up to 27 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;
Completed: alter database convert to physical standby

從alert日誌中可以得到恢復方法使用的閃回資料庫功能實現的，也就是說，即便備庫沒有執行在閃回資料庫狀態，依然可以使用閃回資料庫功能完成備庫的角色轉換。

4）重啟備庫到自動恢復日誌狀態
（1）此時資料庫處於NOMOUNTED狀態，需要重新啟動資料庫。
注意這裡是重啟資料庫，而不是使用alter命令調整，否則會收到如下報錯：
sys@ora11gdg> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted

sys@ora11gdg> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
sys@ora11gdg> startup mount;
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             268438616 bytes
Database Buffers           37748736 bytes
Redo Buffers                6336512 bytes
Database mounted.
sys@ora11gdg> alter database recover managed standby database disconnect;

Database altered.

（2）檢視備庫alert日誌，可以清楚的看到恢復的過程。
Mon Mar 19 19:43:48 2012
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u01/app/oracle/oradata/ora11gdg/redo01.log
Clearing online log 1 of thread 1 sequence number 1
Completed: alter database recover managed standby database disconnect
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/ora11gdg/redo02.log
Clearing online log 2 of thread 1 sequence number 2
Clearing online redo logfile 2 complete
Media Recovery Log /home/oracle/arch/ora11gdg/1_49_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_50_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_51_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_52_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_53_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_54_778023141.arc
Media Recovery Waiting for thread 1 sequence 55

（3）檢視V$ARCHIVED_LOG動態效能檢視檢視日誌應用情況
sys@ora11gdg> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIME        NEXT_TIME         APPLIED
---------- ----------------- ----------------- ---------
……省略其他資料……
        49 20120319 18:32:32 20120319 18:38:03 YES
        50 20120319 18:38:03 20120319 18:51:00 YES
        51 20120319 18:51:00 20120319 18:52:03 YES
        52 20120319 18:52:03 20120319 19:09:57 YES
        53 20120319 19:09:57 20120319 19:10:15 YES
        54 20120319 19:10:15 20120319 19:10:25 YES

52 rows selected.

10.開啟備庫到READ ONLY狀態驗證之前在Snapshot Standby資料庫上的操作已撤銷
sys@ora11gdg> alter database recover managed standby database cancel;

Database altered.

sys@ora11gdg> alter database open read only;

Database altered.

sys@ora11gdg> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

sys@ora11gdg> select username from dba_users where username = 'OCMU';

no rows selected

之前建立的測試使用者OCMU不存在。結論得證。

11.小結
這便是神奇的“Snapshot Standby資料庫”功能，備庫可以臨時成為一個可讀寫的獨立資料庫，這極大的擴充套件了備庫的應用場合，我們可以使用備庫的這一項特殊功能將那些在生產環境中“不敢”模擬和再現的問題在備庫端進行測試，測試完畢後再恢復其物理備庫的身份進行日誌恢復。

Good luck.

secooler
12.03.19

-- The End --

【轉】【DataGuard】Oracle 11g物理Data Guard之Snapshot Standby資料庫功能

相關文章