9 Replies Latest reply on May 6, 2019 1:51 PM by mariam.kupa

    Oracle Golden Gate- Active/Passive : two entries in each table

    Ken18

      Hello Experts,  K.Gan Veera-Oracle Raj.Ogg-Oracle mariam.kupa  Vikas Panwar

      I am a beginner and need your experts help on the below issue.

      ACTIVE - PASSIVE

      ORACLE - ORACLE

      Found that a table: PROCESSTASK being updated from both sites.

      Ideally it should not happen since we are connected to primary db only.

      -> There is a Primary key on ID column for this PROCESSTASK table

      ->  A unique index is also present on ID column

      Noticed some replication errors on this table: PROCESSTASK. It would appear that the table is being updated by the TimedDepositProcessing task of application in some fashion. There were two entries in each table, with this as an example of their contents:

      Table Entries on both sites

      Entries.JPG

      There were discrepancies in these data rows with respect to time stamps and the like. This would appear that the database is being updated by both sides which is upsetting the replication process.

        • 1. Re: Oracle Golden Gate- Active/Passive : two entries in each table
          Veera-Oracle

          Hi Ken18,

           

          Please share the below information from both Source and Target,

           

          1. Extract and Replicat parameter files.

          2. Replicat report file

          3. Create DDL of table Processtask from both Source and Target

          4. From Source GGSCI,

           

          ggsci >dblogin .....

          ggsci >info trandata schema_name.PROCESSTASK

           

          Regards,

          Veera

          • 2. Re: Oracle Golden Gate- Active/Passive : two entries in each table
            aoflima44

            Are you 100% sure there is no users or application connections to the "passive" database?

            AL

            • 3. Re: Oracle Golden Gate- Active/Passive : two entries in each table
              Veera-Oracle

              Yes, that's correct. But if the table has a Primary Key, then it shouldn't allow duplicate entries. We need to primarily check the table structure and parameter files.

               

              Regards,

              Veera

              • 4. Re: Oracle Golden Gate- Active/Passive : two entries in each table
                K.Gan

                it is kind of irrelevant describing what the application does to this table where it is supposed to be updated, ie the Primary.

                You said "Ideally it should not happen since we are connected to primary db only."

                Therefore I take it that only one side is supposed to do anything which obviously isn't the case. Is the application running on the target and if so why?

                If the target DB is meant to be updated then you will need to work out the conflicts accordingly.

                If your concern is not knowing who is updating this table you can secure this table only for ggs user to update, any rogue programs will fail. Alternatively you can configure extract to be running on the target and you can see the updates and from whom (include the user as a token) on the target side.

                Cheers

                Kee Gan

                1 person found this helpful
                • 5. Re: Oracle Golden Gate- Active/Passive : two entries in each table
                  Ken18

                  Thanks for detailing on the conflicts and methods to handle it.

                   

                  I am a beginner, could you kindly help me on setting up the below on target,

                  Alternatively you can configure extract to be running on the target and you can see the updates and from whom (include the user as a token) on the target side.

                   

                   

                  • 6. Re: Oracle Golden Gate- Active/Passive : two entries in each table
                    Vikas Panwar

                    Ken18

                     

                    What he mean to say you can setup a new extract (if you dont know, you can look at the extract running on source side currently)

                    while setting up the extract you can pass extra information in addition to your regular columns. this can be done in many ways here in this suggestion you can need to use token

                     

                    search how to pass tokens in Goldengate extract.

                    In token you will pass which user is doing the transaction which you are observing. I am not able to recollect the token name but it will be something like DBUSER or something

                    below is one example where you will get DBNAME

                     

                    TOKEN_DBNAME = @GETENV("DBENVIRONMENT" , "DBNAME" )

                     

                    getenv, dbenvironment, dbname

                     

                    All is GG syntax.

                    so this is how you will set the token while extracting data and then on target side in your replicat you can save this in some new column in ur table.

                    so suppose you want this under columns DBNAME on target side

                     

                    you will use

                    DBANAME = @TOKEN ('TOKEN_DBNAME')

                     

                    hope this helps

                     

                    Mark this question helpful, correct or like it. It will help others

                     

                    -Vikas

                    • 7. Re: Oracle Golden Gate- Active/Passive : two entries in each table
                      K.Gan

                      See OGG TIP: Using Extract to audit your database

                      and OGG TIP: To token or not to token and GET E n V

                      What you want in the extract is

                      TRANLOGOPTIONS EXCLUDEUSER <your replicat ggsuser>

                      so that you don't track anything that replicat does but other users. If you think the ggs user on target is compromised then this is a good time to change the password, no one except the replicat should be using this.

                      You want to change the TABLE parameter just tracking this single table.

                      and the token you want is

                      @GETENV ('TRANSACTION', 'USERNAME') to see who else is messing with this table.

                      Instead of just providing you the full parameter, work through the examples and you will understand where things come from.

                      Cheers

                      Kee Gan

                      • 8. Re: Oracle Golden Gate- Active/Passive : two entries in each table
                        mariam.kupa

                        Hello @Ken18,

                         

                        As Veera-Oracle mentioned, could you please share Extract and Replicat parameter files?
                        As you mentioned you are a beginner, so to collect that information please see my comments bellow Veera's questions:

                         

                        1. Extract and Replicat parameter files.  -

                         

                              To find all available replicats and extracts:

                                ggsci> info all

                              Find appropriate extract let's say EXT1 and run:

                              ggsci> view params EXT1

                              Do the same on for replicat on the target:

                              ggsci > view params REP1

                        2. Replicat report file

                         

                        ggsci> view report REP1

                         

                        3. Create DDL of table Processtask from both Source and Target

                         

                        On source and target

                        sql> select dbms_metadata.get_ddl('TABLE', 'PROCESSTASK', owner) from dual;


                        Indicate table owner in <owner>

                        • 9. Re: Oracle Golden Gate- Active/Passive : two entries in each table
                          mariam.kupa

                          Hello @Ken18,

                           

                          As Veera-Oracle mentioned, could you please share Extract and Replicat parameter files?
                          As you mentioned you are a beginner, so to collect that information please see my comments bellow Veera's questions:

                           

                          1. Extract and Replicat parameter files.  -

                           

                                To find all available replicats and extracts:

                                  ggsci> info all

                                Find appropriate extract let's say EXT1 and run:

                                ggsci> view params EXT1

                                Do the same on for replicat on the target:

                                ggsci > view params REP1

                          2. Replicat report file

                           

                          ggsci> view report REP1

                           

                          3. Create DDL of table Processtask from both Source and Target

                           

                          On source and target

                          sql> select dbms_metadata.get_ddl('TABLE', 'PROCESSTASK', owner) from dual;


                          Indicate table owner in <owner>