7 Replies Latest reply on Jul 20, 2012 6:03 PM by 950152

    One and two way replication

    902923
      Hi Experts,

      Can you please provide your advice on the below requirements,

      we have installed goldengate on three servers for example A, B , C. the requirement is A - B two way (bi-directional) replication and B - C one way(uni directional) replication. this is only one schema replication between these three environments. the environments are : Solaris and Oracle 11.2 version databases. please provide your suggestions for the below requirements,

      1. which one is best method to do the initial load between these three environments?

      2. Can we use bi directional extract process for uni directional also on server B ? like ( ext -> pump1 for A database(two way) and ext -> Pump 2 for C database(one way))? or we have two to create two separate extract and pump for bi directional and uni directional replication on server B?

      3. How to start these three environment replication processes on parallely?

      Thanks

      Edited by: 899920 on Jun 1, 2012 1:34 AM
        • 1. Re: One and two way replication
          829993
          Hello,
          I'll try to answer your questions...
          1. which one is best method to do the initial load between these three environments?

          It depends on the size of the schema you are going to setup for replication and also if you have the option to stop all activity in the source schema in order to make a one time copy. Based on these criteria you might use export/import or sql loader (via GG initial load) or RMAN backup/restore to get the initial copy of the schema on the target side

          2. Can we use bi directional extract process for uni directional also on server B ? like ( ext -> pump1 for A database(two way) and ext -> Pump 2 for C database(one way))? or we have two to create two separate extract and pump for bi directional and uni directional replication on server B?

          Yes. You can use the same extract on site B with a separate Data Pump to replicate to site C

          3. How to start these three environment replication processes on parallely?

          It is not necessary to start replication processes on all 3 in parallel. They can be done one pair at a time, A-B and then B-C.

          Hope this helps and good luck!
          • 2. Re: One and two way replication
            902923
            Hi,

            If we can use the same trail file for Bi directional and uni directional replication then where we have to put TRANLOGOPTIONS EXCLUDEUSER which will be used for bi directional ? can we put in pump process? please confirm. because this A to B bidirectional and B to C uni directional , can we use same B database trail files for uni directional pump process?
            • 3. Re: One and two way replication
              950152
              I recommend using separate extract for uni-directional.
              So you have one extract on A, 2 extract on B.
              The extract on B for bi-directional will have EXCLUDEUSER to avoid looping, whereas the other extract on B will not have EXCLUDEUSER, because you want to capture all the transactions on B, which are either applied directly on B or through GoldenGate transactions from A.
              • 4. Re: One and two way replication
                MikeN
                Kashif_ogg wrote:
                I recommend using separate extract for uni-directional.
                So you have one extract on A, 2 extract on B.
                The extract on B for bi-directional will have EXCLUDEUSER to avoid looping, whereas the other extract on B will not have EXCLUDEUSER, because you want to capture all the transactions on B, which are either applied directly on B or through GoldenGate transactions from A.
                That does make sense, and is a straight-forward and simple configuration that will be easy to understand and maintain. However, if there is medium to high volume on the source database, this is doing a lot of extra work (specifically, twice the work) without a lot of benefit. (The only benefit would be that one extract would be allowed to go faster than the other extract, since it's skipping some of the log data.)

                GoldenGate "capture" (extract) is of course just a log reader, and every "extract" that is added to the source DB must read all the log data to know what to capture and what to skip. Two extracts means two processes combing through all your database logs. So if you have one extract capturing all the data, and a second extract using "ExcludeUser" -- still, both extracts still have to read the entire transaction log.

                On the other hand, a less intensive approach (less processing, less file I/O) would be to use a single extract capturing all the data, write to a local trail, and then use two data pumps to send the data into two different directions: one data pump would send all the data (passthru), the other data pump would filter based on database username (essentially the same idea as using "excludeUser" at the source DB extract). To accomplish this, I believe that you would have to store the DB user as a token in the trail in the source extract
                TABLE SCH.*, TOKENS (MY_TK_GGREP_USER = @getenv("DBENVIRONMENT", "DBUSER") ... 
                and retrieve it as a token in the pump in a filter :
                TABLE SCH.*, filter( "MY_GG_REP_USER" <> @token("MY_TK_GGREP_USER") ... 
                (Some "getenv" values are always available, whether in a pump or the source extract (e.g., SCN, commit timestamp), but some are only available in that running process -- I think DBENVIRONMENT env values are only available at the source DB extract.)

                Regards,
                -Mike
                • 5. Re: One and two way replication
                  950152
                  See with this approach, you wont be able to do DDL replicaiton from B to A.

                  As it is bi-directional replication, depending on the version of OGG you might need to extract your data in a different way.. Having different set of key cols for confllict detection and resolution, getting before image etc...

                  Having another extract for uni-directional does put some additional i/o overhead on your redo/archivelog, but it does save you other headaches. It's a trade off, and I prefer to keep them separate.

                  - Kashif
                  • 6. Re: One and two way replication
                    MikeN
                    Kashif_ogg wrote:
                    See with this approach, you wont be able to do DDL replicaiton from B to A.

                    As it is bi-directional replication, depending on the version of OGG you might need to extract your data in a different way.. Having different set of key cols for confllict detection and resolution, getting before image etc...

                    Having another extract for uni-directional does put some additional i/o overhead on your redo/archivelog, but it does save you other headaches. It's a trade off, and I prefer to keep them separate.

                    - Kashif
                    I agree with everything you've said (in this post & the previous post). Like I said, "make sense, and is a straight-forward and simple configuration that will be easy to understand and maintain." Occasionally very high throughput is a requirement (100GB+/hr) and trade-offs need to be made. I would always resist the urge to prematurely optimize, since these re-configurations can actually be made later, as necessary, usually without downtime.
                    See with this approach, you wont be able to do DDL replicaiton from B to A.
                    That's true, but DDL wasn't mentioned (I don't think?). Doing DDL replication further complicates the issue (and that's always true, regardless of the problem being solved.) And if you had DDL replication from {noformat}A<->B{noformat}, I assume you would want to replicate DDL to C as well (otherwise you would still have to manually synchronize DDL changes to C when made on A or B -- sort of defeating the purpose of DDL replication.)

                    The DDL replication couldn't be active-active-active (unsupported), and if it's active(A)-active(B), then you'd likely need the two extracts you originally proposed on A (or on B, it doesn't matter), and the first extract (for bi-directional) would be configured for bi-directional DDL (getApplOps / ignoreReplicates) and that second extract (to C) would be configured as a "cascading" DDL configuration (getReplicates / getApplOps). Probably no need to delve into that, since it might be off-topic. (But we're still in agreement.)
                    • 7. Re: One and two way replication
                      950152
                      Mike -
                      I do not disagree with you. I personally prefer to have one extract in most cases, but in cases like these, in order to reduce admin complexity down the road, (depending on the dynamics of environment) I prefer to have separate extracts.
                      Thanks.

                      - Kashif