14 Replies Latest reply: Jan 29, 2013 7:33 AM by Shaz3kgt RSS

    Forcing Extract to check point/extract looking for 5-day old archive_log

    Shaz3kgt
      Hey guys, I'm having some what of an odd issue and can not figure out what the issue is. I believe theoretically I have tracked it down but cant seem to proof it.

      Here is what happens, I have an extract process running for a specific schema, now that schema only gets inserted into every 4-5 days. CDC is able to transfer the data and is running as expected.

      Here is the issue, when I stop cdc , (patching or maintenance) and restart it lets say 20-30 minutes later it errors out.
      The error message indicates its looking for an archive log, the odd part is its looking for an archive log that is 4-5 days old. We only keep 1-day on disk and about 2-3 days in RMAN. (and up to 6 months on tape)

      I can not figure out why the extract process would want an archive log 5 days old?? The only explanation I have is since this schema only gets written too (inserted specifically) every 4-5 days that the last time the extract process actually got data from an archive log(or redo-log) was about 4-5 days ago and somewhere in the checkpoint table is that info. Now , if that is the case and I'm on the right track , what can I do?? Do I need to somehow have the extract process "checkpoint"(not sure how else to describe it) everyday?
      If my theory is wrong, any ideas of what might be causing this??
        • 1. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
          Shaz3kgt
          Also I read this :

          "Documentation states:

          If Extract abends when a long-running transaction is open, it can seem to take a long time
          to recover when it is started again. To recover its processing state, Extract must search
          back through the online and archived logs (if necessary) to find the first log record for that
          long-running transaction. The farther back in time that the transaction started, the longer
          the recovery takes, in general, and Extract can appear to be stalled. "

          I believe the above is only neccessary if the extract process abends. In my case the process doesnt abend, it is stopped. Just for details I docommand:
          lag <extract_process>
          (if no lag then i proceed)
          stop <extract_process>

          Edited by: 983466 on Jan 22, 2013 11:46 AM
          • 3. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
            982808
            Do this command:

            GGSCI> info extract <ext_name>, showch

            This should tell you what archive log it wants.


            For the transaction that are 5 days old.. are they truly just sitiing out there open transaction... 5 days... close transaction?
            Then yes you will need to keep those archives onsite and available.

            One way to prevent this is before you stop extract.. run the above command to make sure no really old archives are needed.

            You can also run this command:
            SEND EXTRACT <ext_name> SHOWTRANS BEFORE you stop extract to see all the old transactions.
            • 4. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
              Shaz3kgt
              Chris, thanks for the response. The commands will really gives me some guide.
              The transactions are completed transactions that happened 5 days ago. Every 5 days the application writes into the source database. I wasn't sure if there was a way from preventing this from happening again where it asks for an archive 5 days old which it already processed.

              I guess the best way for me to do this is too run the command "send extract <extract> showtrans before" make sure its asking for a newer one then try to see what happens when I stop and start the process. If it still fails and asks for something a lot older then I know my issue is still there.
              • 5. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
                Shaz3kgt
                Also what I meant by "transaction" is..Ill be specific. There is an insert to about 15 tables that occurs, the process on the source happens then goes over to the target. This cdc flow is correct and good. Now lets say 3 days later I have to bring down the server for maintenance. This is when I stop the extract process on the source the error comes out. This doesn't happen all the time, it will happen once in a while. I tried to reproduce it but at this point can't, Im going to try to figure out a test case I can build for it to happen again.

                Just wanted to know if there is something I was missing, something that I can do, maybe some sort of parameter that resolves this.
                • 6. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
                  982808
                  What do you mean by 'resolve' it?

                  OGG always keeps track of open transactions.

                  So with those commands earlier you can see the oldest open transactions.

                  with the showtrans command you can see what is open...(so always run that before you stop)... if you do that you can avoid stopping in the middle of a transaction.
                  Now if you say.. that doesn't solve it... (and it might not)

                  You can decide to rolllback that transaction... :) (of course you should talk to your app people to see what the consequences are of that)

                  Here is a test case for you:


                  Insert into Table A

                  Go home

                  Tomorrow do the show trans command.
                  You should see the transaction open and that OGG extract is looking for archive logs from today.
                  • 7. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
                    Shaz3kgt
                    Ahhh..Thanks Chris , helps a lot. Just have some questions. Trying to wrap my mind around this.

                    lets say i do this
                    Here is a test case for you:

                    Insert into Table A

                    Go home

                    Tomorrow do the show trans command.
                    You should see the transaction open and that OGG extract is looking for archive logs from today. "

                    (I'm assuming when your say insert, you also mean to commit the data)

                    Why would the transaction be open? When it says open , does it mean it processed it or open for another reason?

                    I would assume once I inserted into table A and commited, that the transaction would be complete.

                    Edited by: Shaz3kgt on Jan 23, 2013 12:59 PM

                    Edited by: Shaz3kgt on Jan 23, 2013 1:03 PM
                    • 9. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
                      982808
                      Ahhhh you added a piece I didn't.

                      the commit.

                      If it was committed.. OGG extract would capture and move on.

                      If open.... OGG extract has to keep track of it.

                      That is the difference.
                      • 10. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
                        Shaz3kgt
                        Now that makes complete sense. Alright so somehow they had to have left that transaction open.
                        I'm going to now take a look at the application side to see how they insert there data and see maybe if this was just a hiccup.

                        Once again thanks.
                        • 11. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
                          J_K
                          You can also integrated RMAN with GG using logretention ,RMAN wont delete archivelog which is needed by GG.
                          • 12. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
                            Shaz3kgt
                            I never thought of that way. I am using database 11.2.0.3 and GG version is 11.1.1.0.31. THis will proabbaly seem like a novice question but just to verify..
                            1. I just need to add this parameter to my parameter file TRANLOGOPTIONS ?
                            2. Also will I need to register the extract ?? (dblogin userid ..., password ....register extract <extract_name> logretention )



                            Can I add that parameter like so: (my parameter file) :

                            -- Extract of tables for X2X to capture data and replicate to XXX DB
                            --
                            -- GG.LPENTON: Load the macro file containing database userid and password. Do n
                            ot list the contents
                            --
                            nolist
                            include ./dirmac/mac_lib.mac
                            list

                            extract eXX
                            exttrail ./dirdat/X2

                            --
                            -- GG.LPENTON: Connect to the database
                            --
                            #db_connect ()

                            TRANLOGOPTIONS_

                            nocompressdeletes
                            nocompressupdates
                            --getupdateafters
                            notcpsourcetimer

                            table XXXXXXr.XXXXXX_MXM_
                            • 13. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
                              Shaz3kgt
                              I'm probably missing something but I tried to use RMAN with GG for log retention.
                              This is what I did, I put the parameter TRANLOGOPTIONS in my mapping shown in my previous post. I tried to run
                              register extract <extract_name> logretention but it kept saying "ERROR: Invalid command."

                              Is there something that ggate needs privs too for the above command to work?
                              Is that needed, or is putting the TRANLOGOPTIONS parameter sufficient?
                              • 14. Re: Forcing Extract to check point/extract looking for 5-day old archive_log
                                Shaz3kgt
                                I think I figured it out. I went through the goldengate document and this feature is available in v.11.1.1.1.1 and we have v.11.1.1.0.31