更改Oracle数据文件路径(原创)

近日，群里的朋友经常遇到数据文件需要删除或者改名的问题，这里，笔者进行了下总结，也算是抛砖引玉，希望大家拍砖。
删除数据文件
在10g中，如果是未被Oracle写入数据的数据文件则可以被直接删除，如果是已经被写入数据的数据文件，则无法被直接删除。请见下例
SQL> create tablespace test datafile '/home/oracle/test1.dbf' size 1m;
Tablespace created.
SQL>  create table test tablespace test
  2  as
  3* select * from dba_objects where rownum <=8000
Table created.
SQL> select SEGMENT_NAME,BYTES/1024/1024 Mb from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME                 MB                                                                                                                                                    
------------------          ------------                                                                 
TEST                           .875 
SQL> alter tablespace test add datafile '/home/oracle/test2.dbf' size 50m;
Tablespace altered.
SQL> insert into test select * from dba_objects ;
50323 rows created.
SQL> commit;
Commit complete.
SQL> select SEGMENT_NAME,BYTES/1024/1024 from dba_segments where SEGMENT_NAME='TEST'
SEGMENT_NAME         BYTES/1024/1024
-------------------- ---------------
TEST                               7
QL> alter tablespace test add datafile '/u01/app/test3.dbf' size 1m;
Tablespace altered.
SQL> alter tablespace test drop datafile '/u01/app/test3.dbf';
Tablespace altered.
SQL> alter tablespace test drop datafile '/u01/app/test2.dbf';
alter tablespace test drop datafile '/u01/app/test2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty

可以看到，只有非空的数据文件才能进行删除，已经写入数据的数据文件不能进行删除。
更改数据文件位置
更改数据文件位置，笔者认为有3种方法
1、利用rman的set newname来更改数据文件位置，对应用影响较小。

2、利用手工备份，然后拷贝数据到指定位置，原理和rman类似。

3、利用exp导出数据，重建表空间后再倒入数据，笔者认为该方法对应用影响较大，需结合实际情况考虑，这里不做详细介绍。
利用rman的set newname来更改数据文件位置
SQL> select file_name,status,file_id from dba_data_files;
FILE_NAME                                                         STATUS                    FILE_ID
-------------------------------------                ---------------------------         ---------------------------

/u01/app/oradata/orcl/users01.dbf                 AVAILABLE                  5
/u01/app/oradata/orcl/sysaux01.dbf               AVAILABLE                  2
/u01/app/oradata/orcl/undotbs01.dbf             AVAILABLE                  3
/u01/app/oradata/orcl/system01.dbf               AVAILABLE                  1
/u01/app/oradata/orcl/example01.dbf             AVAILABLE                  4
/home/oracle/test1.dbf                                          AVAILABLE                   6
/home/oracle/test2.dbf                                          AVAILABLE                   7
通过使用rman的set newname命令更改数据文件位置，整个过程如下：
1、由于 set newname命令是更改rman repository的信息 ，并根据 set newname的信息 restore备份数据到目标路径，故需 先对数据文件进行那个备份
RMAN> backup datafile 6,7;
Starting backup at 21-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/u01/app/test2.dbf
input datafile fno=00006 name=/u01/app/test1.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-12
channel ORA_DISK_1: finished piece 1 at 21-APR-12
piece handle=/u01/app/flash_recovery_area/ORCL/backupset/2012_04_21/o1_mf_nnndf_TAG20120421T150219_7s4pvvmt_.bkp tag=TAG20120421T150219 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-APR-12
2、 这一步 rman并不认为是将数据restore到新路径下，而是认为将数据文件以copy的方式备份到新路径下 ，官方文档解释如下：
Sets the default name for all subsequent RESTORE or SWITCH commands that affect the specified datafile. If you do not issue this command before the datafile restore operation, then RMAN restores the file to its default location.
After you restore a datafile to a new location, then you can run SWITCH to rename the file in the control file to the NEWNAME. If you do not run SWITCH, then the restored file functions as a datafile copy and is recorded as such in the repository.
RMAN> run{
2> set newname for datafile 6 to '/u01/app/test1.dbf';
3> set newname for datafile 7 to '/u01/app/test2.dbf';
4> restore datafile 6,7;
5>  }

RMAN> list copy;
specification does not match any archive log in the recovery catalog
List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
8       6    A 21-APR-12       832277     21-APR-12       /u01/app/test1.dbf
9       7    A 21-APR-12       832277     21-APR-12       /u01/app/test2.dbf
3、将表空间或者数据文件offline

RMAN>  sql 'alter tablespace test offline';
sql statement: alter tablespace test offline
4、通过switch命令将数据文件信息写入到控制文件中
RMAN> run{  
2> switch datafile all;                   #switch命令必须在run块里面
3> }
5、recover表空间或者数据文件
RMAN> recover datafile 6,7;
Starting recover at 21-APR-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 21-APR-12
6、将表空间或者数据文件online
RMAN> sql 'alter tablespace test online';
sql statement: alter tablespace test online
查看数据文件信息，大功告成
SQL> select file_name,status,file_id from dba_data_files;
FILE_NAME                                                         STATUS                    FILE_ID
-------------------------------------                ---------------------------         ---------------------------

/u01/app/oradata/orcl/users01.dbf                 AVAILABLE                  5
/u01/app/oradata/orcl/sysaux01.dbf               AVAILABLE                  2
/u01/app/oradata/orcl/undotbs01.dbf             AVAILABLE                  3
/u01/app/oradata/orcl/system01.dbf               AVAILABLE                  1
/u01/app/oradata/orcl/example01.dbf             AVAILABLE                  4
/u01/app/test1.dbf                                          AVAILABLE                   6
/u01/app/ test2.dbf                                          AVAILABLE                   7

利用手工备份，然后拷贝数据到指定位置

1、将表空间至于备份状态
SQL> alter tablespace test begin backup;
Tablespace altered.

2、拷贝数据文件到目标位置
$cp /home/oracle/test*.dbf /u01/app/test*

3、将表空间至于offline状态
SQL> alter tablespace test end backup;
Tablespace altered.
SQL> alter tablespace test offline; Tablespace altered.

3、重命名数据文件

SQL> alter tablespace test rename datafile '/home/oracle/test1.dbf' to
'/u01/app/test1.dbf';
Tablespace altered.
SQL> alter tablespace test rename datafile '/home/oracle/test2.dbf' to
'/u01/app/test2.dbf';
Tablespace altered.

4、对test表空间应用日志
SQL> recover tablespace test;
Media recovery complete.

5、将test表空间至于online
SQL> alter tablespace test online;
Tablespace altered.

6、查看表空间状态
SQL> select file_name from dba_data_files;
FILE_NAME
-----------------------------------------------------------------
/u01/app/oradata/orcl/users01.dbf
/u01/app/oradata/orcl/sysaux01.dbf
/u01/app/oradata/orcl/undotbs01.dbf
/u01/app/oradata/orcl/system01.dbf
/u01/app/oradata/orcl/example01.dbf
/u01/app/test1.dbf
/u01/app/test2.dbf
7 rows selected.
总结：以上介绍了如何更改非空数据文件的路径，笔者认为，rman操作的方式相对复杂，但对系统影响最小。手工备份的方式比较简单，但会产生更多的redo，如果表空间不是特别大的话亦可考虑。至于imp/exp导出的方式，对系统影响最大，个人认为需要结合实际情况分析。

参考至:http://docs.oracle.com/cd/B10500_01/server.920/a96565/rcmsynta50.htm
           http://docs.oracle.com/cd/B10500_01/server.920/a96565/rcmsynta56.htm
           http://www.cnblogs.com/rootq/archive/2010/03/05/1678969.html
本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:[e-mail]