目标：将USERS表空间数据文件从RAC_DATA磁盘组移动到MING磁盘组。
这里不用alter database move datafile那种方式，直接用rman copy，然后switch一下就可以了。
首先修改数据库为归档模式
SQL> alter system set log_archive_dest_1='LOCATION=/arch1' scope=spfile sid='ractest1';
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=/arch2' scope=spfile sid='ractest2';
System altered.
重启数据库
srvctl stop database -d ractest
srvctl start database -d ractest -o mount
sqlplus / as sysdba
alter database archivelog;
alter database open; --两个节点
查看要被移动的数据文件
rman target /
RMAN> report schema;
Report of database schema for database with db_unique_name RACTEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM *** +RAC_DATA/ractest/system01.dbf
2 510 SYSAUX *** +RAC_DATA/ractest/sysaux01.dbf
3 110 UNDOTBS1 *** +RAC_DATA/ractest/undotbs01.dbf
4 15 USERS *** +RAC_DATA/ractest/users01.dbf
5 100 EXAMPLE *** +RAC_DATA/ractest/example01.dbf
6 25 UNDOTBS2 *** +RAC_DATA/ractest/undotbs02.dbf
7 100 TBS_MING *** +MING/ractest/datafile/tbs_ming.256.982218743
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 30 TEMP 32767 +RAC_DATA/ractest/temp01.dbf
拷贝数据文件
RMAN> BACKUP AS COPY
2> DATAFILE "+RAC_DATA/ractest/users01.dbf"
3> FORMAT "+MING";
Starting backup at 11-SEP-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+RAC_DATA/ractest/users01.dbf
output file name=+MING/ractest/datafile/users.257.986536253 tag=TAG20180911T055052 RECID=2 STAMP=986536257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 11-SEP-18
asmcmd中查看已经成功复制
ASMCMD> ls
TBS_MING.256.982218743
USERS.257.986536253
离线数据文件
RMAN> SQL "ALTER DATABASE DATAFILE
2> ''+RAC_DATA/ractest/users01.dbf'' OFFLINE";
sql statement: ALTER DATABASE DATAFILE ''+RAC_DATA/ractest/users01.dbf'' OFFLINE
查看数据文件状态
SQL> col name for a30
SQL> select name,status from v$datafile where name like '%user%';
NAME STATUS
------------------------------ -------
+RAC_DATA/ractest/users01.dbf RECOVER
更新控制文件中的信息
RMAN> SWITCH DATAFILE "+RAC_DATA/ractest/users01.dbf" TO COPY;
datafile 4 switched to datafile copy "+MING/ractest/datafile/users.257.986536253"
recover数据文件
RMAN> SWITCH DATAFILE "+RAC_DATA/ractest/users01.dbf" TO COPY;
datafile 4 switched to datafile copy "+MING/ractest/datafile/users.257.986536253"
RMAN> RECOVER DATAFILE "+MING/ractest/datafile/users.257.986536253";
Starting recover at 11-SEP-18
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished recover at 11-SEP-18
online数据文件
RMAN> SQL "ALTER DATABASE DATAFILE
2> ''+MING/ractest/datafile/users.257.986536253'' ONLINE";
sql statement: ALTER DATABASE DATAFILE ''+MING/ractest/datafile/users.257.986536253'' ONLINE
查看数据文件状态
NAME STATUS
-------------------------------------------------- -------
+MING/ractest/datafile/users.257.986536253 ONLINE
删除旧的磁盘组中的复制文件
RMAN> DELETE DATAFILECOPY "+RAC_DATA/ractest/users01.dbf";
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=ractest1 device type=DISK
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
3 4 A 11-SEP-18 1178882 11-SEP-18
Name: +RAC_DATA/ractest/users01.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=+RAC_DATA/ractest/users01.dbf RECID=3 STAMP=986536580
Deleted 1 objects
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name RACTEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM *** +RAC_DATA/ractest/system01.dbf
2 510 SYSAUX *** +RAC_DATA/ractest/sysaux01.dbf
3 110 UNDOTBS1 *** +RAC_DATA/ractest/undotbs01.dbf
4 15 USERS *** +MING/ractest/datafile/users.257.986536253
5 100 EXAMPLE *** +RAC_DATA/ractest/example01.dbf
6 25 UNDOTBS2 *** +RAC_DATA/ractest/undotbs02.dbf
7 100 TBS_MING *** +MING/ractest/datafile/tbs_ming.256.982218743
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 30 TEMP 32767 +RAC_DATA/ractest/temp01.dbf