General
cancel
Showing results for 
Search instead for 
Did you mean: 

Can Oracle write in offline datafile?

Pradeep_3
Frequent Advisor

Can Oracle write in offline datafile?

Hi all
We have Oracle 8.1.7.3 database (raw database)running on HP-UX 11.11. Recenctly we added one datafile on filesystem bymistake.

We made it offline checking and ensuring no data was there on that datafile and created new datafile on raw device correctly.

After three days our application team asked us to make that offline datafile online as application log was showing error that problem reading that datafile.

But there was no error in oracle alert log file. Then we made the datafile online and it has asked for recovery. After recovering datafile in become online and started working.

My question is that even after making datafile offline why Oracle database reffered that offline datafile?

Thanks in advance
Paresh
7 REPLIES
Steven E. Protter
Exalted Contributor

Re: Can Oracle write in offline datafile?

It may not be the datafile or the application.

The applications team is getting errors because the application may be trying to read and write to the offline datafile. They may have a design problem/question on their hands.

I can assure you that while I was doing disaster recovery on an oracle database that was unfortuneate enough to be sitting on a disk drive that died(three years ago), no data was written to that datafile.

During the recovery process though all the datafiles but the problem one were online, so it was possible to start the application. It generated errors because tables it needed were not available.

Your situation is a little different but really looks like an applicaitons problem.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Massimo Bianchi
Honored Contributor

Re: Can Oracle write in offline datafile?

Hi,
making a datafile offline simply means that oracle have not to use it, for example because you want to preserve some data, or because you have a faulty disks and you need to open your database for other purpose, but you do not have that datafile.

Recover was needed because, when you put a datafile offline, its SCN is freezed.

When datafiles is put back online, SCN is compared to the current SCN, and recovery is made if necessary.

I think that you had reference to that datafile because some of the application went to check the db structure, and found and offline datafile, that usually is not a good thing.


HTH,
Massimo
Massimo Bianchi
Honored Contributor

Re: Can Oracle write in offline datafile?

Hi,
in case you want to convert your datafile from fs to raw dev, here is a little procedure from oracle.


It examples the procedure from raw to fs, but it is very clear how to revert it :)


goal: How to convert datafile from raw device to file system
fact: Oracle Server - Enterprise Edition



fix:

Use RMAN to move datafiles from raw devices to file system.

1. Connect to the database:

$ sqlplus system/manager@orcl

2. Put the tablespace with the datafile, which should be converted, offline:

SQL> alter tablespace test_ts offline;

3. Start rman and connect it to the database:

$ rman nocatalog target rman/rman@orcl

4. Move the datafile to file system:

RMAN> run {
2> allocate channel c1 type disk;
3> copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf';
4> }

5. Rename the moved datafile:

SQL> alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts.
dbf';

6. Put the tablespace back online:

SQL> alter tablespace test_ts online;


Notes:
======
1. If you are using RMAN as the backup tool then a backup after the performed
steps is recommended, because otherwise RMAN treats the copied file as a
backup.

2. Usually Oracle datafiles are moved from filesystem to raw devices using
the dd command. Using dd is the fastest method to accomplish it. However, it is
necessary to know how many blocks to skip in the raw device (e.g. it is
necessary to skip 64K on Tru64 Unix), so that the information necessary for
the Operating System is not overwritten. The information on how many blocks to
skip is different on the different platforms. Using RMAN there's no necessity
to know such platform specific information.



HTH,
Massimo
Tom Geudens
Honored Contributor

Re: Can Oracle write in offline datafile?

Hi Paresh,
Just from the top of my head (I used to be a DBA but that's some time in the past now). Others may be more specific/correct.
- The moment you added that file, Oracle noted it his datadictionary and kept a SCN (system change number) for it's datablocks.
- Unless you drop the tablespace (or the file from the tablespace, can't remember if that is possible), Oracle will keep "remembering" that file. You can remove it on OS-level, put it offline, do whatever you like with it, but Oracle will know when it could last write to it.
- And that's why Oracle asked for recovery. It wanted to bring the SCN of that file's datablocks up-to-date with the rest of the database. Once that was done/possible ... everything started working again.

Hope this starts to explain ...
Regards,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
Tim Sanko
Trusted Contributor

Re: Can Oracle write in offline datafile?

First,

IF you or I would screw this up you can recover from the exp. I suggest a good physical DBA to investigate it.

if you have allocated tablespaces / tables / indexes to the datafile you should have exp them, then drop them index by index, table by table, tablespace by tablespace and finally the datfile itself. Even if nothing was allocated to the raw datafile itself, it is still part of the database.

Drop the datafile and you may be happy.

Please exp first, drop only the datafile and its correlation by direction.

Again a good DBA should investigate it.

Tim
Graham Cameron_1
Honored Contributor

Re: Can Oracle write in offline datafile?

Be careful.

I also once added a datafile to the wrong tablespace by mistake, although was filesystem, not raw.
Once you have done this you cannot drop just the file.
You can add a datafile, rename a datafile, but you cannot drop it, even if Oracle has no objects in it and it is offline.
What I did was "ALTER DATABASE DATAFILE OFFLINE DROP", on the mistaken assumption that this would solve my problem.
It didn't, and the only way to drop the file was to drop and recreate the tablespace.
The above was using 8i, but I have checked the 9i documenation, and the same is still true, although you can now drop a tempfile.

Hope this helps.

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Stan_17
Valued Contributor

Re: Can Oracle write in offline datafile?

Paresh,

Can you tell us the error your apps team reported after you took the datafile offline ? was it ORA-00376/ORA-01110.

If they had reported above errors, then definitely there must be some objects lurking in that datafile and apps reported the error when it attempted to read that datafile. I don't think oracle will log these error messages in alert.log file as these are normal ones.

when you took the datafile offline, a smaller version of a checkpoint was issued on that datafile whereby any data related to that datafile in buffer cache will be written too (if any) and put the datafile in frozen state after updating the datafile header with latest SCN.

After three days, you tried to place the datafile online which means the datafile SCN wouldn't have matched with other datafile's SCN as well the control file SCN. hence the datafile recovery brings the offline datafile to up-to-date with other files.

hth,
Stan