Restore deleted datafiles on Linux/Unix

If datafiles were deleted directly by OS command on Linux/Unix, how to restore them back? I demo it with one method at below.

I run the experimentation on my laptop, 1G memory and 512M swap, running RHEL 4 with Oracle 9208.


@>create tablespace mytest datafile '/u04/oradata/9208/chen/mytest01.dbf' size 2049m;

Tablespace created.

@>create table test.t1(id number,a char(2000),b char(2000),c char(2000)) tablespace mytest;

Table created.

@>create sequence test.seq_t1;

Sequence created.

@>begin

2 for i in 1..1000000 loop

3 insert into test.t1 values(test.seq_t1.nextval,'A','B','C');

4 if mod(i,1000)=0 then

5 commit;

6 end if;

7 end loop;

8 end;

9 /

begin

*

ERROR at line 1:

ORA-01653: unable to extend table TEST.T1 by 8192 in tablespace MYTEST

ORA-06512: at line 3

@>select count(*) from test.t1;

COUNT(*)

----------

262000

@>!rm /u04/oradata/9208/chen/mytest01.dbf

@>!ls -l /u04/oradata/9208/chen/mytest01.dbf

ls: /u04/oradata/9208/chen/mytest01.dbf: No such file or directory

@>alter database datafile '/u04/oradata/9208/chen/mytest01.dbf' resize 2100m;

alter database datafile '/u04/oradata/9208/chen/mytest01.dbf' resize 2100m

*

ERROR at line 1:

ORA-01565: error in identifying file '/u04/oradata/9208/chen/mytest01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

@>!ps -ef|grep smon

oracle 2595 1 0 02:47 ? 00:00:00 ora_smon_chen

oracle 2863 2584 0 03:32 pts/1 00:00:00 /bin/bash -c ps -ef|grep smon

oracle 2865 2863 0 03:32 pts/1 00:00:00 grep smon

@>!lsof -p 2595|grep /u04/oradata/9208/chen/mytest01.dbf

oracle 2595 oracle 18u REG 8,65 2148540416 376836 /u04/oradata/9208/chen/mytest01.dbf (deleted)

@>!cat /proc/2595/fd/18 > /u04/oradata/9208/chen/mytest01.dbf

@>!ls -l /u04/oradata/9208/chen/mytest01.dbf

-rw-r--r-- 1 oracle oinstall 2148540416 May 26 03:39 /u04/oradata/9208/chen/mytest01.dbf

@>!lsof -p 2595|grep /u04/oradata/9208/chen/mytest01.dbf

oracle 2595 oracle 18u REG 8,65 2148540416 376836 /u04/oradata/9208/chen/mytest01.dbf (deleted)

@>alter database datafile '/u04/oradata/9208/chen/mytest01.dbf' resize 2100m;

Database altered.

@>select count(*) from test.t1;

COUNT(*)

----------

262000

@>startup force

ORACLE instance started.

Total System Global Area 487658188 bytes

Fixed Size 451276 bytes

Variable Size 369098752 bytes

Database Buffers 117440512 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

@>!ps -ef|grep smon

oracle 3475 1 0 03:43 ? 00:00:00 ora_smon_chen

oracle 3486 2584 0 03:43 pts/1 00:00:00 /bin/bash -c ps -ef|grep smon

oracle 3488 3486 0 03:43 pts/1 00:00:00 grep smon

@>!lsof -p 3475|grep /u04/oradata/9208/chen/mytest01.dbf

oracle 3475 oracle 16u REG 8,65 2202017792 376837 /u04/oradata/9208/chen/mytest01.dbf


This method has one restriction: the file descriptor fd can’t be closed, such as offline file, shutdown database/server, etc.