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-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

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變化機制還是沒搞明白。

ogg的Logretention筆記

相關文章