oracle手工完全恢複（五）建立的資料表空間，沒有備份，datafile被刪除

案例4：recover datafile

——建立的資料表空間，沒有備份，datafile被刪除

恢複步驟：起庫，通過V$recover_file 查看需要恢複的檔案，由於沒有備份，只能將資料檔案offline，進行資料檔案重建，再進行recover datafile ，online即可

（1)類比環境

SQL> create tablespace cuug

2  datafile '/u01/app/oracle/oradata/anny/cuug01.dbf' size 10m;

Tablespace created.

SQL> conn scott/tiger

Connected.

SQL> create table tb02(id int) tablespace cuug;

Table created.

SQL> insert into tb02 values(1);

1 row created.

SQL> insert into tb02 values(2);

1 row created.

SQL> insert into tb02 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select  * from tb02;

ID

----------

1

2

3

SQL> conn /as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

SQL> !

本文URL地址：http://www.bianceng.cn/database/Oracle/201410/45395.htm

[oracle@solaris10 ~]$rm /u01/app/oracle/oradata/anny/cuug01.dbf——刪除資料表空間

[oracle@solaris10 ~]$!exp

export ORACLE_SID=anny

[oracle@solaris10 ~]$!sql

（2）啟動 database

SQL> startup

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  1279964 bytes

Variable Size              58722340 bytes

Database Buffers          251658240 bytes

Redo Buffers                2912256 bytes

Database mounted.

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

ORA-01110: data file 12: '/u01/app/oracle/oradata/anny/cuug01.dbf'

SQL> select file#,error from v$recover_file;——尋找需要恢複的檔案資訊

FILE# ERROR

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

12 FILE NOT FOUND

（3）恢複

SQL> alter database datafile 12 offline;

Database altered.

SQL> alter database open;

Database altered.

——沒有備份，不能做restore，只能重建資料檔案【通過os 刪除，在controlfile檔案仍然有datafile 記錄】，然後recover datafile

SQL> alter database create datafile '/u01/app/oracle/oradata/anny/cuug01.dbf';——重建資料檔案

Database altered.

SQL> recover datafile 12;——恢複資料檔案

Media recovery complete.

警示日誌資訊：

ALTER DATABASE RECOVER  datafile 12

Wed Mar 21 16:18:20 2012

Media Recovery Start

Wed Mar 21 16:18:20 2012

Recovery of Online Redo Log: Thread 1 Group 4 Seq 4 Reading mem 0

Mem# 0 errs 0: /disk3/oradata/anny/redo04a.log

Mem# 1 errs 0: /disk1/oradata/anny/redo04b.log

Mem# 2 errs 0: /disk2/oradata/anny/redo04c.log

Wed Mar 21 16:18:20 2012

Recovery of Online Redo Log: Thread 1 Group 3 Seq 5 Reading mem 0

Mem# 0 errs 0: /disk3/oradata/anny/redo03a.log

Mem# 1 errs 0: /disk1/oradata/anny/redo03b.log

Mem# 2 errs 0: /disk2/oradata/anny/redo03c.log

Wed Mar 21 16:18:20 2012

Media Recovery Complete (anny)

Completed: ALTER DATABASE RECOVER  datafile 12

（4）驗證

SQL> alter database datafile 12 online;

Database altered.

SQL> select * from scott.tb02;

ID

----------

1

2

3