【DATAGUARD】將11g物理備庫轉換為Snapshot Standby

BLOG文件結構圖

【DATAGUARD】基於同一個主機建立物理備庫和邏輯備庫（一）： http://blog.itpub.net/26736162/viewspace-1448197/
【DATAGUARD】基於同一個主機建立物理備庫和邏輯備庫（二 ): http://blog.itpub.net/26736162/viewspace-1448207/
【DATAGUARD】基於同一個主機建立物理備庫和邏輯備庫（三 ): http://blog.itpub.net/26736162/viewspace-1481972/
【DATAGUARD】基於同一個主機建立物理備庫和邏輯備庫 (四)--新增一個物理dg節點：http://blog.itpub.net/26736162/viewspace-1484878/

1.1 將11g物理備庫轉換為Snapshot Standby

Oracle 11g的Data Guard不僅僅帶給我們的是Active Data Guard實時查詢特性，同時還帶來了另外一個驚喜，這便是Snapshot Standby資料庫功能，此項功能可將備庫置身於“可讀寫狀態”用於不方便在生產環境主庫中測試的內容，比如模擬上線測試等任務。當備庫讀寫狀態下任務完成後，可以非常輕鬆的完成Snapshot Standby資料庫角色切換回備庫角色，恢復與主庫資料同步。在Snapshot Standby資料庫狀態下，備庫是可以接受主庫傳過來的日誌，但是不能夠將變化應用在備庫中。當physical standby資料庫轉換為snapshot standby資料庫時，它是一個完全可更新的standby資料庫。snapshot standby資料庫接收和歸檔來自primary資料庫的redo資料，但是它不會應用。當snapshot standby資料庫轉換為physical standby資料庫時，所有在snapshot standby資料庫的操作被丟棄之後，physical standby資料庫才會應用primary資料庫的redo資料。

一般情況下，物理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資料庫，繼續同步主庫日誌

一、將物理備庫轉換為Snapshot Standby 1、停止Redo Apply，讓物理備庫處於mounted狀態

如果備庫正處於Redo Apply過程，需要先取消。

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 7 14:35:29 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

14:35:29 SQL> show parameter name

NAME TYPE VALUE

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

db_file_name_convert string oradg11g, oradgss

db_name string oradg11g

db_unique_name string oradgss

global_names boolean FALSE

instance_name string oradgss

lock_name_space string

log_file_name_convert string oradg11g, oradgss

processor_group_name string

service_names string oradgss

14:35:35 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

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

1403587593 ORADG11G 1192725 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

Elapsed: 00:00:00.00

14:36:03 SQL> ! ps -ef|grep ora_mrp0_oradgss

oracle 5073 1 0 14:26 ? 00:00:00 ora_mrp0_oradgss

oracle 5726 5655 0 14:36 pts/12 00:00:00 /bin/bash -c ps -ef|grep ora_mrp0_oradgss

oracle 5728 5726 0 14:36 pts/12 00:00:00 grep ora_mrp0_oradgss

14:36:59 SQL> alter database recover managed standby database cancel;

Database altered.

Elapsed: 00:00:01.00

14:37:20 SQL> alter database close;

Database altered.

Elapsed: 00:00:00.06

14:37:35 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

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

PHYSICAL STANDBY MOUNTED

Elapsed: 00:00:00.00

14:37:59 SQL>

2、確保閃回恢復區已指定

實現Snapshot Standby資料庫功能並不需要開啟主庫和備庫的閃回資料庫（Flashback Database）功能，與是否開啟閃回資料庫無關。

14:37:35 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

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

PHYSICAL STANDBY MOUNTED

Elapsed: 00:00:00.00

14:37:59 SQL> 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 4122M

14:40:03 SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

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

NO

Elapsed: 00:00:00.01

14:40:32 SQL>

3、調整備庫到Snapshot Standby資料庫狀態

只需要執行一條非常簡單的SQL命令便可以將備庫調整到Snapshot Standby資料庫。

14:40:32 SQL> alter database convert to snapshot standby;

Database altered.

Elapsed: 00:00:03.40

14:42:16 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

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

SNAPSHOT STANDBY MOUNTED

Elapsed: 00:00:00.01

14:42:50 SQL>

分析切換過程中的日誌資訊

ora11g主庫alert日誌：

Tue Apr 07 14:42:12 2015

alter database convert to snapshot standby

Starting background process RVWR

Tue Apr 07 14:42:12 2015

RVWR started with pid=24, OS id=6015

Allocated 3981120 bytes in shared pool for flashback generation buffer

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_04/07/2015 14:42:12

krsv_proc_kill: Killing 4 processes (all RFS)

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

RESETLOGS after incomplete recovery UNTIL CHANGE 1192796

Resetting resetlogs activation ID 1403546633 (0x53a86c09)

Online log /u01/app/oracle/oradata/oradgss/redo01.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/oradata/oradgss/redo02.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/oradata/oradgss/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 1192794

Tue Apr 07 14:42:16 2015

Setting recovery target incarnation to 3

AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.

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的時刻，便於後面的回切。

4、將備庫置於對外可讀寫狀態

14:42:50 SQL> alter database open;

Database altered.

Elapsed: 00:00:06.09

14:43:55 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

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

SNAPSHOT STANDBY READ WRITE

Elapsed: 00:00:00.01

14:44:45 SQL>

5、測試備庫處於Snapshot Standby資料庫對主庫日誌的接收

當主庫切換日誌時，備庫依然可以接收到日誌，只是並不應用

1）主庫切換日誌

14:32:58 SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.01

14:50:04 SQL>

2）主庫記錄的alert日誌內容

ora11g主庫alert日誌：

Tue Apr 07 14:50:04 2015

LNS: Standby redo logfile selected for thread 1 sequence 78 for destination LOG_ARCHIVE_DEST_4

ora11gdg備庫alert日誌：

Tue Apr 07 14:50:04 2015

RFS[5]: Selected log 4 for thread 1 sequence 78 dbid 1403587593 branch 876067148

Tue Apr 07 14:50:04 2015

Archived Log entry 8 added for thread 1 sequence 77 ID 0x53a86c09 dest 1:

3）檢視主庫和備庫歸檔目錄下的日誌檔案內容

（1）主庫歸檔日誌檔案

[oracle@rhel6_lhr ~]$ ls -ltr /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_04_07/

total 41236

。。。。。省略。。。。。

-rw-r----- 1 oracle asmadmin 38400 Apr 7 14:20 o1_mf_1_74_bl6xszqy_.arc

-rw-r----- 1 oracle asmadmin 242176 Apr 7 14:27 o1_mf_1_75_bl6y75kr_.arc

-rw-r----- 1 oracle asmadmin 889344 Apr 7 14:47 o1_mf_1_76_bl6zdp5q_.arc

-rw-r----- 1 oracle asmadmin 69120 Apr 7 14:50 o1_mf_1_77_bl6zjwor_.arc

（2）備庫歸檔日誌檔案

[oracle@rhel6_lhr ~]$ ls -ltr /u01/app/oracle/flash_recovery_area/ORADGSS/archivelog/2015_04_07/

total 14004

-rw-r----- 1 oracle asmadmin 12754944 Apr 7 14:05 o1_mf_1_71_0mq3pp4i_.arc

-rw-r----- 1 oracle asmadmin 251904 Apr 7 14:27 o1_mf_1_72_bl6y72pq_.arc

-rw-r----- 1 oracle asmadmin 38912 Apr 7 14:27 o1_mf_1_73_bl6y72nz_.arc

-rw-r----- 1 oracle asmadmin 38400 Apr 7 14:27 o1_mf_1_74_bl6y72wy_.arc

-rw-r----- 1 oracle asmadmin 242176 Apr 7 14:27 o1_mf_1_75_bl6y765n_.arc

-rw-r----- 1 oracle asmadmin 38400 Apr 7 14:43 o1_mf_1_1_bl6z598f_.arc

-rw-r----- 1 oracle asmadmin 889344 Apr 7 14:47 o1_mf_1_76_bl6zdpk0_.arc

-rw-r----- 1 oracle asmadmin 69120 Apr 7 14:50 o1_mf_1_77_bl6zjwql_.arc

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

6、在Snapshot Standby資料建立使用者和表並初始化資料

14:44:45 SQL> create user TEST identified by test;

User created.

Elapsed: 00:00:00.81

14:55:12 SQL> grant dba to test;

Grant succeeded.

Elapsed: 00:00:00.05

14:55:17 SQL> create table test.test as select * from user_tables;

Table created.

Elapsed: 00:00:02.31

14:55:42 SQL> select count(1) from test.test;

COUNT(1)

----------

984

Elapsed: 00:00:00.00

14:55:59 SQL>

--源庫有lhr使用者這裡我們刪除

14:59:18 SQL> drop user lhr cascade;

User dropped.

Elapsed: 00:00:11.51

14:59:39 SQL>

結論，此時備庫是一個可任意修改和調整的狀態，也就是我們要的“READ WRITE”可讀寫狀態。

特別注意的是，原理上實現Snapshot Standby資料庫功能是基於閃回資料原理的，因此任何導致閃回資料庫無法回退的動作在這裡也要規避，否則Snapshot Standby資料庫將無法回到曾經的備庫恢復狀態。

二、恢復Snapshot Standby資料庫為Physical Standby資料庫 1、重啟備庫到MOUNTED狀態

14:59:39 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

15:00:41 SQL> startup mount

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2228944 bytes

Variable Size 243273008 bytes

Database Buffers 163577856 bytes

Redo Buffers 8466432 bytes

Database mounted.

15:00:57 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

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

SNAPSHOT STANDBY MOUNTED

Elapsed: 00:00:00.01

15:01:00 SQL>

2、一條命令恢復原物理備庫身份

15:01:00 SQL> alter database convert to physical standby;

Database altered.

Elapsed: 00:00:09.42

15:01:58 SQL>

3、備庫的alert日誌清楚的記錄了這個切換的過程

Tue Apr 07 15:01:48 2015

alter database convert to physical standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (oradgss)

Flashback Restore Start

Flashback Restore Complete

Drop guaranteed restore point

Stopping background process RVWR

Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORADGSS/flashback/o1_mf_bl6z24yh_.flb

Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORADGSS/flashback/o1_mf_bl6z28jq_.flb

Guaranteed restore point dropped

Clearing standby activation ID 1403924465 (0x53ae2ff1)

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

Tue Apr 07 15:01:50 2015

ARCH shutting down

ARC3: Archival stopped

Tue Apr 07 15:01:50 2015

ARCH shutting down

ARC1: Archival stopped

Tue Apr 07 15:01:50 2015

ARCH shutting down

ARC0: Archival stopped

Completed: alter database convert to physical standby

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

4、重啟備庫到自動恢復日誌狀態

（1）此時資料庫處於NOMOUNTED狀態，需要重新啟動資料庫。

注意這裡是重啟資料庫，而不是使用alter命令調整，否則會收到如下報錯：

SQL > alter database mount;

alter database mount

*

ERROR at line 1:

ORA-00750: database has been previously mounted and dismounted

15:01:58 SQL> select database_role,open_mode from v$database;

select database_role,open_mode from v$database

*

ERROR at line 1:

ORA-01507: database not mounted

Elapsed: 00:00:00.01

15:04:57 SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

15:05:14 SQL> startup mount;

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2228944 bytes

Variable Size 243273008 bytes

Database Buffers 163577856 bytes

Redo Buffers 8466432 bytes

Database mounted.

15:05:25 SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

Elapsed: 00:00:06.02

15:06:26 SQL>

（2）檢視備庫alert日誌，可以清楚的看到恢復的過程。

alter database recover managed standby database using current logfile disconnect from session

Attempt to start background Managed Standby Recovery process (oradgss)

Tue Apr 07 15:06:20 2015

MRP0 started with pid=27, OS id=7673

MRP0: Background Managed Standby Recovery process started (oradgss)

started logmerger process

Tue Apr 07 15:06:25 2015

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 2 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/oradgss/redo01.log

Clearing online log 1 of thread 1 sequence number 79

Completed: alter database recover managed standby database using current logfile disconnect from session

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 /u01/app/oracle/oradata/oradgss/redo02.log

Clearing online log 2 of thread 1 sequence number 2

Clearing online redo logfile 2 complete

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADGSS/archivelog/2015_04_07/o1_mf_1_76_bl6zdpk0_.arc

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADGSS/archivelog/2015_04_07/o1_mf_1_77_bl6zjwql_.arc

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADGSS/archivelog/2015_04_07/o1_mf_1_78_bl70ghh8_.arc

Media Recovery Waiting for thread 1 sequence 79 (in transit)

Recovery of Online Redo Log: Thread 1 Group 5 Seq 79 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/oradgss/standby_redo05.log

（3）檢視V$ARCHIVED_LOG動態效能檢視檢視日誌應用情況

15:06:26 SQL> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

1 07-APR-15 07-APR-15 NO

71 07-APR-15 07-APR-15 YES

72 07-APR-15 07-APR-15 YES

73 07-APR-15 07-APR-15 YES

74 07-APR-15 07-APR-15 YES

75 07-APR-15 07-APR-15 YES

76 07-APR-15 07-APR-15 YES

77 07-APR-15 07-APR-15 YES

78 07-APR-15 07-APR-15 IN-MEMORY

9 rows selected.

Elapsed: 00:00:00.01

15:08:56 SQL>

5、開啟備庫到READ ONLY狀態驗證之前在Snapshot Standby資料庫上的操作已撤銷

15:08:56 SQL> alter database recover managed standby database cancel;

Database altered.

Elapsed: 00:00:01.00

15:11:51 SQL> alter database open read only;

Database altered.

Elapsed: 00:00:00.21

15:11:56 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

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

PHYSICAL STANDBY READ ONLY

Elapsed: 00:00:00.01

15:12:02 SQL> select username from dba_users where username IN ('LHR','TEST');

USERNAME

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

LHR

Elapsed: 00:00:00.01

15:12:42 SQL>

之前建立的測試使用者TEST不存在,而且被刪除的使用者lhr又被還原了。結論得證。

6、檢查主備庫日誌是否同步

主庫執行：

15:44:24 SQL> col DEST_NAME format a20

15:44:37 SQL> select ads.dest_id,

15:44:37 2 ads.DEST_NAME,

15:44:38 3 ads.STATUS,

15:44:38 4 ads.TYPE,

15:44:38 5 ads.RECOVERY_MODE,

15:44:38 6 ads.DB_UNIQUE_NAME,

15:44:38 7 max(sequence#) "Current Sequence",

15:44:38 8 max(log_sequence) "Last Archived"

15:44:38 9 from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads

15:44:38 10 where ad.dest_id = al.dest_id

15:44:38 11 and al.dest_id = ads.dest_id

15:44:38 12 and al.resetlogs_change# =

15:44:38 13 (select max(resetlogs_change#) from v$archived_log)

15:44:38 14 group by ads.dest_id,

15:44:38 15 ads.DEST_NAME,

15:44:38 16 ads.STATUS,

15:44:38 17 ads.TYPE,

15:44:38 18 ads.RECOVERY_MODE,

15:44:38 19 ads.DB_UNIQUE_NAME

15:44:38 20 order by ads.dest_id;

DEST_ID DEST_NAME STATUS TYPE RECOVERY_MODE DB_UNIQUE_NAME Current Sequence Last Archived

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

1 LOG_ARCHIVE_DEST_1 VALID LOCAL IDLE oradg11g 78 78

2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL MANAGED REAL TIME APPLY oradgphy 78 79

3 LOG_ARCHIVE_DEST_3 VALID LOGICAL LOGICAL REAL TIME APPLY oradglg 78 79

4 LOG_ARCHIVE_DEST_4 VALID PHYSICAL IDLE oradgss 78 79

Elapsed: 00:00:00.00

15:44:38 SQL>

三、小結

這便是神奇的“Snapshot Standby資料庫”功能，備庫可以臨時成為一個可讀寫的獨立資料庫，這極大的擴充套件了備庫的應用場合，我們可以使用備庫的這一項特殊功能將那些在生產環境中“不敢”模擬和再現的問題在備庫端進行測試，測試完畢後再恢復其物理備庫的身份進行日誌恢復。SNAPSHOT STANDBY 模式將備庫置於可讀寫狀態，可以在此備庫上來回折騰，這個結合REAL APPLICATION TESTING 做升級前測試非常方便。要注意如果在SNAPSHOT STANDBY上面的資料更改操作過大，恢復回PHYSICAL STANDBY的時間會非常長。

參考資料：http://blog.itpub.net/519536/viewspace-719056/

About Me

.............................................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-1525548/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：

.............................................................................................................................................

● QQ群號：230161599（滿）、618766405

● 微信群：可加我微信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友（646634621），註明新增緣由

● 於 2017-09-01 09:00 ~ 2017-09-30 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店：

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

小麥苗的微信公眾號小麥苗的DBA寶典QQ群1 小麥苗的DBA寶典QQ群2 小麥苗的微店

.............................................................................................................................................

【DATAGUARD】 將11g物理備庫轉換為Snapshot Standby

1.1 將11g物理備庫轉換為Snapshot Standby

相關文章

【DATAGUARD】將11g物理備庫轉換為Snapshot Standby