备份与恢复--数据文件损坏或丢失

恢复办法适用于：
数据文件损坏或丢失，且损坏或丢失的数据文件没有备份。

前提条件：
数据库为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表空间。

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

转载于:http://blog.itpub.net/271283/viewspace-1016105/