This discussion is archived
10 Replies Latest reply: Oct 12, 2012 7:23 PM by stevencallan RSS

DDL happening but not DML on new tables

916413 Newbie
Currently Being Moderated
Hi,

What i having troubling is that successful DDL is happening on new tables but whenever i m trying to do any new DML on those tables is doesn't replicate ?

Below the details

@Source
GGSCI (prod.naveed.com) 1> view params ext1

EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
EXTTRAIL /home/oracle/golden_gate/dirdat/lt
DDL INCLUDE ALL 
ddloptions getreplicates, getapplops,addtrandata, report
TABLE NAVEED.ORDER_T;
TABLE NAVEED.PRODUCT_T;
TABLE NAVEED.ORDER_LINE_T;


GGSCI (prod.naveed.com) 2> view params dpump

EXTRACT dpump
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST Test.naveed.com, MGRPORT 7809
RMTTRAIL /home/oracle/golden_gate/dirdat/rt
PASSTHRU
TABLE NAVEED.ORDER_T;
TABLE NAVEED.PRODUCT_T;
TABLE NAVEED.ORDER_LINE_T;
@Target
GGSCI (Test.naveed.com) 11> view params rep1

REPLICAT rep1
-- This starts the macro
MACRO #exception_handler
BEGIN
, TARGET ggs_owner.exceptions
, COLMAP ( rep_name = "REP1"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
-- This ends the macro
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
DISCARDFILE /home/oracle/golden_gate/dirrpt/rolap01.dsc, PURGE
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
REPERROR (-1, EXCEPTION)
MAP NAVEED.ORDER_T, TARGET NAVEED.ORDER_T;
MAP NAVEED.ORDER_T #exception_handler();
MAP NAVEED.PRODUCT_T, TARGET NAVEED.PRODUCT_T;
MAP NAVEED.PRODUCT_T #exception_handler();
MAP NAVEED.ORDER_LINE_T, TARGET NAVEED.ORDER_LINE_T;
MAP NAVEED.ORDER_LINE_T #exception_handler();




GGSCI (Test.naveed.com) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP1        00:00:00      00:00:05    


GGSCI (Test.naveed.com) 13> 
  • 1. Re: DDL happening but not DML on new tables
    731067 Newbie
    Currently Being Moderated
    Hi
    You are explicitly defining which tables are going to be replicated
    TABLE NAVEED.ORDER_T;
    TABLE NAVEED.PRODUCT_T;
    TABLE NAVEED.ORDER_LINE_T;
    Any DML operation on tables that are not included on this list won't be replicated.

    The new tables should be included on the list or maybe you could
    use something like this
    TABLE NAVEED.*;
    If there is a table that you don't want to replicate you could use the EXCLUDE clause.

    And for your replicat configuration
    MAP NAVEED.*, TARGET NAVEED.*;
    HTH
  • 2. Re: DDL happening but not DML on new tables
    916413 Newbie
    Currently Being Moderated
    Thanks for the response,


    To better understand GoldenGate,i have created a dummy database with only reserved and NAVEED Schema.
    NAVEED Schema has only 3 tables and all DDLs and DMLs are replicating successfully @Target

    Now moving to next step,lets say i have created few more schemas and respective objects . To Replicate this i have used DDL INCLUDE ALL and not DDL INCLUDE MAPPED. This is perfectly getting replicated @Target.

    Similarly to replicate DMLs on all these newly created objects what should be the correspondent command ?

    I dnt want to explicitly mention the Schema.objects names in extract and replicat process as in actual production there may be many users and objects created and then manually editing extract and replicat process becomes a tedious manuall task.
  • 3. Re: DDL happening but not DML on new tables
    satrap Journeyer
    Currently Being Moderated
    When you add new tables, to capture DML changes you will have to do enable supplemental logging for them using
    ADD TRANDATA SCHEMA_NAME.NEW_TABLE NAME
    ( unless you haven't used ADD SCHEMATRANDATA which automatically enables supplemental logging for any new tables being added to the database schema).

    Edited by: satrap on Oct 12, 2012 3:44 AM
  • 4. Re: DDL happening but not DML on new tables
    916413 Newbie
    Currently Being Moderated
    Thanks for the response,

    For automatically ADD TRANDATA, i have used the below options in extract process and because of this all supplement logging is being automatically enabled for any new objects being created what is not being replicated is the DMLs on those newly created objects

    ddloptions getreplicates, getapplops,addtrandata, report
  • 5. Re: DDL happening but not DML on new tables
    satrap Journeyer
    Currently Being Moderated
    This is from the Oracle documentation:

    There might be a lag between the time when an original DDL operation occurs and when the ADD TRANDATA takes effect. During this time, do not allow DML operations (insert,update, delete) on the affected table if the data is to be
    replicated; otherwise, it will not be captured. To determine when DML can be resumed after ADDTRANDATA:
    1. Edit the Extract parameter file in GGSCI.
    Warning: Do not use the VIEW PARAMS or EDIT PARAMS command to view or edit a parameter file that was created in a character set other than that of the local operating system.
    View the file from outside GGSCI; otherwise, the contents may become corrupted.
    2. Add the REPORT option to DDLOPTIONS, then save and closethe file.
    DDLOPTIONS [, other DDLOPTIONS options], REPORT
    3. Stop and start Extract to activate the parameter changes.
    STOP EXTRACT <group>
    START EXTRACT <group>
    4. View the Extract process report.
    VIEW REPORT <group name>
    5. Look for the ALTER TABLE that added the log group to the table, and make a note of the time that the command took effect. The entry looks similar to the following:
    Successfully added TRAN DATA for table with the key, table [QATEST1.MYTABLE], operation [ALTER TABLE "QATEST1"."MYTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_MYTABLE_53475" (MYID) ALWAYS /*GOLDENGATE_DDL_REPLICATION */ ].
    6. Permit DML operations on the new table.
  • 6. Re: DDL happening but not DML on new tables
    916413 Newbie
    Currently Being Moderated
    Below is the new table which is getting replicated @ Target but not DMLs on it.

    @Source
    SQL> conn cgi/cgi
    Connected.
    SQL> set line 1000
    SQL> select OBJECT_NAME,OBJECT_TYPE from user_objects;
    
    OBJECT_NAME                                                                                                                      OBJECT_TYPE
    -------------------------------------------------------------------------------------------------------------------------------- -------------------
    CGITEST3                                                                                                                         TABLE
    
    SQL> select * from cgitest3;
    
            N1 N2
    ---------- ------------------------------
            23 charger
    
    
    GGSCI (prod.naveed.com) 7> info trandata cgi.*
    
    Logging of supplemental redo log data is enabled for table CGI.CGITEST3.
    
    Columns supplementally logged for table CGI.CGITEST3: N1, N2.
    
    
    GGSCI (prod.naveed.com) 9> info all
    
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING                                           
    EXTRACT     RUNNING     DPUMP       00:00:00      00:00:01    
    EXTRACT     RUNNING     EXT1        00:00:00      00:00:10    
    
    GGSCI (prod.naveed.com) 10> lag ext1
    
    Sending GETLAG request to EXTRACT EXT1 ...
    Last record lag: 110 seconds.
    At EOF, no more records to process.
    @target
    SQL> conn cgi/cgi
    Connected.
    SQL> select OBJECT_NAME,OBJECT_TYPE from user_objects;
    
    OBJECT_NAME                                                                                                                      OBJECT_TYPE
    -------------------------------------------------------------------------------------------------------------------------------- -------------------
    CGITEST3                                                                                                                         TABLE
    
    
    SQL> select * from cgitest3;
    
    no rows selected
    
    GGSCI (Test.naveed.com) 7> info all
    
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING                                           
    REPLICAT    RUNNING     REP1        00:00:00      00:00:10    
    
    GGSCI (Test.naveed.com) 8> lag rep1
    
    Sending GETLAG request to REPLICAT REP1 ...
    No records yet processed.
    At EOF, no more records to process.
  • 7. Re: DDL happening but not DML on new tables
    stevencallan Expert
    Currently Being Moderated
    Go through the tutorial at Oracle Learning Library and get a good working example of DML replication.
  • 8. Re: DDL happening but not DML on new tables
    916413 Newbie
    Currently Being Moderated
    Hi,

    I m having no problems with DMLs for mapped objects but my question is why newly created objects DMLs are not getting replicated @Target whereas DDLs for those are successfully getting replicated @Target?
  • 9. Re: DDL happening but not DML on new tables
    916413 Newbie
    Currently Being Moderated
    Hi,

    I m having no problems with DMLs for mapped objects but my question is why newly created objects are not getting replicated @Target whereas DDLs for those are successfully getting replicated @Target? My db version is 10.2.04

    Also i have used DDLOPTIONS ADDTRANDATA statement and data still is not being captured for newly created objects?
  • 10. Re: DDL happening but not DML on new tables
    stevencallan Expert
    Currently Being Moderated
    Because DDL is a separate type of operation with respect to OGG. If you add a new table, you need to include it in your process group(s) - and restart extract, data pump, and replicat. The param files are not dynamic with respect to changes.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points