使用RAMN将本地的数据文件迁移到ASM磁盘组上

前一段时间遇到一个问题，那就是RAC环境下的一个数据文件，竟然放在了本地硬盘，而没有放在ASM磁盘组中。非常怪异的现象，更怪异的是RAC竟然允许这种情况的发生，下面我们就来进行实验操作：

1.首先在本地创建一个用户表空间（数据文件）

create user zhang identified by oracle123; 

create tablespace zhang_tp datafile '/u01/app/oracle/zhang_tp.dbf' size 100M autoextend on; 

alter user zhang default tablespace zhang_tp; 

grant create session,create table,create view,create sequence,unlimited tablespace to zhang; 

conn zhang/oracle123;

select * from session_privs; 查看用户相关权限

PRIVILEGE
--------------------------------------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
CREATE SEQUENCE

2.将本地数据文件迁移至ASM磁盘组中

su - oracle

[oracle@zdzrac1 ~]$ rman

RMAN> connect target system/oracle123;

RMAN> report schema ;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ZDZRAC

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               ***     +DATA/zdzrac/datafile/system.259.947408807
2    1190     SYSAUX               ***     +DATA/zdzrac/datafile/sysaux.260.947408813
3    965      UNDOTBS1             ***     +DATA/zdzrac/datafile/undotbs1.261.947408819
4    200      UNDOTBS2             ***     +DATA/zdzrac/datafile/undotbs2.263.947408829
5    5        USERS                ***     +DATA/zdzrac/datafile/users.264.947408833
6    110      ZDZ_TP               ***     +DATA/zdzrac/datafile/zdz_tp
7    110      ZDZ_TP               ***     +DATA/zdzrac/datafile/zdz_tp2
8    100      ZHANG_TP             ***     /u01/app/oracle/zhang_tp.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    73       TEMP                 32767       +DATA/zdzrac/tempfile/temp.262.947408821

RMAN> sql 'alter tablespace zhang_tp offline';

RMAN> backup as copy datafile 8 format '+DATA' ;

Starting backup at 16-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=587 instance=zdzrac1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u01/app/oracle/zhang_tp.dbf
output file name=+DATA/zdzrac/datafile/zhang_tp.274.952176119 tag=TAG20170816T132159 RECID=1 STAMP=952176120
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-AUG-17

RMAN>  switch datafile 8 to copy  ;

datafile 8 switched to datafile copy "+DATA/zdzrac/datafile/zhang_tp.274.952176119"

RMAN> report schema ;                              

Report of database schema for database with db_unique_name ZDZRAC

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               ***     +DATA/zdzrac/datafile/system.259.947408807
2    1190     SYSAUX               ***     +DATA/zdzrac/datafile/sysaux.260.947408813
3    965      UNDOTBS1             ***     +DATA/zdzrac/datafile/undotbs1.261.947408819
4    200      UNDOTBS2             ***     +DATA/zdzrac/datafile/undotbs2.263.947408829
5    5        USERS                ***     +DATA/zdzrac/datafile/users.264.947408833
6    110      ZDZ_TP               ***     +DATA/zdzrac/datafile/zdz_tp
7    110      ZDZ_TP               ***     +DATA/zdzrac/datafile/zdz_tp2
8    0        ZHANG_TP             ***     +DATA/zdzrac/datafile/zhang_tp.274.952176119

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    73       TEMP                 32767       +DATA/zdzrac/tempfile/temp.262.947408821

RMAN> sql 'alter tablespace zhang_tp online';

sql statement: alter tablespace zhang_tp online