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

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
 

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

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.

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

(in case you are using datafiles)
?

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

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?

oh yes, sorry, you are right.

what you mean by (iteration) ??

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.

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

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>';
"

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

simply shutdown the network interface.

on unix: ifconfig [interface] down

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.

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.

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.

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?

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.

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?

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

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

Thanks Tom,

Regards,
Senthil

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 :

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.

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

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

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.

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.

ALTER DATABASE OPEN READ ONLY;

ALTER PLUGGABLE DATABASE pluggabledb
  OPEN READ ONLY;

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