--这里我们用一个D_DATA_01表空间为例
1、--我们先查询要迁移的表空间对应的RAW
select d.file_name,d.tablespace_name,d.bytes/1024/1024/1024 "Used(G)" from dba_data_files d where d.tablespace_name='D_DATA_01' order by d.file_name desc
FILE_NAME TABLESPACE_NAME Used(G)
------------------------------ -------------------- ----------
/dev/raw/raw93 D_DATA_01 3.99999237
/dev/raw/raw90 D_DATA_01 3.99999237
/dev/raw/raw52 D_DATA_01 1.99999237
/dev/raw/raw51 D_DATA_01 1.99999237
/dev/raw/raw117 D_DATA_01 7.99999237
2、--我们这里把表空间OFFLINE
SQL> ALTER TABLESPACE D_DATA_01 OFFLINE;
Tablespace altered.
3、--进入到RMAN
rman target /
RMAN> copy datafile '/dev/raw/raw90' to '+ZYDB_DATA';
Starting backup at 01-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=200 instance=centerdb2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00139 name=/dev/raw/raw90
output file name=+ZYDB_DATA/centerdb/datafile/d_data_01.258.830358861 tag=TAG20131101T151419 RECID=167 STAMP=830358902
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
Finished backup at 01-NOV-13
RMAN> copy datafile '/dev/raw/raw93' to '+ZYDB_DATA';
Starting backup at 01-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00151 name=/dev/raw/raw93
output file name=+ZYDB_DATA/centerdb/datafile/d_data_01.256.830358947 tag=TAG20131101T151547 RECID=168 STAMP=830358990
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
Finished backup at 01-NOV-13
RMAN> copy datafile '/dev/raw/raw52' to '+ZYDB_DATA';
Starting backup at 01-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00124 name=/dev/raw/raw52
output file name=+ZYDB_DATA/centerdb/datafile/d_data_01.259.830358999 tag=TAG20131101T151638 RECID=169 STAMP=830359021
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
Finished backup at 01-NOV-13
RMAN> copy datafile '/dev/raw/raw51' to '+ZYDB_DATA';
Starting backup at 01-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00123 name=/dev/raw/raw51
output file name=+ZYDB_DATA/centerdb/datafile/d_data_01.260.830359065 tag=TAG20131101T151744 RECID=170 STAMP=830359086
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 01-NOV-13
RMAN> copy datafile '/dev/raw/raw117' to '+ZYDB_DATA';
Starting backup at 01-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/dev/raw/raw117
output file name=+ZYDB_DATA/centerdb/datafile/d_data_01.261.830359101 tag=TAG20131101T151821 RECID=171 STAMP=830359190
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
Finished backup at 01-NOV-13
4、--[oracle@nydb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 1 15:22:50 2013
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 '/dev/raw/raw90' to '+ZYDB_DATA/centerdb/datafile/d_data_01.258.830358861';
Database altered.
SQL> alter database rename file '/dev/raw/raw93' to '+ZYDB_DATA/centerdb/datafile/d_data_01.256.830358947';
Database altered.
SQL> alter database rename file '/dev/raw/raw52' to '+ZYDB_DATA/centerdb/datafile/d_data_01.259.830358999';
Database altered.
SQL> alter database rename file '/dev/raw/raw51' to '+ZYDB_DATA/centerdb/datafile/d_data_01.260.830359065';
Database altered.
SQL> alter database rename file '/dev/raw/raw117' to '+ZYDB_DATA/centerdb/datafile/d_data_01.261.830359101';
Database altered.
5、--在把表空间online
SQL> alter tablespace D_DATA_01 online;
Tablespace altered.
6、--验证
SQL> select d.file_name,d.tablespace_name,d.bytes/1024/1024/1024 "Used(G)" from dba_data_files d where d.tablespace_name='D_DATA_01' order by d.file_name desc;
FILE_NAME TABLESPACE_NAME Used(G)
---------------------------------------------------- --------------- ----------
+ZYDB_DATA/centerdb/datafile/d_data_01.261.830359101 D_DATA_01 7.99999237
+ZYDB_DATA/centerdb/datafile/d_data_01.260.830359065 D_DATA_01 1.99999237
+ZYDB_DATA/centerdb/datafile/d_data_01.259.830358999 D_DATA_01 1.99999237
+ZYDB_DATA/centerdb/datafile/d_data_01.258.830358861 D_DATA_01 3.99999237
+ZYDB_DATA/centerdb/datafile/d_data_01.256.830358947 D_DATA_01 3.99999237
--到此D_DATA_01表空间也迁移完成,当然在实际生产环境中不只一个表空间,你可以通过脚本完成,大大可以提高工作效率。
--如有问题可以加此QQ2462238群进行讨论 作者网名:小明
--当然你是文件系统迁移方法也是如此。
--这一般是10G在线升级11G后,要求使用ASM
--如下是在线10.2.0.4 RAC 裸设备升级11.2.0.4.0 方法