Diverting Queries to Standby database
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Diverting Queries to Standby database

  1. #1
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588

    Question

    Hi All,

    I have a question about diverting queries (just retriving of data) to standby databsse.

    F.ex I have a aplication which process patients data.

    Application has one Reports module. I want these reports to point to standby database as there no UPDATE/DELETE/INSERT operation. So If I MOUNT my standby database in READ-ONLY mode, I will able to do this, then this can be achived.

    Question is:

    How do I divert ***only*** Reporting Module to Standby database?

    Do I have to use to two differnt connections.. one will point to Live DB and other will point to STDB.. Does this mean division of application?

    Thanks

    Sameer

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Sameer,

    you have to use two different service names(in tnsnames.ora) one connects to live db(primary host) and standby db(stby host). Similar to services you set up in init.ora for automated recovery of standby database thru RFS process.
    Reddy,Sam

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Hi,

    Thanks Sam for the reply..

    I was wondering how queries in one application will able to divert to STDB.. That means I have to take care while designing application. So that I need to seperate-out those queries to different module which needs only read only access to DB and will point to STDB service.

    Or May be a second though: Pl. Let me know your suggestion.

    What If I make a DB link which points to STDB database. In this case I don't have to seperate my application and I don't have to use two different connets then. In this case my application will also remain bind.

    Thanks

    Sameer

  4. #4
    Join Date
    Mar 2001
    Posts
    144
    Yes that will work but the biggest thing about that is the network connection. That could be your bottle neck especially during peak times.

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    If reports module is integrated component of your application, then you are left with no choice of having DBLINK, otherwise configure a dedicated service name for reports module which connects to standby.

    dblink is not a bad idea as you were claiming resources on primary and distributing the reports load to standby. Make sure your network bandwidth meets reports module performance.
    Reddy,Sam

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    So it's not a stand-by database and what you are talking is a "backup" database.

    A standby db will always be in persistant recovery mode and will be looking for the archive logs to apply to it.

    We have a similar environment where we call all ther reports from a reportdb, which get refreshed on a daily basis using the batch updations.

    Badrinath

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    badrinathn,

    You have a GOOD point... I was simply answering questions without making member to realize the point you mentioned...
    Reddy,Sam

  8. #8
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by sreddy
    badrinathn,
    A standby db will always be in persistant recovery mode and will be looking for the archive logs to apply to it.
    Is it possible to keep open Standby database in READ ONLY mode + RECOVER MANAGED STANDBY DATABSE

    Then how can one achive diverting queries to standby database in read only mode.. In this case then standby database becomes a backup database.

    I am not talking about updating DB in batch process or updating it on some intervals...

    Now the question is can I keep standby db open in read only mode and apply logs on background using managed recovery

    Sameer

  9. #9
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    I am not sure how we can put the standby database in the read only mode. 'cos, you mount the database in 'standby mode', there is no where you will be able to mount it in 'read only ' mode.

    Further as the database name is going to be the same it's logical that you cannot access the 'standby database'.

    In our case, we have it on a separate box and differnt name 'backupdb' and the applications go with different tnsname.

    Using the dblink is a most preffered way!! we don't use it though.

    Let me know if this helps

    Badrinath

  10. #10
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by badrinathn

    Badrinath:
    I am not sure how we can put the standby database in the read only mode. 'cos, you mount the database in 'standby mode', there is no where you will be able to mount it in 'read only ' mode.

    Further as the database name is going to be the same it's logical that you cannot access the 'standby database'.

    Sameer:
    Now the question is can I keep standby db open in read only mode and apply logs on background using managed recovery


    It is possible to mount standby database in read-only mode and access it.

    While the standby database is in read-only mode, the site can still receive archived redo logs from the primary site. Nevertheless, Oracle does not apply these logs automatically, as in managed recovery.

    So my option of keeping it in read-only mode and applying logs on background is now out....

    One can achive this but in that case he must have two standby databases.. One will be in managed recovery mode and other will be in read-only mdoe.. He has to synchronize the readonly standby database periodically...

    FYI

    http://www.cs.rose-hulman.edu/docs/o...dbyr.htm#14723

    If above link is not accessable pl. let me know I will copy paste the material..

    http://otn.oracle.com/docs/products/...co.htm#1006255

    Sameer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width