3、Snapshot Standby行為研究

下面我們分析一下Snapshot Standby的工作性質和行為性質。我們在主庫方向研究當前狀態。

--主庫日誌情況

SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

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

1 98 YES INACTIVE

2 99 NO CURRENT

3 97 YES INACTIVE

SQL> select recid,sequence#, archived, applied from v$archived_log where name='vlifesb' and sequence#>90;

RECID SEQUENCE# ARCHIVED APPLIED

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

123 91 YES YES

126 92 YES YES

128 93 YES YES

130 94 YES YES

132 95 YES YES

134 96 YES YES

136 97 YES NO

138 98 YES NO

8 rows selected

注意：傳送到vlifesb端的歸檔日誌是連續的，沒有發生中斷現象。但是97、98號日誌顯然沒有進行apply。此時，我們強行進行switch logfile動作。

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

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

1 101 YES INACTIVE

2 102 NO CURRENT

3 100 YES INACTIVE

SQL> select recid,sequence#, archived, applied from v$archived_log where name='vlifesb' and sequence#>90;

RECID SEQUENCE# ARCHIVED APPLIED

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

123 91 YES YES

126 92 YES YES

128 93 YES YES

130 94 YES YES

132 95 YES YES

134 96 YES YES

136 97 YES NO

138 98 YES NO

140 99 YES NO

142 100 YES NO

144 101 YES NO

11 rows selected

注意：當進行切換的時候，歸檔日誌是被傳輸過去的，但是同樣沒有被apply。也就是說：切換到snapshot之後，Standby還是在不斷地接受Primary資料庫進行積累。到Standby端我們看一下情況。

--Standby日誌

SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

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

1 1 NO CURRENT

3 0 YES UNUSED

2 0 YES UNUSED

SQL> select recid,sequence#, archived, applied from v$archived_log where recid>87;

RECID SEQUENCE# ARCHIVED APPLIED

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

88 92 YES YES

89 93 YES YES

90 94 YES YES

91 95 YES YES

92 96 YES YES

93 97 YES NO

94 98 YES NO

95 99 YES NO

96 100 YES NO

97 101 YES NO

10 rows selected

SQL> select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME

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

4 4207470439 1 102 104857600 512 80896 YES ACTIVE 1794468 2015/10/22 1794623 2015/10/22

5 UNASSIGNED 1 0 104857600 512 0 NO UNASSIGNED

6 UNASSIGNED 0 0 104857600 512 0 YES UNASSIGNED

在Standby端，我們似乎看到兩套體系。從Primary傳輸來的歸檔日誌透過Standby途徑，不斷的在Archived Redo Log中集合積累，只是沒有被Apply。同時，online redo log體系中，原有的日誌sequence系列被打亂了，從1開始重新計數。這個的確是體現出reset log的特點。

思考一下：Snapshot Standby既然是支援更新修改，從整體上看就是在資料上和Primary“分道揚鑣”。Redo Log進行reset動作之後，也就體現出這點特性。

之後，Snapshot可以開啟open。

SQL> alter database open;

Database altered.

開啟之後，我們嘗試在Standby端進行DML操作。

--獨立事務

SQL> create table t_sn as select * from dba_objects;

Table created

SQL> select count(*) from t_sn;

COUNT(*)

----------

86280

這個獨立事務是在Standby端進行的，並沒有在Primary端實現。下面進行一系列的Standby端Redo Log切換。

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

當前online redo log在不斷切換，反映最新的Snapshot資料分散情況。

SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

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

1 4 YES INACTIVE

2 5 NO CURRENT

3 3 YES INACTIVE

SQL> select recid,sequence#, archived, applied from v$archived_log where recid>87;

RECID SEQUENCE# ARCHIVED APPLIED

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

88 92 YES YES

89 93 YES YES

90 94 YES YES

91 95 YES YES

92 96 YES YES

93 97 YES NO

94 98 YES NO

95 99 YES NO

96 100 YES NO

97 101 YES NO

98 1 YES NO

99 2 YES NO

100 3 YES NO

101 4 YES NO

14 rows selected

注意：在歸檔日誌中，Primary傳遞過來的Standby Redo Log歸檔，和Snapshot自身生成的另一個朝代online redo log歸檔，都在一個列表中。

為了更清晰顯示，我們在Primary主庫上進行測試DML操作。

SQL> select count(*) from t_m;

COUNT(*)

----------

99

SQL> delete t_m;

99 rows deleted

SQL> commit;

Commit complete

SQL> select count(*) from t_m;

COUNT(*)

----------

0

下面實驗進行將snapshot恢復為physical standby。

4、恢復Physical Standby

注意：如果將snapshot standby恢復為physical standby，在Open狀態是不允許的，需要切換到mount狀態。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2471931904 bytes

Fixed Size 2255752 bytes

Variable Size 738198648 bytes

Database Buffers 1711276032 bytes

Redo Buffers 20201472 bytes

Database mounted.

使用convert進行切換。

SQL> alter database convert to physical standby;

Database altered.

此時Standby端的alert log關鍵資訊如下：

Thu Oct 22 11:21:09 2015

alter database convert to physical standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (vlifesb)

Killing 3 processes with pids 7474,7461,7463 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 7457

Flashback Restore Start

Flashback Restore Complete

Drop guaranteed restore point

Guaranteed restore point dropped

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/VLIFESB/flashback/o1_mf_c27f0mj2_.flb

Clearing standby activation ID 4208505925 (0xfad8b445)

The primary database controlfile was created using the

'MAXLOGFILES 16' clause. –重建control file

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

Thu Oct 22 11:21:12 2015

ARCH shutting down

Thu Oct 22 11:21:12 2015

ARCH shutting down

Thu Oct 22 11:21:12 2015

ARCH shutting down

ARC3: Archival stopped

Thu Oct 22 11:21:12 2015

ARCH shutting downARC2: Archival stopped

ARC1: Archival stopped

ARC0: Archival stopped

Completed: alter database convert to physical standby

從日誌上，我們可以看到Oracle實際上在進行flashback操作，恢復控制檔案和清理日誌操作。切換回去之後，Oracle需要將資料庫重新啟動。

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2471931904 bytes

Fixed Size 2255752 bytes

Variable Size 738198648 bytes

Database Buffers 1711276032 bytes

Redo Buffers 20201472 bytes

Database mounted.

Database opened.

SQL>

此時，Standby狀態恢復到Read Only+Physical Standby狀態。

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

READ ONLY PHYSICAL STANDBY

5、相關測試實驗

此時，Physical Standby角色恢復，但是沒有啟動Redo Log Apply動作。此時在歸檔日誌中，舊朝代和新朝代的歸檔日誌同時存在。

SQL> select recid,sequence#, archived, applied from v$archived_log where recid>87;

RECID SEQUENCE# ARCHIVED APPLIED

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

88 92 YES YES

89 93 YES YES

90 94 YES YES

91 95 YES YES

92 96 YES YES

93 97 YES NO

94 98 YES NO

95 99 YES NO

96 100 YES NO

97 101 YES NO

98 1 YES NO

99 2 YES NO

100 3 YES NO

101 4 YES NO

102 102 YES NO

103 103 YES NO

16 rows selected

啟動Redo Log應用過程。

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered

日誌資訊：

Thu Oct 22 11:28:08 2015

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

Attempt to start background Managed Standby Recovery process (vlifesb)

Thu Oct 22 11:28:08 2015

MRP0 started with pid=30, OS id=7593

MRP0: Background Managed Standby Recovery process started (vlifesb)

started logmerger process

Thu Oct 22 11:28:13 2015

Managed Standby Recovery starting 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/VLIFESB/onlinelog/o1_mf_1_c261g1mo_.log

Clearing online log 1 of thread 1 sequence number 104

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_2_c261g2d0_.log

Clearing online log 2 of thread 1 sequence number 5

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_3_c261g34d_.log

Clearing online log 3 of thread 1 sequence number 103

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

Clearing online redo logfile 3 complete

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_22/o1_mf_1_97_c2jnsh3g_.arc

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_22/o1_mf_1_98_c2jnvbw6_.arc

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_22/o1_mf_1_99_c2jo0hdc_.arc

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_22/o1_mf_1_100_c2jo0jjm_.arc

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_22/o1_mf_1_101_c2jo0kky_.arc

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_22/o1_mf_1_102_c2jod77o_.arc

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_22/o1_mf_1_103_c2joqhh0_.arc

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

Recovery of Online Redo Log: Thread 1 Group 4 Seq 104 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_4_c265gc9q_.log

Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_4_c265gcfk_.log

清理Redo Log，同時進行Apply過程。

SQL> select recid,sequence#, archived, applied from v$archived_log where recid>87;

RECID SEQUENCE# ARCHIVED APPLIED

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

88 92 YES YES

89 93 YES YES

90 94 YES YES

91 95 YES YES

92 96 YES YES

93 97 YES YES

94 98 YES YES

95 99 YES YES

96 100 YES YES

97 101 YES YES

98 1 YES NO

99 2 YES NO

100 3 YES NO

101 4 YES NO

102 102 YES YES

103 103 YES IN-MEMORY

16 rows selected

從97號日誌開始，逐個進行日誌apply過程。但是不同朝代的1-4日誌，就被閒置起來。

檢查兩個資料表的情況：在新的備庫Standby上，資料表t_m的主庫操作被傳遞過去。在備庫Standby為snapshot期間，資料表t_sn不復存在。

--Standby端測試

SQL> select count(*) from t_m;

COUNT(*)

----------

0

--Primary、Standby端測試

SQL> select count(*) from t_sn;

select count(*) from t_sn

ORA-00942: 表或檢視不存在

6、結論

Oracle Snapshot是11g中新推出的Standby型別，在一些應用場景上，這種型別備庫會越來越扮演重要的角色。

聊聊Oracle 11g的Snapshot Standby Database（下）

相關文章