Main Note - Supplemental Logging and TRANDATA for OGG (Doc ID 1537838.1)

Last updated on JULY 23, 2021

Applies to:

Purpose

 Prior to extracting data from the Oracle logs, it is necessary to issue the ADD TRANDATA commands via GGSCI. This requires a DBLOGIN:

By default, Oracle only logs changed columns for update operations. Normally, this means that primary key columns are not logged during an update operation. However, REPLICAT requires the primary key columns in order to apply the update on the target system. The ADD TRANDATA command in GGSCI is used to cause Oracle to log primary key columns for all updates. To add supplemental log data, issue the following commands on Unix. Table names may be wildcarded.  All tables that will have captured data must have the TRANDATA added.

$ cd /ggs $ ggsci 
GGSCI (unixserver) > DBLOGIN USERID userid  
GGSCI (unixserver) > ADD TRANDATA schema.<table-name>

Log Extract templates for Oracle non-DDL Extraction

-- this is a template for extraction of data from an Oracle database via logs
-- it does not include extraction of DDL
-- it does not include ALO mode
-- it is NOT for a bi-directional configuration.

-- ./dirprm/xlog.prm
-- add extract xlog, begin now, tranlog
-- add exttrail ./dirdat/lt, extract xlog, megabytes 1000
-- name the extract. It must match the <xlog>.prm file name
Extract XLOG
-- point to the proper SID
Setenv (ORACLE_SID=MySID)
-- point to Oracle_home
SETENV (ORACLE_HOME=/rdbms/oracle/ora1012r/64)
-- set the language attribute even if it is standard and default ad matches the target database
-- ! NLS_LANG the parameter should be put in a line BEFORE login USERID parameter.
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
-- set userid and password
USERID ggs, PASSWORD ggspw
-- specify the local trail to write to
EXTTRAIL ./dirdat/lt
-- if the database is using ASM, uncomment the next line and populate it correctly
-- Tranlogoptions ASMUSER SYS@ASM, ASMPASSWORD ASMpw
-- for asm, tranlogoptions dblogreader for oracle 10.2.0.5 on f10g, and 11.2.0.2 on 11g.
-- should be considered. this will be the trend in future for ASM.
-- set reporting options
Reportcount every 10000 records
-- exclude and tables from the following wildcards
-- Tableexclude <schema.tablename>
-- list wildcarded schema and table subsets to capture.

Table <schema>.*;

-- add individual tables including previously excluded ones that you really want
Table schema.<tablename>;
Table schema.<tablenameb>;

Scope

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.