转载

聊聊Oracle 11g的Snapshot Standby Database(上)

 

Oracle 11gData Guard的重要里程碑版本。在11g中,Active DataGuardAdvanced Compression等特性大大丰富了Data Guard的功能和在实践领域应用的广度。其中,除了传统的Physical StandbyLogical Standby11g推出了新的Standby类型——Snapshot Standby

Standby的实质是“同步更新”,无论是Physical Standby还是Logical Standby,都是依照主库Primary的形式进行同步或者异步更新。Standby自身是不能有主动地更新动作的。所以,即使进入11gActive Data guard,备库也只是可以进行只读操作动作,不能自我进行更新动作。

Snapshot Standby的出现改变了这一点。在Oracle官方文档中,对这个特性的解释如下:

A snapshot standby database is a fully updatable standby database. A snapshot

standby database receives and archives, but does not apply, redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

简单的说,Snapshot Standby是一种特殊的Standby类型。它以某一个时点的Physical Standby作为基础,通过convert操作变成snapshot备库。该备库可以进行修改操作(增加、修改和删除)操作。当执行结束之后,可以重新回到Physical Standby角色。“失联”期间发生的所有update操作,都全部被取消掉。“失联”期间主库发生的修改动作,也会在应用apply日志的时候追赶上。

技术是面对应用场景的。Snapshot是针对什么样的需求特征呢?在实际环境,特别是应用开发、测试环境中,我们偶尔会需要一个临时性的测试环境。这个环境上进行一些临时性、可抛弃的测试。这个时候,就可以使用这个snapshot standby

本篇通过一系列的实验,来演示Snapshot Standby的常用操作、工作特性和日志行为分析。

 

1、环境说明

 

笔者使用Oracle 11gR2进行测试,版本编号为11.2.0.4。当前PrimaryStandby都已经搭建完成。

版本信息如下:

 

SQL> select * from v$version;

 

BANNER

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

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

PL/SQL Release 11.2.0.4.0 - Production

CORE     11.2.0.4.0     Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

 

 

主库Primary信息:

 

SQL> select name, open_mode, database_role, protection_mode from v$database;

 

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE

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

VLIFE     READ WRITE           PRIMARY          MAXIMUM PERFORMANCE

 

 

SQL> select instance_name from v$instance;

 

INSTANCE_NAME

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

vlife

 

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

 

    GROUP#  SEQUENCE# ARCHIVED STATUS

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

         1         95 YES      INACTIVE

         2         96 YES      INACTIVE

         3         97 NO       CURRENT

 

 

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      NO

 

6 rows selected

 

 

当前保护模式是Maximum Protection模式,正在apply状态的日志编号是96。当前online redo log编号为97

Standby端信息:

 

 

SQL> select name, open_mode, database_role, protection_mode from v$database;

 

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE

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

VLIFE     READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE

 

SQL> select instance_name from v$instance;

 

INSTANCE_NAME

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

vlifesb

 

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

 

    GROUP#  SEQUENCE# ARCHIVED STATUS

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

         1         95 YES      CLEARING

         2         96 YES      CLEARING

         3         97 YES      CURRENT

 

 

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

 

     RECID  SEQUENCE# ARCHIVED APPLIED

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

        87         91 YES      YES

        88         92 YES      YES

        89         93 YES      YES

        90         94 YES      YES

        91         95 YES      YES

        92         96 YES      IN-MEMORY

 

6 rows selected

 

 

当前Standby端的Flashback Database开启。

 

 

SQL> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

 

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME

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

             1646229 2015/10/21 3:41:25

 

 

2、切换到Snapshot Standby

 

当前我们的vlifesbphysical standby类型,保护模式是默认的最大性能模式。这也就意味着两者之前是允许存在Gap的。切换Snapshot Standby,有两个限制前提条件:

首先,如果切换为Snapshot Standby,就不能在期间进行SwitchoverFailover操作。如果需要使用操作,就需要先切换回Physical Standby才能实现。

其次,如果使用的保护模式是Maximum Protection模式,必须有其他的Standby与之相匹配。要不然就会出现由于日志传输引起的宕机情况。

当前Standby状态是只读Apply状态,这个时候需要终止Apply过程,并且切换回mount状态。否则是不允许进行convert动作的。

 

SQL> alter database convert to snapshot standby;

 

alter database convert to snapshot standby

 

ORA-38784: 无法创建还原点 'SNAPSHOT_STANDBY_REQUIRED_10/22/2015 10:59:37'??

ORA-01153: 激活了不兼容的介质恢复

 

 

重启数据库进入mount状态。

 

SQL> conn / as sysdba

Connected.

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

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 snapshot standby;

Database altered.

 

 

此时standby端的alert log展示了动作。

 

Thu Oct 22 11:09:21 2015

alter database convert to snapshot standby

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_10/22/2015 11:09:21 创建出一个可用的flashback database restore point

Killing 3 processes with pids 7314,7318,7316 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 7312

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

RESETLOGS after incomplete recovery UNTIL CHANGE 1794139

Resetting resetlogs activation ID 4208260171 (0xfad4f44b)

Online log /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_1_c261g1mo_.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_1_c261g1pt_.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_2_c261g2d0_.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_2_c261g2gl_.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_3_c261g34d_.log: Thread 1 Group 3 was previously cleared

Online log /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_3_c261g36q_.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 1794137

Thu Oct 22 11:09:23 2015

Setting recovery target incarnation to 3 –创建出一个新的朝代

CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby

Completed: alter database convert to snapshot standby

 

Thu Oct 22 11:09:30 2015

RFS[3]: Assigned to RFS process 7324

RFS[3]: Selected log 5 for thread 1 sequence 98 dbid -87496857 branch 892734889

Thu Oct 22 11:09:30 2015

ARC0: Becoming the 'no SRL' ARCH

Thu Oct 22 11:09:30 2015

RFS[4]: Assigned to RFS process 7326

RFS[4]: Selected log 4 for thread 1 sequence 99 dbid -87496857 branch 892734889

Archived Log entry 94 added for thread 1 sequence 98 ID 0xfad4f44b dest 1:

Thu Oct 22 11:09:54 2015

ARC2: Becoming the 'no SRL' ARCH

 

 

从日志上看,我们看到Oracle将普通Physical Standby切换到Snapshot Standby的过程,经过了如下步骤:

 

ü  创建出一个Flashback的恢复点;

ü  传输剩余的Standby Redo Log日志信息;

ü  清理Standby端的online redo log日志组;

ü  使用reset log方法,创建出新的朝代数据;

 

结束之后,可以看到Standby端的状态变化为Snapshot Standby

 

 

SQL> select open_mode, database_role, protection_mode from v$database;

 

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE

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

MOUNTED              SNAPSHOT STANDBY MAXIMUM PERFORMANCE

 

 

正文到此结束
Loading...