[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小時)。
--這樣日誌產生量對延遲的影響就很小。

--其它那位知道還有什麼好方法。