DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2000
    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.

  2. #2
    Join Date
    Apr 2000
    Edison, NJ
    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.

  3. #3
    Join Date
    Dec 2000
    Ljubljana, Slovenia
    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...).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Apr 2001
    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.
    Brainbench MVP for Oracle DBA

  5. #5
    Join Date
    Oct 2000
    Thanks for the response. Yes, we will be upgrading to Oracle 8i EE from 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!

  6. #6
    Join Date
    Nov 2000

    the other option is use snapshot, refresh each morning or 2 twice a day it should much faster.

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.