• Home
  • About
  •  

    Oracle GoldenGate – DDL Replication Objects

    Oracle GoldenGate – DDL Replication Objects

    The following post describes the objects created for Oracle GoldenGate DDL support.

    The post is based on Oracle GoldenGate version 11.2.1.0.1. For a basic configuration I used two Linux VMs (OEL5U6) running single instance Oracle 11.2.0.3 databases. I created both databases using DBCA.

    The motivation behind this post was to determine the source from which GoldenGate extracted DDL. Options were the online redo log or DDL triggers. The conclusion of these investigations is that GoldenGate uses a DDL trigger to capture DDL changes. It supplements this information with data collected from various tables and views including NLS_SESSION_PARAMETERS.

    Note that it is possible for aliases to be created for all DDL replication tables. However, there does not appear to be any real benefit in doing in a production environment. Therefore this post refers to all tables by their default names.

    The main tables used during DDL replication are:

    • GGS_MARKER
    • GGS_DDL_SEQ

    Tables

    GGS_MARKER

    The GGS_MARKER table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    FRAGMENTNO NUMBER
    OPTIME CHAR(19)
    TYPE VARCHAR2(100)
    SUBTYPE VARCHAR2(100)
    MARKER_TEXT VARCHAR2(4000)

    This table has the following indexes:

    Index Name Column Name
    GGS_MARKER_IND1 SEQNO
    FRAGMENTNO
    SYS_Cnnnnnn OPTIME
    SEQNO
    FRAGMENTNO

    This table captures all DDL executed in the source database including DDL for Oracle objects (such as the AWR).

    Each DDL statement is allocated a new sequence number from the GGS_MARKER_SEQ sequence.

    Each DDL statement appears to occupy two rows in the GGS_MARKER table with the following fragment numbers:

    • Fragment 1 contains the DDL statement in the MARKER_TEXT column.
    • Fragment 2 contains the marker environment.

    For example, consider the following statement:

    ALTER TABLE t100 ADD c4 NUMBER

    In this example, the sequence number allocated was 501.

    The DDL statement is stored in the MARKER_TEXT column of the first fragment:

    ,C1='ALTER TABLE t100 ADD c4 NUMBER ',

    The commas appear to be used as delimiters.

    The environment is stored in the MARKER_TEXT column of the second fragment. The following example has been reformatted and commented for readability:

    ,C5='501',# GGS_MARKER Sequence Number
    ,B2='77002',# Object ID
    ,G4='',# Sequence ROWID?
    ,B3='US01',             # Object Owner
    ,B4='T100',# Object Name
    ,C12='',# Master Owner
    ,C13='',# Master Object
    ,B5='TABLE',# Object Type
    ,B6='ALTER',# DDL Type
    ,B7='501',# DDL_HIST Sequence Number
    ,B8='GG01.GGS_DDL_HIST',# DDL_HIST table name
    ,B9='US01',# Login User Name
    ,C7='11.2.0.3.0',# Table Version 
    ,C8='11.2.0.0.0',# Table Version - Compatible
    ,C9='VALID',# Table Validity
    ,C10='1',# Instance Number
    ,C11='NORTH',# Instance Name
    ,G3='NONUNIQUE',# Is Index Unique?
    ,C14='NO',# Object Table?
    ,C20='NO',# XML Type Table?
    ,C17('1')='NLS_LANGUAGE',# NLS Parameter Name
    ,C18('1')='AMERICAN',# NLS Parameter Value
    ,C17('2')='NLS_TERRITORY',              # NLS Parameter Name
    ,C18('2')='AMERICA',# NLS Parameter Value
    ,C17('3')='NLS_CURRENCY',# NLS Parameter Name
    ,C18('3')='$',# NLS Parameter Value
    ,C17('4')='NLS_ISO_CURRENCY',# NLS Parameter Name
    ,C18('4')='AMERICA',# NLS Parameter Value
    ,C17('5')='NLS_NUMERIC_CHARACTERS',# NLS Parameter Name
    ,C18('5')='.\,',# NLS Parameter Value
    ,C17('6')='NLS_CALENDAR',# NLS Parameter Name
    ,C18('6')='GREGORIAN',# NLS Parameter Value
    ,C17('7')='NLS_DATE_FORMAT',# NLS Parameter Name
    ,C18('7')='DD-MON-RR',# NLS Parameter Value
    ,C17('8')='NLS_DATE_LANGUAGE',# NLS Parameter Name
    ,C18('8')='ENGLISH',# NLS Parameter Value
    ,C17('9')='NLS_SORT',# NLS Parameter Name
    ,C18('9')='BINARY',# NLS Parameter Value
    ,C17('10')='NLS_TIME_FORMAT',# NLS Parameter Name
    ,C18('10')='HH.MI.SSXFF AM',# NLS Parameter Value
    ,C17('11')='NLS_TIMESTAMP_FORMAT',# NLS Parameter Name
    ,C18('11')='DD-MON-RR HH.MI.SSXFF AM',# NLS Parameter Value
    ,C17('12')='NLS_TIME_TZ_FORMAT',# NLS Parameter Name
    ,C18('12')='HH.MI.SSXFF AM TZR',# NLS Parameter Value
    ,C17('13')='NLS_TIMESTAMP_TZ_FORMAT',# NLS Parameter Name
    ,C18('13')='DD-MON-RR HH.MI.SSXFF AM TZR',# NLS Parameter Value
    ,C17('14')='NLS_DUAL_CURRENCY',# NLS Parameter Name
    ,C18('14')='$',# NLS Parameter Value
    ,C17('15')='NLS_COMP',# NLS Parameter Name
    ,C18('15')='BINARY',# NLS Parameter Value
    ,C17('16')='NLS_LENGTH_SEMANTICS',# NLS Parameter Name
    ,C18('16')='BYTE',# NLS Parameter Value
    ,C17('17')='NLS_NCHAR_CONV_EXCP',# NLS Parameter Name
    ,C18('17')='FALSE',# NLS Parameter Value
    ,C19='17',# Number of NLS parameters

    The NLS parameter names and values are determined from NLS_SESSION_PARAMETERS using the following query:

    SELECT parameter, value
    FROM nls_session_parameters;

    We can conclude from the above NLS parameters with a reasonable level of certainty that GoldenGate does not use the values stored in the Redo Operation 24.1 (DDL) records.

    GGS_DDL_HIST

    The GGS_DDL_HIST table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    OBJECTID NUMBER
    DATAOBJECTID NUMBER
    DDLTYPE VARCHAR2(40)
    OBJECTNAME VARCHAR2(100)
    OBJECTOWNER VARCHAR2(100)
    OBJECTTYPE VARCHAR2(40)
    FRAGMENTNO NUMBER
    OPTIME CHAR(19)
    STARTSCN NUMBER
    METADATA_TEXT VARCHAR2(4000)
    AUDITCOL VARCHAR2(80)

    This table has the following indexes:

    Index Name Column Name
    GGS_DDL_HIST_INDEX1 OBJECTID
    GGS_DDL_HIST_i1 SEQNO
    FRAGMENTNO
    GGS_DDL_HIST_i2 OBJECTID
    STARTSCN
    FRAGMENTNO
    GGS_DDL_HIST_i3 STARTSCN
    FRAGMENTNO
    GGS_DDL_HIST_i4 OBJECTNAME
    OBJECTOWNER
    OBJECTTYPE
    STARTSCN
    FRAGMENTNO
    GGS_DDL_HIST_i5 OPTIME
    GGS_DDL_HIST_i6 STARTSCN
    AUDITCOL
    FRAGMENTNO

    This table contains all DDL statements executed for non-Oracle objects, irrespective of whether they are being replicated or not.

    The table captures ALTER and DROP DDL, but does not capture CREATE DDL.

    The GGS_DDL_HIST table is similar to the GGS_MARKER table in many ways.

    Each captured DDL statement is allocated a new sequence number from the GGS_MARKER_SEQ sequence.

    Each captured DDL statement appears to occupy two rows in the GGS_MARKER table with the following fragment numbers:

    • Fragment 1 contains the DDL statement in the METADATA_TEXT column.
    • Fragment 2 contains the metadata in the METADATA_TEXT column:

    The following example uses table T100 which was created as follows:

    CREATE TABLE t100 
    (
      c1 NUMBER,
      c2 VARCHAR2(30),
      c3 DATE
    );

    The above statement is not captured in the GGS_DDL_HIST table.

    Consider the following statement:

    ALTER TABLE t100 ADD c4 NUMBER

    The above statement is captured in the GGS_DDL_HIST table.

    In this example, the sequence number allocated was 501.

    The DDL statement is stored in the METADATA_TEXT column of the first fragment:

    ,G1='ALTER TABLE t100 ADD c4 NUMBER ',

    Again the commas appear to be used as delimiters.

    The environment is stored in the METADATA_TEXT column of the second fragment. The following example has been reformatted and commented for readability:

    ,C6='GG01.GGS_MARKER',# Marker Table Name
    ,C5='501',# Marker Sequence Number
    ,C2='1885306',# SCN
    ,S='77002',# Object ID
    ,W='US01',# Owner
    ,X='T100',# Name
    ,Y='TABLE',# Object Type
    ,Z='ALTER',# Operation Type
    ,A1='84',# User ID
    ,A1='84',# User ID
    ,C3='',# Master Owner
    ,C4='',# Master Name
    ,C15='NO',# Ignore?
    ,R='8192',# Block Size
    ,I='77002',# Data Object ID
    ,J='',# Cluster Columns
    ,K='3',# Total Number of Columns
    ,L='0',# Log Group Exists
    ,N='VALID',# Valid?
    ,G11='FALSE',# Cluster?
    ,G7='NO',# IOT?
    ,G8='NO',# IOT Overflow?
    ,O='NO',# Subpartitioned?
    ,P='NO',# Partitioned?
    
    # Column C1 definitions
    ,C('1'),C('C1')='C1',# Alternative Name
    ,D('1'),D('C1')='NUMBER',# Alternative Type
    ,G13('1'),G13('C1')='',# Alternative XML Type
    ,E('1'),E('C1')='',# Alternative Precision
    ,F('1'),F('C1')='',# Alternative Char Used
    ,G10('1'),G10('C1')='22',# Alternative Length
    ,G20('1'),G20('C1')='NO',# Is Encrypted?
    ,G22('1'),G22('C1')='NO',# Is LOB?
    ,G21('1'),G21('C1')='NO',# No Salt?
    ,G27('1'),G27('C1')='NO',# Has Not Null Default?
    ,G('1'),G('C1')='0',# Alternative XML Type
    ,G9('1'),G9('C1')='0',# Alternative BinaryXML Type
    ,G26('1'),G26('C1')='0',# Alternative ObjectXML Type
    ,A2('1'),A2('C1')='C1',# Column Name
    ,A3('1'),A3('C1')='1',# Column Number
    ,A4('1'),A4('C1')='1',# Segment Column Number
    ,A5('1'),A5('C1')='2',# Column Type (2=NUMBER)
    ,A6('1'),A6('C1')='22',# Length
    ,A7('1'),A7('C1')='1',# Is NULL?
    ,A8('1'),A8('C1')='',# Precision
    ,A9('1'),A9('C1')='',# Scale
    ,B1('1'),B1('C1')='0',# Character Set ID
    ,A('1'),A('C1')='0',# Character Set Form
    
    # Column C2 definitions
    ,C('2'),C('C2')='C2',# Alternative Name
    ,D('2'),D('C2')='VARCHAR2',# Alternative Type
    ,G13('2'),G13('C2')='',# Alternative XML Type
    ,E('2'),E('C2')='',# Alternative Precision
    ,F('2'),F('C2')='B',# Alternative Char Used
    ,G10('2'),G10('C2')='30',# Alternative Length
    ,G20('2'),G20('C2')='NO',# Is Encrypted?
    ,G22('2'),G22('C2')='NO',# Is LOB?
    ,G21('2'),G21('C2')='NO',# No Salt?
    ,G27('2'),G27('C2')='NO',# Has Not NULL Default?
    ,G('2'),G('C2')='0',# Alternative XML Type 
    ,G9('2'),G9('C2')='0',# Alternative BinaryXML Type
    ,G26('2'),G26('C2')='0',# Alternative ObjectXML Type
    ,A2('2'),A2('C2')='C2',# Column Name 
    ,A3('2'),A3('C2')='2',# Column Number
    ,A4('2'),A4('C2')='2',# Segment Column Number
    ,A5('2'),A5('C2')='1',# Column Type (1=VARCHAR2)
    ,A6('2'),A6('C2')='30',# Length
    ,A7('2'),A7('C2')='1',# Is NULL?
    ,A8('2'),A8('C2')='',# Precision
    ,A9('2'),A9('C2')='',# Scale
    ,B1('2'),B1('C2')='178',# Character Set ID
    ,A('2'),A('C2')='1',# Character Set Form
    
    # Column 3 definitions
    ,C('3'),C('C3')='C3',# Alternative Name
    ,D('3'),D('C3')='DATE',# Alternative Type
    ,G13('3'),G13('C3')='',# Alternative XML Type
    ,E('3'),E('C3')='',# Alternative Precision
    ,F('3'),F('C3')='',# Alternative Char Used
    ,G10('3'),G10('C3')='7',# Alternative Length
    ,G20('3'),G20('C3')='NO',# Is Encrypted?
    ,G22('3'),G22('C3')='NO',# Is LOB?
    ,G21('3'),G21('C3')='NO',# No Salt?
    ,G27('3'),G27('C3')='NO',# Has Not NULL Default?
    ,G('3'),G('C3')='0',# Alternative XML Type
    ,G9('3'),G9('C3')='0',# Alternative BinaryXML Type
    ,G26('3'),G26('C3')='0',# Alternative ObjectXML Type
    ,A2('3'),A2('C3')='C3',# Column Name
    ,A3('3'),A3('C3')='3',# Column Number
    ,A4('3'),A4('C3')='3',# Segment Column Number
    ,A5('3'),A5('C3')='12',# Column Type (12=DATE)
    ,A6('3'),A6('C3')='7',# Length
    ,A7('3'),A7('C3')='1',# Nullable
    ,A8('3'),A8('C3')='',# Precision
    ,A9('3'),A9('C3')='',# Scale
    ,B1('3'),B1('C3')='0',# Character Set ID
    ,A('3'),A('C3')='0',# Character Set Form
    
    ,H='3',# Column Count (=3)
    ,G28='NO',# XML Type Table?

    GGS_DDL_HIST_ALT

    The GGS_DDL_HIST_ALT table contains the following columns:

    Column Name Data Type
    ALTOBJECT_ID NUMBER
    OBJECTID NUMBER
    OPTIME CHAR(19)

    This table has the following indexes:

    Index Name Column Name
    GGS_DDL_HIST_ALT_u1 OBJECTID
    ALTOBJECTID
    GGS_DDL_HIST_ALT_u2 OPTIME
    GGS_DDL_HIST_ALT_u3 ALTOBJECTID
    OBJECTID

    GGS_DDL_OBJECTS

    The GGS_DDL_OBJECTS table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    OPTIME CHAR(19)
    MARKER_TABLE VARCHAR2(100)
    MARKER_SEQ NUMBER
    START_SCN NUMBER
    OPTYPE VARCHAR2(40)
    OBJTYPE VARCHAR2(40)
    DB_BLOCKSIZE NUMBER
    OBJOWNER VARCHAR2(100)
    OBJNAME VARCHAR2(100)
    OBJECTID NUMBER
    MASTER_OWNER VARCHAR2(100)
    MASTER_NAME VARCHAR2(100)
    DATA_OBJECTID NUMBER
    VALID VARCHAR2(30)
    CLUSTER_COLS NUMBER
    LOG_GROUP_EXISTS VARCHAR2(20)
    SUBPARTITION VARCHAR2(20)
    PARTITION VARCHAR2(20)
    PRIMARY_KEY VARCHAR2(100)
    TOTAL_COLS NUMBER
    COLS_COUNT NUMBER
    DDL_STATEMENT CLOB

    This table does not have any regular indexes. However it does have a LOB index for the DDL_STATEMENT column.

    This table is used by DUMPDDL for debugging.

    GGS_DDL_PARTITIONS

    The GGS_DDL_PARTITIONS table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    PARTITION_ID NUMBER

    This table does not have any indexes.

    This table is used by DUMPDDL for debugging.

    GGS_DDL_COLUMNS

    The GGS_DDL_COLUMNS table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    NAME VARCHAR2(100)
    POS NUMBER
    TYPE VARCHAR2(40)
    LENGTH NUMBER
    ISNULL VARCHAR2(30)
    PREC NUMBER
    SCALE NUMBER
    CHARSETID VARCHAR2(30)
    CHARSETFORM VARCHAR2(50)
    SEGPOS NUMBER
    ALTNAME VARCHAR2(100)
    ALTTYPE VARCHAR2(40)
    ALTPREC NUMBER
    ALTCHARUSED VARCHAR2(50)
    ALTXMLTYPE VARCHAR2(50)

    This table does not have any regular indexes.

    This table is used by DUMPDDL for debugging.

    GGS_DDL_PRIMARY_KEYS

    The GGS_DDL_PRIMARY_KEYS table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    COLUMN_NAME VARCHAR2(100)

    This table does not have any indexes.

    This table is used by DUMPDDL for debugging.

    GGS_DDL_LOG_GROUPS

    The GGS_DDL_LOG_GROUPS table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    COLUMN_NAME VARCHAR2(100)

    Note – this table does have the same definition as GGS_DDL_COLUMNS – It is not a cut-and-paste error.

    This table does not have any indexes.

    This table is used by DUMPDDL for debugging.

    GGS_DDL_RULES

    The GGS_DDL_RULES table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    OBJ_NAME VARCHAR2(200)
    OWNER_NAME VARCHAR2(200)
    BASE_OBJ_NAME VARCHAR2(200)
    BASE_OWNER_NAME VARCHAR2(200)
    BASE_OBJ_PROPERTY NUMBER
    OBJ_TYPE NUMBER
    COMMAND VARCHAR2(50)
    INCLUSION NUMBER

    This table has the following index:

    Index Name Column Name
    SYS_Cnnnnnn SNO

    GGS_DDL_RULES_LOG

    The GGS_DDL_RULES_LOG table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    OBJ_NAME VARCHAR2(200)
    OWNER_NAME VARCHAR2(200)
    BASE_OBJ_NAME VARCHAR2(200)
    BASE_OWNER_NAME VARCHAR2(200)
    BASE_OBJ_PROPERTY NUMBER
    OBJ_TYPE NUMBER
    COMMAND VARCHAR2(50)

    This table does not have any indexes.

    GGS_SETUP

    The GGS_SETUP table contains the following columns:

    Column Name Data Type
    PROPERTY VARCHAR2(100)
    VALUE VARCHAR2(4000)

    In GoldenGate 11.2.1.0.1, GGS_SETUP is created with six initial parameters as follows:

    Parameter Name Value
    ALLOWNONVALIDATEDKEYS 0
    DDL_SQL_TRACING 0
    DDL_STAYMETADATA OFF
    DDL_TRACE_LEVEL 0
    _LIMIT32K 0
    _USEALLKEYS 0

    This table has the following index:

    Index Name Column Name
    GGS_SETUP_UKEY PROPERTY

    GGS_STICK

    The GGS_STICK table contains the following columns:

    Column Name Data Type
    PROPERTY VARCHAR2(100)
    VALUE VARCHAR2(100)

    In GoldenGate 11.2.1.0.1, GGS_STICK is initially created as an empty table.

    This table has the following index:

    Index Name Column Name
    SYS_Cnnnnnn PROPERTY

    GGS_TEMP_COLS

    The GGS_TEMP_COLS table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    COLNAME VARCHAR2(100)
    NULLABLE NUMBER
    VIRTUAL NUMBER
    UDT NUMBER
    ISSYS NUMBER

    This table has the following index:

    Index Name Column Name
    SYS_Cnnnnnn SEQNO
    COLNAME

    GGS_TEMP_UK

    The GGS_TEMP_UK table contains the following columns:

    Column Name Data Type
    SEQNO NUMBER
    KEYNAME VARCHAR2(100)
    COLNAME VARCHAR2(100)
    NULLABLE NUMBER
    VIRTUAL NUMBER
    UDT NUMBER
    ISSYS NUMBER

    This table has the following index:

    Index Name Column Name
    SYS_Cnnnnnn SEQNO
    KEYNAME
    COLNAME

    Sequences

    GGS_MARKER_SEQ

    This sequence is used to generate primary keys for the GGS_MARKER table

    This sequence is created by marker_setup.sql

    GGS_DDL_SEQ

    This sequence is used to generate primary keys for the GGS_DDL_HIST table

    This sequence is created by ddl_setup.sql

    Packages

    DDLREPLICATION

    This package is owned by GGSCHEMA.

    This package is created by ddl_setup.sql.

    The package declares metadata and marker constants

    Metadata constants declared are:

    Name Value
    MD_TAB_USERID A1
    MD_COL_NAME A2
    MD_COL_NUM A3
    MD_COL_SEGCOL A4
    MD_COL_TYPE A5
    MD_COL_LEN A6
    MD_COL_ISNULL A7
    MD_COL_PREC A8
    MD_COL_SCALE A9
    MD_COL_CHARSETID B1
    MD_COL_CHARSETFORM A
    MD_COL_ALT_NAME C
    MD_COL_ALT_TYPE D
    MD_COL_ALT_PREC E
    MD_COL_ALT_CHAR_USED F
    MD_COL_ALT_XML_TYPE G
    MD_TAB_COLCOUNT H
    MD_TAB_DATAOBJECTID I
    MD_TAB_CLUCOLS J
    MD_TAB_TOTAL_COL_NUM K
    MD_TAB_LOG_GROUP_EXISTS L
    MD_COL_ALT_LOG_GROUP_COL M
    MD_TAB_VALID N
    MD_TAB_SUBPARTITION O
    MD_TAB_PARTITION P
    MD_TAB_PARTITION_IDS Q
    MD_TAB_BLOCKSIZE R
    MD_TAB_OBJECTID S
    MD_TAB_PRIMARYKEY T
    MD_TAB_PRIMARYKEYNAME V
    MD_TAB_OWNER W
    MD_TAB_NAME X
    MD_TAB_OBJTYPE Y
    MD_TAB_OPTYPE Z
    MD_TAB_SCN CONSTANT C2
    MD_TAB_MASTEROWNER C3
    MD_TAB_MASTERNAME C4
    MD_TAB_MARKERSEQNO C5
    MD_TAB_MARKERTABLENAME C6
    MD_TAB_DDLSTATEMENT G1
    MD_TAB_BIGFILE G2
    MD_TAB_ISINDEXUNIQUE G3
    MD_TAB_SEQUENCEROWID G4
    MD_TAB_SEQCACHE G5
    MD_TAB_SEQINCREMENTBY G6
    MD_TAB_IOT CONSTANT G7
    MD_TAB_IOT_OVERFLOW G8
    MD_COL_ALT_BINARYXML_TYPE G9
    MD_COL_ALT_LENGTH G10
    MD_TAB_CLUSTER G11
    MD_TAB_CLUSTER_COLNAME G12
    MD_COL_ALT_TYPE_OWNER G13
    MD_TAB_SESSION_OWNER G14
    MD_TAB_ENC_MKEYID G15
    MD_TAB_ENC_ENCALG G16
    MD_TAB_ENC_INTALG G17
    MD_TAB_ENC_COLKLC G18
    MD_TAB_ENC_KLCLEN G19
    MD_COL_ENC_ISENC G20
    MD_COL_ENC_NOSALT G21
    MD_COL_ENC_ISLOB G22
    MD_COL_LOB_ENCRYPT G23
    MD_COL_LOB_COMPRESS G24
    MD_COL_LOB_DEDUP G25
    MD_COL_ALT_OBJECTXML_TYPE G26
    MD_COL_HASNOTNULLDEFAULT G27
    MD_TAB_XMLTYPETABLE G28

    The above constants are used in GGS_DDL_SEQ.METADATA_TEXT.

    Marker constants declared are:

    Name Value
    MK_OBJECTID B2
    MK_OBJECTOWNER B3
    MK_OBJECTNAME B4
    MK_OBJECTTYPE B5
    MK_DDLTYPE B6
    MK_DDLSEQ B7
    MK_DDLHIST B8
    MK_LOGINUSER B9
    MK_DDLSTATEMENT C1
    MK_TAB_VERSIONINFO C7
    MK_TAB_VERSIONINFOCOMPAT C8
    MK_TAB_VALID C9
    MK_INSTANCENUMBER C10
    MK_INSTANCENAME C11
    MK_MASTEROWNER C12
    MK_MASTERNAME C13
    MK_TAB_OBJECTTABLE C14
    MK_TAB_TOIGNORE C15
    MK_TAB_NLS_PARAM C17
    MK_TAB_NLS_VAL C18
    MK_TAB_NLS_CNT C19
    MK_TAB_XMLTYPETABLE C20

    The above constants are used in GGS_MARKER.MARKER_TEXT.

    This package declares the following procedures and functions:

    • PROCEDURE setCtxInfo
    • PROCEDURE getObjTypeName
    • PROCEDURE getObjType
    • PROCEDURE getDDLObjInfo
    • PROCEDURE getDDLBaseObjInfo
    • PROCEDURE getKeyCols
    • PROCEDURE getKeyColsUseAllKeys
    • PROCEDURE saveSeqInfo
    • PROCEDURE getColDefs
    • PROCEDURE getTableInfo
    • PROCEDURE insertToMarker
    • FUNCTION itemHeader
    • FUNCTION getDDLText
    • FUNCTION isRecycle
    • PROCEDURE getVersion
    • PROCEDURE beginHistory
    • PROCEDURE endHistory
    • PROCEDURE setTracing
    • FUNCTION replace_string
    • FUNCTION escape_string
    • PROCEDURE saveMarkerDDL
    • FUNCTION trace_header_name
    • FUNCTION removeSQLcomments
    • PROCEDURE getTableFromIndex
    • PROCEDURE getObjectTableType
    • PROCEDURE DDLtooLarge

    DDLAUX

    This package is owned by GGSCHEMA. It handles INCLUDE and EXCLUDE rules.

    The package is created by ddl_setup.sql

    This package declares the following procedures and functions:

    • FUNCTION addRule
    • FUNCTION dropRule
    • PROCEDURE listRules
    • FUNCTION SKIP_OBJECT
    • PROCEDURE recordExclusion

    DDLVERSIONSPECIFIC

    This package is owned by GGSCHEMA

    This package declares a couple of cursors based on data dictionary views. It does not have a body

    As the name suggests, the package is version specific. It is created by one of the following scripts:

    • ddl_ora9.sql
    • ddl_ora10.sql
    • ddl_ora11.sql

    DDLCTXINFO

    This package is owned by SYS. It is created by ddl_setup.sql

    Procedures

    INITIAL_SETUP

    This procedure is created by ddl_setup.sql

    It creates the objects required for GGSCI, tracing etc.

    Tables created include:

    • GGS_DDL_RULES
    • GGS_DDL_RULES_LOG
    • GGS_TEMP_COLS
    • GGS_TEMP_UK
    • GGS_STICK
    • GGS_SETUP
    • GGS_DDL_HIST_ALT
    • GGS_DDL_HIST
    • GGS_DDL_COLUMNS
    • GGS_DDL_LOG_GROUPS
    • GGS_DDL_PARTITIONS
    • GGS_DDL_PRIMARY_KEYS
    • GGS_DDL_OBJECTS

    Sequences created include:

    • GGS_DDL_SEQ

    CREATE_TRACE

    This procedure is created by ddl_setup.sql and creates the tracing environment for DDL replication.

    All tracing code is created outside of the DDL packages in order to allow package creation to be traced

    This procedure determines the value of USER_DUMP_DEST from V$PARAMETER and creates the GGS_DDL_TRACE directory in this location.

    CLEAR_TRACE

    This procedure is created by ddl_setup.sql and deletes the trace file from the GGS_DDL_TRACE directory.

    TRACE_PUT_LINE

    This procedure is created by ddl_setup.sql and writes a line to the trace file. If the line length exceeds the output line size, it will be split into multiple lines. If the line exceeds 1000 bytes, it will be split into multiple recursive calls to PUT_LINE.

    DDLORA_GETLOBS

    This procedure is version specific. It is created by one of the following scripts:

    • ddl_ora9.sql
    • ddl_ora10.sql
    • ddl_ora11.sql

    Functions

    DDLORA_GETERRORSTACK

    This function is only available in Oracle 10.1 and above. It is declared in ddl_ora10upCommon.sql for all releases.

    It calls DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to generate the error stack. It returns the last 5000 characters of the error stack to the caller.

    DDLORA_ERRORISUSERCANCEL

    This function is only available in Oracle 10.1 and above. It is declared in ddl_ora10upCommon.sql for all releases.

    It calls DBMS_UTILITY.FORMAT_ERROR_STACK and checks for the following error:

    ORA-01013 user requested cancel of current operation

    DDLORA_GETALLCOLSLOGGING

    This function is only available in Oracle 10.1 and above. It is declared in ddl_ora10upCommon.sql for all releases.

    It reports where a table has all column logging enabled for supplemental logging.

    Internally it joins the data dictionary objects (OBJ$) and constraint definitions (CDEF$) tables for constraint type 17 (All column supplemental logging)

    DDLORA_VERIFYDDL

    This function is declared in ddl_setup.sql. It verifies that all objects required for GoldenGate DDL support have been created successfully by checking the dictionary views.

    Objects checked include:

    Object Name Object Type Dictionary View
    DDLORA_GETERRORSTACK FUNCTION DBA_ERRORS
    CREATE_TRACE PROCEDURE DBA_ERRORS
    TRACE_PUT_LINE PROCEDURE DBA_ERRORS
    FILE_SEPARATOR FUNCTION DBA_ERRORS
    INITIAL_SETUP PROCEDURE DBA_ERRORS
    DDLORA_GETLOBS PROCEDURE DBA_ERRORS
    DDLORA_GETALLCOLSLOGGING PROCEDURE DBA_ERRORS
    DDLREPLICATION PACKAGE DBA_ERRORS
    DDLVERSIONSPECIFIC PACKAGE DBA_ERRORS
    DDLREPLICATION PACKAGE BODY DBA_ERRORS
    GGS_DDL_RULES TABLE DBA_TABLES
    GGS_DDL_RULES_LOG TABLE DBA_TABLES
    DDLAUX PACKAGE DBA_ERRORS
    DDLAUX PACKAGE BODY DBA_ERRORS
    DDLCTXINFO PACKAGE DBA_ERRORS
    DDLCTXINFO PACKAGE BODY DBA_ERRORS
    GGS_DDL_HIST TABLE DBA_TABLES
    GGS_DDL_HIST_ALT TABLE DBA_TABLES
    GGS_DDL_OBJECTS TABLE DBA_TABLES
    GGS_DDL_COLUMNS TABLE DBA_TABLES
    GGS_DDL_LOG_GROUPS TABLE DBA_TABLES
    GGS_DDL_PARTITIONS TABLE DBA_TABLES
    GGS_DDL_PRIMARY_KEYS TABLE DBA_TABLES
    GGS_TEMP_COLS TABLE DBA_TABLES
    GGS_TEMP_UK TABLE DBA_TABLES
    GGS_DDL_SEQ SEQUENCE DBA_SEQUENCES
    GGS_DDL_TRIGGER_BEFORE TRIGGER DBA_TRIGGERS

    The script also checks for the ENC$ table which is created as part of the data dictionary in Oracle 11.2.

    FILTERDDL

    This function is declared in ddl_filter.sql

    This function determines whether DDL for a specific object should be included or excluded.

    It takes as input parameters the statement, owner, object name, object type and operation type and returns either INCLUDE or EXCLUDE.

    The function can be customized, but after it has been modified, Oracle will no longer support it.

    FILE_SEPARATOR

    This function determines the file separator for the operating system. It will be ‘/’ for Unix/Linux and ‘\’ for Windows.

    The file separator is determined by selecting the value of USER_DUMP_DEST from V$PARAMETER and checking for the existence of ‘/’ or ‘\’.

    Directories

    GGS_DDL_TRACE

    This directory is owned by the GGSCHEMA user. It defaults to the USER_DUMP_DEST directory.

    For example for the single-instance NORTH database the GGS_DDL_TRACE directory is

    /u01/app/oracle/diag/rdbms/north/NORTH/trace

    Within the trace directory GoldenGate maintains a trace file recording all DDL activity. This file is called ggs_ddl_trace.log

    Triggers

    GGS_DDL_TRIGGER_BEFORE

    This DDL trigger is owned by SYS. It is called every time a DDL statement is executed in the source database.

    The trigger body is 1181 lines in size. The trigger makes numerous calls to procedures and functions in the DDLReplication package. It also inserts rows directly into the GGS_MARKER table.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    *

    HTML tags are not allowed.