• Home
  • About
  •  

    Oracle GoldenGate – DDL Replication

    Oracle GoldenGate – DDL Replication

    The following procedure describes 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.

    This post assumes that the source and target databases have already been configured as described in a previous post: Oracle GoldenGate – Basic Configuration.

    This configuration uses following hosts and databases.

      Source Target
    Hostname vm4 vm5
    Database Name NORTH SOUTH

    The configuration includes the following on both nodes:

    • Creation of a GoldenGate schema owner called GG01.
    • Specification of GGSCHEMA as GG01 in GoldenGate parameters
    • Creation of GOLDENGATE tablespace which is default tablespace for GG01

    The GoldenGate process names are:

      Source Target
    Extract ex1 -
    Data Pump dp1 -
    Replicat - rep1

    Full DDL support is not required for sequences or the TRUNCATE statement. These are discussed in other posts. Note that full DDL support is not compatible with standalone TRUNCATE support.

    Installation of DDL Support

    DDL support is not installed by default. A few additional steps are required:

    Run Marker Setup script

    On the source server run the Marker setup script. Specify the GoldenGate schema name when prompted.

    [oracle@vm4]$ cd /home/oracle/goldengate
    
    [oracle@vm4]$ sqlplus / as sysdba
    
    SQL> @marker_setup
    
    Marker setup script
    
    You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
    NOTE: The schema must be created prior to running this script.
    NOTE: Stop all DDL replication before starting this installation.
    
    Enter Oracle GoldenGate schema name:GG01
    
    
    Marker setup table script complete, running verification script...
    Please enter the name of a schema for the GoldenGate database objects:
    Setting schema name to GG01
    
    MARKER TABLE
    -------------------------------
    OK
    
    MARKER SEQUENCE
    -------------------------------
    OK
    
    Script complete.
    

    Run DDL Setup script

    On the source server run the Marker setup script. Specify the GoldenGate schema name when prompted.

    [oracle@vm4]$ cd /home/oracle/goldengate
    
    [oracle@vm4]$ sqlplus / as sysdba
    
    SQL> @ddl_setup
    
    Oracle GoldenGate DDL Replication setup script
    
    Verifying that current user has privileges to install DDL Replication...
    
    You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
    NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
    NOTE: The schema must be created prior to running this script.
    NOTE: Stop all DDL replication before starting this installation.
    
    Enter Oracle GoldenGate schema name:GG01
    
    Working, please wait ...
    Spooling to file ddl_setup_spool.txt
    
    Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
    
    Check complete.
    
    
    Using GG01 as a Oracle GoldenGate schema name.
    
    Working, please wait ...
    
    DDL replication setup script complete, running verification script...
    Please enter the name of a schema for the GoldenGate database objects:
    Setting schema name to GG01
    
    CLEAR_TRACE STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    CREATE_TRACE STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    TRACE_PUT_LINE STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    INITIAL_SETUP STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    DDLVERSIONSPECIFIC PACKAGE STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    DDLREPLICATION PACKAGE STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    DDLREPLICATION PACKAGE BODY STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    DDL IGNORE TABLE
    -----------------------------------
    OK
    
    DDL IGNORE LOG TABLE
    -----------------------------------
    OK
    
    DDLAUX  PACKAGE STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    DDLAUX PACKAGE BODY STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    SYS.DDLCTXINFO  PACKAGE STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    SYS.DDLCTXINFO  PACKAGE BODY STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    DDL HISTORY TABLE
    -----------------------------------
    OK
    
    DDL HISTORY TABLE(1)
    -----------------------------------
    OK
    
    DDL DUMP TABLES
    -----------------------------------
    OK
    
    DDL DUMP COLUMNS
    -----------------------------------
    OK
    
    DDL DUMP LOG GROUPS
    -----------------------------------
    OK
    
    DDL DUMP PARTITIONS
    -----------------------------------
    OK
    
    DDL DUMP PRIMARY KEYS
    -----------------------------------
    OK
    
    DDL SEQUENCE
    -----------------------------------
    OK
    
    GGS_TEMP_COLS
    -----------------------------------
    OK
    
    GGS_TEMP_UK
    -----------------------------------
    OK
    
    DDL TRIGGER CODE STATUS:
    
    Line/pos   Error
    ---------- -----------------------------------------------------------------
    No errors  No errors
    
    DDL TRIGGER INSTALL STATUS
    -----------------------------------
    OK
    
    DDL TRIGGER RUNNING STATUS
    -----------------------------------
    ENABLED
    
    STAYMETADATA IN TRIGGER
    -----------------------------------
    OFF
    
    DDL TRIGGER SQL TRACING
    -----------------------------------
    0
    
    DDL TRIGGER TRACE LEVEL
    -----------------------------------
    0
    
    LOCATION OF DDL TRACE FILE
    --------------------------------------------------------------------------
    /u01/app/oracle/diag/rdbms/north/NORTH/trace/ggs_ddl_trace.log
    
    Analyzing installation status...
    
    
    STATUS OF DDL REPLICATION
    --------------------------------------------------------------------------
    SUCCESSFUL installation of DDL Replication software components
    
    Script complete.
    
    

    Note that the above script states that the recycle bin must be disabled in Oracle 10g databases; it can be enabled in Oracle 11.1 and above.

    Run Role Setup script

    On the source server run the Role setup script. Specify the GoldenGate schema name when prompted.

    [oracle@vm4]$ sqlplus / as sysdba
    
    SQL> @role_setup
    
    GGS Role setup script
    
    This script will drop and recreate the role GGS_GGSUSER_ROLE
    To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
    
    You will be prompted for the name of a schema for the GoldenGate database objects.
    NOTE: The schema must be created prior to running this script.
    NOTE: Stop all DDL replication before starting this installation.
    
    Enter GoldenGate schema name:GG01
    Wrote file role_setup_set.txt
    
    PL/SQL procedure successfully completed.
    
    
    Role setup script complete
    
    Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
    
    GRANT GGS_GGSUSER_ROLE TO 
    
    where  is the user assigned to the GoldenGate processes.
    

    On the source server grant GGS_GGSUSER_ROLE to the GoldenGate user:

    [oracle@vm4]$ sqlplus / as sysdba
    
    SQL> GRANT GGS_GGSUSER_ROLE TO gg01;
    
    Grant succeeded.
    

    Enable the DDL Trigger

    On the source server run the following script to enable the DDL trigger:

    [oracle@vm4]$ sqlplus / as sysdba
    
    SQL> @ddl_enable
    
    Trigger altered.
    

    The default trigger name is GGS_DDL_TRIGGER_BEFORE; it is owned by the SYS user.

    Enabling DDL Support

    By default DDL replication support is:

    • disabled for the extract process
    • enabled for the replicat process

    To enable DDL support, therefore, it is only necessary to specify the DDL parameter for the extract process.

    In the following examples, the DDL parameter has also been set for the replicat process.

    The DDL parameter can only be specified once in a parameter file. However, one or more DDL inclusion criteria can be specified to include or exclude DDL operations based on:

    • scope
    • object type
    • operation type
    • object name
    • strings in the DDL statement or comments

    If multiple DDL filtering options are specified then all criteria must be true for the DDL to be included.

    The syntax for the DDL parameter is as follows:

    DDL [
     { INCLUDE | EXCLUDE }
    
      [, MAPPED | UNMAPPED | OTHER | ALL]
      [, OPTYPE ]
      [, OBJTYPE '']
      [, OBJNAME ]
      [, INSTR '']
      [, INSTRCOMMENTS '']
      [, STAYMETADATA]
      [, EVENTACTIONS {}
    ]
    

    If an EXCLUDE clause is specified, then a corresponding INCLUDE clause must exist.

    For example the following is valid as it contains both clauses:

    DDL INCLUDE ALL, EXCLUDE OBJNAME "US03.*"
    

    The following is valid as it includes an INCLUDE clause:

    DDL INCLUDE OBJNAME "US03.*"
    

    However the following is invalid as it only contains an EXCLUDE clause:

    DDL EXCLUDE OBJNAME "US03.*"
    
    

    EXCLUDE clauses have priority over INCLUDE clauses where both reference the same objects.

    Basic DDL Configuration

    The following example parameter files contain a minimal configuration that I use for testing. I would recommend specifying more restrictive parameters for non-test environments.

    Parameters for the extract process (ex1):

    EXTRACT ex1
    USERID gg01, PASSWORD gg01
    EXTTRAIL /home/oracle/goldengate/dirdat/ex
    DDL INCLUDE ALL
    TABLE US03.t*;
    

    Parameters for the data pump process (dp1):

    EXTRACT dp1
    USERID gg01, PASSWORD gg01
    RMTHOST vm5, MGRPORT 7809
    RMTTRAIL /home/oracle/goldengate/dirdat/rt
    DDL INCLUDE ALL
    TABLE US03.t*;
    

    Parameters for the replicat process (rep1):

    REPLICAT rep1
    USERID gg01, PASSWORD gg01
    ASSUMETARGETDEFS
    DISCARDFILE /home/oracle/goldengate/discards, PURGE
    DDL INCLUDE ALL
    DDLERROR DEFAULT IGNORE
    MAP US03.*, TARGET US03.*;
    

    The DDLERROR parameter prevents the replicat process from abending if there is a mismatch between the source and target environments. This should not really happen under normal circumstances; in a test environment this parameter may be required to synchronize the objects between the databases.

    Although the above configurations only replicate DML for the US03 schema, the DDL INCLUDE ALL command replicates DDL for all schemas. So if a new table is created under the US01 schema, this should be replicated to the same schema in the target database.

    DDL Implementation

    On the source side, DDL replication is implemented using a set of objects that are installed by the ddl_setup.sql script in the GGSCHEMA. These objects include tables, indexes, packages, procedures, functions, sequences, a directory and a function.

    A full description of these objects can be found here.

    DDL replication is implemented using the GGS_DDL_TRIGGER_BEFORE database trigger which is configured to fire whenever a DDL statement is executed. This trigger uses procedures the DDL_REPLICATION package to store the DDL statement and other attributes in the DDL tables.

    The most significant DDL tables are:

    • GGS_MARKER – all DDL statements executed in the database are stored in this table. CREATE statements are stored in this table.
    • GGS_DDL_HIST – only DDL statements executed by non-Oracle users are stored in this table. CREATE statements are not stored in this table.

    When the extract process identifies that a DDL statement has been executed, it generates an entry in the extract trail for the DDL statement. The exact process was not entirely clear at the time of writing. However, the likely sequence of events is:

    • DDL statement is issued within database
    • DDL trigger fires and stores statement in GoldenGate DDL tables
    • DDL operation is stored in redo log (operation 24.1)
    • Extract process captures DDL operation from redo log
    • Extract process looks up corresponding entries in GoldenGate DDL tables
    • Extract process stores DDL statement and attributes in GoldenGate trail.

    Once the DDL statement and attributes have been stored in the GoldenGate trail, they can be processed as normal by the data pump and replicat processes.

    GGSCI DDL Dumps

    The contents of the metadata stored in GGS_DDL_HIST table can be dumped using the DUMPDDL SHOW command in GGSCI. For example:

    CREATE TABLE Statement

    For example:

    CREATE TABLE t200 (c1 NUMBER);
    

    No entry is created in GGS_DDL_HIST for CREATE statements so no output is generated by DUMPDDL SHOW

    ALTER TABLE Statement

    For example:

    ALTER TABLE t200 ADD c2 VARCHAR2(30);
    

    The following output was generated by DUMPDDL SHOW for the above statement:

    [oracle@vm4 goldengate]$ ggsci
    
    GGSCI (vm4.juliandyke.com) 1> DBLOGIN USERID us03 PASSWORD us03
    
    GGSCI (vm4.juliandyke.com) 2> DUMPDDL SHOW
    
    *** Dumping DDL Metadata for DDL sequence [1572]...
    Time of capture                   = Before DDL
    Time of DDL operation             = 2013-04-14 09:44:32
    DDL operation (maybe partial)     = [ALTER TABLE t200 ADD c2 VARCHAR2(30) ]
    Start SCN of DDL operation        = 2034347
    DDL operation type                = ALTER
    Object type                       = TABLE
    DB Blocksize                      = 8192
    
    Object owner                      = US03
    Object name                       = T200
    Object ID                         = 77210
    Base object owner                 = US03
    Base object name                  = T200
    Data object ID                    = 77210
    
    Object valid                      = VALID
    Clustered columns                 =
    Log group exists                  = 0
    Subpartition                      = NO
    Partition                         = NO
    Total number of columns           = 1
    Number of columns used            = 1
    
    Column #1, name                   = C1
    Column #1, ID                     = 1
    Column #1, type                   = 2
    Column #1, length                 = 22
    Column #1, is NOT NULL            = 1
    Column #1, precision              =
    Column #1, scale                  =
    Column #1, charset ID             = 0
    Column #1, charset form           = 0
    Column #1, alternate column ID    = 1
    Column #1, alternate name         = C1
    Column #1, alternate type         = NUMBER
    Column #1, alternate precision    =
    Column #1, alternate char used    =
    Column #1, alternate XML type     = 0
    
    Finished displaying metadata information (sequence number [1572], DDL history table [gg01.GGS_DDL_HIST]).
    

    Note that in this example table initially only contained one column.

    DROP TABLE Statement

    For example:

    DROP TABLE t200;
    

    The following output was generated by DUMPDDL SHOW for the above statement:

    *** Dumping DDL Metadata for DDL sequence [1573]...
    Time of capture                   = Before DDL
    Time of DDL operation             = 2013-04-14 09:52:28
    DDL operation (maybe partial)     = [DROP TABLE t200 ]
    Start SCN of DDL operation        = 2034666
    DDL operation type                = DROP
    Object type                       = TABLE
    DB Blocksize                      = 8192
    
    Object owner                      = US03
    Object name                       = T200
    Object ID                         = 77210
    Data object ID                    = 77210
    
    Object valid                      = VALID
    Clustered columns                 =
    Log group exists                  = 0
    Subpartition                      = NO
    Partition                         = NO
    Total number of columns           = 2
    Number of columns used            = 2
    
    Column #1, name                   = C1
    Column #1, ID                     = 1
    Column #1, type                   = 2
    Column #1, length                 = 22
    Column #1, is NOT NULL            = 1
    Column #1, precision              =
    Column #1, scale                  =
    Column #1, charset ID             = 0
    Column #1, charset form           = 0
    Column #1, alternate column ID    = 1
    Column #1, alternate name         = C1
    Column #1, alternate type         = NUMBER
    Column #1, alternate precision    =
    Column #1, alternate char used    =
    Column #1, alternate XML type     = 0
    Column #2, name                   = C2
    Column #2, ID                     = 2
    Column #2, type                   = 1
    Column #2, length                 = 30
    Column #2, is NOT NULL            = 1
    Column #2, precision              =
    Column #2, scale                  =
    Column #2, charset ID             = 178
    Column #2, charset form           = 1
    Column #2, alternate column ID    = 2
    Column #2, alternate name         = C2
    Column #2, alternate type         = VARCHAR2
    Column #2, alternate precision    =
    Column #2, alternate char used    = B
    Column #2, alternate XML type     = 0
    
    Finished displaying metadata information (sequence number [1573], DDL history table [gg01.GGS_DDL_HIST]).
    

    In this example, the table contained two columns before it was deleted.

    Logdump DDL Dumps

    GoldenGate trails can be dumped using the logdump utility which is supplied with GoldenGate software.

    CREATE TABLE Statement

    For example:

    CREATE TABLE t200 (c1 NUMBER);
    

    The above statement is stored as follows in the GoldenGate trail:

    TokenID x47 'G' Record Header    Info x01  Length 1305
    TokenID x48 'H' GHDR             Info x00  Length   35
    TokenID x44 'D' Data             Info x00  Length 1165
    TokenID x54 'T' GGS Tokens       Info x00  Length   85
    TokenID x5a 'Z' Record Trailer   Info x01  Length 1305
    
    Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)
    UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
    RecLength  :  1165  (x048d)   IO Time    : 2013/04/14 09:43:42.000.000
    IOType     :   160  (xa0)     OrigNode   :     0  (x00)
    TransInd   :     .  (x03)     FormatType :     R  (x52)
    SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
    AuditRBA   :          0       AuditPos   : 0
    Continued  :     N  (x00)     RecCount   :     1  (x01)
    
    2013/04/14 09:43:42.000.000 DDLOP                Len  1165 RBA 5041
    Name:
    After  Image:                                             Partition 0   G  s
     2c43 353d 2731 3537 3127 2c2c 4237 3d27 3135 3731 | ,C5='1571',,B7='1571
     272c 2c42 323d 2727 2c2c 4233 3d27 5553 3033 272c | ',,B2='',,B3='US03',
     2c42 343d 2754 3230 3027 2c2c 4331 323d 2727 2c2c | ,B4='T200',,C12='',,
     4331 333d 2727 2c2c 4235 3d27 5441 424c 4527 2c2c | C13='',,B5='TABLE',,
     4236 3d27 4352 4541 5445 272c 2c42 383d 2747 4730 | B6='CREATE',,B8='GG0
     312e 4747 535f 4444 4c5f 4849 5354 272c 2c42 393d | 1.GGS_DDL_HIST',,B9=
     2755 5330 3327 2c2c 4337 3d27 3131 2e32 2e30 2e33 | 'US03',,C7='11.2.0.3
     2e30 272c 2c43 383d 2731 312e 322e 302e 302e 3027 | .0',,C8='11.2.0.0.0'
     2c2c 4339 3d27 272c 2c43 3130 3d27 3127 2c2c 4331 | ,,C9='',,C10='1',,C1
     313d 274e 4f52 5448 272c 2c47 333d 274e 4f4e 554e | 1='NORTH',,G3='NONUN
     4951 5545 272c 2c43 3134 3d27 4e4f 272c 2c43 3135 | IQUE',,C14='NO',,C15
     3d27 4e4f 272c 2c43 3139 3d27 3137 272c 2c43 3137 | ='NO',,C19='17',,C17
     2827 3127 293d 274e 4c53 5f4c 414e 4755 4147 4527 | ('1')='NLS_LANGUAGE'
     2c2c 4331 3828 2731 2729 3d27 414d 4552 4943 414e | ,,C18('1')='AMERICAN
     272c 2c43 3137 2827 3227 293d 274e 4c53 5f54 4552 | ',,C17('2')='NLS_TER
     5249 544f 5259 272c 2c43 3138 2827 3227 293d 2741 | RITORY',,C18('2')='A
     4d45 5249 4341 272c 2c43 3137 2827 3327 293d 274e | MERICA',,C17('3')='N
     4c53 5f43 5552 5245 4e43 5927 2c2c 4331 3828 2733 | LS_CURRENCY',,C18('3
     2729 3d27 2427 2c2c 4331 3728 2734 2729 3d27 4e4c | ')='$',,C17('4')='NL
     535f 4953 4f5f 4355 5252 454e 4359 272c 2c43 3138 | S_ISO_CURRENCY',,C18
     2827 3427 293d 2741 4d45 5249 4341 272c 2c43 3137 | ('4')='AMERICA',,C17
     2827 3527 293d 274e 4c53 5f4e 554d 4552 4943 5f43 | ('5')='NLS_NUMERIC_C
     4841 5241 4354 4552 5327 2c2c 4331 3828 2735 2729 | HARACTERS',,C18('5')
     3d27 2e5c 2c27 2c2c 4331 3728 2736 2729 3d27 4e4c | ='.\,',,C17('6')='NL
     535f 4341 4c45 4e44 4152 272c 2c43 3138 2827 3627 | S_CALENDAR',,C18('6'
     293d 2747 5245 474f 5249 414e 272c 2c43 3137 2827 | )='GREGORIAN',,C17('
     3727 293d 274e 4c53 5f44 4154 455f 464f 524d 4154 | 7')='NLS_DATE_FORMAT
     272c 2c43 3138 2827 3727 293d 2744 442d 4d4f 4e2d | ',,C18('7')='DD-MON-
     5252 272c 2c43 3137 2827 3827 293d 274e 4c53 5f44 | RR',,C17('8')='NLS_D
     4154 455f 4c41 4e47 5541 4745 272c 2c43 3138 2827 | ATE_LANGUAGE',,C18('
     3827 293d 2745 4e47 4c49 5348 272c 2c43 3137 2827 | 8')='ENGLISH',,C17('
     3927 293d 274e 4c53 5f53 4f52 5427 2c2c 4331 3828 | 9')='NLS_SORT',,C18(
     2739 2729 3d27 4249 4e41 5259 272c 2c43 3137 2827 | '9')='BINARY',,C17('
     3130 2729 3d27 4e4c 535f 5449 4d45 5f46 4f52 4d41 | 10')='NLS_TIME_FORMA
     5427 2c2c 4331 3828 2731 3027 293d 2748 482e 4d49 | T',,C18('10')='HH.MI
     2e53 5358 4646 2041 4d27 2c2c 4331 3728 2731 3127 | .SSXFF AM',,C17('11'
     293d 274e 4c53 5f54 494d 4553 5441 4d50 5f46 4f52 | )='NLS_TIMESTAMP_FOR
     4d41 5427 2c2c 4331 3828 2731 3127 293d 2744 442d | MAT',,C18('11')='DD-
     4d4f 4e2d 5252 2048 482e 4d49 2e53 5358 4646 2041 | MON-RR HH.MI.SSXFF A
     4d27 2c2c 4331 3728 2731 3227 293d 274e 4c53 5f54 | M',,C17('12')='NLS_T
     494d 455f 545a 5f46 4f52 4d41 5427 2c2c 4331 3828 | IME_TZ_FORMAT',,C18(
     2731 3227 293d 2748 482e 4d49 2e53 5358 4646 2041 | '12')='HH.MI.SSXFF A
     4d20 545a 5227 2c2c 4331 3728 2731 3327 293d 274e | M TZR',,C17('13')='N
     4c53 5f54 494d 4553 5441 4d50 5f54 5a5f 464f 524d | LS_TIMESTAMP_TZ_FORM
     4154 272c 2c43 3138 2827 3133 2729 3d27 4444 2d4d | AT',,C18('13')='DD-M
     4f4e 2d52 5220 4848 2e4d 492e 5353 5846 4620 414d | ON-RR HH.MI.SSXFF AM
     2054 5a52 272c 2c43 3137 2827 3134 2729 3d27 4e4c |  TZR',,C17('14')='NL
     535f 4455 414c 5f43 5552 5245 4e43 5927 2c2c 4331 | S_DUAL_CURRENCY',,C1
     3828 2731 3427 293d 2724 272c 2c43 3137 2827 3135 | 8('14')='$',,C17('15
     2729 3d27 4e4c 535f 434f 4d50 272c 2c43 3138 2827 | ')='NLS_COMP',,C18('
     3135 2729 3d27 4249 4e41 5259 272c 2c43 3137 2827 | 15')='BINARY',,C17('
     3136 2729 3d27 4e4c 535f 4c45 4e47 5448 5f53 454d | 16')='NLS_LENGTH_SEM
     414e 5449 4353 272c 2c43 3138 2827 3136 2729 3d27 | ANTICS',,C18('16')='
     4259 5445 272c 2c43 3137 2827 3137 2729 3d27 4e4c | BYTE',,C17('17')='NL
     535f 4e43 4841 525f 434f 4e56 5f45 5843 5027 2c2c | S_NCHAR_CONV_EXCP',,
     4331 3828 2731 3727 293d 2746 414c 5345 272c 2c47 | C18('17')='FALSE',,G
     3134 3d27 5553 3033 272c 2c43 313d 4352 4541 5445 | 14='US03',,C1=CREATE
     2054 4142 4c45 2074 3230 3020 2863 3120 4e55 4d42 |  TABLE t200 (c1 NUMB
     4552 2920 00                                      | ER) .
    
    GGS tokens:
    TokenID x52 'R' ORAROWID         Info x00  Length   20
     4141 4153 6338 4141 4641 4141 4145 3241 414a 0001 | AAASc8AAFAAAAE2AAJ..
    TokenID x44 'D' DDL              Info x00  Length   33
     5553 3033 0054 3230 3000 3131 2e32 2e30 2e33 2e30 | US03.T200.11.2.0.3.0
     0031 312e 322e 302e 302e 3000 4e                  | .11.2.0.0.0.N
    TokenID x4c 'L' LOGCSN           Info x00  Length    7
     3230 3334 3332 33                                 | 2034323
    TokenID x36 '6' TRANID           Info x00  Length    9
     332e 3238 2e31 3136 33                            | 3.28.1163
    

    ALTER TABLE Statement

    For example:

    ALTER TABLE t200 ADD c2 VARCHAR2(30);
    

    The above statement is stored as follows in the GoldenGate trail:

    TokenID x47 'G' Record Header    Info x01  Length 1321
    TokenID x48 'H' GHDR             Info x00  Length   35
    TokenID x44 'D' Data             Info x00  Length 1181
    TokenID x54 'T' GGS Tokens       Info x00  Length   85
    TokenID x5a 'Z' Record Trailer   Info x01  Length 1321
    
    Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)
    UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
    RecLength  :  1181  (x049d)   IO Time    : 2013/04/14 09:44:32.000.000
    IOType     :   160  (xa0)     OrigNode   :     0  (x00)
    TransInd   :     .  (x03)     FormatType :     R  (x52)
    SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
    AuditRBA   :          0       AuditPos   : 0
    Continued  :     N  (x00)     RecCount   :     1  (x01)
    
    2013/04/14 09:44:32.000.000 DDLOP                Len  1181 RBA 6346
    Name:
    After  Image:                                             Partition 0   G  s
     2c43 353d 2731 3537 3227 2c2c 4237 3d27 3135 3732 | ,C5='1572',,B7='1572
     272c 2c42 323d 2737 3732 3130 272c 2c42 333d 2755 | ',,B2='77210',,B3='U
     5330 3327 2c2c 4234 3d27 5432 3030 272c 2c43 3132 | S03',,B4='T200',,C12
     3d27 272c 2c43 3133 3d27 272c 2c42 353d 2754 4142 | ='',,C13='',,B5='TAB
     4c45 272c 2c42 363d 2741 4c54 4552 272c 2c42 383d | LE',,B6='ALTER',,B8=
     2747 4730 312e 4747 535f 4444 4c5f 4849 5354 272c | 'GG01.GGS_DDL_HIST',
     2c42 393d 2755 5330 3327 2c2c 4337 3d27 3131 2e32 | ,B9='US03',,C7='11.2
     2e30 2e33 2e30 272c 2c43 383d 2731 312e 322e 302e | .0.3.0',,C8='11.2.0.
     302e 3027 2c2c 4339 3d27 5641 4c49 4427 2c2c 4331 | 0.0',,C9='VALID',,C1
     303d 2731 272c 2c43 3131 3d27 4e4f 5254 4827 2c2c | 0='1',,C11='NORTH',,
     4733 3d27 4e4f 4e55 4e49 5155 4527 2c2c 4331 343d | G3='NONUNIQUE',,C14=
     274e 4f27 2c2c 4331 353d 274e 4f27 2c2c 4331 393d | 'NO',,C15='NO',,C19=
     2731 3727 2c2c 4331 3728 2731 2729 3d27 4e4c 535f | '17',,C17('1')='NLS_
     4c41 4e47 5541 4745 272c 2c43 3138 2827 3127 293d | LANGUAGE',,C18('1')=
     2741 4d45 5249 4341 4e27 2c2c 4331 3728 2732 2729 | 'AMERICAN',,C17('2')
     3d27 4e4c 535f 5445 5252 4954 4f52 5927 2c2c 4331 | ='NLS_TERRITORY',,C1
     3828 2732 2729 3d27 414d 4552 4943 4127 2c2c 4331 | 8('2')='AMERICA',,C1
     3728 2733 2729 3d27 4e4c 535f 4355 5252 454e 4359 | 7('3')='NLS_CURRENCY
     272c 2c43 3138 2827 3327 293d 2724 272c 2c43 3137 | ',,C18('3')='$',,C17
     2827 3427 293d 274e 4c53 5f49 534f 5f43 5552 5245 | ('4')='NLS_ISO_CURRE
     4e43 5927 2c2c 4331 3828 2734 2729 3d27 414d 4552 | NCY',,C18('4')='AMER
     4943 4127 2c2c 4331 3728 2735 2729 3d27 4e4c 535f | ICA',,C17('5')='NLS_
     4e55 4d45 5249 435f 4348 4152 4143 5445 5253 272c | NUMERIC_CHARACTERS',
     2c43 3138 2827 3527 293d 272e 5c2c 272c 2c43 3137 | ,C18('5')='.\,',,C17
     2827 3627 293d 274e 4c53 5f43 414c 454e 4441 5227 | ('6')='NLS_CALENDAR'
     2c2c 4331 3828 2736 2729 3d27 4752 4547 4f52 4941 | ,,C18('6')='GREGORIA
     4e27 2c2c 4331 3728 2737 2729 3d27 4e4c 535f 4441 | N',,C17('7')='NLS_DA
     5445 5f46 4f52 4d41 5427 2c2c 4331 3828 2737 2729 | TE_FORMAT',,C18('7')
     3d27 4444 2d4d 4f4e 2d52 5227 2c2c 4331 3728 2738 | ='DD-MON-RR',,C17('8
     2729 3d27 4e4c 535f 4441 5445 5f4c 414e 4755 4147 | ')='NLS_DATE_LANGUAG
     4527 2c2c 4331 3828 2738 2729 3d27 454e 474c 4953 | E',,C18('8')='ENGLIS
     4827 2c2c 4331 3728 2739 2729 3d27 4e4c 535f 534f | H',,C17('9')='NLS_SO
     5254 272c 2c43 3138 2827 3927 293d 2742 494e 4152 | RT',,C18('9')='BINAR
     5927 2c2c 4331 3728 2731 3027 293d 274e 4c53 5f54 | Y',,C17('10')='NLS_T
     494d 455f 464f 524d 4154 272c 2c43 3138 2827 3130 | IME_FORMAT',,C18('10
     2729 3d27 4848 2e4d 492e 5353 5846 4620 414d 272c | ')='HH.MI.SSXFF AM',
     2c43 3137 2827 3131 2729 3d27 4e4c 535f 5449 4d45 | ,C17('11')='NLS_TIME
     5354 414d 505f 464f 524d 4154 272c 2c43 3138 2827 | STAMP_FORMAT',,C18('
     3131 2729 3d27 4444 2d4d 4f4e 2d52 5220 4848 2e4d | 11')='DD-MON-RR HH.M
     492e 5353 5846 4620 414d 272c 2c43 3137 2827 3132 | I.SSXFF AM',,C17('12
     2729 3d27 4e4c 535f 5449 4d45 5f54 5a5f 464f 524d | ')='NLS_TIME_TZ_FORM
     4154 272c 2c43 3138 2827 3132 2729 3d27 4848 2e4d | AT',,C18('12')='HH.M
     492e 5353 5846 4620 414d 2054 5a52 272c 2c43 3137 | I.SSXFF AM TZR',,C17
     2827 3133 2729 3d27 4e4c 535f 5449 4d45 5354 414d | ('13')='NLS_TIMESTAM
     505f 545a 5f46 4f52 4d41 5427 2c2c 4331 3828 2731 | P_TZ_FORMAT',,C18('1
     3327 293d 2744 442d 4d4f 4e2d 5252 2048 482e 4d49 | 3')='DD-MON-RR HH.MI
     2e53 5358 4646 2041 4d20 545a 5227 2c2c 4331 3728 | .SSXFF AM TZR',,C17(
     2731 3427 293d 274e 4c53 5f44 5541 4c5f 4355 5252 | '14')='NLS_DUAL_CURR
     454e 4359 272c 2c43 3138 2827 3134 2729 3d27 2427 | ENCY',,C18('14')='$'
     2c2c 4331 3728 2731 3527 293d 274e 4c53 5f43 4f4d | ,,C17('15')='NLS_COM
     5027 2c2c 4331 3828 2731 3527 293d 2742 494e 4152 | P',,C18('15')='BINAR
     5927 2c2c 4331 3728 2731 3627 293d 274e 4c53 5f4c | Y',,C17('16')='NLS_L
     454e 4754 485f 5345 4d41 4e54 4943 5327 2c2c 4331 | ENGTH_SEMANTICS',,C1
     3828 2731 3627 293d 2742 5954 4527 2c2c 4331 3728 | 8('16')='BYTE',,C17(
     2731 3727 293d 274e 4c53 5f4e 4348 4152 5f43 4f4e | '17')='NLS_NCHAR_CON
     565f 4558 4350 272c 2c43 3138 2827 3137 2729 3d27 | V_EXCP',,C18('17')='
     4641 4c53 4527 2c2c 4731 343d 2755 5330 3327 2c2c | FALSE',,G14='US03',,
     4331 3d41 4c54 4552 2054 4142 4c45 2074 3230 3020 | C1=ALTER TABLE t200
     4144 4420 6332 2056 4152 4348 4152 3228 3330 2920 | ADD c2 VARCHAR2(30)
     00                                                | .
    
    GGS tokens:
    TokenID x52 'R' ORAROWID         Info x00  Length   20
     4141 4153 6338 4141 4641 4141 4145 7941 4142 0001 | AAASc8AAFAAAAEyAAB..
    TokenID x44 'D' DDL              Info x00  Length   33
     5553 3033 0054 3230 3000 3131 2e32 2e30 2e33 2e30 | US03.T200.11.2.0.3.0
     0031 312e 322e 302e 302e 3000 4e                  | .11.2.0.0.0.N
    TokenID x4c 'L' LOGCSN           Info x00  Length    7
     3230 3334 3335 37                                 | 2034357
    TokenID x36 '6' TRANID           Info x00  Length    9
     382e 3238 2e31 3137 37                            | 8.28.1177
    

    DROP TABLE Statement

    For example:

    DROP TABLE t200;
    

    The above statement is stored as follows in the GoldenGate trail:

    TokenID x47 'G' Record Header    Info x01  Length 1298
    TokenID x48 'H' GHDR             Info x00  Length   35
    TokenID x44 'D' Data             Info x00  Length 1158
    TokenID x54 'T' GGS Tokens       Info x00  Length   85
    TokenID x5a 'Z' Record Trailer   Info x01  Length 1298
    
    Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)
    UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
    RecLength  :  1158  (x0486)   IO Time    : 2013/04/14 09:52:28.000.000
    IOType     :   160  (xa0)     OrigNode   :     0  (x00)
    TransInd   :     .  (x03)     FormatType :     R  (x52)
    SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
    AuditRBA   :          0       AuditPos   : 0
    Continued  :     N  (x00)     RecCount   :     1  (x01)
    
    2013/04/14 09:52:28.000.000 DDLOP                Len  1158 RBA 7667
    Name:
    After  Image:                                             Partition 0   G  s
     2c43 353d 2731 3537 3327 2c2c 4237 3d27 3135 3733 | ,C5='1573',,B7='1573
     272c 2c42 323d 2737 3732 3130 272c 2c42 333d 2755 | ',,B2='77210',,B3='U
     5330 3327 2c2c 4234 3d27 5432 3030 272c 2c43 3132 | S03',,B4='T200',,C12
     3d27 272c 2c43 3133 3d27 272c 2c42 353d 2754 4142 | ='',,C13='',,B5='TAB
     4c45 272c 2c42 363d 2744 524f 5027 2c2c 4238 3d27 | LE',,B6='DROP',,B8='
     4747 3031 2e47 4753 5f44 444c 5f48 4953 5427 2c2c | GG01.GGS_DDL_HIST',,
     4239 3d27 5553 3033 272c 2c43 373d 2731 312e 322e | B9='US03',,C7='11.2.
     302e 332e 3027 2c2c 4338 3d27 3131 2e32 2e30 2e30 | 0.3.0',,C8='11.2.0.0
     2e30 272c 2c43 393d 2756 414c 4944 272c 2c43 3130 | .0',,C9='VALID',,C10
     3d27 3127 2c2c 4331 313d 274e 4f52 5448 272c 2c47 | ='1',,C11='NORTH',,G
     333d 274e 4f4e 554e 4951 5545 272c 2c43 3134 3d27 | 3='NONUNIQUE',,C14='
     4e4f 272c 2c43 3135 3d27 4e4f 272c 2c43 3139 3d27 | NO',,C15='NO',,C19='
     3137 272c 2c43 3137 2827 3127 293d 274e 4c53 5f4c | 17',,C17('1')='NLS_L
     414e 4755 4147 4527 2c2c 4331 3828 2731 2729 3d27 | ANGUAGE',,C18('1')='
     414d 4552 4943 414e 272c 2c43 3137 2827 3227 293d | AMERICAN',,C17('2')=
     274e 4c53 5f54 4552 5249 544f 5259 272c 2c43 3138 | 'NLS_TERRITORY',,C18
     2827 3227 293d 2741 4d45 5249 4341 272c 2c43 3137 | ('2')='AMERICA',,C17
     2827 3327 293d 274e 4c53 5f43 5552 5245 4e43 5927 | ('3')='NLS_CURRENCY'
     2c2c 4331 3828 2733 2729 3d27 2427 2c2c 4331 3728 | ,,C18('3')='$',,C17(
     2734 2729 3d27 4e4c 535f 4953 4f5f 4355 5252 454e | '4')='NLS_ISO_CURREN
     4359 272c 2c43 3138 2827 3427 293d 2741 4d45 5249 | CY',,C18('4')='AMERI
     4341 272c 2c43 3137 2827 3527 293d 274e 4c53 5f4e | CA',,C17('5')='NLS_N
     554d 4552 4943 5f43 4841 5241 4354 4552 5327 2c2c | UMERIC_CHARACTERS',,
     4331 3828 2735 2729 3d27 2e5c 2c27 2c2c 4331 3728 | C18('5')='.\,',,C17(
     2736 2729 3d27 4e4c 535f 4341 4c45 4e44 4152 272c | '6')='NLS_CALENDAR',
     2c43 3138 2827 3627 293d 2747 5245 474f 5249 414e | ,C18('6')='GREGORIAN
     272c 2c43 3137 2827 3727 293d 274e 4c53 5f44 4154 | ',,C17('7')='NLS_DAT
     455f 464f 524d 4154 272c 2c43 3138 2827 3727 293d | E_FORMAT',,C18('7')=
     2744 442d 4d4f 4e2d 5252 272c 2c43 3137 2827 3827 | 'DD-MON-RR',,C17('8'
     293d 274e 4c53 5f44 4154 455f 4c41 4e47 5541 4745 | )='NLS_DATE_LANGUAGE
     272c 2c43 3138 2827 3827 293d 2745 4e47 4c49 5348 | ',,C18('8')='ENGLISH
     272c 2c43 3137 2827 3927 293d 274e 4c53 5f53 4f52 | ',,C17('9')='NLS_SOR
     5427 2c2c 4331 3828 2739 2729 3d27 4249 4e41 5259 | T',,C18('9')='BINARY
     272c 2c43 3137 2827 3130 2729 3d27 4e4c 535f 5449 | ',,C17('10')='NLS_TI
     4d45 5f46 4f52 4d41 5427 2c2c 4331 3828 2731 3027 | ME_FORMAT',,C18('10'
     293d 2748 482e 4d49 2e53 5358 4646 2041 4d27 2c2c | )='HH.MI.SSXFF AM',,
     4331 3728 2731 3127 293d 274e 4c53 5f54 494d 4553 | C17('11')='NLS_TIMES
     5441 4d50 5f46 4f52 4d41 5427 2c2c 4331 3828 2731 | TAMP_FORMAT',,C18('1
     3127 293d 2744 442d 4d4f 4e2d 5252 2048 482e 4d49 | 1')='DD-MON-RR HH.MI
     2e53 5358 4646 2041 4d27 2c2c 4331 3728 2731 3227 | .SSXFF AM',,C17('12'
     293d 274e 4c53 5f54 494d 455f 545a 5f46 4f52 4d41 | )='NLS_TIME_TZ_FORMA
     5427 2c2c 4331 3828 2731 3227 293d 2748 482e 4d49 | T',,C18('12')='HH.MI
     2e53 5358 4646 2041 4d20 545a 5227 2c2c 4331 3728 | .SSXFF AM TZR',,C17(
     2731 3327 293d 274e 4c53 5f54 494d 4553 5441 4d50 | '13')='NLS_TIMESTAMP
     5f54 5a5f 464f 524d 4154 272c 2c43 3138 2827 3133 | _TZ_FORMAT',,C18('13
     2729 3d27 4444 2d4d 4f4e 2d52 5220 4848 2e4d 492e | ')='DD-MON-RR HH.MI.
     5353 5846 4620 414d 2054 5a52 272c 2c43 3137 2827 | SSXFF AM TZR',,C17('
     3134 2729 3d27 4e4c 535f 4455 414c 5f43 5552 5245 | 14')='NLS_DUAL_CURRE
     4e43 5927 2c2c 4331 3828 2731 3427 293d 2724 272c | NCY',,C18('14')='$',
     2c43 3137 2827 3135 2729 3d27 4e4c 535f 434f 4d50 | ,C17('15')='NLS_COMP
     272c 2c43 3138 2827 3135 2729 3d27 4249 4e41 5259 | ',,C18('15')='BINARY
     272c 2c43 3137 2827 3136 2729 3d27 4e4c 535f 4c45 | ',,C17('16')='NLS_LE
     4e47 5448 5f53 454d 414e 5449 4353 272c 2c43 3138 | NGTH_SEMANTICS',,C18
     2827 3136 2729 3d27 4259 5445 272c 2c43 3137 2827 | ('16')='BYTE',,C17('
     3137 2729 3d27 4e4c 535f 4e43 4841 525f 434f 4e56 | 17')='NLS_NCHAR_CONV
     5f45 5843 5027 2c2c 4331 3828 2731 3727 293d 2746 | _EXCP',,C18('17')='F
     414c 5345 272c 2c47 3134 3d27 5359 5327 2c2c 4331 | ALSE',,G14='SYS',,C1
     3d44 524f 5020 5441 424c 4520 7432 3030 2000      | =DROP TABLE t200 .
    
    GGS tokens:
    TokenID x52 'R' ORAROWID         Info x00  Length   20
     4141 4153 6338 4141 4641 4141 4145 7941 4144 0001 | AAASc8AAFAAAAEyAAD..
    TokenID x44 'D' DDL              Info x00  Length   33
     5553 3033 0054 3230 3000 3131 2e32 2e30 2e33 2e30 | US03.T200.11.2.0.3.0
     0031 312e 322e 302e 302e 3000 4e                  | .11.2.0.0.0.N
    TokenID x4c 'L' LOGCSN           Info x00  Length    7
     3230 3334 3639 31                                 | 2034691
    TokenID x36 '6' TRANID           Info x00  Length    9
     382e 3237 2e31 3137 38                            | 8.27.1178
    

    TrailAnalyzer DDL Dumps

    This section shows how the DDL statements are stored in the GoldenGate trail using TrailAnalyzer output.

    CREATE TABLE Statement

    For example:

    CREATE TABLE t200 (c1 NUMBER);
    

    The above statement is stored as follows in the GoldenGate trail:

    # Header: Type=47 (G) Flag=1 Len=1305 (4 bytes)
    <47>
      # Body
      # Header: Type=48 (H) Flag=0 Len=35 (4 bytes)
      <47_48>
        # Row Header (35 bytes)
        # Flags - 0xA0 DDL Statement
        # Timestamp 2013:04:14 09:43:42 000000 ms
      
      # Header: Type=44 (D) Flag=0 Len=1165 (4 bytes)
      <47_44>
        # Row Data (1165 bytes)
          C5='1571'
          B7='1571'
          B2=''
          B3='US03'
          B4='T200'
          C12=''
          C13=''
          B5='TABLE'
          B6='CREATE'
          B8='GG01.GGS_DDL_HIST'
          B9='US03'
          C7='11.2.0.3.0'
          C8='11.2.0.0.0'
          C9=''
          C10='1'
          C11='NORTH'
          G3='NONUNIQUE'
          C14='NO'
          C15='NO'
          C19='17'
          C17('1')='NLS_LANGUAGE'
          C18('1')='AMERICAN'
          C17('2')='NLS_TERRITORY'
          C18('2')='AMERICA'
          C17('3')='NLS_CURRENCY'
          C18('3')='$'
          C17('4')='NLS_ISO_CURRENCY'
          C18('4')='AMERICA'
          C17('5')='NLS_NUMERIC_CHARACTERS'
          C18('5')='$'
          C17('6')='NLS_CALENDAR'
          C18('6')='GREGORIAN'
          C17('7')='NLS_DATE_FORMAT'
          C18('7')='DD-MON-RR'
          C17('8')='NLS_DATE_LANGUAGE'
          C18('8')='ENGLISH'
          C17('9')='NLS_SORT'
          C18('9')='BINARY'
          C17('10')='NLS_TIME_FORMAT'
          C18('10')='HH.MI.SSXFF AM'
          C17('11')='NLS_TIMESTAMP_FORMAT'
          C18('11')='DD-MON-RR HH.MI.SSXFF AM'
          C17('12')='NLS_TIME_TZ_FORMAT'
          C18('12')='HH.MI.SSXFF AM TZR'
          C17('13')='NLS_TIMESTAMP_TZ_FORMAT'
          C18('13')='DD-MON-RR HH.MI.SSXFF AM TZR'
          C17('14')='NLS_DUAL_CURRENCY'
          C18('14')='$'
          C17('15')='NLS_COMP'
          C18('15')='BINARY'
          C17('16')='NLS_LENGTH_SEMANTICS'
          C18('16')='BYTE'
          C17('17')='NLS_NCHAR_CONV_EXCP'
          C18('17')='FALSE'
          G14='US03'
          C1=CREATE TABLE t200 (c1 NUMBER) 
      
      # Header: Type=54 (T) Flag=0 Len=85 (4 bytes)
      <47_54>
        # Row Metadata (85 bytes)
        # Header: Type=52 (R) Flag=0 Len=20 (4 bytes)
        <47_54_52>
          # ROWID: 20 bytes
          AAASc8AAFAAAAE2AAJ 1
        
        # Header: Type=44 (D) Flag=0 Len=33 (4 bytes)
        <47_54_44>
          # DDL (33 bytes)
          US03
          T200
          11.2.0.3.0
          11.2.0.0.0
          N
        
        # Header: Type=4C (L) Flag=0 Len=7 (4 bytes)
        <47_54_4C>
          # Commit SCN (7 bytes)
          2034323
        
        # Header: Type=36 (6) Flag=0 Len=9 (4 bytes)
        <47_54_36>
          # Transaction ID (9 bytes)
          3.28.1163
        
      
    
    # Header: Type=5A (Z) Flag=1 Len=1305 (4 bytes)
    

    ALTER TABLE Statement

    For example:

    ALTER TABLE t200 ADD c2 VARCHAR2(30);
    

    The above statement is stored as follows in the GoldenGate trail:

    # Header: Type=47 (G) Flag=1 Len=1321 (4 bytes)
    <47>
      # Body
      # Header: Type=48 (H) Flag=0 Len=35 (4 bytes)
      <47_48>
        # Row Header (35 bytes)
        # Flags - 0xA0 DDL Statement
        # Timestamp 2013:04:14 09:44:32 000000 ms
      
      # Header: Type=44 (D) Flag=0 Len=1181 (4 bytes)
      <47_44>
        # Row Data (1181 bytes)
          C5='1572'
          B7='1572'
          B2='77210'
          B3='US03'
          B4='T200'
          C12=''
          C13=''
          B5='TABLE'
          B6='ALTER'
          B8='GG01.GGS_DDL_HIST'
          B9='US03'
          C7='11.2.0.3.0'
          C8='11.2.0.0.0'
          C9='VALID'
          C10='1'
          C11='NORTH'
          G3='NONUNIQUE'
          C14='NO'
          C15='NO'
          C19='17'
          C17('1')='NLS_LANGUAGE'
          C18('1')='AMERICAN'
          C17('2')='NLS_TERRITORY'
          C18('2')='AMERICA'
          C17('3')='NLS_CURRENCY'
          C18('3')='$'
          C17('4')='NLS_ISO_CURRENCY'
          C18('4')='AMERICA'
          C17('5')='NLS_NUMERIC_CHARACTERS'
          C18('5')='$'
          C17('6')='NLS_CALENDAR'
          C18('6')='GREGORIAN'
          C17('7')='NLS_DATE_FORMAT'
          C18('7')='DD-MON-RR'
          C17('8')='NLS_DATE_LANGUAGE'
          C18('8')='ENGLISH'
          C17('9')='NLS_SORT'
          C18('9')='BINARY'
          C17('10')='NLS_TIME_FORMAT'
          C18('10')='HH.MI.SSXFF AM'
          C17('11')='NLS_TIMESTAMP_FORMAT'
          C18('11')='DD-MON-RR HH.MI.SSXFF AM'
          C17('12')='NLS_TIME_TZ_FORMAT'
          C18('12')='HH.MI.SSXFF AM TZR'
          C17('13')='NLS_TIMESTAMP_TZ_FORMAT'
          C18('13')='DD-MON-RR HH.MI.SSXFF AM TZR'
          C17('14')='NLS_DUAL_CURRENCY'
          C18('14')='$'
          C17('15')='NLS_COMP'
          C18('15')='BINARY'
          C17('16')='NLS_LENGTH_SEMANTICS'
          C18('16')='BYTE'
          C17('17')='NLS_NCHAR_CONV_EXCP'
          C18('17')='FALSE'
          G14='US03'
          C1=ALTER TABLE t200 ADD c2 VARCHAR2(30) 
      
      # Header: Type=54 (T) Flag=0 Len=85 (4 bytes)
      <47_54>
        # Row Metadata (85 bytes)
        # Header: Type=52 (R) Flag=0 Len=20 (4 bytes)
        <47_54_52>
          # ROWID: 20 bytes
          AAASc8AAFAAAAEyAAB 1
        
        # Header: Type=44 (D) Flag=0 Len=33 (4 bytes)
        <47_54_44>
          # DDL (33 bytes)
          US03
          T200
          11.2.0.3.0
          11.2.0.0.0
          N
        
        # Header: Type=4C (L) Flag=0 Len=7 (4 bytes)
        <47_54_4C>
          # Commit SCN (7 bytes)
          2034357
        
        # Header: Type=36 (6) Flag=0 Len=9 (4 bytes)
        <47_54_36>
          # Transaction ID (9 bytes)
          8.28.1177
        
      
    
    # Header: Type=5A (Z) Flag=1 Len=1321 (4 bytes)
    

    DROP TABLE Statement

    For example:

    DROP TABLE t200;
    

    The above statement is stored as follows in the GoldenGate trail:

    # Header: Type=47 (G) Flag=1 Len=1298 (4 bytes)
    <47>
      # Body
      # Header: Type=48 (H) Flag=0 Len=35 (4 bytes)
      <47_48>
        # Row Header (35 bytes)
        # Flags - 0xA0 DDL Statement
        # Timestamp 2013:04:14 09:52:28 000000 ms
      
      # Header: Type=44 (D) Flag=0 Len=1158 (4 bytes)
      <47_44>
        # Row Data (1158 bytes)
          C5='1573'
          B7='1573'
          B2='77210'
          B3='US03'
          B4='T200'
          C12=''
          C13=''
          B5='TABLE'
          B6='DROP'
          B8='GG01.GGS_DDL_HIST'
          B9='US03'
          C7='11.2.0.3.0'
          C8='11.2.0.0.0'
          C9='VALID'
          C10='1'
          C11='NORTH'
          G3='NONUNIQUE'
          C14='NO'
          C15='NO'
          C19='17'
          C17('1')='NLS_LANGUAGE'
          C18('1')='AMERICAN'
          C17('2')='NLS_TERRITORY'
          C18('2')='AMERICA'
          C17('3')='NLS_CURRENCY'
          C18('3')='$'
          C17('4')='NLS_ISO_CURRENCY'
          C18('4')='AMERICA'
          C17('5')='NLS_NUMERIC_CHARACTERS'
          C18('5')='$'
          C17('6')='NLS_CALENDAR'
          C18('6')='GREGORIAN'
          C17('7')='NLS_DATE_FORMAT'
          C18('7')='DD-MON-RR'
          C17('8')='NLS_DATE_LANGUAGE'
          C18('8')='ENGLISH'
          C17('9')='NLS_SORT'
          C18('9')='BINARY'
          C17('10')='NLS_TIME_FORMAT'
          C18('10')='HH.MI.SSXFF AM'
          C17('11')='NLS_TIMESTAMP_FORMAT'
          C18('11')='DD-MON-RR HH.MI.SSXFF AM'
          C17('12')='NLS_TIME_TZ_FORMAT'
          C18('12')='HH.MI.SSXFF AM TZR'
          C17('13')='NLS_TIMESTAMP_TZ_FORMAT'
          C18('13')='DD-MON-RR HH.MI.SSXFF AM TZR'
          C17('14')='NLS_DUAL_CURRENCY'
          C18('14')='$'
          C17('15')='NLS_COMP'
          C18('15')='BINARY'
          C17('16')='NLS_LENGTH_SEMANTICS'
          C18('16')='BYTE'
          C17('17')='NLS_NCHAR_CONV_EXCP'
          C18('17')='FALSE'
          G14='SYS'
          C1=DROP TABLE t200 
      
      # Header: Type=54 (T) Flag=0 Len=85 (4 bytes)
      <47_54>
        # Row Metadata (85 bytes)
        # Header: Type=52 (R) Flag=0 Len=20 (4 bytes)
        <47_54_52>
          # ROWID: 20 bytes
          AAASc8AAFAAAAEyAAD 1
        
        # Header: Type=44 (D) Flag=0 Len=33 (4 bytes)
        <47_54_44>
          # DDL (33 bytes)
          US03
          T200
          11.2.0.3.0
          11.2.0.0.0
          N
        
        # Header: Type=4C (L) Flag=0 Len=7 (4 bytes)
        <47_54_4C>
          # Commit SCN (7 bytes)
          2034691
        
        # Header: Type=36 (6) Flag=0 Len=9 (4 bytes)
        <47_54_36>
          # Transaction ID (9 bytes)
          8.27.1178
        
      
    
    # Header: Type=5A (Z) Flag=1 Len=1298 (4 bytes)
    

    End of post

    Leave a Reply

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

    *

    HTML tags are not allowed.