3 Replies Latest reply on Dec 4, 2017 9:55 PM by K.Gan

    How to identify if source Goldengate – Refresh single table

    user12293113

      Hello There...

       

      Currently I'm implementing incremental extract in our ETL system.

      We have ETL system which is developed in SQL Server 2014 SSIS, where we used to full extracts (wipe and load) data from source Oracle tables to SQL tables. Recently source data is added on Oracle Golden-Gate 12c.

      Now we are implementing Incremental Extracts from Oracle Golden-Gate 12c enabled tables, and we do retrieval of data based on filters on source Oracle Golden-Gate 12c table of GG_ACTION_SCN, which we are storing in ETL run track tables.

       

      There is one scenario where we stuck, is if Single Extract Table is refreshed in Oracle Golden-Gate 12c environment, how ETL system will identify it that this source table is refreshed, and it need to be full extract in place of incremental.

       

      Please help...

       

      Regards

      - Bhudev Prakash

        • 1. Re: How to identify if source Goldengate – Refresh single table
          K.Gan

          Hi Bhudev

          Can you please elaborate more?

          "There is one scenario where we stuck, is if Single Extract Table is refreshed in Oracle Golden-Gate 12c environment, how ETL system will identify it that this source table is refreshed, and it need to be full extract in place of incremental."

          When you say refreshed, do you mean overwriting the existing table in Oracle 12c? How do you do this?

          You are asking how does the ETL system know that the source table is refreshed. Are you asking how does OGG extract know?

          Trying to understand your system.

          Cheers

          Kee Gan

          • 2. Re: How to identify if source Goldengate – Refresh single table
            user12293113

            Thank You K.Gen...

             

            When I say refreshed, I mean single Oracle table in Golden Gate 12c data is refreshed / overwritten.

            https://anjo.pt/wp/keyword-oracle/2013/11/13/goldengate-refresh-single-table/

             

            I mean, if for a single Oracle table in Golden Gate 12c data is refreshed / overwritten (Sorry I do not have much knowledge on Oracle Golden Gate terms), in that case I doubt that all the GG_ACTION_SCN, GG_ACTION_DT, and GG_ACTION_CD data will refreshed or overwritten with NULLs.

             

            On the other hand ETL system stores boundaries of GG_ACTION_SCN, GG_ACTION_DT for each ETL execution and supply this information as a filter on source Golden Gate 12c table. So next-time when I trigger ETL and it might not found old GG_ACTION_SCN values in table, and can wrong data extract (if for a single Oracle table in Golden Gate 12c data is refreshed / overwritten and re-transaction INSERT/UPDATE/DELETE entries available in GoldenGate enabled table ) or no data extract (if after for a single Oracle table in Golden Gate 12c data refreshed / overwritten, no other transaction).

             

             

            I believe I tried to explain...

             

            On the other hand I found

            Oracle 12c GoldenGate View

            V$GOLDENGATE_TABLE_STATS

            GV$GOLDENGATE_TABLE_STATS

             

            Which might be helpful.. But not sure till now if
            TOTAL_INSERTS

            TOTAL_UPDATES

            TOTAL_DELETES

            information works...

             

             

            Not sure if above mentioned count information refreshed if GoldenGate table is overwritten.

             

            Please note I have almost little knowledge on Oracle Golden-Gate.

             

            Regards

            - Bhudev

            • 3. Re: How to identify if source Goldengate – Refresh single table
              K.Gan

              Hi Nhudev

              Unfortunately I got an access denied on the link probably because my server thinks this link isn't safe. Anyway you are describing something from an article from this url? Whatever you have described GG_ACTION_SCN, GG_ACTION_DT, and GG_ACTION_CD do not come from the product.

              V$GOLDENGATE_TABLE_STATS is not something I think you want from the description. OGG works by having numerous database appliers. When they are active they update this table among a lot of other OGG tables. When it stops and restarts it gets cleared out. It only mean something to the appliers not something external that you want to capture.

              It looks like you want to know the count of DML operations done by OGG. I also do not quite get the ETL bit. I take it that it is your system and not a ETL utility like Oracle data integrator.

              Forget about OGG, describe what you want to do.

              Cheers

              Kee Gan