-
Currently we copy our production database over every morning to create a read only database that is used solely for long running reports. This process is automated with scripts and takes about 2.5 hours. My question is would a standby database be a suitable alternative to this? I don't know which would be more desirable, the read only database in a constant state of recovery or having it recreated every morning. The obvious benefit of having the read only database a standby database would be that should there be a production failure, we could quickly "fail over" to the standby and activate it. But we have never used a standby db before and are not sure how easy they are to maintain. Or is this scenario even possible? Some standby articles I have read indicate that for it to be in read only mode, the recovery has to be cancelled. Am I understanding this correctly?
Any thoughts on the matter would be greatly appreciated.
-
AFAIK, yes, if you want to open the db, say for reporting, you have to suspend the recovery being performed on the standby db. Another option is to create snapshot(s) for reporting purposes.
-
Yes, you understand the concept of standby correctly. When standby is opened in read only mode, it can obviously not be in automated recovery mode at the same time, so when you return it from OPEN READ ONLY to automated recovery it must apply all the arch redo logs that were transfered from the primary database in the meantime, but everything is totaly automated and transparent for you.
If you are using 8i Enterprise Edittion then I would definitelly use standby in your situation. In 8i EE the administration of standby is realy extremely simplified, certainly more simple than recreating another database from your production database every morning. And it brings many additional advantages (no need to backup primary database if you backup standby, can be used for early block corruption detection, etc...).
-
Standby databases are primarily used for high availability,
most of the time they spend being in a state of constant recovery
(process of applying of archive logs from primary database).
End-user access is impossible when standby database is in recovery mode.
If primary database fails, standby database can be activated quickly
and downtime is usually quite low (15-30 minutes). But once activated,
the standby database can not be placed back to recovery mode and
must be created again if needed.
In Oracle8i standby database can be temporarily placed into read only mode
and then back to recovery mode. End-users can run various reporting
applications when the standby database is in read only mode.
But in read only mode archive logs can not be applied to standby database
and it will lag behind the primary database. If the primary database fails,
the standby database can be far behind, its activating will require applying
all accumulated archivelog files, and downtime may increase too much.
For this reason, standby databases are kept in recovery mode most of the time.
If you are interested in reporting database only and don't consider
high availability issues like minimizing downtime etc., you can keep
standby database in read only mode all the time, and place it into
recovery mode only for refresh, to apply accumulated archive logs
from the primary database.
-
Thanks for the response. Yes, we will be upgrading to Oracle 8i EE from 8.0.5.1 very soon. I'm just worried about how long it will take to apply all the accrued redo logs that are generated while the standby db is open and in read only mode. I need to try and figure this out how many redos are generated in the production db in a 24 hour period. If it would take more than 3 or 4 hours to put standby back in recovery mode and apply all these logs, they may not want to go this route.
Thanks for the input!
-
the other option is use snapshot, refresh each morning or 2 twice a day it should much faster.
