9 Replies Latest reply: May 3, 2012 4:52 AM by 713555 RSS

    Application failover with mviews

    713555 SE : RHEL

      I have an application hitting a database on Box A. If the db has any trouble customer wants to failover to box B so immediately I think SE standby manual log apply with 15 minutes gaps. However, before I implement, the amount of tables that will have updated TX is only 5 so Im thinking maybe materialized views over db link instead. Less overhead and setup time.

      Ive replicated the schema on Box B via datapump schema export so I'll be replacing the core 5 tables with mviews as select * from table@link_to_Box_A and set refresh schedule to every 15 minutes.

      what challenges have I got if I needed to switch the application to Box B? indexes? constraints? do I precreate?
        • 1. Re: Application failover with mviews
          Hemant K Chitale
          How do you plan to allow users to update (INSERT / DELETE / UPDATE) rows in the tables (MVs now !) in Box B ? Are you planning to create UPDATEABLE MATERIALIZED VIEWs ?

          As you've guessed, simple MVs do not replicate constraints and indexes. Similarly views and plsql objects may need to be altered / recompiled if the table / MV names are different.

          How do you plan to ensure cross-table consistency ? You'd have to define Refresh Groups and not refresh MVs individually.

          Hemant K Chitale
          • 2. Re: Application failover with mviews
            Girish Sharma
            I am not going further to your question because I am stuck at this line :
            If the db has any trouble customer wants to failover to box B
            means you want failover, don't want switchover ? I mean after resolving the issue at Box A, your customer don't want to switch over to Box A ?

            If yes, then SE only support failover, not switchover. For that customer have to EE.

            I have somewhere read that Niall Litchfield have a good presentation on 10g SE for standby.

            Yes, Now I found :

            PS :
            the amount of tables that will have updated TX is only 5
            At least I failed to understand it clearly this line.

            Girish Sharma

            Edited by: Girish Sharma on May 1, 2012 9:24 PM
            • 3. Re: Application failover with mviews
              Im fully versed with SE arc log apply failover so no need to discuss that side of it. (I do thank you for the effort in replying though)

              This is just a discussion of a potential solution for a method of having a standby system with uptodate data.

              Ill try explain further, customer has application. application points over tns to DB@box_A if database has failure that results in a decision being made that he needs to failover to DR (discussion for another day of what those events maybe), he can point application at alternate database on box_B that has no more then 15 minutes of data out from box_A.

              there are many schemas and objects on the database but for the purpose of this application I only have 5 TX (transaction) tables that are updated that I need to worry about for replicating to box B. there are many other static tables but I have these already replicated. So in the box B database I only need to keep those 5 tables in synch with Box A.

              So, I either setup SE manual arc apply, or I do something else. mviews seem the most likely candidate for fast refreshing to keep uptodate.

              I just found this note from the wonderful mr kyte. its sums up what I want to do and will probably be the driver for my solution.

              • 4. Re: Application failover with mviews
                Girish Sharma
                Im fully versed with SE arc log apply failover
                Thats great ! At least I found a member who says that I am fully versed with "x" topic of Oracle !

                But still my question/doubt is unanswered by you. When you are saying :
                if database has failure
                you wish to migrate on another box right ?

                Then, how will you able to sync the data on another box when source db is failure/down ?

                The link which you posted, db is alive; Tom is doing :

                ops$tkyte@ORA9IR2> create materialized view emp
                2 on prebuilt table
                3 refresh complete
                4 as
                5 select from emp@ora9ir1;*

                How it will work, when ora9ir1 (Box_A in your case) is failure/down ?

                ....Anyway, may be I am wrong or mis-understand something here. Let us know, how you worked.

                Girish Sharma
                • 5. Re: Application failover with mviews
                  If box A is down We obviously can't synch, I would have thought that self explanatory so I guess you may be asking something else. However, if the Mview refresh groups are working the way I want them to on box B we should be no more then 15 minutes out if the app had to be repointed to look at the box B.

                  The same could be said of manual arc log apply though.

                  Is this what you were asking?

                  If box B became production then to fail back it's recreate back the other way as per toms note.
                  • 6. Re: Application failover with mviews
                    further questions on this, when creating my mviews, I must create the constraints deferrable.

                    From Toms note, steps are

                    a) create empty tables in new database....
                    b) put constraints etc on them (deferrable please, important for mview groups!)
                    c) refresh them (fill them up)
                    d) put mv logs on remote tables
                    e) keep fast refreshing after that (could switch d/c above to avoid possible double complete
                    f) when ready to cut over -- kill access to old db, do a last refresh -- drop materialized views
                    and there you go.


                    and see further reference note from JL on deferrable constraints on mview

                    2 questions.

                    1. If I switch over at point F above, is it necessary to make the constraints not deferrable if they currently are in the prod table? I read through the rest Toms note and didnt see. Im a bit vague on the meaning of deferrable here. I understand it means the constraint wont be enforced until after commit but does this impact the functionality in anyway?

                    2. I have the indexes pre-created on the underlying table I will be creating the mview on prebuilt. Are the indexes stats automatically kept up to date with the nightly autogather or is there anything I got to worry about there, Im guessing they should be?

                    Im going to test this today anyway but appreciate any input.
                    • 7. Re: Application failover with mviews
                      Hemant K Chitale
                      See Oracle "Bugs" 7329341 and 6523114
                      • 8. Re: Application failover with mviews
                        Hemant K Chitale
                        I would think that Tom Kyte's note is about migrating data to a new database, rather than about setting up the ability to failover to an alternate database.

                        Hemant K Chitale
                        • 9. Re: Application failover with mviews
                          Thanks for the notes Hemant.

                          I think once the concepts and understanding of the solution are there then we can live with it as in the end I only had 4 tables I needed to replicate. Following Toms notes, I put the solution in place.

                          schema export from Box_A
                          schema import to Box_B and Truncate the 4 relevant tables, this was fastest way of replicating all objects
                          create the mview log on tables on Box_A
                          on box_B for the 4 tables, create the mview on prebuilt table as select from table@link_to_box_A
                          mview refresh complete (millions of rows first time) about 2 hours.
                          schedule refresh fast every 10 minutes, (ran a few times, success, takes seconds to fast refresh the tables as only a few hundred rows each time)

                          implement application failover to test,
                          stop app, 1 last fast refresh, then drop the mview leaving the table with all data in it. In a live situation we have to live with the last refresh time.

                          Repoint app server via tns at Box_B for test and application works. As I was in a test window I just repointed app back at box_A but in a live situation and I wanted to point back at Box_A then I would need to replicate all the steps back the other way to get latest changes.

                          So now to discuss what are the advantages and disadvantages?
                          1. very fast replication with the refresh ,
                          2. rest of the instance on Box_B can be open for business (nothing is updating any tables in my DR schema)
                          3. Lot easier to configure and admin then standby manual log apply
                          4. My DR schema can be queried for BI if needed although it is open for updates. more could be done here to protect the schema here if needs be but its a small shop and not worried about this.

                          1. must have primary key on table (this is more a limitation then a disadvantage, one of the tables didnt have 1, the mview will error out if you try, yes, yes, yes all tables should have PK but best practice table design outside scope here)
                          2. cant refresh BLOBS datatype. I tried this on one a table with a BLOB and it just hung. I couldnt find a note on support which listed this limitation so as my key tables didnt have BLOBs I didnt pursue it. May revisit this to get an answeer
                          3. number of bugs with fast refresh and indexes not replicating exact data. See Hemants notes above, none seemed to impact me here.