Home » Server Options » Data Guard » Standby DB as Read-Only
Standby DB as Read-Only [message #236710] Thu, 10 May 2007 12:17 Go to next message
mlgoins
Messages: 116
Registered: March 2007
Location: Denver, CO
Senior Member

Currently, the Standby Database has to be started like this:

SQL> Startup nomount;
SQL> Alter database mount standby database;
SQL> alter database recover managed standby database parallel 16 disconnect from session;

And then I can't open it [or show parameters, grant sysdba to a user, etc] -- only mount it.

Is there a clause that allows you to go ahead and open the Standby Database so that it IS read-only and OPEN?

Mike
Re: Standby DB as Read-Only [message #236742 is a reply to message #236710] Thu, 10 May 2007 16:01 Go to previous messageGo to next message
tinojam
Messages: 6
Registered: April 2005
Location: Florida
Junior Member

What version of Oracle are you using?

For Oracle10gR2:
When the physical database is down, simply do: STARTUP. This will bring up the database in read-only mode.

See example:

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

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY NOT ALLOWED

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 75499764 bytes
Database Buffers 88080384 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select open_mode, database_role, switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ ONLY PHYSICAL STANDBY NOT ALLOWED

SQL> select count(*) from scott.emp;

COUNT(*)
----------
14

SQL>

I hope this helps you.
Re: Standby DB as Read-Only [message #236990 is a reply to message #236742] Fri, 11 May 2007 09:13 Go to previous messageGo to next message
mlgoins
Messages: 116
Registered: March 2007
Location: Denver, CO
Senior Member

Here's as far as I get along that route:

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

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- ------------------
MOUNTED PHYSICAL STANDBY SESSIONS ACTIVE

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 705685208 bytes
Fixed Size 734936 bytes
Variable Size 285212672 bytes
Database Buffers 419430400 bytes
Redo Buffers 307200 bytes
ORA-01666: controlfile is for a standby database

Re: Standby DB as Read-Only [message #237010 is a reply to message #236710] Fri, 11 May 2007 10:34 Go to previous messageGo to next message
tinojam
Messages: 6
Registered: April 2005
Location: Florida
Junior Member

I don't know if this matters, but what version of Oracle are you running?
Re: Standby DB as Read-Only [message #237011 is a reply to message #237010] Fri, 11 May 2007 10:38 Go to previous messageGo to next message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
Hi ,

Tiojnams solution is specific to oracle 10g , for 9i database , you need to cancel the managed recovery.Then open the database with read only option , but remember to create temp tablespace/files other wise your queries are going to fail.


Thanks
Re: Standby DB as Read-Only [message #237019 is a reply to message #237011] Fri, 11 May 2007 11:21 Go to previous messageGo to next message
mlgoins
Messages: 116
Registered: March 2007
Location: Denver, CO
Senior Member

Thanks, I am running 9i Rel 2. I'll do the reading, thanks!
icon6.gif  Re: Standby DB as Read-Only [message #237046 is a reply to message #237019] Fri, 11 May 2007 13:17 Go to previous message
mlgoins
Messages: 116
Registered: March 2007
Location: Denver, CO
Senior Member

The answer to the question is in Oracle online documentation, at the following link:

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96653/manage_ps.htm#1014494

To open a standby database for read-only access when it is currently performing managed recovery:

Cancel log apply services:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open the database for read-only access:
SQL> ALTER DATABASE OPEN READ ONLY;

Just as you said! Now to check the temporary files/tablespace...
Previous Topic: REDO LOGS IN STANDBY DATABASE
Next Topic: Temporary files
Goto Forum:
  


Current Time: Sat Dec 10 04:57:36 CST 2016

Total time taken to generate the page: 0.11626 seconds