[20150910]11G ADG与延迟日志应用.txt

[20150910]11G ADG与延迟日志应用.txt

--11G ADG是一个非常好的特性，它可以一边应用日志，一边提供查询，前一阵子跟别人讨论ADG 是否可以与延迟日志应用结合起来，既
--提供只读查询，又延迟日志应用，自己从来没有测试过，今天测试看看。

--实际上一种可能就是在dg上打开flashback，这样在出现问题时闪回到出问题的时间点。但是这个是回滚，而我延迟应用是前进。

1.测试环境：

SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--我现在喜欢使用dgmgrl管理dg，这样简单一些，特别在11g的环境下。

DGMGRL> show configuration
Configuration - study

  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database
    testdg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> edit database testdg set PROPERTY DelayMins=2;
Property "delaymins" updated
--注意修改DelayMins参数是dg，而不是主数据库的。
--但是我的测试遇到了问题：

DGMGRL> show database  testdg
Database - testdg
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       30 minutes 55 seconds
  Real Time Query: ON
  Instance(s):
    testdg

--延迟了30分钟日志还没有应用。几乎想放弃！

2.上午，我仔细看了dg的alert日志：

--alert 日志：
ARC1: Archive log thread 1 sequence 3520 available in 1 minute(s)
Wed Sep 09 22:01:22 2015
Media Recovery Delayed for 1 minute(s) (thread 1 sequence 3520)
Wed Sep 09 22:02:22 2015
Media Recovery Log /u01/app/oracle11g/archivelog/1_3520_798551880.dbf
Media Recovery Waiting for thread 1 sequence 3521 (in transit)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thu Sep 10 01:50:16 2015
RFS[3]: Selected log 4 for thread 1 sequence 3522 dbid 2071943378 branch 798551880
Thu Sep 10 01:50:16 2015
Archived Log entry 17 added for thread 1 sequence 3521 ID 0x806ffa4c dest 1:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ARC3: Archive log thread 1 sequence 3521 available in 2 minute(s)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thu Sep 10 01:50:21 2015
Media Recovery Delayed for 2 minute(s) (thread 1 sequence 3521)
Thu Sep 10 01:52:16 2015
Media Recovery Log /u01/app/oracle11g/archivelog/1_3521_798551880.dbf
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Media Recovery Waiting for thread 1 sequence 3522 (in transit)
Thu Sep 10 08:05:15 2015
RFS[3]: Selected log 5 for thread 1 sequence 3523 dbid 2071943378 branch 798551880
Thu Sep 10 08:05:15 2015
Archived Log entry 18 added for thread 1 sequence 3522 ID 0x806ffa4c dest 1:
ARC0: Archive log thread 1 sequence 3522 available in 2 minute(s)

RMAN> list archivelog time between '2015-09-10' and '2015-09-11';

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name TEST
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
5177    1    3521    A 2015-09-09 22:01:20
        Name: /u01/app/oracle11g/archivelog/1_3521_798551880.dbf

5179    1    3522    A 2015-09-10 01:50:15
        Name: /u01/app/oracle11g/archivelog/1_3522_798551880.dbf

5181    1    3523    A 2015-09-10 08:05:14
        Name: /u01/app/oracle11g/archivelog/1_3523_798551880.dbf

--注意看seq=3521传输与归档，应用情况,注意看~的情况。
--从这里看出，seq=3521从2015-09-09 22:01:20 开始，到2015-09-10 01:50:15结束。而alert显示2015-09-10 01:50:16 开始归档。
Thu Sep 10 01:52:16 2015
Media Recovery Log /u01/app/oracle11g/archivelog/1_3521_798551880.dbf
Media Recovery Waiting for thread 1 sequence 3522 (in transit)
--2015-09-10 01:52:16 开始恢复。
--才想起来oracle 至少10g以前延迟应用不能开始实时应用。

--从上面的提示可以看出，所谓的延迟实际上是归档以后延迟2分钟应用，这样的情况不符合我的需求。可能要配合其它参数来控制这种
--行为，也就是控制每次归档的时间，比如ARCHIVE_LAG_TARGET。

3.必须配合参数ARCHIVE_LAG_TARGET
--应该这样设置，注意如果你使用DGMGRL，最好使用它来修改与维护：
DGMGRL> edit database test set PROPERTY ArchiveLagTarget=120;
--注意这个单位是秒。这样2分钟就会归档1次。

SCOTT@test> select name,COMPLETION_TIME from v$archived_log where name is not null and  completion_time between '2015-09-10' and '2015-09-11' and name<>'testdg';
NAME                                               COMPLETION_TIME
-------------------------------------------------- -------------------
/u01/app/oracle11g/archivelog/1_3521_798551880.dbf 2015-09-10 01:50:16
/u01/app/oracle11g/archivelog/1_3522_798551880.dbf 2015-09-10 08:05:15
/u01/app/oracle11g/archivelog/1_3523_798551880.dbf 2015-09-10 08:05:39
/u01/app/oracle11g/archivelog/1_3524_798551880.dbf 2015-09-10 09:03:13
/u01/app/oracle11g/archivelog/1_3525_798551880.dbf 2015-09-10 09:05:11
/u01/app/oracle11g/archivelog/1_3526_798551880.dbf 2015-09-10 09:07:13
/u01/app/oracle11g/archivelog/1_3527_798551880.dbf 2015-09-10 09:09:14
/u01/app/oracle11g/archivelog/1_3528_798551880.dbf 2015-09-10 09:11:12
/u01/app/oracle11g/archivelog/1_3529_798551880.dbf 2015-09-10 09:13:14
/u01/app/oracle11g/archivelog/1_3530_798551880.dbf 2015-09-10 09:15:12
/u01/app/oracle11g/archivelog/1_3531_798551880.dbf 2015-09-10 09:17:13
/u01/app/oracle11g/archivelog/1_3532_798551880.dbf 2015-09-10 09:19:15
/u01/app/oracle11g/archivelog/1_3533_798551880.dbf 2015-09-10 09:21:10
/u01/app/oracle11g/archivelog/1_3534_798551880.dbf 2015-09-10 09:23:14
/u01/app/oracle11g/archivelog/1_3535_798551880.dbf 2015-09-10 09:25:13
15 rows selected.

--alert 日志情况：
Thu Sep 10 09:21:10 2015
Archived Log entry 29 added for thread 1 sequence 3533 ID 0x806ffa4c dest 1:
ARC3: Archive log thread 1 sequence 3533 available in 2 minute(s)
Thu Sep 10 09:21:10 2015
RFS[3]: Selected log 4 for thread 1 sequence 3534 dbid 2071943378 branch 798551880
Thu Sep 10 09:21:10 2015
Media Recovery Delayed for 2 minute(s) (thread 1 sequence 3533)
Thu Sep 10 09:23:10 2015
Media Recovery Log /u01/app/oracle11g/archivelog/1_3533_798551880.dbf
Media Recovery Waiting for thread 1 sequence 3534 (in transit)
Thu Sep 10 09:23:14 2015
Archived Log entry 30 added for thread 1 sequence 3534 ID 0x806ffa4c dest 1:
ARC0: Archive log thread 1 sequence 3534 available in 2 minute(s)
Thu Sep 10 09:23:14 2015
RFS[3]: Selected log 4 for thread 1 sequence 3535 dbid 2071943378 branch 798551880
Media Recovery Delayed for 2 minute(s) (thread 1 sequence 3534)
Thu Sep 10 09:25:13 2015
Archived Log entry 31 added for thread 1 sequence 3535 ID 0x806ffa4c dest 1:
ARC1: Archive log thread 1 sequence 3535 available in 2 minute(s)
Thu Sep 10 09:25:13 2015
RFS[3]: Selected log 4 for thread 1 sequence 3536 dbid 2071943378 branch 798551880
Thu Sep 10 09:25:16 2015
Media Recovery Log /u01/app/oracle11g/archivelog/1_3534_798551880.dbf
Media Recovery Delayed for 2 minute(s) (thread 1 sequence 3535)

DGMGRL> show database  testdg
Database - testdg
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       2 minutes 26 seconds
  Real Time Query: ON
  Instance(s):
    testdg
Database Status:
SUCCESS

--总结如下:
--这样配合起来就可以实现ADG+日志延迟应用。设置dg的DelayMins=2;注意不能是0，这样会变成实时应用，
--修改参数主库参数ArchiveLagTarget=1800(DGMGRL)，注意前面DelayMins单位是分钟，而ArchiveLagTarget的单位是秒。对应的oracle
--参数是archive_lag_target。
--这样延迟的时间 32分钟 上下。
--当然如果日志产生很大，可能不到30分钟就归档，这样可能提前应用日志。不过正常我估计生产系统设置DelayMins会很大，比如180(3小时)。
--这样日志产生量对延迟的影响就很小。

--其它那位知道还有什么好方法。

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

转载于:http://blog.itpub.net/267265/viewspace-1796078/