Open Data Guard standby in read-only mode Oracle Database Tips by Donald BurlesonDecember 9, 2015

Data Guard

Opening a Data Guard Oracle instance in Read-only Mode

As explained before, a physical standby database can be toggled between recovery and read-only mode. If the database is currently in managed recovery mode, in order to open it for reporting, cancel the recovery and open it as read-only. The following statements summarize the steps involved:

Opening the Oracle instance in Read-only Mode

• Stop the managed recovery process using:

ALTER DATABASE RECOVER MANAGED
Oracle instance CANCEL;

• Open database as read only:

ALTER DATABASE OPEN READ ONLY;

If the database is shutdown, in order to open it as read-only, mount the database as standby and then open it as read-only. The following statements illustrate this:

• Startup the database using:

STARTUP NOMOUNT;

• Mount the Oracle instance using:

ALTER DATABASE MOUNT STANDBY DATABASE;

• Open the database in read only mode using:

ALTER DATABASE OPEN READ ONLY;

To start recovery again on the standby site, terminate all active user sessions and issue the following statement:

ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION;

The v$session dynamic performance view can be used to find the active user sessions in the database.

When the database is open in read-only mode for reporting and query purposes, it is very likely that sorting operations will take place. In any database, sorting operations occur in memory specified by the sort_area_size or on a disk in a temporary tablespace. Ideally, all sorting operations should occur in memory, but that is not always possible, so temporary tablespaces are required.

If the temporary tablespace was not created during the creation of the physical standby database, it must be created before queries can be executed on the standby database. There are few things that must be considered about a temporary tablespace in the standby database:

A temporary tablespace cannot be created on the standby database.  Tempfiles can only be associated with a temporary tablespace. Therefore, the temporary tablespace should be present on the primary database before creating the standby database, or it should be created on a Oracle instance through the recovery mechanism.

Opening the Oracle instance in Read-only Mode

• The temporary tablespace should contain only temporary files.
   

• The temporary tablespace should be locally managed.

To add a temporary file to a temporary tablespace, open the Oracle instance in read-only mode and add the tempfile. The following statement adds a tempfile to the temporary tablespace temp on the standby database:

ALTER TABLESPACE TEMP
ADD TEMPFILE '/oracle/appsdb/data/temp01.dbf'
SIZE 200M REUSE;

Manual Recovery of Standby Databases

Starting with Oracle9i, manual recovery may not be the natural option for recovering a standby database, but this mode formed the foundation of Oracle instance technology back in the days of Oracle7.x. Apart from the historical importance, there are other good reasons to learn manual recovery.  The knowledge of manual recovery can prove to be very handy when the managed recovery is not working. Moreover, in some cases it may not be possible to establish an Oracle Net connection between the primary and the standby node, and in this situation manual recovery is the only option.