Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sujith.

Asked: December 25, 2005 - 5:40 pm UTC

Answered by: Tom Kyte - Last updated: June 29, 2020 - 12:47 pm UTC

Category: Database - Version: 9205

Viewed 50K+ times! This question is

You Asked

Hi Tom,

Once I open the Database using ALTER DATABASE OPEN READ ONLY, how can I change the status to READ WRITE??

Can a Database that is in Read-Write mode to be changed to Read-Only and Vis-Versa??

Thanks and I appreciate your help..

Kandy Train..

and we said...

read only and read write are modifiers of the alter database OPEN clause.

since an instance may mount and open only one database (you have to "kill it" and create a new instance - set of processes plus memory), you have to in effect shutdown and restart the instance to go from read only to read write or vice versa.

and you rated our response

  (30 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

READ ONLY Database

December 26, 2005 - 2:08 pm UTC

Reviewer: A reader

Thanks for the response. That is what exactly I had to do when I tried it my self, but I wanted to clarify it with the expert, because it wasn't cleary stated any where.

Thanks again.

Kandy Train

making a tablespace read only

August 29, 2006 - 2:37 am UTC

Reviewer: abz

Scenario:

I have a database which is shut down. There are several
tablespaces in that database, the users of that
database are geographically spread.

I want to make one of the tablespaces READ ONLY, and guarantee its state to be same as it was before startup.

problem is, to make that tablespace read only, I have
to OPEN the database, and then issue alter tablespace...,
but as soon as I open the database, and just before I issue ALTER TABLESPACE..., the users start logging
and they may change (through INSERT, UPDATE) the state
of that tablespace. 

Any solution to this?

I tried the following two things but did not worked.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2147840008 bytes
Fixed Size                   745480 bytes
Variable Size            1207959552 bytes
Database Buffers          838860800 bytes
Redo Buffers              100274176 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL> alter tablespace cisadm read only;
alter tablespace cisadm read only
                        *
ERROR at line 1:
ORA-16000: database open for read-only access


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2147840008 bytes
Fixed Size                   745480 bytes
Variable Size            1207959552 bytes
Database Buffers          838860800 bytes
Redo Buffers              100274176 bytes
Database mounted.
SQL> alter tablespace cisadm read only;
alter tablespace cisadm read only
*
ERROR at line 1:
ORA-01109: database not open
 

Tom Kyte

Followup  

August 29, 2006 - 7:10 am UTC

use restricted session. presumably your normal users won't have this privilege.

idle> startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
Database mounted.
idle> alter system enable restricted session;

System altered.

idle> alter database open;

Database altered.

idle> connect scott/tiger
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Warning: You are no longer connected to ORACLE.
idle> connect / as sysdba
Connected.
sys%ORA10GR2> alter system disable restricted session;

System altered.

sys%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2>


additional information

August 29, 2006 - 2:48 am UTC

Reviewer: abz

Please add this to the above message

I am using Oracle database version 9i Release 2

If its not possible in 9i, is it possible in 10g?

Thanks

making a tablespace read only

August 29, 2006 - 5:27 am UTC

Reviewer: abz

it is possible with opening database in RESTRICTED mode
and then making the tablespace read only and then remove
the restricted mode,

BUT then again, the users with RESTRICTED privilege might
update/insert/delete objects in that tablespace, so
the problem is not completely solved even by this solution.


Tom Kyte

Followup  

August 29, 2006 - 7:16 am UTC

then revoke restricted session from anyone that should not have it. If they have it, not too much we can do here.

if the users are remote, just shutdown the listener.

what about

August 29, 2006 - 9:38 am UTC

Reviewer: A reader

foreach datafile in (tablespace to read only)
chmod -w datafile

(in case you are using datafiles)
?

Tom Kyte

Followup  

August 29, 2006 - 3:29 pm UTC

go ahead, try that - see what Oracle does when it tries to mount and open those read write tablespaces...

a recommendation from metalink

August 30, 2006 - 9:10 am UTC

Reviewer: abz

Here is a recommendation from metalink for this problem.
But what if the client computer's power fail for 5,10
minutes after the first line of the script is executed
and before the second is executed.


++ startup database mount stage
> startu mount

++ Take the datafiles of that particular tablespace to offline.
> alter database datafile 'd:\data_file_location' offline;

++ Open the database
> alter database open;

++ save the scripts of making the datafile online and tablespace to read only in .sql file.
> alter database datafile 'd:\data_file_location' online;
> alter tablespace <tablespace_name> read only;

++ execute the .sql file , which will bring the datafile online and will take tablespace offline.
> @d:\file_name.sql


Tom Kyte

Followup  

August 30, 2006 - 9:20 am UTC

what do you think would happen?

the database would be started and mounted but not opened.

and it still doesn't prevent anything - since the alter online and alter read only are not atomic - and the alter read only must wait for ALL outstanding transactions in the database to commit - hence there could be a very very long time between the alter online of the datafile and the read only command.

I still think this is a 'restricted session' problem and too many people have restricted session in this scenario (almost no one should have that)

agreed but...

August 30, 2006 - 9:27 am UTC

Reviewer: abz

agreed , but you said
"....must wait for ALL outstanding
transactions in the database to commit ....",

The database is just STARTUP MOUNTED, how can there
be outstanding transactions there, and even such transactions which can take so long?


Tom Kyte

Followup  

August 30, 2006 - 9:32 am UTC

read the script again please.

there is an alter database open

followed by (iteration)
alter file ONLINE;
alter tablespace read only;


the open happens AND THEN the read only's start. Well, as soon as we open - users are in and active and doing tranactions.

sorry

August 30, 2006 - 9:53 am UTC

Reviewer: abz

oh yes, sorry, you are right.

iteration?

August 30, 2006 - 9:59 am UTC

Reviewer: abz

what you mean by (iteration) ??

Tom Kyte

Followup  

August 30, 2006 - 10:23 am UTC

loop, to do over and over, repeatedly.

how about restricting no. of user?

August 30, 2006 - 10:06 am UTC

Reviewer: abz

Is there any such init.ora parameter which can limit
the no. of users allowed to be logged, what I will do
is set that parameter to 1 and then start the database.

LICENCE_MAX_USERS will not work here.


Tom Kyte

Followup  

August 30, 2006 - 10:24 am UTC

geez, just shutdown the listener - enable restricted session and be done with it.

I assume people log in over the network, take away their listener.



people may use emulation software

August 30, 2006 - 10:34 am UTC

Reviewer: abz

People may use emulation software over a network, and log in the database from the OS, thus bypassing the listner.


i know its loop, but its executing only once in the
script file why you say its in loop




Tom Kyte

Followup  

August 30, 2006 - 10:47 am UTC

umm, because if you have more than one tablespace to deal with, you better be doing more than one right.

see what the script says:

...
++ save the scripts of making the datafile online and tablespace to read only in
.sql file.
> alter database datafile 'd:\data_file_location' online;
> alter tablespace <tablespace_name> read only;
.........


save the scriptS of making the data.....
^^^

they gave you psuedo code, I was merely pointing out that you will do the online/readonly over and over.



This is seriously time to "get your situation under control" - sorry, but people frankly should not be logging directly into the server box, these people should not have restricted session.

another answer from metalink

September 02, 2006 - 4:48 am UTC

Reviewer: abz

Here is another answer from metalink. But my question is,
even for creating the procedure and trigger , I have to open the database atleast once, and then again the users
start loging in.

"
Thanks for your update...

Kindly see below are the steps that can be followed for making tablespace read only during startup using
database startup triggers::

++ Step 1:

Create the below procedure:

CREATE OR REPLACE PROCEDURE sys.test_trigger AS
cursor1 INTEGER;
BEGIN
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'ALTER tablespace test read only',dbms_sql.native);
dbms_sql.close_cursor(cursor1);
END;

++ Step 2:

Execute the procedure sys.test_trigger

> execute sys.test_trigger;

++ Step 3:

Create the database Startup Trigger:

CREATE OR REPLACE TRIGGER trigger_tbsp
AFTER STARTUP ON DATABASE
BEGIN
sys.test_trigger;
end;

++ Step 4:

Shutdown the daabase

>Shutdown immediate

++ Startup database

>Startup

++ See the status of the tablespace:

> select * from dba_tablespaces where tablespace_name = '<tabsp_name>';
"

Tom Kyte

Followup  

September 02, 2006 - 11:59 am UTC

I give up, you've gotten what you are getting from me in the way of comments - sorry.



Excellent

September 04, 2006 - 2:24 am UTC

Reviewer: Steve from UK

haha - pull the <expletive deleted> network cable out - that'll sort 'em!

Tom Kyte

Followup  

September 04, 2006 - 8:55 am UTC

indeed, that is is. ABZ - pull out the cable, log on using the terminal, done.

no need to pull a cable

September 04, 2006 - 12:36 pm UTC

Reviewer: A reader

simply shutdown the network interface.

on unix: ifconfig [interface] down

Tom Kyte

Followup  

September 04, 2006 - 2:30 pm UTC

yah, but pulling the cable is so much more dramatic. Besides - next thing ABZ would say is "we have a job that automatically ifup's the network if it is down"

pulling the cable, yeah, that'll do it ;)

:)))))))))

September 04, 2006 - 3:29 pm UTC

Reviewer: abz

I am glad, pulling out the cable is really the solution:))))

The last two posts and followups are really interesting:))
"A solution with a smile":))))))))


Ok, one last question (Tom please dont mind :))

Do you think this is a real business case whose solution
can be requested in upcoming versions of ORACLE.



Tom Kyte

Followup  

September 04, 2006 - 7:52 pm UTC

nope, we already addressed it.... way way back years and years ago.

It is called "securing your system and only granting that which people need to people that should have it"



Read only DB question

November 28, 2006 - 5:20 am UTC

Reviewer: Yoav

Hi Tom,

We have a read only database that is using for running reports against it.
Since its a read only databse , i cant create statspack
to see what are the top sql statments ("SQL ordered by Gets" , "SQL ordered by Reads" ...)
Can you please suggest what to do in order to collect this information ?
Thanks.

Tom Kyte

Followup  

November 28, 2006 - 7:28 am UTC

you'd have to query the v$views (like v$sql and the like) and take a look at the information contained therein, bear in mind - that view is cumulative since the instance was started.

Statspack

November 29, 2006 - 9:38 am UTC

Reviewer: Jmv from Green Bay, WI

Hi,

From the "for what it's worth department", another solution might be to gather the statpack information and save it to another central repository.

Unfortunately I do not recall the steps to do it, nor the location of supporting documentation.

Yet another solution might be to make the database read/write, and tablespaces, other than the perfstat tablespaces, read only. Which would allow perfrstat to save the collected data locally, but not any other users.





true read only?

February 28, 2007 - 8:27 am UTC

Reviewer: abz

is the database opened in read only truly read only?
I was thinking that, although we cannot do INSERT, UPDATE,
DELETE, but we can do SELECT and that can also be
SELECT ... ORDER BY, and we know that SELECT can generate
UNDO, therefore that undo tablespace might be written?,
also when doing ORDER BY there might be sorts on disk
which write to TEMP tablespace, and due to above (UNDO and TEMP both being written), the
data dictionary might get written and therefore the data
files related to SYSTEM tablespace might also get written?

also the online redo logs might also get written?

Tom Kyte

Followup  

February 28, 2007 - 3:47 pm UTC

with a read only database like that, you would have to use true temporary tablespaces with tempfiles.

there is no undo. there is no updates to the system tablespace. there is no redo.

March 02, 2007 - 5:08 am UTC

Reviewer: A reader

Hi,

From the "for what it's worth department", another solution might be to gather the statpack
information and save it to another central repository.

Unfortunately I do not recall the steps to do it, nor the location of supporting documentation.

Yet another solution might be to make the database read/write, and tablespaces, other than the
perfstat tablespaces, read only. Which would allow perfrstat to save the collected data locally,
but not any other users.


true read only

March 02, 2007 - 5:08 am UTC

Reviewer: abz

So this means if we dont use true temporary tablespaces
with tempfiles, then it will
1- write to dictionary? and therefore SYSTEM datafiles?
2- It will write to datafiles of temporary tablespace?

Even if it is TRUE temporary tablespace with tempfiles,
the tempfiles will get written right? and didnt this writing
to tempfiles cause modificatios in the dictionary?

Tom Kyte

Followup  

March 04, 2007 - 5:42 pm UTC

it means it will not work.

the tempfiles will not cause writes to the dictionary - no. they manage space in the datafile header itself.

read only database

March 05, 2007 - 5:05 am UTC

Reviewer: abz

Just want to confirm my understandings.

you said

"it means it will not work"
1-
So we can say
"All statements causing/requiring
Sort/hash on disk will not work in a read only opened
database with dictionary managed temporary tablespaces"?

2- Will the following work?
SHUTDOWN IMMEDIATE;
...host out and remove all online redolog groups/members
...permanently.
STARTUP MOUNT
ALTER DATABASE OPEN READ ONLY;

... start query/reporting with this database.

I mean is it possible to open a database read only and
play with it, without any online redolog files on disk.?

Tom Kyte

Followup  

March 05, 2007 - 12:58 pm UTC

1) we can say you won't have a read only database without true temporary tablespaces.

2) no, I don't know where you were even trying to go with that one. What do the redo logs have to do with anything.

what is your goal here - there is only one way to have a read only database and we do not write to anything other then the tempfiles.

read only database - Tablespace and datafile

March 06, 2007 - 4:16 am UTC

Reviewer: Senthil from India

Hi Tom,

Oracle documentaion says the following :

you cannot take permanent tablespaces offline while a database is open in read-only mode. However, datafiles can be taken offline and online.

Could you please explain why we cannt make tablespace offline, since we are able to make datafile offline.

Thanks in Advance,

Regards,
Senthil
Tom Kyte

Followup  

March 06, 2007 - 11:05 am UTC

tablespace stuff would require dictionary updates (SYSTEM), datafile offlines do not - that is controlfile stuff.

March 07, 2007 - 2:03 am UTC

Reviewer: Senthil from India

Thanks Tom,

Regards,
Senthil

READ ONLY DATABASE

March 07, 2007 - 9:32 am UTC

Reviewer: abz

2) Actually, I was just experimenting that if we accidentally delete the online redolog files in closed
database, we can still open it read only and read write
with reset logs.


3) If we have Dictionary managed temp tablespaces, then
we cannot do the following right?
1- do incomplete recovery
2- alter database read only;
3- do some queries with large sorts
4- shutdown immediate
5- startup mount
6- restart recovery --- this will not be possible right? because the system tablespace/datafiles got written,
OR will the step 2 will fail.


Tom Kyte

Followup  

March 07, 2007 - 11:04 am UTC

you do not use that sort of temporary tablespace with a read only database.

September 03, 2008 - 4:51 am UTC

Reviewer: Ralph V. from Manila, Phils

Tom :

Does Oracle (by design) check all datafiles , including those from other tablespaces, in the DB before altering a tablespace to READ ONLY. I'm trying to skirt around one dbfile with a corrupted block while trying to transport a different tablespace and I encountered the following error. 

system@MCIEDW1> /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

5 rows selected.

system@MCIEDW1> ALTER TABLESPACE OLS_DUPL READ ONLY
  2  /
ALTER TABLESPACE OLS_DUPL READ ONLY
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 99, block # 1079009)
ORA-01110: data file 99: '/d0/oracle/oradata/mciedw1/temp_promo10.dbf'


system@MCIEDW1> select tablespace_name from dba_data_files
  2  where file_name='/d0/oracle/oradata/mciedw1/temp_promo10.dbf'
  3  /

TABLESPACE_NAME
------------------------------
TEMP_PROMO

1 row selected.



Tom Kyte

Followup  

September 03, 2008 - 11:42 am UTC

well, it surely doesn't scan them at that point in time - that would make making a tablespace read only infeasible.

is there an entry in v$database_block_corruption for this block?

Read Only Database Restore

November 06, 2008 - 12:51 am UTC

Reviewer: Deep from Cary, NC

Hi.
Can you please advise me on the following scenario?. I took a production hot backup at morning 9 AM.. restored it in a new server (say B) at 9:45.. and mounted the database on read only mode.. now if my prod crashes some time at say 12 AM.. Can I take a backup from B.. restore it on Prod.. and then apply the archive logs and restore the production db to the time of the crash/last archived log..

Thanks a lot
Deep
Tom Kyte

Followup  

November 11, 2008 - 2:15 pm UTC

if you took a hot backup, and then restored it, and then opened it - you must have also recovered it (but you neglected to mention that step :) )

therefore, it is no longer a backup - it is it's own database in it's own right.

drop object from read only table space

January 07, 2010 - 11:21 pm UTC

Reviewer: naresh kumar from india

can we drop the object from database which is started in read only.
and how it is possible, plz, tell me the workflow.
Tom Kyte

Followup  

January 11, 2010 - 8:53 pm UTC

when you drop the object what do we need to do?

update rows in the system tablespace - to unlink the object, if it isn't in the dictionary, it doesn't exist.

so that is what happens, we update the dictionary. In system. which is read write always.

Notify if database goes into READ ONLY mode

June 25, 2020 - 7:36 am UTC

Reviewer: Durga from India

Hi Team,

Recently we had an issue in our application - some of our databases went into a READ ONLY mode (I am not sure how).
Because of this the replication to these databases stopped and they didn't have the latest updated data on them.

How can we notify the application team if a database goes into READ ONLY mode?
I am asking because, when a database goes into READ ONLY mode we cannot login to it, right?

If we can login to a database which is in READ ONLY mode then we can query --
select open_mode from v$database;

If open_mode == 'READ ONLY'
send mail to app team.....

A ping from command prompt to database won't be foolproof , I think. I am not sure.

I am not a DBA, i don't have knowledge about this topic at all.
Please guide if you get time.
Thank you.
Chris Saxon

Followup  

June 25, 2020 - 12:25 pm UTC

You can connect to and query a read only database, you just can't make any changes to it.

So your proposal would work.

Really you need to get to the bottom of why the database was opened in read only mode - and ensure you have checks/processes in place to avoid this happening!

Notify if database goes into READ ONLY mode

June 26, 2020 - 12:43 pm UTC

Reviewer: Durga from India

Thank you very much for the quick response.

Where can we check for the possible reasons for a READ WRITE database to go into a READ ONLY mode? (given that this is not done manually)

Are there any errors to check for?
Do some events take place (which we can monitor) before the mode of a database changes?

Is there any other way to track this apart from checking the open_mode in v$database table?

Please excuse me if my question is not correct.
Chris Saxon

Followup  

June 26, 2020 - 3:14 pm UTC

Someone has to use the READ ONLY clause when opening the database, e.g.:

ALTER DATABASE OPEN READ ONLY;


Or:

ALTER PLUGGABLE DATABASE pluggabledb
  OPEN READ ONLY;


I suspect either there's a script around that does this or someone's covering their tracks...

Use SRVCTL

June 29, 2020 - 6:23 am UTC

Reviewer: A reader


You can use SRVCTL as well to check the open mode for the DB specified if it is RAC.

Will work for single instance RAC DBs as well assuming grid home is installed.

Hope it helps.

Cheers


Chris Saxon

Followup  

June 29, 2020 - 12:47 pm UTC

Good point.