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

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(*)

----------

SQL> delete t_m;

99 rows deleted

SQL> commit;

Commit complete

SQL> select count(*) from t_m;

COUNT(*)

----------

下面实验进行将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(*)

----------

--Primary、Standby端测试

SQL> select count(*) from t_sn;

select count(*) from t_sn

ORA-00942: 表或视图不存在

6、结论

Oracle Snapshot是11g中新推出的Standby类型，在一些应用场景上，这种类型备库会越来越扮演重要的角色。

转：http://blog.itpub.net/17203031/viewspace-1816341/

posted on 2016-09-19 20:18 张冲andy 阅读(566) 评论(0) 编辑收藏举报

刷新页面返回顶部

张冲andy

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

导航

公告