備份與恢復--資料檔案損壞或丟失

恢復辦法適用於：
資料檔案損壞或丟失，且損壞或丟失的資料檔案沒有備份。

前提條件：
資料庫為archive模式，且保留著所有的從這個表空間建立起到現在的歸檔日誌。

方法：
首先建立一個新的空資料檔案，然後利用完全的歸檔日誌來恢復表空間中的資料。

[@more@]

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6

SQL> CREATE TABLESPACE test DATAFILE '/u01/app/oracle/oradata/orcl/test01.dbf' SIZE 10M

AUTOEXTEND OFF;

Tablespace created.

SQL> CREATE TABLESPACE test DATAFILE '/u01/app/oracle/oradata/orcl/test01.dbf' SIZE 10M

AUTOEXTEND OFF;

Tablespace created.

SQL> conn jglu/jglu
Connected.

SQL> create table test1
2 tablespace test
3 as select * from all_objects;

Table created.

SQL> select count(*) from jglu.test1;

COUNT(*)
----------
40689

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@rhel131 backup]$ cd /u01/app/oracle/oradata/

[oracle@rhel131 oradata]$ cd orcl

[oracle@rhel131 orcl]$ mv test01.dbf test01.bak

[oracle@rhel131 orcl]$ sqlplus / as sysdba

SQL> startup
ORACLE instance started.

Total System Global Area 335544320 bytes
Fixed Size 1219280 bytes
Variable Size 130024752 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/app/oracle/oradata/orcl/test01.dbf'

SQL> alter database datafile 8 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT * FROM V$RECOVER_FILE;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
----- ---------- ------- -------------------- ----------- ---------------
8 OFFLINE OFFLINE FILE NOT FOUND 0

SQL> alter database create datafile '/u01/app/oracle/oradata/orcl/test01.dbf'
2 as '/u01/app/oracle/oradata/orcl/test02.dbf';

Database altered.

SQL> recover datafile '/u01/app/oracle/oradata/orcl/test02.dbf';
Media recovery complete.

SQL> select ts#,name,status from v$datafile;

TS# NAME STATUS
---------- -------------------------------------------------- -------
0 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
1 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE
6 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE
7 /u01/app/oracle/oradata/orcl/jglu01.dbf ONLINE
7 /u01/app/oracle/oradata/orcl/jglu02.dbf ONLINE
8 /u01/app/oracle/oradata/orcl/test02.dbf OFFLINE

8 rows selected.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test02.dbf' online;

Database altered.

SQL> select ts#,name,status from v$datafile;

TS# NAME STATUS
---------- -------------------------------------------------- -------
0 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
1 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE
6 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE
7 /u01/app/oracle/oradata/orcl/jglu01.dbf ONLINE
7 /u01/app/oracle/oradata/orcl/jglu02.dbf ONLINE
8 /u01/app/oracle/oradata/orcl/test02.dbf ONLINE

8 rows selected.

SQL> select count(*) from jglu.test1;

COUNT(*)
----------
40688

這個操作需要注意的是：需要表空間建立後所有的歸檔日誌，且控制檔案中必須包含受損檔名。另外，

這種方式的恢復不能用於SYSTEM表空間。