数据文件迁移
模拟：
1、sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 7 13:57:23 2017http://www.cndba.cn/Marvinn/article/2582

Copyright (c) 1982, 2013, Oracle. All rights reserved.http://www.cndba.cn/Marvinn/article/2582

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> conn scott/tiger
Connected.

—-创建表空间
SQL> create tablespace w datafile ‘/u01/w.dbf’ size 5m autoextend on;

Tablespace created

——创建表并添加测试数据
. SQL> create table ww (id number) tablespace w ;

Table created.

SQL> insert into ww values (1);

1 row created.http://www.cndba.cn/Marvinn/article/2582

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

—-查询数据文件号
SQL> select file_name ,file_id ,status from dba_data_files;

FILE_NAME

FILE_ID STATUS

---

+DATA/orcl/datafile/users.295.951084225
4 AVAILABLEhttp://www.cndba.cn/Marvinn/article/2582

+DATA/orcl/datafile/undotbs1.294.951084225
3 AVAILABLE

+DATA/orcl/datafile/sysaux.293.951084225
2 AVAILABLE

FILE_NAME

FILE_ID STATUS

---

+DATA/orcl/datafile/system.292.951084223
1 AVAILABLE

+DATA/orcl/datafile/undotbs2.300.951084363
5 AVAILABLE

/u01/w.dbf
6 AVAILABLE

6 rows selected.

—-设置数据文件离线offline
SQL> alter database datafile 6 offline;

Database altered.

SQL> exit

—-利用rman转换文件存放位置
oracle@rac1:/u01>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 7 14:01:59 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1478865032)
RMAN> convert datafile ‘/u01/w.dbf’ db_file_name_convert ‘/u01/‘,’+DATA/orcl/datafile/ww.dbf’;

Starting conversion at target at 2017-08-07 14:06:07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/u01/w.dbf
converted datafile=+DATA/orcl/datafile/ww.dbfw.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 2017-08-07 14:06:08

RMAN> exit

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 7 14:06:24 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

—-重命名数据文件
SQL> alter database rename file ‘/u01/w.dbf’ to ‘+data/orcl/datafile/ww.dbfw.dbf’;

Database altered.
SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: ‘+DATA/orcl/datafile/ww.dbfw.dbf’

—-恢复数据文件
SQL> recover datafile 6 ;
Media recovery complete.

—-启用数据文件
SQL> alter database datafile 6 online;

Database altered.
—-查看并验证数据
SQL> select file_name,file_id,online_status from dba_data_files;

FILE_NAME

FILEID ONLINE

---

+DATA/orcl/datafile/users.295.951084225
4 ONLINE

+DATA/orcl/datafile/undotbs1.294.951084225
3 ONLINE

+DATA/orcl/datafile/sysaux.293.951084225
2 ONLINE

FILE_NAME

FILEID ONLINE

---

+DATA/orcl/datafile/system.292.951084223
1 SYSTEMhttp://www.cndba.cn/Marvinn/article/2582

+DATA/orcl/datafile/undotbs2.300.951084363
5 ONLINEhttp://www.cndba.cn/Marvinn/article/2582

+DATA/orcl/datafile/ww.dbfw.dbf
6 ONLINEhttp://www.cndba.cn/Marvinn/article/2582

6 rows selected.

SQL> select * from scott.ww;http://www.cndba.cn/Marvinn/article/2582

ID

---

 1
 1
 1
 1
 1
 1

6 rows selected.

——-成功！

版权声明：本文为博主原创文章，未经博主允许不得转载。