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类型，在一些应用场景上，这种类型备库会越来越扮演重要的角色。
转：http://blog.itpub.net/17203031/viewspace-1816341/