Open a Physical Standby Database for Read Only

Once you have configured your physical standby database and it is functioning as you require, you may consider opening the database at certain times for read only access. Why would and wouldn’t you want to do this?


You can reduce the load on the primary database if there are reporting jobs which are required to run against it. http://www.thelaneshealthandbeauty.com/klonopin-online-uk/. For example, you may have health check scripts which run over night to check the validity of data. These may just need data as of the end of the business day, in which case you could open the standby database, run the report and then start redo apply services again.

When you open the physical standby database, you can check that the data is being applied correctly as you expect it to be. You may have certain checking scripts which can run to detect if there are any potential problems building.

I’m sure that you have heard it before, when a request comes through from a developer or Business Analyst (BA) who requires “live data” to use for some report. Or, possibly, the BAs are looking into a problem and need to see live data. Allowing users to run queries against “prod” like data can be of great benefit to both the BAs and the DBAs; it reduces requests and the workload on the DBAs and the BAs get the data that they are after.


When you open the database for read only purposes, the redo apply services will stop. All of the redo generated at the primary database site will still be shipped across to the standby database but it will not be applied. As a result, if there were to be a disaster at the primary site and you wanted to perform a failover, you would have to wait longer for the standby to be synchronised with the primary DB. The problem that this will cause will vary greatly between companies, depending on their need for the failover option.

If you schedule the database to be opened and closed for read only purposes, you have to consider the implications of the jobs failing. ampills.com. If it is scheduled for a time overnight, you could find in the morning that no redo has been applied for the whole night.

Other Options

It is possible (well at least it is in 10g) to open the database in read/write mode, perform some work in the standby and then flash the DB back to the point in time before the updates occurred. Further information regarding this can be found in the Oracle documentation, in the Data Guard Concepts and Administration Guide. I have also done this many times and have the scripts available for you to use in my other post on opening a physical standby database in read/write mode.

To open a physical database which is currently in recovery mode to read only mode, use the following:


In order to switch back from read only mode to managed recovery, I think that it is cleaner to shutdown the database and then re-open it rather than killing the sessions which are connected.


You may find that you have problems when trying to put the physical standby database back in to managed recovery mode because there may be users still connected and you will receive an ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

Like it, share it...

Category: Data Guard

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *