lsof恢復誤刪的檔案

[oracle@dg ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 10:39:00 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

10:40:10 SYS@wailon> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------------------------------------
TBS_LRJ                        /u01/app/oracle/oradata/lrj.dbf                               -- 模擬要刪除的檔案
SYSTEM                         /u01/app/oracle/oradata/system01.dbf
SYSAUX                         /u01/app/oracle/oradata/sysaux01.dbf
UNDOTBS1                       /u01/app/oracle/oradata/undotbs01.dbf
USERS                          /u01/app/oracle/oradata/users01.dbf

10:40:51 SYS@wailon> select segment_name,owner from dba_segments where tablespace_name='TBS_LRJ';

SEGMENT_NAME                                                                      OWNER
--------------------------------------------------------------------------------- ------------------------------
UNDOTEST                                                                          SYS
REDOTEST                                                                          SYS
BBED_TEST                                                                         LRJ

10:41:01 SYS@wailon> select count(*) from redotest;

  COUNT(*)
----------
     10000

10:41:28 SYS@wailon> !rm -f /u01/app/oracle/oradata/lrj.dbf        -- 刪除檔案

10:41:45 SYS@wailon> !ls -lk /u01/app/oracle/oradata/lrj.dbf          -- 已刪除，找不到該檔案
ls: cannot access /u01/app/oracle/oradata/lrj.dbf: No such file or directory

10:41:51 SYS@wailon> insert into redotest select * from redotest where rownum<11;           -- 還可以往該檔案寫入資料

10 rows created.

10:42:21 SYS@wailon> commit;

Commit complete.

10:42:27 SYS@wailon> select count(*) from redotest;

  COUNT(*)
----------
     10010

10:42:36 SYS@wailon> host

[oracle@dg ~]$ lsof | grep deleted                -- 查詢被刪除的檔案，切記此時不能關閉或重啟資料庫，否則將丟失檔案描述符，則無法恢復。
oracle    2059    oracle  262uW     REG              252,0  961224704  525160 /u01/app/oracle/oradata/lrj.dbf (deleted)
oracle    2061    oracle  265u      REG              252,0  961224704  525160 /u01/app/oracle/oradata/lrj.dbf (deleted)
oracle    2065    oracle  260u      REG              252,0  961224704  525160 /u01/app/oracle/oradata/lrj.dbf (deleted)
oracle    5067    oracle  259u      REG              252,0  961224704  525160 /u01/app/oracle/oradata/lrj.dbf (deleted)

[oracle@dg ~]$ ps -ef | grep dbw                -- DBWR程式操作的檔案就是要恢復的檔案
oracle    2059     1  0 02:07 ?        00:00:10 ora_dbw0_wailon
oracle    5100  5079  0 10:43 pts/1    00:00:00 grep dbw
oracle    5100  5079  0 10:43 pts/1    00:00:00 grep dbw

[oracle@dg ~]$ ll /proc/2059/fd/* | grep lrj        -- 根據程式ID號查詢檔案描述符，找到具體的控制程式碼
lrwx------ 1 oracle oinstall 64 Sep 28 10:42 /proc/2059/fd/262 -> /u01/app/oracle/oradata/lrj.dbf (deleted)

[oracle@dg ~]$
[oracle@dg ~]$ cat /proc/2059/fd/262 > /u01/app/oracle/oradata/lrj.dbf     -- 恢復檔案
[oracle@dg ~]$ ll /u01/app/oracle/oradata/lrj.dbf
-rw-r--r-- 1 oracle oinstall 961224704 Sep 28 10:47 /u01/app/oracle/oradata/lrj.dbf
[oracle@dg ~]$ exit
exit

10:48:19 SYS@wailon> select count(*) from redotest;

  COUNT(*)
----------
     10010

10:48:26 SYS@wailon> alter tablespace tbs_lrj offline;        -- 測試檔案是否恢復成功

Tablespace altered.

10:48:39 SYS@wailon> alter tablespace tbs_lrj online;
alter tablespace tbs_lrj online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/lrj.dbf'

10:48:44 SYS@wailon> recover datafile 5;
Media recovery complete.
10:48:54 SYS@wailon> alter tablespace tbs_lrj online;

Tablespace altered.

10:48:57 SYS@wailon> select count(*) from redotest;

  COUNT(*)
----------
     10010

 
以上檔案恢復成功，無資料丟失。

切記：DBA或SA在執行任何命令前，必須明確該命令帶來的風險，尤其是刪除等不可逆的操作。否則寧可保留現狀，也不要輕舉妄動。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/429786/viewspace-776343/，如需轉載，請註明出處，否則將追究法律責任。