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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/271063/viewspace-1330626/
4148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
