How to open Manual Physical Standby Database in READ ONLY mode – Oracle 11g Manual Data Guard Part-III

You can use Manual Physical Standby database for query access for query offloading and reporting purposes by making a standby database to read only mode, resulting we can use the primary database exclusively for core purpose.(i.e. OLTP)


Simply you can’t apply archive logs to standby database server when database is in READ ONLY mode, but you can continue to transfer your archive logs to standby database server, Once your standby database back to MOUNT mode than you can apply your remaining archive logs.

Previously, We covered Manual data guard switch over and switch back activity step by step approach.

Steps are as follows:


Verify database name, its mode and role by following command:

SQL> select name,open_mode,database_role from v$database;

The database is in mount state and its role is Physical Standby, as expected, good to go.


Transfer all remaining archive logs from the primary database server to standby database server and apply it (If there is an archive log gap), before opening a standby database to READ ONLY mode. For more information on it, you can refer Step ā€“ XVI from my one of post on “Configuring Manual Data Guard”.


Delete existing tempfile[s]:

select from v$tablespace ts,v$tempfile tf where ts.TS#=tf.TS# and’TEMP’;
alter database tempfile ‘Above_temp_file[s]’ drop;


Open your Standby database in READ ONLY mode from MOUNTED mode:

SQL> alter database open read only;


Create new tempfile[s]:

SQL> alter tablespace TEMP add tempfile '/path/temp02.dbf' size 200m autoextend on maxsize unlimited;


Repeat Step-I to verify standby database mode and its role.


Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on Incremental backup from SCN.

Thank you. šŸ™‚

Leave a Reply

%d bloggers like this: