當資料檔案表空間丟失的時候怎麼恢復該資料檔案

gholay發表於2014-02-02
原文網址 : http://blog.itpub.net/29196873/viewspace-1076840/
當資料檔案表空間丟失的時候怎麼恢復該資料檔案。
步驟如下：
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章