Yaping's Weblog

August 30, 2008

Restore deleted datafiles on Linux

Filed under: Oracle — Yaping @ 2:50 am
Tags:

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.

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: