The following procedure summarizes Oracle GoldenGate DDL tracing
The page 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 page assumes that the source and target databases have already been configured as described here.
This configuration uses following hosts and databases.
Source | Target | |
---|---|---|
Hostname | vm4 | vm5 |
Database Name | NORTH | SOUTH |
The configuration includes the following on both nodes:
The GoldenGate process names are:
Source | Target | |
---|---|---|
Extract | ex1 | - |
Data Pump | dp1 | - |
Replicat | - | rep1 |
The DDL trigger is already instrumented with trace statements. It is also possible to enable SQL trace within the trigger.
By default trace is written to a file called ggs_ddl_trace.log which is in the directory specified by the USER_DUMP_DEST database parameter.
The DDL trace level determines the amount of trace written by the DDL trigger. The default level is 0 which is writes a minimal amount of trace. Additional trace can be written by setting the trace level to 1 or 2. The documentation recommends that the trace level is only changed under supervision of Oracle support.
Trace messages are written to ggs_ddl_trace.log in the directory specified by the USER_DUMP_DEST database parameter.
To determine the current trace level, use the following query:
[oracle@vm4]$ sqlplus gg01/gg01 SQL> SELECT value FROM ggs_setup WHERE property = 'DDL_TRACE_LEVEL'; VALUE --------------------------------------------------------------------------- 0
To modify the trace level, use the ddl_tracelevel.sql script.
For example to set the trace level to 1 use:
[oracle@vm4]$ cd /home/oracle/goldengate [oracle@vm4]$ sqlplus gg01/gg01 SQL> @ddl_tracelevel Set DDL replication trace level script. Please enter GoldenGate schema name (schema for GoldenGate database objects). NOTE: GoldenGate schema must be created prior to running this script. Enter GoldenGate schema name (read above first):GG01 Please enter trace level:1 PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Script complete, running verification script...
The script calls ddl_status.sql with the GGSCHEMA name to verify the trace configuration. The output from this script has been omitted for clarity.
Check the current trace level using:
[oracle@vm4]$ sqlplus gg01/gg01 SQL> SELECT value FROM ggs_setup WHERE property = 'DDL_TRACE_LEVEL'; VALUE --------------------------------------------------------------------------- 1
To reset the trace level, run ddl_tracelevel.sql again specifying a value of 0.
SQL trace can also be enabled for the DDL trigger. There are probably more sophisticated ways to enable SQL trace in newer versions. The GoldenGate supplied trace relies on the SQL_TRACE parameter which enables event 10046 level 1. This trace does not include bind variables, wait events or plan statistics (Oracle 11.2 and above).
Trace messages are written to the server process trace file in the directory specified by the USER_DUMP_DEST parameter. For example NORTH_ora_6589.trc.
To check if DDL SQL tracing is currently enabled use:
[oracle@vm4]$ sqlplus gg01/gg01 SQL> SELECT value FROM ggs_setup WHERE property = 'DDL_SQL_TRACING'; VALUE --------------------------------------------------------------------------- 0
If the value is 0, then trace is disabled; if the value is 1 then trace is enabled.
[oracle@vm4]$ cd /home/oracle/goldengate [oracle@vm4]$ sqlplus gg01/gg01 SQL> @ddl_trace_on 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. Enter GoldenGate schema name:GG01 You will be prompted for your own tracing identifier (for files in UDUMP directory, this session only) Enter your own tracing identifier:DDL Working, please wait ... Spooling to file ddl_trace_on_spool.txt PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. 1 row updated. Commit complete. Session altered. Script complete, running verification script...
The script prompts for an identifier. This is used as the TRACEFILE_IDENTIFIER parameter for the current session. For example:
ALTER SESSION SET tracefile_identifier='&identname';
Note that the above value will only persist for the duration of the session.
The script calls ddl_status.sql with the GGSCHEMA name to verify the trace configuration. The output from this script has been omitted for clarity.
Check the current trace level using:
[oracle@vm4]$ sqlplus gg01/gg01 SQL> SELECT value FROM ggs_setup WHERE property = 'DDL_SQL_TRACING'; VALUE --------------------------------------------------------------------------- 1
To reset the trace level, run the ddl_trace_off.sql script.
The ddl_status.sql script reports the current status of the parameters and objects required for GoldenGate DDL tracing. The script is also invoked by the ddl_tracelevel.sql, ddl_trace_on.sql and ddl_trace_off.sql scripts.
This script can be executed standalone. For example:
[oracle@vm4]$ cd /home/oracle/goldengate [oracle@vm4]$ sqlplus gg01/gg01 SQL> @ddl_status Please enter the name of a schema for the GoldenGate database objects: GG01 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 ----------------------------------- 1 DDL TRIGGER TRACE LEVEL ----------------------------------- 1 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 SQL>
The contents of the metadata stored in GGS_DDL_HIST table can be dumped using the DUMPDDL SHOW command in GGSCI.
Consider the following statement:
ALTER TABLE t200 ADD c2 VARCHAR2(30);
This can be dumped using the GGSCI DUMPDDL command as follows:
[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]).
The table was originally created using:
CREATE TABLE t200 (c1 NUMBER)
In the above example the DUMPDDL command shows the columns in the table BEFORE the ALTER TABLE statement was executed.
Output is dependent on the number of columns in the table.
Note that the output is generated from GGS_DDL_HIST. CREATE statements and excluded objects are not stored in this table, so DUMPDDL SHOW will not produce any output in these cases.