[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
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
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 開始歸檔。
--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)
RFS[3]: Selected log 4 for thread 1 sequence 3534 dbid 2071943378 branch 798551880
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)
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)
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)
Apply Lag: 2 minutes 26 seconds
Database Status:
--總結如下:
--這樣配合起來就可以實作ADG+日志延遲應用。設定dg的DelayMins=2;注意不能是0,這樣會變成實時應用,
--修改參數主庫參數ArchiveLagTarget=1800(DGMGRL),注意前面DelayMins機關是分鐘,而ArchiveLagTarget的機關是秒。對應的oracle
--參數是archive_lag_target。
--這樣延遲的時間 32分鐘 上下。
--當然如果日志産生很大,可能不到30分鐘就歸檔,這樣可能提前應用日志。不過正常我估計生産系統設定DelayMins會很大,比如180(3小時)。
--這樣日志産生量對延遲的影響就很小。
--其它那位知道還有什麼好方法。