7 Replies Latest reply: Apr 29, 2014 10:02 AM by chris_here RSS

    Read-only agent synching to a Data Guard physical standby?

    chris_here

      Hi all,

       

      we are trying to use TimesTen 11.2.2.4.1 as a read-only memory cache for an Oracle 11.2.3.0.7 schema on Linus RedHat 6.3, while using Oracle Data Guard to replicate the Oracle instance over geographically remote sites. On each site we would like to have two TT instances synchronizing with the local Oracle 11g instance. This works fine against the master DB, but are the TT agents going to be able to synchronize against physical standby instances?

       

      The problem it seems is that the TT agent uses dedicated structures in the Oracle master instance (related to the cache grid), which are going to be replicated into the standby instances. Is  the TT agent able to use the read-only, replicated structures to complete synchronization, or is this approach unworkable? What would be your advise as how to achieve this?

       

      Thanks for your help,

      Chris

        • 1. Re: Read-only agent synching to a Data Guard physical standby?
          Chrisjenkins-Oracle

          No, I'm afraid that won't work and is not supported. We do support use of READONLY and AWT cache groups in conjunction with Data Guard in the following configuration (documented in the TimesTen IMDB Cache Guide):

           

          1.    Data Guard is configured as synchronous physical standby

           

          2.    All TT caches run against the active Oracle DB

           

          3.    When a Data Guard failover occurs, TT caches can be migrated to run against the standby DB that has now beed promoted to active

          This is the only supported setup.

           

          Regards,

           

          Chris

          • 2. Re: Read-only agent synching to a Data Guard physical standby?
            chris_here

            Hi Chris,

             

            thanks a lot for the clarification. Just another question: what about using logical Data Guard replication to replicate the user schema only, not the ttadmin schema, and creating the ttdadmin schemas locally in read-write mode, just like it's done on the master instance? Would this approach be viable / supported? It seems to me this would simplify our cache configuration as the TT agents would always synchronize with the site-local Oracle instance.

             

            Thanks for your support,

            Chris

            • 3. Re: Read-only agent synching to a Data Guard physical standby?
              Chrisjenkins-Oracle

              Provided that logical Data Guard apply fires triggers then this approach ought to work okay (cache change tracking is based on triggers), though we have not explicitly tested tor certified it. If logical Data Guard apply does not fire triggers then this is a nonn-starter I'm afraid. I do not know if apply fires triggers; that is something I will need to check out.

               

              Chris

              • 4. Re: Read-only agent synching to a Data Guard physical standby?
                chris_here

                Hi,

                 

                thanks for that. Indeed the documentation seems to indicate that triggers will be applied to the standby database, but clearly this would need to be tested. I'll try to test this and post the results.

                 

                Thanks,

                Chris

                • 5. Re: Read-only agent synching to a Data Guard physical standby?
                  chris_here

                  Hi again,

                   

                  so after testing a little bit it appears that this approach works indeed, at least against a limited number of manual DML operations. What I needed to do on the slave instance to have it working is the following:

                   

                  1 - Entirely exclude TTADMIN and TIMESTEN schemas from the Data Guard replication:

                  ALTER DATABASE STOP LOGICAL STANDBY APPLY;
                  execute dbms_logstdby.skip(stmt => 'SCHEMA_DDL', schema_name => 'TTADMIN', object_name => '%');
                  execute dbms_logstdby.skip(stmt => 'SCHEMA_DDL', schema_name => 'TIMESTEN', object_name => '%');
                  execute dbms_logstdby.skip(stmt => 'DML', schema_name => 'TTADMIN', object_name => '%');
                  execute dbms_logstdby.skip(stmt => 'DML', schema_name => 'TIMESTEN', object_name => '%');
                  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
                  
                  

                   

                  2 - Erase both schemas from the local instance:

                  DROP USER TTADMIN CASCADE;
                  DROP USER TIMESTEN CASCADE;
                  CREATE USER TTADMIN etc
                  
                  

                   

                  3 - Temporarily disable the database guard while creating the local ttCache structures, as the scripts seem to need to set a table-level lock on the source table:

                  ALTER DATABASE GUARD NONE;
                  ttIsql> CREATE READONLY CACHE GROUP etc
                  ALTER DATABASE GUARD STANDBY;
                  
                  

                   

                  4 - Unset the "Fire_Once_Only" property for the local TTADMIN triggers:

                  execute dbms_ddl.set_trigger_firing_property(trig_owner=> 'TTADMIN', trig_name=> 'TT_06_70560_T', fire_once => FALSE);
                  
                  

                   

                  At that point the cache seems to replicate properly in the most simple cases. I will try to test with some substantial load and against DG failovers to see how this behaves.

                   

                  Regards,
                  Chris

                  • 6. Re: Read-only agent synching to a Data Guard physical standby?
                    Chrisjenkins-Oracle

                    Hi Chris,

                     

                    Your previous post seems to have been truncated?

                     

                    Chris

                    • 7. Re: Read-only agent synching to a Data Guard physical standby?
                      chris_here

                      Hi,

                       

                      yes sorry it went off too soon. Should be better now.

                       

                      Thanks,

                      Chris