6 Replies Latest reply on Jun 14, 2018 3:48 PM by Adam Leszczyński

    Oracle GoldenGate point in time source database recovery

    Adam Leszczyński

      Hi OGG Gurus,

       

      I have thought about a scenario where the source database had to be restored using point in time recovery and the target database had also be synced with the source.

      But without any full initial load, just with recovering from backup to an earlier point in time recovery (preferably with table space point in time recovery) and later syncing the OGG.

       

      My scenario seems to work. But Oracle has not described it in any form of documentation or MOS note.

      It requires some messing up with the checkpoint tables in the SYS schema.

       

      Have I missed something or might it actually work like in my lab?

       

      Here is a full description of my recovery scenario:

      https://www.bersler.com/blog/oracle-goldengate-point-in-time-source-database-recovery/

       

      What do you think about it?

       

      Kind regards,

      Adam

        • 1. Re: Oracle GoldenGate point in time source database recovery
          K.Gan

          Hi Adam

          I suppose there are 1 million ways for your database to die (plagiarise from Seth MacFarlane) and similarly to recovery. Understanding how OGG captures and replicates is probably a much better knowledge note. In your example you are overly complicating the recovery as you are using integrated replicat

          Basically you need to recovery using backup which is the most basic fallback way. You don't need to be that precise, except that your source has lost data as you say to an earlier point in time. You do the same for the target to a comfortable margin before the source, say 10 mins. You are working with timestamps not SCN. When all is up and running, you alter your integrated Extract to a much earlier time that you recovery time and covering the 10 mins that you are doing for the replicat. Alter the replicat to the new extract sequence and start replicat WITHOUT parameters, ie do not specify NOFILTERDUPTRANSACTIONS

          The replicat will sort itself, skipping transactions already applied. As a rule don't change your extract or replicat parameters.

          Classic extract/replicat is a little messier.

          Cheers

          Kee Gan

          • 2. Re: Oracle GoldenGate point in time source database recovery
            Adam Leszczyński

            Hi Kee Gan,

             

            Thank you for your answer.

            I agree, that the scenario I have presented is very complex and complicated.

             

            But:

            - having the source of the replication back in time

            - having the target of the replication just in one table space

            - having other table spaces in the target database with other important data

             

            Can you propose an alternate approach to go back in time with the target of replication (having the activities in other table spaces not being disturbed)?

             

            My approach is:

            - go back in time with the table space (below low watermark)

            - go back in time with checkpoint table to the same SCN

            - resume replication

             

            It is not very complex if you look at that this way. Any other ideas to achieve the same goal?

             

            Kind regards,

            Adam

            • 3. Re: Oracle GoldenGate point in time source database recovery
              K.Gan

              OGG works only with tables so look up all the tables in that tablespace. If you have one replicat doing all tables then you need another replicat just for this set. The issue here will be to find the source SCN to match. If this is planned then it's easier. Stop both extract and replicat and take a GRP at source and save the info replicat checkpoint in a text file. When you decide to flashback your source just alter the replicat to the saved info checkpoint

              Cheers

              Kee Gan

              • 4. Re: Oracle GoldenGate point in time source database recovery
                Adam Leszczyński

                Hi Kee Gan,

                 

                Unfortunately flashback target database will not work.

                 

                It would also undo changes in other non-replicated-by-ogg table spaces. And we don't want to do that.

                The key question is how to flashback just a part of the database.

                 

                Unless you know how to do a flashback just for ogg checkpoint tables in sys schema + one table space (tables replicated by OGG).

                 

                Kind regards,

                Adam

                • 5. Re: Oracle GoldenGate point in time source database recovery
                  K.Gan

                  Hi Adam

                  You can flashback individual tables using flashback table or use RMAN to recover table to a point in time using backups. In any case this is not a OGG issue per se. How you go back in time is really outside OGG. However you can always replay trails if you know when it last stopped. Therefore if you have determined the restore point, you first stop replicat, save that checkpoint information, then create the restore point. Some time in future, if you want to flashback the tables that you are replicating, stop the replicat, flashback the tables, alter the replicat to the saved checkpoint and start replicat again.

                  Cheers

                  Kee Gan

                  • 6. Re: Oracle GoldenGate point in time source database recovery
                    Adam Leszczyński

                    Hi Kee Gan,

                     

                    Unfortunately you can not use table recover or flashback table in all cases.

                    -> First of all, this will work very slow if you have many tables with many indexes. It requires table by table restore & recovery and performance will suffer if you have a  large database.

                    -> And this will not work for checkpoint tables for Integrated Replicat. The tables are owned by SYS and tables from SYS schema can not be recovered this way - neither with flashback nor with rman

                     

                    Kind regards,

                    Adam