ogg的Logretention筆記
ogg的Logretention筆記
為了方便OGG的使用資料庫歸檔日誌，從OGG11.1版本開始，提供了LOGRETENTION功能。
但是這個功能剛開始有一些BUG。
目前使用的OGG 11.2.1.0.1 版本中，這個功能能否正常使用了呢？
下面就在單機環境下做個驗證。
You enable this feature
when you issue the REGISTER EXTRACT command before creating your Extract
processes (see "Configuring Extract for change capture" on page 4-10)
To use this feature, the Extract database user must have the following privileges, in
addition to the basic privileges
11.2.0.3 and later Run package to grant Oracle Streams admin privilege.
exec dbms_goldengate_auth.grant_admin_privilege('user')
Note: To support RMAN log retention on Oracle RAC, you must
download and install the database patch that is provided in BUGFIX
11879974 before you add the Extract groups.
To have even more integration of Oracle GoldenGate capture with the Oracle database
engine, you can use integrated capture if the source database is Oracle 11.2.0.3 or later.
In integrated capture mode, log retention is enabled automatically, and Extract
receives data changes directly from a database logmining server instead of reading the
redo logs directly. See "About integrated capture" on page 4-4.
設定 LOGRETENTION 需要什麼許可權，參考DBLOGIN章節：
11.2.0.2 and later
exec dbms_goldengate_auth.grant_admin_privilege('
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ogg')
PL/SQL procedure successfully completed.
GGSCI (node1) 8> register extract ext1 logretention
2014-11-12 15:10:01 INFO OGG-01749 Successfully registered EXTRACT EXT1
to start managing log retention at SCN 888158.
GGSCI (node1) 9>
RMAN> backup archivelog all format '/bak/al32_log_%U' delete all input;
RMAN> backup archivelog all format '/bak/al32_log_%U' delete all input;
......
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/arch/1_52_862147314.dbf thread=1 sequence=52
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/arch/1_53_862147314.dbf thread=1 sequence=53
Finished backup at 12-NOV-14
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@node1 ~]$ ls /arch
1_52_862147314.dbf 1_53_862147314.dbf 這兩個日誌OGG還需要，所以沒有自動刪除。
[oracle@node1 ~]$
下面開啟EXT1程式，使其讀取日誌，然後再進行歸檔日誌的備份和刪除。
SQL> select first_change#,next_change#,sequence# from v$archived_log
where sequence#>50;
FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
------------- ------------ ----------
884000 890277 51
890277 890420 52
890420 890501 53
890501 891003 54
dba_capture檢視中記錄了註冊的LOGRETENTION項：
SQL> select capture_name,capture_user,start_scn,status from dba_capture;
CAPTURE_NAME CAPTURE_USER START_SCN
------------------------------ ------------------------------ ----------
STATUS
--------
OGG2$_EXT19C0B1A0D OGG 890342
DISABLED
SQL>
SCN 890342 之後的日誌，將被保留。對應在日誌序列號是SEQUENCE#=52
890342 這個值什麼時候發生變化呢？
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 318770480 bytes
Database Buffers 92274688 bytes
Redo Buffers 4272128 bytes
Database mounted.
Database opened.
SQL> select count(1) from dba_capture;
COUNT(1)
----------
1
SQL> select first_scn,start_scn from dba_capture;
FIRST_SCN START_SCN
---------- ----------
892717 892717 這個時候，SCN已經向前滾動了。
SQL>
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Nov 12 16:18:10 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SALES (DBID=735328178)
RMAN> backup archivelog all format '/bak/al32_log_%U' delete all input;
......
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/arch/1_60_862147314.dbf thread=1 sequence=60
Finished backup at 12-NOV-14
RMAN>
原來的日誌順利刪除！
如果不重啟資料庫，它就不會變化嗎？
LOGRETENTION PREVENTING RMAN FROM DELETING OLD ARCHIVES (Doc ID 1610702.1)
SOLUTION
SQL>select capture_name, queue_owner, capture_user, start_scn, status from dba_capture;
OGG2$_ECISCO1F863961EE GGUSER GGUSER 13243512068101 DISABLED
OGG2$_ECISCO17C63D846 GGUSER GGUSER 13239510951183 DISABLED
ggsci> Dblogin userid xx password xx
ggsci> Unregister extract LOGRETENTION
Remove the entries. Use the following command
exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('OGG2$_ECISCO1F863961EE');
exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('OGG2$_ECISCO17C63D846');
Make sure no rows are returned:
Select capture_name, queue_owner, capture_user, start_scn, status from dba_capture;
Then try and register again the extract with logretention
After reregistering for extract for retention, please run:
1. rman> crosscheck archivelog all
2. rman> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
3. SQL> exec dbms_backup_restore.refreshagedfiles ;
------------------
SQL> select CAPTURE_NAME,start_scn,first_scn from dba_capture;
CAPTURE_NAME START_SCN FIRST_SCN
------------------------------ ---------- ----------
OGG$_EXT19C0B1A0D 894448 894448
GGSCI (node1) 16> unregister extract ext1 logretention
2014-11-12 16:42:24 INFO OGG-01750 Successfully unregistered EXTRACT EXT1 from database.
GGSCI (node1) 17>
SQL> /
no rows selected 被unregister自動清理了 省去了DBMS_CAPTURE_ADM.DROP_CAPTURE
SQL>
GGSCI (node1) 17> register extract ext1 logretention
2014-11-12 16:44:25 INFO OGG-01749 Successfully registered EXTRACT EXT1 to start managing log retention at SCN 895020.
SQL> /
CAPTURE_NAME START_SCN FIRST_SCN
------------------------------ ---------- ----------
OGG$_EXT19C0B1A0D 895020 895020
SQL>
SQL> select first_change#,next_change#,sequence# from v$archived_log
where sequence#>50; 2
FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
------------- ------------ ----------
884000 890277 51
890277 890420 52
890420 890501 53
890501 891003 54
891003 891171 55
891171 891174 56
891174 891220 57
891220 891525 58
891525 892282 59
892282 893083 60
893083 893525 61
893525 893800 62
893800 893842 63
893842 895698 64
895698 895720 65
895720 895723 66
895723 896212 67
896212 896229 68
18 rows selected.
SQL>
SQL> select CAPTURE_NAME,start_scn,first_scn from dba_capture;
CAPTURE_NAME START_SCN FIRST_SCN
------------------------------ ---------- ----------
OGG2$_EXT19C0B1A0D 894448 894448
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/arch/1_64_862147314.dbf thread=1 sequence=64
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/arch/1_65_862147314.dbf thread=1 sequence=65
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/arch/1_66_862147314.dbf thread=1 sequence=66
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/arch/1_67_862147314.dbf thread=1 sequence=67
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/arch/1_68_862147314.dbf thread=1 sequence=68
Finished backup at 12-NOV-14
dba_capture 中在SCN為什麼不會變化？
第二天來了以後，看看SCN正常向前滾動了。
SQL> /
CAPTURE_NAME START_SCN FIRST_SCN
------------------------------ ---------- ----------
OGG2$_EXT19C0B1A0D 929485 929485
SQL> select first_change#,next_change#,sequence#,deleted from v$archived_log
where sequence#>50; 2
FIRST_CHANGE# NEXT_CHANGE# SEQUENCE# DEL
------------- ------------ ---------- ---
884000 890277 51 YES
890277 890420 52 YES
890420 890501 53 YES
890501 891003 54 YES
891003 891171 55 YES
891171 891174 56 YES
891174 891220 57 YES
891220 891525 58 YES
891525 892282 59 YES
892282 893083 60 YES
893083 893525 61 YES
893525 893800 62 YES
893800 893842 63 YES
893842 895698 64 NO
895698 895720 65 NO
895720 895723 66 NO
895723 896212 67 NO
896212 896229 68 NO
896229 896454 69 NO
896454 921084 70 NO
20 rows selected.
SQL>
GGSCI (node1) 4> info ext1
EXTRACT EXT1 Last Started 2014-11-12 16:45 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2014-11-13 08:51:06 Thread 1, Seqno 71, RBA 7136256
SCN 0.929642 (929642)
RMAN再次來備份日誌，一切正常。
RMAN> backup archivelog all format '/bak/al32_log_%U' delete all input;
小結：
1 還是要有點兒耐心。
2 dba_capture 中的SCN變化機制還是沒搞明白。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271063/viewspace-1330626/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【OGG】OGG的下載和安裝篇
- ogg的manager程式
- numpy的學習筆記\pandas學習筆記筆記
- 傳統OGG與Microservice Architecture OGG的通訊ROS
- 使用ogg 從oracle 同步mysql遇到問題記錄OracleMySql
- OGG用NOCOMPRESSDELETES記錄所有列刪除值delete
- 印象筆記 --- 方法分享筆記筆記
- git的使用筆記Git筆記
- Docker的使用筆記Docker筆記
- js類的筆記JS筆記
- 我的Webpack 筆記Web筆記
- MongoDB的聚合筆記MongoDB筆記
- Git submodule 的筆記Git筆記
- 以前的shell筆記筆記
- 我的線上筆記筆記
- go interface 的筆記Go筆記
- 我的Git筆記Git筆記
- Oracle OGG 到 Kafka OGG-01431 OGG-01003 OGG-01151 OGG-01296 OGG-01668OracleKafka
- 維修筆記本的技巧 筆記本維修解析辦法筆記
- Paperwork可替代印象筆記Evernote的開源筆記筆記
- WPF製作的小型筆記本-仿有道雲筆記筆記
- 在原ogg基礎上重新搭建ogg
- OGG-01028問題處理記錄
- 筆記筆記
- OGG的抽取程式合併
- OGG問題處理(OGG-01031,OGG-01416)
- CUUG筆記 ORACLE索引學習筆記筆記Oracle索引
- 主動筆記與被動筆記筆記
- ogg 同步kafka OGG-15051 Java or JNI exception:KafkaJavaException
- Redis的字串物件筆記Redis字串物件筆記
- 棧的學習筆記筆記
- 重學 C 的筆記筆記
- 初學Golang的筆記Golang筆記
- Cypress 的學習筆記筆記
- ES的優化筆記優化筆記
- AIDL的使用筆記AI筆記
- Cris 的 Spark SQL 筆記SparkSQL筆記
- MUI的踩坑筆記UI筆記