oracle 11g移动数据文件到其他磁盘组

目标：将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