将数据文件还原到新位置的恢复过程：
1.使用操作系统命令COPY文件到新位置
2.启动并装载数据库
3.使用alter database命令更新控制文件 例如：alter database rename file 'xxxxxxxx' to 'xxxxxxxx';
SQL> create tablespace tbs_test datafile '/database/oradata/skyread/tbs_test.dbf' size 10M; --创建测试表空间
Tablespace created.
SQL> select file_name from dba_data_files where tablespace_name='TBS_TEST';
FILE_NAME
--------------------------------------------------------------------------------
/database/oradata/skyread/tbs_test.dbf
1 rows selected.
SQL> alter tablespace tbs_test begin backup; --热备
Tablespace altered.
SQL> !
oracle@readerlogdb-> cp /database/oradata/skyread/tbs_test.dbf /home/oracle/tbs_test.dbf
oracle@readerlogdb-> exit
exit
SQL> alter tablespace tbs_test end backup;
Tablespace altered.
SQL> alter system archive log current;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 5049942016 bytes
Fixed Size 2090880 bytes
Variable Size 1375733888 bytes
Database Buffers 3657433088 bytes
Redo Buffers 14684160 bytes
Database mounted.
SQL> alter database rename file '/database/oradata/skyread/tbs_test.dbf' to '/home/oracle/tbs_test.dbf'; --用新位置的数据文件还原
Database altered.
SQL> alter database open; --打开数据库
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/home/oracle/tbs_test.dbf'
SQL> recover automatic datafile 2; --需要介质恢复，恢复一下
Media recovery complete.
SQL> alter database open;
SQL> select file_name，tablespace_name from dba_data_files where tablespace_name='TBS_TEST'; --可见已经用到新位置的数据文件
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/home/oracle/tbs_test.dbf TBS_TEST
完全恢复：
1.恢复关闭的数据库
恢复的文件是系统表空间或者UNDO表空间
需要恢复整个数据库或者大多数数据文件
数据库非7*24小时运行
SQL> alter tablespace SYSTEM begin backup;
Tablespace altered.
SQL> !cp /database/oradata/skyread/system01.dbf /home/oracle/system01.dbf --热备system表空间
SQL> alter tablespace system end backup;
Tablespace altered.
SQL> create table t2 (a int);
SQL> ! mv /database/oradata/skyread/system01.dbf /database/oradata/skyread/system01.dbf.bak --损坏system表空间
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 5049942016 bytes
Fixed Size 2090880 bytes
Variable Size 1375733888 bytes
Database Buffers 3657433088 bytes
Redo Buffers 14684160 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/database/oradata/skyread/system01.dbf'
SQL> ! cp /home/oracle/system01.dbf /database/oradata/skyread/system01.dbf --从备份中copy文件
SQL> recover database; --介质恢复，这里也可以用recover datafile 1;应用了所有归档和redo
Media recovery complete.
SQL> alter database open; --打开数据库
Database altered.
SQL> desc t2 --看到恢复到了宕机前的状态
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
2.恢复打开的数据库（数据库没有因为故障宕机）：
损坏的文件不是系统表空间和回滚表空间
7*24小时运行
未导致数据库关闭
SQL> alter tablespace TBS_SF begin backup;
SQL> !cp /database/oradata/skyread/sf01.dbf /home/oracle/sf01.dbf --进行热备
SQL> alter tablespace tbs_sf end backup;
rm -rf /database/oradata/skyread/sf01.dbf --模拟故障系统误删数据文件
SQL> drop table t1; --数据文件不可用
drop table t1
*
ERROR at line 1:
ORA-01116: error in opening database file 26
ORA-01110: data file 26: '/database/oradata/skyread/sf01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> col NAME for a50
SQL> SELECT d.file# f#, d.name, d.status, h.status
2 FROM v$datafile d, v$datafile_header h
3 WHERE d.file# = h.file#;
F# NAME STATUS STATUS
---------- -------------------------------------------------- ------- -------
1 /database/oradata/skyread/system01.dbf SYSTEM ONLINE
3 /database/oradata/skyread/sysaux01.dbf ONLINE ONLINE
4 /database/oradata/skyread/users01.dbf ONLINE ONLINE
18 /database2/oradata/skyread/TBS_MRPMUSIC01.dbf ONLINE ONLINE
26 /database/oradata/skyread/sf01.dbf ONLINE ONLINE
31 /database2/oradata/skyread/undotbs02 ONLINE ONLINE
SQL> ! cp /home/oracle/sf01.dbf /database/oradata/skyread/sf01.dbf --copy备份的数据文件
SQL> recover datafile 26; --进行恢复出错
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 26 - file is in use or recovery
ORA-01110: data file 26: '/database/oradata/skyread/sf01.dbf'
SQL> alter database datafile '/database/oradata/skyread/sf01.dbf' offline drop; --手工offline
Database altered.
SQL> SELECT d.file# f#, d.name, d.status, h.status
2 FROM v$datafile d, v$datafile_header h
3 WHERE d.file# = h.file#;
F# NAME STATUS STATUS
---------- -------------------------------------------------- ------- -------
1 /database/oradata/skyread/system01.dbf SYSTEM ONLINE
3 /database/oradata/skyread/sysaux01.dbf ONLINE ONLINE
4 /database/oradata/skyread/users01.dbf ONLINE ONLINE
18 /database2/oradata/skyread/TBS_MRPMUSIC01.dbf ONLINE ONLINE
26 /database/oradata/skyread/sf01.dbf RECOVER OFFLINE
31 /database2/oradata/skyread/undotbs02 ONLINE ONLINE
6 rows selected.
SQL> recover datafile 26; --再次进行介质恢复
Media recovery complete.
SQL> alter database datafile '/database/oradata/skyread/sf01.dbf' online; --数据文件online
Database altered.
SQL> drop table t1; --恢复正常
Table dropped
这里需要注意的是，在误删了数据文件后，数据并没有自动offline数据文件，有时候可能会自动offline文件，如果没有自动offline
需要手工去offline该文件，还有就是如果一个表空间含有多个数据文件，如果只有某一个数据文件损坏，不影响表空间使用，只是损坏
的数据文件里的数据无法使用。
