前一段时间遇到一个问题,那就是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