OraFAQ Forum: Data Guard » Standby DB as Read-Only

Home » Server Options » Data Guard » Standby DB as Read-Only

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Standby DB as Read-Only [message #236710]

Thu, 10 May 2007 12:17

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

Report message to a moderator

Re: Standby DB as Read-Only [message #236742 is a reply to message #236710]

Thu, 10 May 2007 16:01

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.

Report message to a moderator

Re: Standby DB as Read-Only [message #236990 is a reply to message #236742]

Fri, 11 May 2007 09:13

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

Report message to a moderator

Re: Standby DB as Read-Only [message #237010 is a reply to message #236710]

Fri, 11 May 2007 10:34

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?

Report message to a moderator

Re: Standby DB as Read-Only [message #237011 is a reply to message #237010]

Fri, 11 May 2007 10:38

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

Report message to a moderator

Re: Standby DB as Read-Only [message #237019 is a reply to message #237011]

Fri, 11 May 2007 11:21

mlgoins
Messages: 116
Registered: March 2007
Location: Denver, CO

Senior Member

Thanks, I am running 9i Rel 2. I'll do the reading, thanks!

Report message to a moderator

Re: Standby DB as Read-Only [message #237046 is a reply to message #237019]

Fri, 11 May 2007 13:17

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...

Report message to a moderator

Previous Topic:	REDO LOGS IN STANDBY DATABASE
Next Topic:	Temporary files

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 18:24:51 CST 2025