10 Replies Latest reply: Oct 12, 2012 9:23 PM by stevencallan RSS

    DDL happening but not DML on new tables

    916413
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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.