Oracle数据文件迁移（本地）

本地数据文件迁移：

将数据文件为：/u01/app/oracle/oradata/orcl/qianyitbs01.dbf
迁移至：/u01/app/oracle/oradata/orcl/disk（假设orcl下边挂载的一块新盘）

一、查看表空间及其对应数据文件

SQL> select t1.name,t2.name 
from v$tablespace t1,v$datafile t2 
where t1.ts#=t2.ts#;

NAME                           NAME
----------------------------- ----------------------------------------------
SYSTEM                         /u01/app/oracle/oradata/orcl/system01.dbf
SYSAUX                         /u01/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1                       /u01/app/oracle/oradata/orcl/undotbs01.dbf
USERS                          /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS2                       /u01/app/oracle/oradata/orcl/undotbs02.dbf
QINYI                          /u01/app/oracle/oradata/orcl/qianyitbs01.dbf

6 rows selected
SQL> 

二、关闭数据库

#使用sys用户执行：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

三、使用Oracle用户在本地操作系统将数据文件复制或移动到目标磁盘（目录）

$ cp /u01/app/oracle/oradata/orcl/qianyitbs01.dbf /u01/app/oracle/oradata/orcl/disk/qianyitbs01.dbf

四、将数据库加载至mount状态

#sys用户执行：
SQL> startup mount
ORACLE instance started.

Total System Global Area 2555445248 bytes
Fixed Size			     2255952 bytes
Variable Size		 	 1056965552 bytes
Database Buffers	 	 1476395008 bytes
Redo Buffers		   	 19828736 bytes
Database mounted.
SQL> 

五、修改控制文件中数据文件路径

SQL> alter database rename file
'/u01/app/oracle/oradata/orcl/qianyitbs01.dbf' 
to 
'/u01/app/oracle/oradata/orcl/disk/qianyitbs01.dbf';

Database altered.

SQL> 

六、查询v$datafile数据字典验证表空间数据文件路径是否修改为新的磁盘下

SQL> select name from v$datafile;

NAME
-------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs02.dbf
/u01/app/oracle/oradata/orcl/disk/qianyitbs01.dbf

6 rows selected.

SQL> 

七、启动数据库，再次查询表空间及其对应数据文件

#这时启动数据库报错：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/disk/qianyitbs01.dbf'

SQL> 

一般来说将刚迁移的数据文件设置为脱机状态，在进行recovery即可，
处理如下：

#首先将对应报错的数据文件脱机
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/disk/qianyitbs01.dbf' offline; 
alter database datafile  '/u01/app/oracle/oradata/orcl/disk/qianyitbs01.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
#报错不允许立即脱机，除非启用了介质恢复
SQL> 

#数据文件确实已经复制到了/u01/app/oracle/oradata/orcl/disk/目录下，所以直接recovery
SQL> alter database recover datafile '/u01/app/oracle/oradata/orcl/disk/qianyitbs01.dbf';

Database altered.

SQL>

启动数据库,并查询数据库状态：

SQL> alter database open;

Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
orcl			 OPEN
#数据库开启状态
SQL>

八、再次查询表空间及其对应数据文件

SQL> select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts#=t2.ts#;

NAME                           NAME
------------------------------ --------------------------------------------------------------------------------
SYSTEM                         /u01/app/oracle/oradata/orcl/system01.dbf
SYSAUX                         /u01/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1                       /u01/app/oracle/oradata/orcl/undotbs01.dbf
USERS                          /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS2                       /u01/app/oracle/oradata/orcl/undotbs02.dbf
QINYI                          /u01/app/oracle/oradata/orcl/disk/qianyitbs01.dbf
								#数据文件路径成功更改，迁移完毕
6 rows selected
SQL> 

将文件系统中/u01/app/oracle/oradata/orcl/qianyitbs01.dbf数据文件删除即可（千万不要删错了！）

至此结束，有什么问题欢迎留言，谢谢。