Oracle误删数据文件灾难恢复 一、 检查oracle数据文件

SQL> conn store/store_password

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

CUSTOMERS TABLE

PRODUCT_TYPES TABLE

PRODUCTS TABLE

PURCHASES TABLE

EMPLOYEES TABLE

SALARY_GRADES TABLE

PURCHASES_WITH_TIMESTAMP TABLE

PURCHASES_TIMESTAMP_WITH_TZ TABLE

PURCHASES_WITH_LOCAL_TZ TABLE

COUPONS TABLE

PROMOTIONS TABLE

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

ORDER_STATUS TABLE

PRODUCT_CHANGES TABLE

MORE_PRODUCTS TABLE

MORE_EMPLOYEES TABLE

DIVISIONS TABLE

JOBS TABLE

EMPLOYEES2 TABLE

ALL_SALES TABLE

PRODUCT_PRICE_AUDIT TABLE

REG_EXPS TABLE

BINARY_TEST TABLE

22 rows selected.

SQL> col file_name format a40;

SQL> select file_name from dba_data_files;

FILE_NAME

----------------------------------------

/u03/app/oracle/oradata/ora10g/users01.dbf

/u03/app/oracle/oradata/ora10g/sysaux01.dbf

/u03/app/oracle/oradata/ora10g/undotbs01.dbf

/u03/app/oracle/oradata/ora10g/system01.dbf

FILE_NAME

----------------------------------------

/u03/app/oracle/oradata/ora10g/example01.dbf

SQL>

二、 删除数据库文件

/u03/app/oracle/oradata/ora10g

[oracle10g@ora9i ora10g]$ ll

total 1064596

-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:16 control01.ctl

-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:16 control02.ctl

-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:16 control03.ctl

-rw-r----- 1 oracle10g oinstall 104865792 Sep 2 19:08 example01.dbf

-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:16 redo01.log

-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo02.log

-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo03.log

-rw-r----- 1 oracle10g oinstall 251666432 Sep 2 19:13 sysaux01.dbf

-rw-r----- 1 oracle10g oinstall 503324672 Sep 2 19:13 system01.dbf

-rw-r----- 1 oracle10g oinstall 20979712 Sep 1 07:17 temp01.dbf

-rw-r----- 1 oracle10g oinstall 26222592 Sep 2 19:13 undotbs01.dbf

-rw-r----- 1 oracle10g oinstall 6561792 Sep 2 19:08 users01.dbf

[oracle10g@ora9i ora10g]$ rm -rf *.dbf

[oracle10g@ora9i ora10g]$

三、 强制关闭数据库

[oracle10g@ora9i ora10g]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 2 19:18:07 2012

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

SQL> conn / as sysdba;

Connected.

SQL> shutdown abort;

ORACLE instance shut down.

SQL>

四、 将数据文件分区挂为只读

[root@ora9i ~]# umount -f /u03

[root@ora9i ~]#

[root@ora9i ~]# fdisk -l

Disk /dev/sda: 26.8 GB, 26843545600 bytes

255 heads, 63 sectors/track, 3263 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sda1 * 1 25 200781 83 Linux

/dev/sda2 26 286 2096482 82 Linux swap

/dev/sda3 287 3263 23912752 83 Linux

Disk /dev/sdb: 4294 MB, 4294967296 bytes

255 heads, 63 sectors/track, 522 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdb1 1 522 4192933 83 Linux

Disk /dev/sdc: 1073 MB, 1073741824 bytes

255 heads, 63 sectors/track, 130 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdc1 1 130 1044193 83 Linux

Disk /dev/sdd: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdd1 1 2610 20964793 83 Linux

[root@ora9i ~]# mount -o ro /dev/sdd1 /u03/

[root@ora9i ~]#

五、 安装ext3grep恢得软件http://code.google.com/p/ext3grep/tar zxf ext3grep-0.10.1.tar.gz

cd /home/tank/ext3grep-0.10.1

./configure

make

make install

六、 查找丢失数据库数据库文件

注意由于修复数据库文件会保存用户的当前目录下，请考虑当前目录的磁盘空间

[root@ora9i opt]# ext3grep /dev/sdd1 --ls --inode 2 // 一般/根目录的inode值为2

Running ext3grep version 0.10.0

Number of groups: 160

Loading group metadata... done

Minimum / maximum journal block: 1547 / 9749

Loading journal descriptors... sorting... done

The oldest inode block that is still in the journal, appears to be from 1346292905 = Thu Aug 30 10:15:05 2012

Number of descriptors in journal: 6294; min / max sequence numbers: 9918 / 13836

Inode is Allocated

Loading sdd1.ext3grep.stage2............................................ done

The first block of the directory is 1541.

Inode 2 is directory "".

Directory block 1541:

.-- File type in dir_entry (r=regular file, d=directory, l=symlink)

| .-- D: Deleted ; R: Reallocated

Indx Next | Inode | Deletion time Mode File name

========== ========== ----------------data-from-inode------ ----------- =========

0 1 d 2 drwxrwxrwx .

1 2 d 2 drwxrwxrwx ..

2 3 d 11 drwxrwxrwx lost found

3 4 r 12 rrw-r--r-- 10201_database_linux32.zip

4 5 d 147457 drwxr-xr-x app

5 6 r 13 rrw-r--r-- sysstat-5.0.5-11.rhel4.i386.rpm

6 12 d 393217 drwxr-xr-x database

7 9 r 14 D 1346584012 Sun Sep 2 19:06:52 2012 rrw-r--r-- ext3grep-0.10.2.tar.gz

8 9 d 917507 D 1346584012 Sun Sep 2 19:06:52 2012 drwxr-xr-x ext3grep-0.10.2

9 12 r 15 D 1346584013 Sun Sep 2 19:06:53 2012 rrw-r--r-- ext3grep-0.7.0.tar.gz

10 12 d 2080771 D 1346584013 Sun Sep 2 19:06:53 2012 drwxr-xr-x ext3grep-0.7.0

11 12 r 16 D 1346584012 Sun Sep 2 19:06:52 2012 rrw-r--r-- ext3grep-0.10.0-1.el4.rf.i386.rpm

12 end r 17 rrw-r--r-- sda3.ext3grep.stage1

[root@ora9i opt]#

查找文件名：

[root@ora9i inode.147457]# ext3grep /dev/sdd1 --dump-name

app/oracle/oradata/ora10g/control01.ctl

app/oracle/oradata/ora10g/control02.ctl

app/oracle/oradata/ora10g/control03.ctl

app/oracle/oradata/ora10g/example01.dbf

app/oracle/oradata/ora10g/redo01.log

app/oracle/oradata/ora10g/redo02.log

app/oracle/oradata/ora10g/redo03.log

app/oracle/oradata/ora10g/sysaux01.dbf

app/oracle/oradata/ora10g/system01.dbf

app/oracle/oradata/ora10g/temp01.dbf

app/oracle/oradata/ora10g/undotbs01.dbf

app/oracle/oradata/ora10g/users01.dbf

app/oracle/product

七、 恢复丢失数据库数据文件

Running ext3grep version 0.10.0

Number of groups: 160

Minimum / maximum journal block: 1547 / 9749

Loading journal descriptors... sorting... done

The oldest inode block that is still in the journal, appears to be from 1346292905 = Thu Aug 30 10:15:05 2012

Number of descriptors in journal: 6294; min / max sequence numbers: 9918 / 13836

Loading sdd1.ext3grep.stage2............................................ done

Restoring app/oracle/oradata/ora10g/example01.dbf

[root@ora9i opt]#

..............................................................................

逐个数据文个进恢复

[root@ora9i ora10g]# pwd

/opt/RESTORED_FILES/app/oracle/oradata/ora10g

[root@ora9i ora10g]# ll

total 890092

-rw-r----- 1 root root 104865792 Sep 2 19:08 example01.dbf

-rw-r----- 1 root root 251666432 Sep 2 19:17 sysaux01.dbf

-rw-r----- 1 root root 503324672 Sep 2 19:13 system01.dbf

-rw-r----- 1 root root 17899520 Sep 1 07:17 temp01.dbf

-rw-r----- 1 root root 26222592 Sep 2 19:13 undotbs01.dbf

-rw-r----- 1 root root 6561792 Sep 2 19:08 users01.dbf

[root@ora9i ora10g]#

八、 将恢复数据库文件复制到Oracle目录

注意重新将分区挂成可读可写，并修改数据库文件的权限

[root@ora9i ora10g]# umount /dev/sdd1

[root@ora9i ora10g]# mount /dev/sdd1 /u03/

[root@ora9i ora10g]# pwd

/opt/RESTORED_FILES/app/oracle/oradata/ora10g

[root@ora9i ora10g]# mv * /u03/app/oracle

oracle/ oracle_base/

[root@ora9i ora10g]# mv * /u03/app/oracle/oradata/ora10g/

[root@ora9i ora10g]# cd /u03/app/oracle/oradata/ora10g/

[root@ora9i ora10g]# ll

total 1064604

-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control01.ctl

-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control02.ctl

-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control03.ctl

-rw-r----- 1 root root 104865792 Sep 2 19:08 example01.dbf

-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:18 redo01.log

-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo02.log

-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo03.log

-rw-r----- 1 root root 251666432 Sep 2 19:17 sysaux01.dbf

-rw-r----- 1 root root 503324672 Sep 2 19:13 system01.dbf

-rw-r----- 1 root root 17899520 Sep 1 07:17 temp01.dbf

-rw-r----- 1 root root 26222592 Sep 2 19:13 undotbs01.dbf

-rw-r----- 1 root root 6561792 Sep 2 19:08 users01.dbf

[root@ora9i ora10g]# chown oracle10g:oinstall *

[root@ora9i ora10g]# ll

total 1064604

-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control01.ctl

-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control02.ctl

-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control03.ctl

-rw-r----- 1 oracle10g oinstall 104865792 Sep 2 19:08 example01.dbf

-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:18 redo01.log

-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo02.log

-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo03.log

-rw-r----- 1 oracle10g oinstall 251666432 Sep 2 19:17 sysaux01.dbf

-rw-r----- 1 oracle10g oinstall 503324672 Sep 2 19:13 system01.dbf

-rw-r----- 1 oracle10g oinstall 17899520 Sep 1 07:17 temp01.dbf

-rw-r----- 1 oracle10g oinstall 26222592 Sep 2 19:13 undotbs01.dbf

-rw-r----- 1 oracle10g oinstall 6561792 Sep 2 19:08 users01.dbf

[root@ora9i ora10g]#

九、 启动Oracle数据库（激动。。）

[oracle10g@ora9i ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 2 20:03:14 2012

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

SQL> conn / as sysdba;

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 230686720 bytes

Fixed Size 1218652 bytes

Variable Size 71305124 bytes

Database Buffers 150994944 bytes

Redo Buffers 7168000 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL>

SQL> conn store/store_password

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

CUSTOMERS TABLE

PRODUCT_TYPES TABLE

PRODUCTS TABLE

PURCHASES TABLE

EMPLOYEES TABLE

SALARY_GRADES TABLE

PURCHASES_WITH_TIMESTAMP TABLE

PURCHASES_TIMESTAMP_WITH_TZ TABLE

PURCHASES_WITH_LOCAL_TZ TABLE

COUPONS TABLE

PROMOTIONS TABLE

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

ORDER_STATUS TABLE

PRODUCT_CHANGES TABLE

MORE_PRODUCTS TABLE

MORE_EMPLOYEES TABLE

DIVISIONS TABLE

JOBS TABLE

EMPLOYEES2 TABLE

ALL_SALES TABLE

PRODUCT_PRICE_AUDIT TABLE

REG_EXPS TABLE

BINARY_TEST TABLE

22 rows selected.

SQL> select * from jobs;

JOB NAME

--- --------------------

WOR Worker

MGR Manager

ENG Engineer

TEC Technologist

PRE President

SQL>

－－－－－致此Oracle数据库已经可以正常访问