8 Replies Latest reply: Aug 20, 2012 3:45 PM by Mark Malakanov (user11181920) RSS

    How to replicate schema to other database

    880346
      Hi,

      I have a database of version 11.2.0.3 and running on version Enterprise Version 5.

      On this database, I have a application which resides on a schema called -> APPS (Size of it being 8G)
      I need to setup the same schema in a DR site and replicate the whole data continuously so that if there is an issue in primary site, I can switch to the DR site.

      I cannot setup a Dataguard (physical standby) configuration in this database as there is another application which resides on this database and that application basically does the replication by itself to the DR site (The application is Oracle Content Management)

      I am thinking of 3 options. Appreciate if you could please suggest me the best option and also pointers on how to implement them.

      1. Set up Materialized View in both Primary and DR database schema so that the schema gets refreshed continuously
      2. Implement streams and replicate the data across the schema's (Both from Primary db to DR and viceversa)
      3. Move this application (schema) to a new database with Physical Standby Dataguard configuration.

      Since the schema is of a very small size (8G), i want to try options 1 and 2 before trying option 3. i.e. creating new db altogether.

      Thanks!
        • 1. Re: How to replicate schema to other database
          955059
          877343 wrote:
          I need to setup the same schema in a DR site and replicate the whole data continuously so that if there is an issue in primary site, I can switch to the DR site.
          - If you need Disaster Recovery Solution ->
          3. Move this application (schema) to a new database with Physical Standby Dataguard configuration.
          Oracle Data Guard provides the management, monitoring, and automation software
          infrastructure to create and maintain one or more standby databases to protect Oracle data from
          failures, disasters, errors, and data corruptions. Data Guard is unique among Oracle replication
          solutions in supporting both synchronous (zero data loss) and asynchronous (near-zero data loss)
          configurations. Administrators can chose either manual or automatic failover of production to a
          standby system if the primary system fails in order to maintain high availability for mission
          critical applications.

          - If you have good knowledges in "Materialized View"(More Developer Role than DBA).
          1. Set up Materialized View in both Primary and DR database schema so that the schema gets refreshed continuously
          - Streams -> complicated for beginners.
          2. Implement streams and replicate the data across the schema's (Both from Primary db to DR and viceversa)
          +

          Try Oracle Golden Gate!!!
          • 2. Re: How to replicate schema to other database
            955059
            877343     
                 
            Registered:     Aug 4, 2011
            Total Posts:     28
            Total Questions:     *17 (15 unresolved)*
            • 3. Re: How to replicate schema to other database
              880346
              Yes even I feel dataguard is a good option.
              But I definitely want to try the streams too since its an 8G application and creating a new environment (dev/stage/prod and DR) for an 8G application is not something that I would want to proceed at this point.

              Can you please help me with good pointers or notes for performing a schema level replication setup in streams?
              • 4. Re: How to replicate schema to other database
                asifkabirdba
                http://www.oracle.com/pls/db112/portal.portal_db?selected=15&frame=

                Regards
                Asif Kabir
                • 5. Re: How to replicate schema to other database
                  880346
                  Thanks Asif. Appreciate your help
                  • 6. Re: How to replicate schema to other database
                    Osama_Mustafa
                    Read this
                    http://docs.oracle.com/cd/B28359_01/server.111/b28321/strms_mprep.htm#CHDFDCAC
                    http://prodlife.wordpress.com/2008/02/21/oracle-streams-replication-example/
                    • 7. Re: How to replicate schema to other database
                      743201
                      i would add golden gate to the mix (it will cost you ) depending your SLA requirements.
                      • 8. Re: How to replicate schema to other database
                        Mark Malakanov (user11181920)
                        Streams is complex and heavy performance-wise because works via AQ.
                        Also Oracle advocates to move from Streams to Golden Gates.
                        Golden Gates is fast but costly and does not have GUI for convenient management.
                        You can try some 3-rd party small footprint, easy to manage and inexpensive solutions like DataCurrents.
                        I know DC can work on schema level, even can pass DDLs like create/alter/drop table, partitions etc.