ITPub博客

首页 > 数据库 > Oracle > 聊聊Oracle 11g的Snapshot Standby Database(下)

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

原创 Oracle 作者:realkid4 时间:2015-10-26 14:05:12 0 删除 编辑

 

3Snapshot 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端的归档日志是连续的,没有发生中断现象。但是9798号日志显然没有进行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端实现。下面进行一系列的StandbyRedo 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的主库操作被传递过去。在备库Standbysnapshot期间,数据表t_sn不复存在。

 

 

--Standby端测试

SQL> select count(*) from t_m;

 

  COUNT(*)

----------

         0

 

--PrimaryStandby端测试

SQL> select count(*) from t_sn;

 

select count(*) from t_sn

 

ORA-00942: 表或视图不存在

 

 

6、结论

 

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

 

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-1816341/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7639612