DBA Tips Archive for Oracle


Placing Standby Database in READ ONLY Mode

by Jeff Hunter, Sr. Database Administrator


  1. Introduction
  2. New READ ONLY Feature of Oracle Standby Database
  3. Opening Database in READ ONLY Mode
  4. Performing Disk-Based Sorts on a READ ONLY Database


This article provides detailed instructions and notes for putting an Oracle standby database (Oracle8i) in READ ONLY mode.

Keep in mind that while the Oracle standby database (Oracle8i and lower) is in READ ONLY mode, it cannot also be in manged recovery mode.

The Standby Database feature of Oracle (Oracle7 and higher) allows the DBA to maintain a duplicate, or standby, copy of a database at a remote site to provide continued primary database availability in the event of failure. The standby database is created with a special copy of the control file from the primary database. The standby database is kept in close synchronization with the primary database by applying the primary database's archived log files to the standby database. It is therefore necessary to operate the primary database in Archivelog Mode to avail the benefit of a standby database.

A standby database is generally used where high availability of data is required. A standby database is one way of quickly providing access to data if the primary database fails and recovery will take longer than the desired time.

New READ ONLY Feature of Oracle Standby Database

A new feature of the standby database in Oracle8i, is the ability to open the database in READ ONLY mode. While the standby database cannot be maintained while in READ ONLY mode, (using managed recovery mode), having the standby database in READ ONLY mode provides the ability to use the processing capacity of the standby host during off hours batch processing for example, and return to managed recovery mode in the morning.

Opening Database in READ ONLY Mode

In order to open the database in READ ONLY mode, the DBA will first want to "cancel" any current recovery session. From another session:
SQL> alter database recover managed standby database cancel;

NOTE: It is possible that the standby database was configured to shutdown the standby database instance when managed recovery mode is cancelled. If this is the case, the command above will shutdown the standby instance and you will need to mount the standby database in standby mode before attempting to put the standby database in READ ONLY mode:
SQL> startup nomount
SQL> alter database mount standby database;
SQL> recover standby database until cancel;

The database can now be opened in read-only mode:

SQL> alter database open read only;

Database altered.
At this stage, the standby database is opened for reporting type queries. You will not be able to perform DML operations like INSERT, UPDATE, and DELETE.

Performing Disk-Based Sorts on a READ ONLY Database

Disk-based sorts create sort segments on disk, which traditionally incurs DML operations on certain dictionary tables. Thus, disk sorts, which are managed in this way, are prohibited on READ ONLY databases. If a session attempts a disk sort and the standby database is configured with dictionary managed extents for its TEMP tablespace, you will receive the following error message:
ORA-01647: tablespace 'TEMP' is read-only, cannot allocate space in it
If you already have a locally managed TEMP tablespace defined for the standby database, you will receive the following error message:
ORA-25153: Temporary Tablespace is Empty

NOTE: To perform disk sorts on READ ONLY databases, you must use a locally-managed temporary tablespace, consisting of one or more 'tempfiles'. (At least this is how I managed to get it to work!)

Extent allocation in locally-managed tablespaces is controlled by a bitmap stored in the tablespace itself (in contrast to dictionary-managed tablespaces, the default, where extent allocation is managed in the data dictionary).

Tempfiles definitions are not stored in the controlfile and data dictionary. It is not possible to create a suitable temporary tablespace on the standby, and so it must be created on the primary database.

The following steps allow the DBA to configure the standby database for READ ONLY queries.

  1. Create a locally-managed temporary tablespace, specifying a tempfile:
    create temporary tablespace temp_ro tempfile
    '/u06/app/oradata/ORA817/temp_ro.dbf' size 10m
    autoextend on next 10m maxsize unlimited
    extent management local uniform size 1m;

    NOTE: It is very possible that the standby database alrady has a TEMP tablespace that was configured with a locally managed tempfile. If this is the case, you can skip all the steps below and simply add the tempfile to the standby database as follows:
    alter tablespace temp add tempfile
    '/u06/app/oradata/ORA817/temp01.dbf' size 10m reuse
    autoextend on next 10m maxsize unlimited;

  2. The users who will be performing the disk sort should have their temporary tablespace definitions pointing to the newly created tablespace, for example:
    SQL> alter user scott temporary tablespace temp_ro;

  3. Create the standby database from the primary. Note that you do not have to transfer the newly created tempfile to the standby.

  4. Recover and open (read-only) the standby.

  5. Connect as the user performing the disk sort.

  6. Now for the trick. If you try to perform a disk sort at this stage you will get the following error:
    ORA-25153: Temporary Tablespace is Empty
    Before attempting a sort, you must add a new tempfile datafile to the tablespace, for example:
    alter tablespace temp_ro add tempfile
    '/u06/app/oradata/ORA817/temp_ro.dbf' size 10m;

    NOTE: The reason for this is that tempfile definitions are not stored in the "SYS.FILE$" dictionary table. Because adding a tempfile on the primary does not update "SYS.FILE$", there is no redo generated. So recovery on the standby cannot create the file. However, changes are made to the tablespace dictionary table, "SYS.TS$", so the tablespace definition is in the standby dictionary.

  7. Disk sorts are now possible.

    NOTE: Changes to the standby database (creation of the temporary tablespace (1), and changes to the user's definition (2)) can alternatively be made on the standby by applying the relevant redo to the standby while it is in recovery mode (rather than making the changes to the primary before creating the standby database). However, it will still be necessary to manually add the tempfile to the standby for reasons explained in (6).

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 21-Jun-2007 16:31:21 EDT
Page Count: 51076