Oracle Goldengate是绝佳的数据实时同步工具,支持各种数据库和常用平台。OGG本身的灵活性加上其内置的大量函数,可以满足DBA各种同步、过滤、转换需求。在OGG的同步逻辑中,数据的来源是数据库的增量日志(对于oracle数据库就是archivelog)。在实际运维中各类故障都好处理,但是一旦源端的归档日志被前提删除,抽取进程报找不到归档日志,这将是特别棘手的问题。特别是归档日志无备份直接delete的情况,简直是运维人员的恶梦!
哪些情况OGG抽取进程需要旧的归档日志呢:
- 抽取进程abend或stop了一段时间,再次启动自然需要这段时间内的所有归档日志。比如重启了OS或数据库,但忘了启动ogg;长假期间抽取进程abend了无人知晓。
- 数据库存在迟迟未提交的长事务,时间长了事务的redo信息可能刷到了归档文件中,后面事务提交时抽取进程要扫描这些归档。比如开发人员通过plsqldeveloper修改了表数据,忘了提交;数据库(BI/DW)存在运行比较久的事务或job。
- OGG bug导致抽取进程需要一个非常旧的归档(可能几年前的)。
抽取进程如何找归档
-
For classic Extract
OGG优先到数据库默认归档路径查找归档(SELECT THREAD#,SEQUENCE#,FIRST_TIME ,name FROM v$archived_log)。也可以通过参数指定归档查找路径:
TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY INSTANCE oraXPAD1 /gg/sarch1, ALTARCHIVELOGDEST INSTANCE oraXPAD2 /gg/sarch2
加PRIMARY的话就是优先从ALTARCHIVELOGDEST参数路径下是查找。
-
For Integrated Extract
Integrated Extract原理是由于stream+logminer去抽取数据,每一个抽取进程运行时都有对应的logminer进程在工作,基本是OGG由于数据库字典信息寻找归档所在路径。
如果想指定归档路径,可以使用以下参数(OGG 12.3起)
TRANLOGOPTIONS INTEGRATEDPARAMS (_LOGMINER_ALTARCHIVEDLOGDEST path-string)
TRANLOGOPTIONS INTEGRATEDPARAMS (_LOGMINER_ALTARCHIVEDLOGFORMAT format-string)
如何防止归档被删除
一般数据库都有定时备份和清理归档的脚本,我们也是强烈建议归档要rman备份后再删除。这样的话,哪怕归档已被删除,我们仍然可以从备份中恢复再注册回数据库。当然最好能保证抽取进程所需的归档不会删除。但也有很多不备份直接rman清理过去几天归档的情况(本文不讨论直接rm的情况)。
OGG 11.1开始可以通过将经典抽取进程注册到数据库,可以防止rman删除ogg所需的归档。步骤如下:
- 停止抽取进程
- 执行以下注册命令:
-
GGSCI> dblogin userid <username>, password <password> GGSCI> register extract <Extract-name>, LOGRETENTION
- 确认抽取进程参数中没有配置TRANLOGOPTIONS LOGRETENTION DISABLED
- 启动抽取进程
- 检查抽取进程是否已注册成功
SELECT capture_name, status, captured_scn, applied_scn, capture_type,REQUIRED_CHECKPOINT_SCN,SOURCE_RESETLOGS_SCN from dba_capture;
CAPTURE_NAME STATUS CAPTURED_SCN APPLIED_SCN CAPTURE_TY REQUIRED_CHECKPOINT_SCN SOURCE_RESETLOGS_SCN
------------------------------ ---------- -------------------- -------------------- ---------- ----------------------- --------------------
OGG2$_EXT015D78F7E DISABLED LOCAL 0 0
The extract would show up in dba_capture and status would be disabled (if classic & using logretention) and enabled (if IE). The rman integration is default in IE.
实验:如何防止归档被删除
参照以上的命令我们做了LOGRETENTION注册,停止抽取进程,做个大事务,但不提交,试试归档日志能不能删除。
GGSCI (orcl11g) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT01 00:00:00 00:00:01
sys@ORCL11G > insert into T1 select * from t1;
22020096 rows created.
Elapsed: 00:00:31.77
先不提交,我们看到造成了大量的归档日志。
[oracle@orcl11g:/u01/arch/orcl11g]$ll
total 1110704
-rw-r----- 1 oracle oinstall 50049024 Apr 9 21:10 1_582_991987504.dbf
-rw-r----- 1 oracle oinstall 47244288 Apr 9 21:11 1_583_991987504.dbf
-rw-r----- 1 oracle oinstall 50043904 Apr 9 21:11 1_584_991987504.dbf
-rw-r----- 1 oracle oinstall 50043392 Apr 9 21:11 1_585_991987504.dbf
.....
-rw-r----- 1 oracle oinstall 50048512 Apr 9 21:11 1_604_991987504.dbf
21:15:26 sys@ORCL11G > commit;
Commit complete.
Elapsed: 00:00:00.00
抽取进程先不启动,我们尝试通过rman删除归档,直接报无法删除。
RMAN> DELETE NOPROMPT ARCHIVELOG ALL;
archived log file name=/u01/arch/orcl11g/1_582_991987504.dbf thread=1 sequence=582
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/arch/orcl11g/1_583_991987504.dbf thread=1 sequence=583
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/arch/orcl11g/1_584_991987504.dbf thread=1 sequence=584
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/arch/orcl11g/1_585_991987504.dbf thread=1 sequence=585
......
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/arch/orcl11g/1_604_991987504.dbf thread=1 sequence=604
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/arch/orcl11g/1_605_991987504.dbf thread=1 sequence=605
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/arch/orcl11g/1_606_991987504.dbf thread=1 sequence=606
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/arch/orcl11g/1_607_991987504.dbf thread=1 sequence=607
启动抽取进程,过了一会抽取进程就完成抽取。
GGSCI (orcl11g) 13> start EXT01
GGSCI (orcl11g) 21> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:03:44 00:00:15
GGSCI (orcl1