Diverting Queries to Standby database

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

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.

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

Yes that will work but the biggest thing about that is the network connection. That could be your bottle neck especially during peak times.

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.

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

badrinathn,

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

Quote:

---

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

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

Quote:

---

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

I understand that. If the purpose of standby is for load balancing purpose its Ok. If the real purpose of standby is for high availability, you will be in trouble cuz, standby is not in recovery mode. Depending upon hte size of the logs and number of logs its might take hours or days to sync up with primary... and you have to safe guard your archive logs until the recovery is applied. Even if single log missed you can't apply recovery and you will end up rebuilding the sdtandby from scratch...

Hope you got what I mean...

Thanks for the advice...

I understood what you mean and the seriousness if something goes wrong.

Now I will change my design and will have a seperate backup database which will be used for reporting insted of using standby database for the same.

Thanks

Sameer