oracle文件损坏恢复数据,oracle部分数据文件损坏无备份的恢复

条件：

1.磁盘损坏，

2.部分oracle数据库文件损坏

3.非system和sysaux表空间数据文件

4.无备份可以用来恢复

5.online redo和archived log都存在，并且完整有效

1.故障出现

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area  209715200 bytes

Fixed Size                  1218556 bytes

Variable Size              62916612 bytes

Database Buffers          142606336 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/u01/oradata/orcl/dtt01.dbf'

2.利用rman进行恢复

[oracle@ora10g orcl]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 3 17:23:22 2013

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

connected to target database: ORCL (DBID=1332636334, not open)

RMAN> restore datafile 5,6;

Starting restore at 03-APR-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

creating datafile fno=5 name=/u01/oradata/dtt01.dbf

creating datafile fno=6 name=/u01/oradata/dtt02.dbf

restore not done; all files readonly, offline, or already restored

Finished restore at 03-APR-13

rman利用备份恢复失败。

3.检查数据文件信息

SQL> col NAME format a50

SQL> select name,status from v$datafile

SQL> /

NAME                                               STATUS

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

/u01/oradata/orcl/system01.dbf                     SYSTEM

/u01/oradata/orcl/undotbs01.dbf                    ONLINE

/u01/oradata/orcl/sysaux01.dbf                     ONLINE

/u01/oradata/orcl/users01.dbf                      ONLINE

/u01/oradata/orcl/dtt01.dbf                        ONLINE

/u01/oradata/orcl/dtt02.dbf                        ONLINE

6 rows selected.

4.创建数据文件到新的位置并进行恢复

SQL> ALTER DATABASE CREATE DATAFILE '/u01/oradata/orcl/dtt01.dbf'

2     AS '/u01/oradata/dtt01.dbf';

Database altered.

SQL>

SQL>

SQL> RECOVER DATAFILE '/u01/oradata/dtt01.dbf';

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/oradata/orcl/dtt02.dbf'

SQL> ALTER DATABASE CREATE DATAFILE '/u01/oradata/orcl/dtt02.dbf'

2     AS '/u01/oradata/dtt02.dbf';

Database altered.

SQL>

SQL> RECOVER DATAFILE '/u01/oradata/dtt02.dbf';

Media recovery complete.

SQL>

SQL> alter database open;

Database altered.

恢复完成打开数据库

SQL> select name,status from v$datafile;

NAME                                               STATUS

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

/u01/oradata/orcl/system01.dbf                     SYSTEM

/u01/oradata/orcl/undotbs01.dbf                    ONLINE

/u01/oradata/orcl/sysaux01.dbf                     ONLINE

/u01/oradata/orcl/users01.dbf                      ONLINE

/u01/oradata/dtt01.dbf                             ONLINE

/u01/oradata/dtt02.dbf                             ONLINE

6 rows selected.

5.数据验证：

SQL> select count(*) from dy;

COUNT(*)

----------

49782

SQL> select count(*) from dy union all

2      select count(*) from dba_objects;

COUNT(*)

----------

49782

49782

========================================================================= -- The End --