Supplemental logging有2种类型:database 和 table,后者又可分为unconditional和conditional log group;
Unconditional:只记录指定列的前镜像,不管这些列是否有改动,又称为always log
Conditional:只在日志组指定列有改动时才记录其前镜像
表级日志记录该表的primary/unique key,在两者均缺失时记录所有列;
对于OGG,应同时开启db和表级日志;
至少supplemental_log_data_min=’YES’或’IMPLICIT’以便抽取redo log,可查看v$database;
SUPPLEMENTAL_LOG_DATA_MIN :Ensures that LogMiner will have sufficient information to support chained rows and various storage arrangements such as cluster tables:
SUPPLEMENTAL_LOG_DATA_PK :For all tables with a primary key, indicates whether all columns of the primary key are placed into the redo log whenever an update is performed
SUPPLEMENTAL_LOG_DATA_UI :For all tables with a unique key, indicates whether all other columns belonging to the unique key are placed into the redo log if any of the unique key columns are modified (YES) or not (NO)
表级supplelemental log以日志组为单位
可通过dba_log_groups查看日志组信息,DBA_LOG_GROUP_COLUMNS查看每个日志组对应的列
SELECT LOG_GROUP_NAME,TABLE_NAME,DECODE(ALWAYS,'ALWAYS', 'Unconditional',NULL, 'Conditional') ALWAYS FROM DBA_LOG_GROUPS;
添加/删除日志组
推荐OGG命令add/delete trandata,也可使用sql命令
alter table [TABLE_NAME] add supplemental log group [GROUP_NAME] ([COLUMN_LIST]) always;
alter table [TABLE_NAME] drop supplemental log group [GROUP_NAME]
如果没有unique/primary key,column_list需要列举该表所有列
注意事项:
执行alter database add supplemental log data可能会长时间不返回结果,因为此操作需要invalidate所有的DML cursor,另外需等待running事务运行完毕;
对于繁忙的数据库,最好在mount阶段运行此命令;
如果在数据库open时运行,当前redo file不立即生效,需执行alter system switch logfile;
--406498.1
默认OGG对delete采用compressdeletes选项,即只记录其primary key信息,当source/target表主键不一致时会导致missing delete;
可对extract添加nocompressdeletes,或对map使用keycols强制OGG使用指定列作主键
--此参数不用于ORACLE 1549316.1
Main Note - Oracle GoldenGate - Supplemental Logging [ID 1304503.1]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-761789/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-761789/