一、ADD SCHEMATRANDATA 作用
Use ADD SCHEMATRANDATA to enable schema-level supplemental logging for a table. ADD SCHEMATRANDATA acts on all of the current and future tables in a given schema to automatically log a superset of available keys that Oracle GoldenGate needs for row identification.
可以说这个方式，真正的把OGG 源端经常DDL修改表结构,每次都需要运维人员停表,然后重新trandata的方式解放了出来。Oracle自动对新建的表，以及原有的表DDL操作，自动删除新增TRANDATA!!!
二、学习MOS 关于ADD SCHEMATRANDATA的文章
2.1 10046观察做了什么操作？
GGSCI (wsgw1) 2> dblogin userid OGG01 ,password OGG01 SQL> select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET from
gv$session where username='OGG01'; INST_ID SID SERIAL# USERNAME STATUS MACHINE SQL_ID EVENT ------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ 1 46 545 OGG01 INACTIVE wsgw1 SQL*Net message from client SQL>execute sys.dbms_system.set_ev(46,545,10046,12,''); GGSCI (wsgw1 as OGG01@wsgw1) 3> add SCHEMATRANDATA scott 2021-06-06 11:41:59 INFO OGG-01788 SCHEMATRANDATA has been added on schema scott. 2021-06-06 11:41:59 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema scott. SQL>execute sys.dbms_system.set_ev(46,545,10046,0,''); $ tkprof wsgw1_ora_24926.trc wsgw1_ogg_add_schema.txt [oracle@wsgw1 trace]$ cat wsgw1_ogg_add_schema.txt |grep -A5 "SQL ID">wsgw1_ogg_add_schema.sql SELECT COUNT(1) FROM DBA_OBJECTS WHERE object_name = 'LOGMNR$ALWAYS_SUPLOG_COLUMNS' and object_type = 'SYNONYM' and status = 'VALID' SELECT sys.dbms_assert.SCHEMA_NAME(UPPER('scott')) FROM dual select procedure#,procedurename,properties,itypeobj# from procedureinfo$ where obj#=:1 order by procedurename desc, overload# desc SELECT COUNT(1) FROM DBA_TAB_COLUMNS WHERE OWNER = 'SYS' AND TABLE_NAME = 'LOGMNR$SCHEMA_ALLKEY_SUPLOG' SELECT count(1) FROM v$parameter WHERE name ='enable_goldengate_replication' SELECT value FROM v$parameter WHERE name = 'enable_goldengate_replication' BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('scott','ALLKEYS_ON') ; END; BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('scott','KEYS'); END; select NVL(flags, 0) from sys.streams$_prepare_ddl p where ((p.global_flag = 1 and :1 is null) or (p.global_flag = 0 and p.usrid = :2)) for update insert into sys.streams$_prepare_ddl(scn, flags, usrid, global_flag, timestamp) values (:1, :2, :3, decode(:4, NULL, 1, 0), sysdate) INSERT INTO sys.expdepact$ (obj#, package, schema) VALUES (:1, 'DBMS_LOGREP_EXP', 'SYS') select 1 from sys.expdepact$ e where e.obj# = :1 and e.package = 'DBMS_LOGREP_EXP' and e.schema = 'SYS' SQL> select * from LOGMNR$SCHEMA_ALLKEY_SUPLOG; SCHEMA_NAME ALL ALL ------------------------------ --- --- OGG01 NO NO YZ NO NO BI NO NO ······ HR NO NO SCOTT YES NO
没看出什么来,调用了一些内部的存储过程,修改了一些标识！ 反过来可以通过
LOGMNR$SCHEMA_ALLKEY_SUPLOG 查询整个用户是否开启最小补充日志！
> delete SCHEMATRANDATA SCOTT
SQL> select * from LOGMNR$SCHEMA_ALLKEY_SUPLOG where schema_name='SCOTT';
SCHEMA_NAME ALL ALL
------------------------------ --- ---
SCOTT NO NO
2.2 如何检查表的那些列开启补充日志
How to Check Supplemental Logging When ADD SCHEMATRANDATA is Enabled (Doc ID 1537837.1) To BottomTo Bottom In this Document Goal Solution APPLIES TO: Oracle GoldenGate - Version 10.4.0.0 and later Information in this document applies to any platform. GOAL Provide additional information to the user on how to check supplemental logging when ADD SCHEMATRANDATA is enabled. SOLUTION It is not listed in dba_log_groups or dba_log_group_columns. The dba_log_groups view is empty. select * from table(logmnr$always_suplog_columns( SCHEMA, TABLE )); select * from table(logmnr$always_suplog_columns('SCHEMA1', 'TABLE1')); OWNER TABLE_NAME COLUMN_NAME INTCOL SEGCOL USERCOL ---------- ------------------------------ ------------------------------ ---------- ---------- ---------- SCHEMA TABLE1 ADDRESS_ID 1 1 1 SCHEMA TABLE1 EFFECTIVE_START_DATE 2 2 2 SCHEMA TABLE1 EFFECTIVE_END_DATE 3 3 3
实际测试，DB 11.2.0.4，MOS的方式不太好使！ 另外一种方法！
SQL> select * from table(logmnr$always_suplog_columns('SCOTT','EMP'));
no rows selected
SQL> select * from dba_log_groups where table_name='EMP1';
no rows selected
GGSCI (wsgw1 as OGG01@wsgw1) 9> INFO TRANDATA SCOTT.EMP
2021-06-06 12:53:12 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema SCOTT.
2021-06-06 12:53:12 INFO OGG-01980 Schema level supplemental logging is enabled on schema SCOTT for all scheduling columns.
Logging of supplemental redo log data is enabled for table SCOTT.EMP.
针对这种方式，无法通过以上的方式确认列的补充日志的情况，看不出来。 非这种方式以上三种方式，应该都是可以看到列的信息！！！2.3 执行add 报错failed to find function
Goldengate Add Schematrandata Fails With OGG-01783 or OGG-06522 (Doc ID 1912424.1) To BottomTo Bottom APPLIES TO: Oracle GoldenGate - Version 12.1.2.0.0 and later Information in this document applies to any platform. SYMPTOMS When attempting to invoke the ggsci command ADD SCHEMATRANDATA got one of the following errors: GGSCI> add schematrandata <SCHEMA> 1. 2014-07-11 13:32:55 ERROR OGG-01783 Cannot verify existence of table function that is required to enable schema level supplemental logging,
failed to find function. 2. 2015-09-08 12:59:04 ERROR OGG-06522 Cannot verify existence of table function that is required to ADD schema level supplemental logging,
failed. CAUSE public synonym LOGMNR$ALWAYS_SUPLOG_COLUMNS invalid SELECT owner, object_name, status, object_type FROM DBA_OBJECTS WHERE object_name = 'LOGMNR$ALWAYS_SUPLOG_COLUMNS'; SOLUTION Error message #1: check status of the LOGMNR$ALWAYS_SUPLOG_COLUMNS and recompile the object as sysdba, alter public synonym LOGMNR$ALWAYS_SUPLOG_COLUMNS compile; SELECT owner, object_name, status, object_type FROM DBA_OBJECTS WHERE object_name = 'LOGMNR$ALWAYS_SUPLOG_COLUMNS'; Error message #2: Run utlrp.sql script to compile the invalid objects
2.4 执行add,花费了50分钟
Add Schematrandata Takes A Very Long Time. (Doc ID 2496606.1) To BottomTo Bottom APPLIES TO: Oracle GoldenGate - Version 12.3.0.1.0 and later Oracle Database - Enterprise Edition - Version 12.1.0.2 and later Information in this document applies to any platform. SYMPTOMS add schematrandata taking longer time to execute ggsci> add schematrandata <schema name> allcols ---------------------takes 50minutes to executes CHANGES CAUSE optimizer_adaptive_features parameter was set to true add schematrandata takes 50minutes to execute when optimizer_adaptive_features is set to true. The default value for the optimizer_adaptive_features
is true. SOLUTION ALTER SESSION SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE;
2.5 如何只抽取指定列
OGG: How To Log All Columns With Add Schematrandata To Get NOCOMPRESSUPDATES (Doc ID 1413142.1) To BottomTo Bottom In this Document Goal Solution APPLIES TO: Oracle GoldenGate - Version 11.1.1.1.2 and later Information in this document applies to any platform. GOAL How to have columns always logged with ADD SCHEMATRANDATA to get NOCOMPRESSUPDATE SOLUTION ADD SCHEMATRANDATA does not allow columns to be specified. This enables logging of Primary Key columns only. By default, updates are compressed.
In order to log all columns ADD TRANDATA would have to be used. The ADD TRANDATA can be used in conjunction with ADD SCHEMATRANDATA to specify
the non-primary key columns. If you must log additional, non-key columns of a specific table (or tables) for use by Oracle GoldenGate, such as those needed for FILTER statements
and KEYCOLS clauses in the TABLE and MAP parameters, issue an ADD TRANDATA command for those columns. That command has a COLS option to issue
table-level supplemental logging for the columns, and it can be used in conjunction with ADD SCHEMATRANDATA. ADD SCHEMATRANDATA logs the key columns of a table in the following order of priority: ● Primary key ● In the absence of a primary key, all of the unique keys of the table, including those that are disabled, unusable or invisible. Unique keys that contain ADT member columns are also logged. Only unique keys on virtual columns (function-based indexes) are not logged. ● If none of the preceding exists, all scalar columns of the table are logged. (Systemgenerated row-OIDs are always logged.) Then not only enable logging for unique and pk