OPENING A STANDBY DATABASE IN READ-ONLY MODE

OPENING A STANDBY DATABASE IN READ-ONLY MODE

———————————————

In order to perform queries on a read-only standby database, the database

needs to be able to perform on-disk sorting operations. Locally managed

temporary tablespaces do not record transactions in the data dictionary.

Steps to create a locally managed temporary tablespace

1. Create a temporary tablespace on the primary database.

SQL> CREATE TEMPORARY TABLESPACE tbs_1 TEMPFILE ‘file_1.f’

SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

2. Create a user in the primary database with necessary privileges to perform

queries.

SQL> CREATE USER IDENTIFIED BY
DEFAULT TABLESPACE
TEMPORARY TABLESPACE ;

3. Grant the necessary prvileges to the user to perform the queries.

4. Archive the current redo logs that recorded these commands and apply

them to the standby database. So Tablespace definition and USER information will be applied to standby database.

5. Start up the standby database in read-only mode.

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> SELECT * FROM V$DATAFILE;

SQL> SELECT * FROM V$RECOVER_FILE;

SQL> ALTER DATABASE OPEN READ ONLY;

6. Add the tempfile to the LOCALLY MANAGED temporary tablspace.

SQL> ALTER TABLESPACE ADD TEMPFILE SIZE [M/K];

for example,

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/uocdb1/oradata/CIF/cif_temp.dbf’ SIZE 100M;

7. Logon as the user to perform read-only queries.

8. After performing all your read-only operations, you need to shutdown and restart your database in MOUNT STANDBY mode.