数据文件迁移案例

     (一)在数据库打开的情况下

 

SQL> alter database rename file '/oracle/product/11.2.0/dbhome_1/dbs/dms.dbf' to '/oracle/oradata/DCS/dms.dbf';

 

alter database rename file '/oracle/product/11.2.0/dbhome_1/dbs/dms.dbf' to '/oracle/oradata/DCS/dms.dbf'

 

ORA-01511: 重命名日志/数据文件时出错

ORA-01121: 无法重命名数据库文件 5 -文件在使用中或在恢复中

ORA-01110: 数据文件 5: '/oracle/product/11.2.0/dbhome_1/dbs/dms.dbf'

 

 

注：在数据库打开的状态下，会报错，数据文件需要在数据库关闭的情况下进行迁移。

 

 

 (二)以下是数据文件搬迁的一个全过程：

 

 

 

第一步：关闭数据库，启动到mount状态

 

Sqlplus>shutdown immediate;

Sqlplus>startup mount

 

cp/oracle/product/11.2.0/dbhome_1/dbs/dms.dbf

/oracle/oradata/DCS/

 

[oracle@DCSTEST01 dbs]$ cp /oracle/product/11.2.0/dbhome_1/dbs/UNDOTBS1.DBF /oracle/oradata/DCS/

[oracle@DCSTEST01 dbs]$ cp /oracle/product/11.2.0/dbhome_1/dbs/auto.dbf /oracle/oradata/DCS/

 

第二步：

 

 

alterdatabaserenamefile'/oracle/product/11.2.0/dbhome_1/dbs/auto.dbf'to '/oracle/oradata/DCS/auto.dbf';

alterdatabaserenamefile'/oracle/product/11.2.0/dbhome_1/dbs/UNDOTBS1.DBF'to '/oracle/oradata/DCS/UNDOTBS1.DBF';

 

 

SQL> alter database rename file '/oracle/product/11.2.0/dbhome_1/dbs/auto.dbf' to '/oracle/oradata/DCS/auto.dbf';

 

Database altered.

 

SQL> alter database rename file '/oracle/product/11.2.0/dbhome_1/dbs/UNDOTBS1.DBF' to '/oracle/oradata/DCS/UNDOTBS1.DBF';

 

Database altered.

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/oracle/oradata/DCS/auto.dbf'

 

 

 

 

SQL> recover database;

Media recovery complete.

SQL> alter database open;

 

Database altered.

 

注：重启数据库（Startup mount）时需要重新设置。测试成功。