oracle转移数据文件

首先关闭数据库，将要转移的数据文件拷贝到新的目录下。然后：

1、打开数据库至mount状态：

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  805306368 bytes

Fixed Size                  1270076 bytes

Variable Size             486543044 bytes

Database Buffers          310378496 bytes

Redo Buffers                7114752 bytes

Database mounted.

2、重命名数据文件

SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/addm1.dbf' to '/u01/app/oracle/oradata/orcl/addm1.dbf';

 

Database altered.

 

3、打开数据库，发生错误

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 13 needs media recovery

ORA-01110: data file 13: '/u01/app/oracle/oradata/orcl/addm1.dbf'

 

打开失败，数据库还处于mounted状态：

SQL> select status from v$instance;

 

STATUS

------------

MOUNTED

 

查看数据文件存放目录，编号13的文件已指向新的位置：

SQL> col name format a50

SQL> SELECT FILE#, NAME FROM V$DATAFILE;

 

     FILE# NAME

---------- --------------------------------------------------

         1 /u01/app/oracle/oradata/orcl/system01.dbf

         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf

         3 /u01/app/oracle/oradata/orcl/sysaux01.dbf

         4 /u01/app/oracle/oradata/orcl/users01.dbf

         5 /u01/app/oracle/oradata/orcl/example01.dbf

         6 /u01/app/oracle/oradata/orcl/example02.dbf

         7 /tmp/ts_16k.dbf

         8 /u01/app/oracle/oradata/orcl/rman.dbf

         9 /u01/app/oracle/oradata/orcl/gx_dev01.dbf

        10 /u01/app/oracle/oradata/orcl/gx_dev_cus.dbf

        11 /u01/app/oracle/oradata/orcl/gx_biz_mon.dbf

 

     FILE# NAME

---------- --------------------------------------------------

        12 /u01/app/oracle/oradata/orcl/inventory01.dbf

        13 /u01/app/oracle/oradata/orcl/addm1.dbf

 

13 rows selected.

 

 4、恢复数据库

SQL> recover database

Media recovery complete.

5 打开数据库

SQL> alter database open;

 

Database altered.