【DataGuard】Oracle 11g物理Data Guard之Snapshot Standby資料庫功能
Oracle 11g的Data Guard不僅僅帶給我們的是Active Data Guard實時查詢特性，參見文章《【DataGuard】Oracle 11g物理Active Data Guard實時查詢（Real-time query）特性》（http://space.itpub.net/519536/viewspace-718742）。同時還帶來了另外一個驚喜，這便是Snapshot Standby資料庫功能，此項功能可將備庫置身於“可讀寫狀態”用於不方便在生產環境主庫中測試的內容，比如模擬上線測試等任務。當備庫讀寫狀態下任務完成後，可以非常輕鬆的完成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資料庫功能並不需要開啟主庫和備庫的閃回資料庫（Flashback 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資料庫狀態
只需要執行一條非常簡單的SQL命令便可以將備庫調整到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 dba to ocmu;
Grant succeeded.
secooler@ora11gdg> conn ocmu/ocmu
Connected.
ocmu@ora11gdg> create table 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
--------
Secooler
結論，此時備庫是一個可任意修改和調整的狀態，也就是我們要的“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 --
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資料庫功能並不需要開啟主庫和備庫的閃回資料庫（Flashback 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資料庫狀態
只需要執行一條非常簡單的SQL命令便可以將備庫調整到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 dba to ocmu;
Grant succeeded.
secooler@ora11gdg> conn ocmu/ocmu
Connected.
ocmu@ora11gdg> create table 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
--------
Secooler
結論，此時備庫是一個可任意修改和調整的狀態，也就是我們要的“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 --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30208428/viewspace-1796772/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【轉】【DataGuard】Oracle 11g物理Data Guard之Snapshot Standby資料庫功能Oracle資料庫
- Data Guard之Snapshot Standby資料庫功能[轉]資料庫
- Oracle 11g RAC Data Guard 物理standby 建立Oracle
- 管理物理STANDBY資料庫——DATA GUARD概念和管理資料庫
- 建立物理STANDBY資料庫——DATA GUARD概念和管理資料庫
- 【DataGuard】Oracle 11g DataGuard 新特性之 Snapshot Standby DatabaseOracleDatabase
- oracle10g data guard建立物理standby資料庫的例子Oracle資料庫
- 【DATAGUARD】 將11g物理備庫轉換為Snapshot Standby
- Oracle 11g Data Guard (physical standby - active dataguard) [final]Oracle
- Data Guard - Snapshot Standby Database配置Database
- 【DataGuard】物理Data Guard之Failover轉換AI
- Data Guard學習之物理standby建立步驟
- 物理data guard備standby庫的時候報錯。
- Data Guard 之RMAN備份線上搭建物理standby
- DATA GUARD物理STANDBY的 SWITCHOVER切換
- 搭建Oracle Data Guard 11g（物理備用）Oracle
- 11g Dataguard中的snapshot standby特性
- Oracle 11g Data Guard 物理備庫快速配置指南（上)Oracle
- dataguard之物理standby庫failover 切換AI
- 【DataGuard】Oracle 11g物理Active Data Guard實時查詢（Real-time query）特性Oracle
- 【DATAGUARD 學習】使用duplicate 建立物理standby 資料庫資料庫
- DATA GUARD物理STANDBY的FAILOVER切換AI
- DATA GUARD物理STANDBY的 SWITCHOVER切換[zt]
- 【DataGuard】使用Grid Control調整Oracle物理Data Guard資料保護模式Oracle模式
- Oracle 11g Data guard 物理備庫故障恢復重建例項Oracle
- Oracle 11g Data guard 物理備庫應急切換(failover)流程OracleAI
- Oracle 11g Data guard 物理主備庫正常切換(switchover)流程Oracle
- 【DataGuard】使用Grid Control快速部署Oracle物理Data GuardOracle
- 【DataGuard】同一臺主機部署Oracle 11g物理Active Data Guard詳細過程Oracle
- 總結11g 物理data guard
- ORACLE DUPLICATE建立物理standby資料庫Oracle資料庫
- 管理邏輯STANDBY資料庫——DATA GUARD概念和管理資料庫
- 建立邏輯STANDBY資料庫——DATA GUARD概念和管理資料庫
- 【DataGuard】11g 新特性：Active Data Guard
- ORACLE DATAGUARD 資料庫---建立物理備用資料庫Oracle資料庫
- Oracle 11R2 snapshot Data GuardOracle
- 使用Data Guard Broker進行Data Guard物理備用庫配置(Oracle 19c)Oracle
- 【DataGuard】手工冷備搭建 Oracle 11g DataGuard 物理備庫Oracle