How to open standby database for read only

Steps for opening a Standby Database for Read Only access when it is shutdown

Standby Database sid is sid1 here

1. Start the Oracle instance for the standby database without mounting it:

SQL> startup nomount pfile='/u01/app/oracle/product/9.2.0/dbs/

initsid1.ora';

2. Now Mount the standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

3. Now Open  database for read-only access:

SQL> ALTER DATABASE OPEN READ ONLY;

Steps for standby database for read only access when it is mounted and performing

managed recovery(MRP):

1. Firstly Cancel log apply services by:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Now Open the database for read-only access:

SQL> ALTER DATABASE OPEN READ ONLY;

Steps for converting Standby database from Read only to start performing managed

recovery(MRP):

1. Check all the connected sessions if there is any connected session then Terminate all

active user sessions on the standby database.

2. Now Restart log apply services:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

 2> DISCONNECT FROM SESSION;

example of converting standby database for Read Only access

Steps for opening a Standby Database for Read Only access when it is shutdown

Standby Database sid is sid1 here

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount pfile='/u01/app/oracle/product/9.2.0/dbs/initpri16.ora';

ORACLE instance started.

Total System Global Area  236000356 bytes

Fixed Size                    451684 bytes

Variable Size                201326592 bytes

Database Buffers               33554432 bytes

Redo Buffers                     667648 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from emp;

select * from emp

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn

Enter user-name: schema/pwd@sid1

Connected.

SQL> select * from emp;

NAME                                                                           

--------------------                                                           

john                                                                         

SQL> create table destination(location varchar2(100));

create table destination(location varchar2(100))

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

SQL> alter database recover managed standby database disconnect from session;

alter database recover managed standby database disconnect from session

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn

Enter user-name: sys/pwd@sid1

ERROR:

ORA-28009: connection to sys should be as sysdba or sysoper

Warning: You are no longer connected to ORACLE.

SQL> conn

Enter user-name: sys/pwd@sid1

Connected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> conn

Enter user-name: schema/pwd@sid1

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01219: database not open: queries allowed on fixed tables/views only

Warning: You are no longer connected to ORACLE.

SQL> show user

USER is ""

SQL> conn

Enter user-name: sys/pwd@sid1 as sysdba

Connected.

SQL> show user

USER is "SYS"

Steps for standby database for read only access when it is mounted and

performing managed recovery(MRP):

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> conn

Enter user-name: schema/pwd@sid1

Connected.

SQL> select * from emp;

NAME                                                                           

--------------------                                                           

john                                                                         

Steps for converting Standby database from Read only to start performing

managed recovery(MRP):

SQL> alter database recover managed standby database disconnect from session;

alter database recover managed standby database disconnect from session

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn

Enter user-name: sys/pwd@sid1 as sysdba

Connected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> conn

Enter user-name: schema/pwd@sid

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> conn

Enter user-name: sys/pwd@sid1 as sysdba

Connected.