oracel数据库Solaris rm datafile recovery―利用句柄误删除数据文件恢复
今天早上接到有客户恢复请求，他一不小心在solaris系统中使用rm -rf oradata命令把一个分区下面的所有数据文件全部删除了。现在不知道怎么办,请求我们给予支持.上去检查发现比较幸运,数据库没有crash,看来直接考虑使用句柄进行恢复
root@CNISORCLSVR # uname -a
SunOS CNISORCLSVR 5.9 Generic_112233-08 sun4u sparc SUNW,Sun-Fire-880
root@CNISORCLSVR # ps -ef|grep lgwr
oracle 597 1 0 Mar 05 ? 17:14 ora_lgwr_xifenfei
root 28069 28043 0 18:51:17 pts/2 0:00 grep lgwr
root@CNISORCLSVR # ls -ltr
total 189348454
-r--r--r-- 1 oracle dba 657920 Apr 26 2002 12
c--------- 1 root sys 13, 12 Mar 27 2004 8
c--------- 1 root sys 13, 12 Mar 27 2004 10
-rw-r----- 0 oracle dba 34359730176 Nov 12 2013 291
-rw-r----- 0 oracle dba 1073750016 Nov 13 2013 293
D--------- 1 root root 0 Mar 5 19:31 11
-rw-r----- 1 oracle dba 1758 Mar 5 22:04 9
-rw-rw---- 1 oracle dba 24 Mar 5 22:04 13
s--------- 0 root root 0 Mar 8 00:45 14
-rw-r----- 1 oracle dba 1887444992 Mar 12 03:27 289
-rw-r----- 1 oracle dba 943726592 Mar 12 11:17 290
-rw-r----- 0 oracle dba 4294975488 Mar 13 00:09 292
-rw-r----- 0 oracle dba 268443648 Mar 13 01:33 288
-rw-r----- 0 oracle dba 536879104 Mar 13 01:33 279
-rw-r----- 1 oracle dba 134225920 Mar 13 01:33 278
-rw-r----- 0 oracle dba 134225920 Mar 13 01:33 269
-rw-r----- 1 oracle dba 268443648 Mar 13 01:33 267
-rw-r----- 1 oracle dba 148119552 Mar 13 01:33 266
-rw-r----- 1 oracle dba 10493952 Mar 13 01:33 265
-rw-r----- 1 oracle dba 26222592 Mar 13 01:33 264
-rw-r----- 1 oracle dba 62922752 Mar 13 01:33 263
-rw-r----- 1 oracle dba 20979712 Mar 13 01:33 262
-rw-r----- 0 oracle dba 134225920 Mar 13 01:33 287
-rw-r----- 1 oracle dba 209723392 Mar 13 01:33 285
-rw-r----- 0 oracle dba 536879104 Mar 13 01:33 283
-rw-r----- 1 oracle dba 67117056 Mar 13 01:33 282
-rw-r----- 0 oracle dba 536879104 Mar 13 01:33 281
-rw-r----- 0 oracle dba 536879104 Mar 13 01:33 280
-rw-r----- 0 oracle dba 536879104 Mar 13 01:33 276
-rw-r----- 0 oracle dba 1073750016 Mar 13 01:33 275
-rw-r----- 0 oracle dba 2214600704 Mar 13 01:33 274
-rw-r----- 0 oracle dba 134225920 Mar 13 01:33 273
-rw-r----- 0 oracle dba 536879104 Mar 13 01:33 272
c--------- 1 root sys 13, 2 Mar 13 02:00 5
c--------- 1 root sys 13, 2 Mar 13 02:00 4
c--------- 1 root sys 13, 2 Mar 13 02:00 3
c--------- 1 root sys 13, 2 Mar 13 02:00 2
c--------- 1 root sys 13, 2 Mar 13 02:00 1
c--------- 1 root sys 13, 2 Mar 13 02:00 0
--w------- 1 oracle dba 4640842 Mar 13 04:43 7
--w------- 1 oracle dba 4640842 Mar 13 04:43 6
-rw-r----- 0 oracle dba 1207967744 Mar 13 18:21 271
-rw-r----- 0 oracle dba 15929974784 Mar 13 18:39 284
-rw-r----- 0 oracle dba 134225920 Mar 13 18:45 277
-rw-r----- 0 oracle dba 2122326016 Mar 13 18:46 286
-rw-r----- 0 oracle dba 9261031424 Mar 13 18:47 270
-rw-r----- 0 oracle dba 18253619200 Mar 13 18:47 268
-rw-r----- 1 oracle dba 134225920 Mar 13 18:51 261
-rw-r----- 1 oracle dba 524296192 Mar 13 18:51 260
-rw-r----- 1 oracle dba 104858112 Mar 13 18:52 259
-rw-r----- 1 oracle dba 1941504 Mar 13 18:52 258
-rw-r----- 1 oracle dba 1941504 Mar 13 18:52 257
-rw-r----- 1 oracle dba 1941504 Mar 13 18:52 256
SQL> select file#,name from v$datafile wehre name like '/disk%';
FILE# NAME
---------- --------------------------------------------------
9 /disk/oradata/xifenfei/xifenfei.dbf
10 /disk/oradata/xifenfei/CSSN.dbf
11 /disk/oradata/xifenfei/NCSSN.dbf
12 /disk/oradata/xifenfei/CSIC_RDS.dbf
13 /disk/oradata/xifenfei/CSIC_CSSN.dbf
14 /disk/oradata/xifenfei/CNIS_I.dbf
15 /disk/oradata/xifenfei/CNIS.dbf
16 /disk/oradata/xifenfei/TRSWCM6_CSSN.dbf
17 /disk/oradata/xifenfei/TRSWCM6_CSSN_PLUGINS.dbf
18 /disk/oradata/xifenfei/DIGIREF.dbf
20 /disk/oradata/xifenfei/TRSWCM.dbf
21 /disk/oradata/xifenfei/TRSWCM52_NSLC.dbf
22 /disk/oradata/xifenfei/TRSWCM52_PLUGINS_NSLC.dbf
24 /disk/oradata/xifenfei/TRSWCM_PLUGINS.dbf
25 /disk/oradata/xifenfei/CNIS_ALL.dbf
27 /disk/oradata/xifenfei/undotbs01.dbf
28 /disk/oradata/xifenfei/TRS_IDS02.dbf
29 /disk/oradata/xifenfei/xdb02.dbf
在solaris中比较郁闷,虽然进入了fd目录,但是无法知道哪些文件句柄是删除,哪些是正常的,因此没有办法，只能使用lsof进一步分析
root@CNISORCLSVR # pkgadd -d lsof-4.80-sol9-sparc-local
The following packages are available:
1 IBMlsof lsof
(sparc) 4.80
Select package(s) you wish to process (or 'all' to process
all packages). (default: all) [?,??,q]: all
Processing package instance
lsof
(sparc) 4.80
Vic Abell
Using as the package base directory.
## Processing package information.
## Processing system information.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
The following files are already installed on the system and are being
used by another package:
* /usr/local/bin
* - conflict with a file which does not belong to any package.
Do you want to install these conflicting files [y,n,?,q] y
## Checking for setuid/setgid programs.
The following files are being installed with setuid and/or setgid
permissions:
/usr/local/bin/lsof
/usr/local/bin/sparcv7/lsof
/usr/local/bin/sparcv9/lsof
Do you want to install these as setuid/setgid files [y,n,?,q] y
Installing lsof as
## Installing part 1 of 1.
/usr/local/bin/lsof
/usr/local/bin/sparcv7/lsof
/usr/local/bin/sparcv9/lsof
/usr/local/doc/lsof/00.README.FIRST
/usr/local/doc/lsof/00CREDITS
/usr/local/doc/lsof/00DCACHE
/usr/local/doc/lsof/00DIALECTS
/usr/local/doc/lsof/00DIST
/usr/local/doc/lsof/00FAQ
/usr/local/doc/lsof/00LSOF-L
/usr/local/doc/lsof/00MANIFEST
/usr/local/doc/lsof/00PORTING
/usr/local/doc/lsof/00QUICKSTART
/usr/local/doc/lsof/00README
/usr/local/doc/lsof/00TEST
/usr/local/doc/lsof/00XCONFIG
/usr/local/doc/lsof/lsof.man
/usr/local/man/man8/lsof.8
[ verifying class
Installation of
root@CNISORCLSVR # ./lsof -p 597
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 597 oracle cwd VDIR 85,5 2048 106299 /export/home/oracle/app/product/9.2.0/dbs
oracle 597 oracle txt VREG 85,5 61272672 2332 /export/home/oracle/app/product/9.2.0/bin/oracle
…………
oracle 597 oracle 260u VREG 85,5 524296192 106517 /export/home/oracle/oradata/xifenfei/system01.dbf
oracle 597 oracle 261u VREG 85,5 134225920 106518 /export/home/oracle/oradata/xifenfei/undotbs01.dbf
…………
oracle 597 oracle 268u VREG 118,70 18253619200 109 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)
oracle 597 oracle 269u VREG 118,70 134225920 110 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)
oracle 597 oracle 270u VREG 118,70 9261031424 111 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)
…………
oracle 597 oracle 293u VREG 118,70 1073750016 14 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)
到这一步,基本上定位/disk部分是我们需要恢复的数据,从而可以定位到句柄,然后结合数据文件信息,直接使用cp命令恢复出来文件.然后数据库层面recover并且online.
cd /proc/597/fd
cp 269 /disk/oradata/cnisora2/CSSN.dbf
chown oracle:dba /disk/oradata/xifenfei/CSSN.dbf
SQL> recover datafile 10;
ORA-00283: 恢复会话因错误而取消
ORA-01124: 无法恢复数据文件 10 - 文件在使用中或在恢复中
ORA-01110: 数据文件 10: '/disk/oradata/xifenfei/CSSN.dbf'
SQL> alter database datafile 10 offline;
数据库已更改。
SQL> recover datafile 10;
完成介质恢复。
SQL> alter database datafile 10 online;
数据库已更改。
至此基本上恢复完成,万幸是数据库没有crash,遇到此类问题,千万不要盲目关闭数据库.另外数据库备份重于一切
