11 Replies Latest reply on Aug 12, 2019 6:38 PM by RBodo-Oracle

    Integrated vs coordinated replicate

    user10126973

      Hi All,

      I would like implement parallel for data replication, Could you please suggest me  which one better for fast replication

      Looks like some issues with coordinate replicate if process abend  then we need start manually (i am not sure about this)

      My  GG version 12.1

      DB version 11.2.4

      Please advise on this?

      Regards,

      Dharma

        • 1. Re: Integrated vs coordinated replicate
          K.Gan

          Hi Dharma

          Integrated replicat will use multiple threads for different transactions. So if you have one large batch transaction it will NOT use multiple threads. Coordinated replicat allows you to manage the threads for whatever tables you want to range over. Again you manage how many threads.

          My experience with coordinated has been terrible. It takes forever to stop and agenda are difficult to manage.

          Cheers

          Kee Gan

          • 2. Re: Integrated vs coordinated replicate
            user10126973

            Hi Kee,

            thank you very much for your input, in this case range is better than any other parallelism right?

            Regards,

            Dharma

            • 3. Re: Integrated vs coordinated replicate
              K.Gan

              Yes and no. In a way, range is coordinated replicat except that you handle all the moving parts yourself. Therefore if you have 3 range like 1 of 3, 2 of 3 and 3 of 3 you define your own 3 replicats. Each of them has no idea others exists so if 1 of the replicats abended and wasn't notice for a week then that table you don't get real time in sync data. It does not mean it is out of sync as if you fix it and process all the records it will be in sync.

              Conceptually coordinated replicat is a good design, but the product never worked well for me. Coordinated replicat is a bunch of multi threaded classic replicats and each thread automatically spawns a replicat. It also has the concept of a barrier transaction where example a DLL is a barrier transaction which makes all the threads sync and waits for this DDL.

              It does not mean it will not work for you. Get your largest sets of transaction and it can be just one table and test it out. Spread over say 5 or 6 threads so one table can have 5 or 6 threads. Work it hard then try to do a clean stop. Sometimes my coord rep took over 40 mins to 1 hour to stop which isn't practical. Also, make sure you test a DDL like add a column and do more large transactions. In my case the DLL cause my replicat to lag excessively which defeats the purpose of such a replicat. And the most annoying thing was that one or 2 of my threads will have a lag of 5 or 6 trail files. Maybe you don't have this issue but for my project (2 contracts ago :-) ) of which I am pushing transactions with close to  1/4 million DMLs per transaction, the coord rep was a disaster.

              Cheers

              Kee Gan

              • 4. Re: Integrated vs coordinated replicate
                user10126973

                thank you very much for detail explanation, I will try to implement integrated parallelism will see how it works.

                Once again thank you very much.

                • 5. Re: Integrated vs coordinated replicate
                  K.Gan

                  Good luck with your project. Also, I meant to say DDL instead of DLL. I am currently writing a few windows libs (DLL), so my mind kept steering towards the incorrect spelling :-(

                  Cheers

                  Kee

                  • 6. Re: Integrated vs coordinated replicate
                    user10126973

                    Hi Kee,

                    I have implemented  integrated replicate and its working fine

                    I did not mention any parallelism(my OGG version is 12.1) in my param file I just put minimal parameters in my param file like BATCHSQL , Assumetargetdef etc,,

                    My question here, when we  set up integrated by default  it will have parallelism ? if yes, where to check my tables replicates in parallel mode

                    is this parameter works in 12.1 ogg   DBOPTIONS INTEGRATEDPARAMS(parallelism 6)

                    Please let me where to check data replicating parallel mode

                    Regards,

                    Dharma

                    • 7. Re: Integrated vs coordinated replicate
                      K.Gan

                      I think by default you get 4 but you can specify those as you have done. You will need to download a health script from the support site, key in the search box goldengate health check and run the script for DB 12.1. Go to replicat stats for the generated report.

                      You cannot tell which tables go to which threads as it does not work like this. IR pushes based on transactions into whichever threads it thinks is best. So as mentioned if all you have is one huge transaction you will see IR using only one thread. IR works best for small frequent transactions. So if you have a million transactions and each has a handful of DMLs, IR works best.

                      Cheers

                      Kee Gan

                      • 8. Re: Integrated vs coordinated replicate
                        user10126973

                        Hi Kee,

                        Finally i have implemented Integrated replicate, One of My replicate huge lag(15 hours), how to boost up performance ?

                        How to see which sql causing this huge lag  from golden gate command ?

                        Please share with me your input/thoughts

                        Regards,

                        Dharma

                        • 9. Re: Integrated vs coordinated replicate
                          K.Gan

                          Hi Dharma

                          if you have OEM just click on the replicat session. If not you can use this select clause

                          select sid, serial#,username, program, logon_time, seconds_in_wait, state, status  from v$session where username = 'your_ggsuser' and program like 'replicat%';

                           

                          then

                           

                          SET PAGESIZE 0

                           

                           

                          SELECT REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,

                                ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'

                                 || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'

                                 || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT

                            FROM V$SESSION SES,  

                                 V$SQLtext_with_newlines SQL

                          where SES.STATUS = 'ACTIVE'

                             and SES.SID = '382'

                             and SES.USERNAME is not null

                             and SES.SQL_ADDRESS    = SQL.ADDRESS

                             and SES.SQL_HASH_VALUE = SQL.HASH_VALUE

                             and Ses.AUDSID <> userenv('SESSIONID')

                          order by runt desc, 1,sql.piece;

                           

                          You can then filter out the relevant sid that denotes the replicat. It is a little ugly but it is there.

                          Maybe it needs an index or whatever. If this IR and it is doing one table it is not going to use multiple threads.

                          Cheers

                          Kee Gan

                          • 10. Re: Integrated vs coordinated replicate
                            user10126973

                            Thank you very much Kee.

                            • 11. Re: Integrated vs coordinated replicate
                              RBodo-Oracle

                              Hi user10126973,

                               

                              I'm up to your business, and I saw that K.gan helped you with Golden Gate questions.

                               

                              Please could you help me with the topic that i opened:

                               

                              Some questions about OGG

                               

                              Thank you very much

                              Rodrigo.