Re: Not able to find data file on server but database is working fine

  • From: "Jeremy Paul Schneider" <jeremy.schneider@xxxxxxxxxxxxxx>
  • To: patrick.elliott@xxxxxxxxxxxxx
  • Date: Mon, 25 Jun 2007 18:50:55 -0500

Actually you can recover these files depending on your OS.

On Linux you can use the /proc filesystem.  You have to be root.  Warning:
be VERY careful, you can really mess up your filesystem if you don't know
what you're doing here.

# ll /proc/10785/fd
lrwx------  1 jschneider jschneider 64 Jun 25 16:17 0 -> /dev/pts/3
lrwx------  1 jschneider jschneider 64 Jun 25 16:17 1 -> /dev/pts/3
lrwx------  1 jschneider jschneider 64 Jun 25 16:17 2 -> /dev/pts/3
lr-x------  1 jschneider jschneider 64 Jun 25 16:17 3 ->
/usr/home/jscheinder/test2 (deleted)
# stat -L /proc/10785/fd/3
 File: `3'
 Size: 11              Blocks: 8          IO Block: 4096   regular file
Device: 803h/2051d      *Inode: 246096      *Links: 0
Access: (0664/-rw-rw-r--)  Uid: (  619/jschneider)   Gid: (  619/jschneider)
Access: 2007-06-25 16:18:06.000000000 -0700
Modify: 2007-06-25 16:12:39.000000000 -0700
Change: 2007-06-25 16:17:30.000000000 -0700


(that dereferences the still open file and gives you the inode - even though
it's deleted)


# debugfs -w /dev/sda3
debugfs: cd /home/jschneider/fixed
debugfs: ln <246096> test2

and shazam, you've got the file back.

-Jeremy



On 6/22/07, Elliott, Patrick <patrick.elliott@xxxxxxxxxxxxx> wrote:


I didn't mention ASM.  ASM is a different animal altogether, and I
understand that you are able to insert data and use the file.

Here is the way it works...
When you open a file in UNIX, you are opening an i-node.  When you
delete a file, the i-node does not get released until all processes that
have it open close it.  Oracle still has the file open, so the i-node is
in use and the database does not even know that the file has been
deleted.


Pat

-----Original Message-----
From: Harvinder Singh [mailto:Harvinder.Singh@xxxxxxxxxxxxx]
Sent: Friday, June 22, 2007 3:08 PM
To: Elliott, Patrick; oracle-l@xxxxxxxxxxxxx
Subject: RE: Not able to find data file on server but database is
working fine

This is not an ASM instance, also I am able to create table in this
tablespace and able to isnert data.

-----Original Message-----
From: Elliott, Patrick [mailto:patrick.elliott@xxxxxxxxxxxxx]
Sent: Friday, June 22, 2007 4:05 PM
To: Harvinder Singh; oracle-l@xxxxxxxxxxxxx
Subject: RE: Not able to find data file on server but database is
working fine


If the file was deleted outside the database, then it will still be held
open while the database is running.  The database sees the file because
it is still holding the i-node for the file.  If this is what happened,
then I would quickly create a new tablespace and move everything out of
the one that has the hidden file.  Otherwise, you will lose all of the
data in the tablespace when the database goes down.  The only way to
recover the file will be to do a recovery from backup and apply the
archive logs.

The only other situation that I can see causing this might be file
permissions.


Pat

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Harvinder Singh
Sent: Friday, June 22, 2007 2:18 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Not able to find data file on server but database is working
fine

Hi,

On the test machine the developer created a new tablespace and I can see
in the alert.log file that the command was successful. Also I can see
from dba_data_fies that the datafile is available and also the
tablespace is showing as Online in dba_tablespaces. Also I am able to
create and insert data into table created in this tablespace but problem
is I don't see the file on server and I done search(find) as root and
don't see file anywhere on the system. Where the file can be and what
can be the possible reason for this behavior?
Environment: Oracle 10.2.0.3 on RHAT4

Thanks
--Harvinder



--
//www.freelists.org/webpage/oracle-l



________________________________________________________________________
___________________________
CONFIDENTIALITY AND PRIVACY NOTICE
Information transmitted by this email is proprietary to Medtronic and is
intended for use only by the individual or entity to which it is
addressed, and may contain information that is private, privileged,
confidential or exempt from disclosure under applicable law. If you are
not the intended recipient or it appears that this mail has been
forwarded to you without proper authority, you are notified that any use
or dissemination of this information in any manner is strictly
prohibited. In such cases, please delete this mail from your records.

To view this notice in other languages you can either select the
following link or manually copy and paste the link into the address bar
of a web browser: http://emaildisclaimer.medtronic.com



___________________________________________________________________________________________________
CONFIDENTIALITY AND PRIVACY NOTICE
Information transmitted by this email is proprietary to Medtronic and is
intended for use only by the individual or entity to which it is addressed,
and may contain information that is private, privileged, confidential or
exempt from disclosure under applicable law. If you are not the intended
recipient or it appears that this mail has been forwarded to you without
proper authority, you are notified that any use or dissemination of this
information in any manner is strictly prohibited. In such cases, please
delete this mail from your records.

To view this notice in other languages you can either select the following
link or manually copy and paste the link into the address bar of a web
browser: http://emaildisclaimer.medtronic.com
--
//www.freelists.org/webpage/oracle-l





--
Jeremy Schneider
Chicago, IL
http://www.ardentperf.com/category/technical

Other related posts: