ITPub博客

首页 > 数据库 > Oracle > 当数据文件表空间丢失的时候怎么恢复该数据文件

当数据文件表空间丢失的时候怎么恢复该数据文件

原创 Oracle 作者:gholay 时间:2014-02-02 15:39:03 0 删除 编辑
当数据文件表空间丢失的时候怎么恢复该数据文件。
步骤如下:
1. 启动数据库到mount状态 
2. 使该数据文件处于offline状态  alter database datafile 4 offline
3. restore数据文件restore datafile 4 
4. recover数据文件restore datafile 4 
5. 合该数据文件处于online状态 alter database datafile 4 online 

重启后发现报错,先offline,再打开数据库,然后在用rman restore和recover,online数据文件。顺序为52436 D答案

sys@TEST1107> startup

ORACLE instance started.


Total System Global Area 1252663296 bytes

Fixed Size                  2227944 bytes

Variable Size            1006633240 bytes

Database Buffers          234881024 bytes

Redo Buffers                8921088 bytes

Database mounted.

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

ORA-01110: data file 4: '/u01/app/oracle/oradata/test1107/users01.dbf' (可以看出数据文件号为4的数据文件损坏



sys@TEST1107> alter database datafile 4 offline;


Database altered.


sys@TEST1107> alter database open;


Database altered.


sys@TEST1107> select * from scott.dept;

select * from scott.dept

                    *

ERROR at line 1:

ORA-00376: file 4 cannot be read at this time

ORA-01110: data file 4: '/u01/app/oracle/oradata/test1107/users01.dbf'


[oracle@rtest ~]$ rman target /


Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 24 10:06:13 2013


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: TEST1107 (DBID=2336818266)


RMAN> restore datafile 4;


Starting restore at 24-DEC-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=192 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=221 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=6 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test1107/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST1107/backupset/2013_12_24/o1_mf_nnndf_TAG20131224T090841_9cm5cf76_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST1107/backupset/2013_12_24/o1_mf_nnndf_TAG20131224T090841_9cm5cf76_.bkp tag=TAG20131224T090841

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Finished restore at 24-DEC-13


RMAN> recover datafile 4;


Starting recover at 24-DEC-13

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3


starting media recovery


archived log for thread 1 with sequence 437 is already on disk as file /u01/rmanbak/d1/1_437_830778999.dbf

archived log for thread 1 with sequence 438 is already on disk as file /u01/rmanbak/d1/1_438_830778999.dbf

archived log for thread 1 with sequence 439 is already on disk as file /u01/rmanbak/d1/1_439_830778999.dbf

archived log file name=/u01/rmanbak/d1/1_437_830778999.dbf thread=1 sequence=437

media recovery complete, elapsed time: 00:00:02

Finished recover at 24-DEC-13


RMAN> sql 'alter database datafile 4 online';


sql statement: alter database datafile 4 online



sys@TEST1107> select * from scott.dept;


    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

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

下一篇: SCN的机制
请登录后发表评论 登录
全部评论

注册时间:2013-12-28

  • 博文量
    82
  • 访问量
    646368