Today, we faced a problem, that somebody accidentaly deleted symbolic links, which were pointing to the directory, where datafiles were located. It was a SAP database. There was a sapdata1 directory, where were all datafiles. And the there were sapdata2, sapdata3, sapdata4 directories, which were pointing to sapdata1. the unix guy accidentaly deleted those links. so the database was complaining, that the datafiles are not on the disks. As first thing we tryed was to create the symbolic links again. After the links were there, everything was back to normal. I wanted to understand this behaivior, so I tested it on my linux machine. The result was the same. SO I started to look on the internet, to find out, why it is so. And I found very interesting article from Franck Pachot. I have reproduced the whole scenario on my testing database, and here it is:
First, I have created new tablespace in my database:
create tablespace testing datafile '/oradata/ora10g/testing01.dbf' size 100M;
First, I have created new tablespace in my database:
create tablespace testing datafile '/oradata/ora10g/testing01.dbf' size 100M;
Now, we create table in it and fill it with data:
create table testing tablespace testing as select * from dba_objects;
create table testing tablespace testing as select * from dba_objects;
Now, I "accidentally" delete the datafile:
Now, when I want to insert data in the table I got an error:
The datafile is not there and the data are not accessible. However, the datafile should have open file descriptor by an oracle background process.
check the PID of the database writter:
check the PID of the database writter:
In my case, it is 3427.
And now, we check its opened file descriptors for our file:
ls -l /proc/3427/fd |grep testing
And now, we check its opened file descriptors for our file:
ls -l /proc/3427/fd |grep testing
The fd number is in my case 25
Now, first we set up an symbolic link, so the oracle will see the datafile as before deletion:
ln -s /proc/3427/fd/25/oradata/ora10g/testing01.dbf
Now, first we set up an symbolic link, so the oracle will see the datafile as before deletion:
ln -s /proc/3427/fd/25/oradata/ora10g/testing01.dbf
Now, the data are accessible again. But will be lost, if the DBW close its file descriptor (the DB restart).
So now, we set the tablespace read only, so it will be checkpointed and no writes occurs on it.
So now, we set the tablespace read only, so it will be checkpointed and no writes occurs on it.
Now, we can safely copy the file back to its directory.
First we delete the symbolic link we created and then we copy the datafile:
First we delete the symbolic link we created and then we copy the datafile:
Now, we have the data back, so we can put the tablespace back to read write mode and try to insert in the table:
This is not to be used like that in production. This is unsupported and may behave differently on different unix/linux or oracle versions.