表空间被删除,无备份恢复操作

首先说明这个方法是来自于 ITPUB上小V提供的方法,本人做了测试,能成功执行,具体执行方法如下

Retrieve deleted files on Unix / Linux using File Descriptors [ID 444749.1]
 Modified 04-MAY-2009     Type HOWTO     Status MODERATED   
In this Document
  Goal
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 10.2.0.1
Linux x86-64
Sun Solaris SPARC (64-bit)
Goal
Retrieve deleted datafiles/logfiles from the operating system if the database has not been restarted.
Solution
Using the PROC file system available on Unix/Linux, we can retrieve deleted datafile/logfile when all of the following hold good:-

1.) Database is not restarted.
2.) Server is not restarted.
3.) The file was not offline before deletion.

Background processes (DBWR, PMON, SMON etc.) have access to all the datafiles currently opened by the database. So, with the PID of a background procss, list of files currently opened by that process can be obtained by using 'lsof' command.
Also, there is a file descriptor associated with every file opened by a process. If the file gets accidently deleted from the operating system, its entry is not removed from the proc file system. Using that entry, we can recreate the deleted file.

This is explained with an example below.

1.) Create a tablespace
SQL> create tablespace my_test datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' size 200k;

Tablespace created.
2.) Accidently, datafile belonging to this tablespace got deleted
$ rm /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf
3.) Try resizing the datafile
SQL> alter database datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' resize 250k;
alter database datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' resize 250k
*
ERROR at line 1:
ORA-01565: error in identifying file
'/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
Rescuing the file

1.) Find the process id of DBWr process:-
Format is:-
--&gt $ ps -ef |grep ''| grep ''
$ ps -ef |grep EMR102U6|grep dbw
emrdbms 21943 1 0 10:27:08 ? 0:00 ora_dbw0_EMR102U6
Note that Process ID for DBW0 here is 21943.
2.) Find open files for this Process Id using 'lsof' command :-
$ lsof -p 21943 |grep /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf
Command
 PID
 USER
 FD
 TYPE
 DEVICE
 SIZE/OFF
 NODE
 NAME
 
oracle
 21943
 emrdbms
 270uW
 VREG
 304,25
 212992
 11273825
 /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf
 
Note:- If you are using NAS then the file name in above command may not be displayed properly and hence this procedure should not be used under these circumstances.
Note the value of FD in the table above (270) .
3.) Go to the file descriptors directory :-
Format is :-
--&gt $ cd /proc/ / '/
$ cd /proc/21943/fd/
4.)  Make the tablespace containing "deleted" datafiles READ-ONLY:
     alter tablespace my_test read only;

Making the tablespace read only freezes the file header, preventing updates from being made to the file header. Since this datafile is then at a read only state, it is possible to copy the file while the database is open.

This will allow users to select from the tablespace, but prevents them from doing inserts, updates, and deletes.
5.) Make a copy of file using file descriptor (270uW) :-
$ cat 270 > /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf
6.) To make sure that the old "deleted" copies of files will not be used after the copy has been done, do the following:

a) Take datafile offline
    alter tablespace my_test offline;

   Query the view v$datafile to verify the datafile is offline:
   select status from v$datafile where file#=;

b) Bring datafile back online
    alter tablespace my_test online;

7.) Put tablespace back in read write mode:
     alter tablespace my_test read write;
    Query view dba_tablespaces to check status of the tablespace:
    select tablespace_name,status from dba_tablespaces where tablespace_name='MY_TEST';
8.) Now datafile resize operation goes through fine.
SQL> alter database datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' resize 250k;
Database altered.
Note:- This procedure can also be used for retrieving the deleted current redo logfile.
References
NOTE:115424.1 - How to Rename or Move Datafiles and Logfiles

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14474335/viewspace-661914/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14474335/viewspace-661914/