oracle adg应用日志,[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小时)。

--这样日志产生量对延迟的影响就很小。

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