以下文章转发自墨天轮社区，原文链接如下：
https://www.modb.pro/db/545425
正文如下：
测试环境
ORACLE RAC 19.12
方案概述
计划将ASM中的数据文件从一个磁盘组迁移到另外一个磁盘组。
12c开始，支持在线迁移数据文件，操作步骤简单。
在线移动一个正在被访问的数据文件；就算是system表空间中的数据文件也可以。
可以在线移动数据文件，表示当用户正在访问系统的时候，很多维护操作可以在线执行。这确保了服务的连续性，并且满足正常运行时的服务水平协议（SLA）。
实施步骤
离线迁移数据文件
不能迁移system表空间数据文件
offline数据文件
SQL> alter session set container=PDB1;
Session altered.
SQL> ALTER DATABASE datafile '+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.293.1107279497' offline ;
Database altered.
rman copy数据文件
RMAN> COPY DATAFILE '+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.293.1107279497' TO '+DATA12C';
Starting backup at 2022-10-20 11:25:53
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.293.1107279497
output file name=+DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.277.1118575553 tag=TAG20221020T112553 RECID=5 STAMP=1118575559
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2022-10-20 11:26:00
Starting Control File and SPFILE Autobackup at 2022-10-20 11:26:01
piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-3345856831-20221020-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2022-10-20 11:26:02
RMAN>
ASMCMD [+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE] > ls -l
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE OCT 20 11:00:00 Y SYSAUX.293.1107279497
DATAFILE MIRROR COARSE OCT 20 10:00:00 Y TBS_OGG.290.1109777737
DATAFILE MIRROR COARSE OCT 20 10:00:00 Y UNDOTBS1.292.1107279511
DATAFILE MIRROR COARSE OCT 20 10:00:00 Y UNDO_2.291.1107279513
DATAFILE MIRROR COARSE OCT 20 10:00:00 Y USERS.303.1107279547
ASMCMD [+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE] > ls -l +DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE OCT 20 11:00:00 Y SYSAUX.277.1118575553
DATAFILE UNPROT COARSE OCT 20 10:00:00 Y SYSTEM.276.1118572217
ASMCMD [+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE] >
rename数据文件
rename后，旧的数据文件被自动删除
SQL> alter session set container=PDB1;
Session altered.
SQL> ALTER DATABASE RENAME FILE '+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.293.1107279497' TO '+DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/SYSAUX.277.1118575553';
Database altered.
2022-10-20T11:27:22.484153+08:00
PDB1(4):ALTER DATABASE RENAME FILE '+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.293.1107279497' TO '+DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/SYSAUX.277.1118575553'
2022-10-20T11:27:22.567716+08:00
PDB1(4):Deleted Oracle managed file +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.293.1107279497
PDB1(4):Completed: ALTER DATABASE RENAME FILE '+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.293.1107279497' TO '+DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/SYSAUX.277.1118575553'
ASMCMD [+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE] > ls -l
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE OCT 20 10:00:00 Y TBS_OGG.290.1109777737
DATAFILE MIRROR COARSE OCT 20 10:00:00 Y UNDOTBS1.292.1107279511
DATAFILE MIRROR COARSE OCT 20 10:00:00 Y UNDO_2.291.1107279513
DATAFILE MIRROR COARSE OCT 20 10:00:00 Y USERS.303.1107279547
ASMCMD [+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE] >
recover数据文件
SQL> RECOVER DATAFILE '+DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.277.1118575553';
Media recovery complete.
online数据文件
SQL> ALTER DATABASE DATAFILE '+DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.277.1118575553' ONLINE;
Database altered.
SQL> select con_id,file#,name,status from v$datafile;
CON_ID FILE# NAME STATUS
---------- ---------- ---------------------------------------------------------------------------------------------------- -------
4 15 +DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/system.276.1118572217 SYSTEM
4 16 +DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.277.1118575553 ONLINE
4 17 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/undotbs1.292.1107279511 ONLINE
4 18 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/undo_2.291.1107279513 ONLINE
4 19 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/users.303.1107279547 ONLINE
4 32 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/tbs_ogg.290.1109777737 ONLINE
6 rows selected.
离线迁移表空间
不能迁移system表空间数据文件
offline表空间
SQL> alter tablespace sysaux offline;
Tablespace altered.
SQL> select con_id,file#,name,status from v$datafile;
CON_ID FILE# NAME STATUS
---------- ---------- ---------------------------------------------------------------------------------------------------- -------
4 15 +DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/system.276.1118572217 SYSTEM
4 16 +DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.277.1118575553 OFFLINE
4 17 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/undotbs1.292.1107279511 ONLINE
4 18 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/undo_2.291.1107279513 ONLINE
4 19 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/users.303.1107279547 ONLINE
4 32 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/tbs_ogg.290.1109777737 ONLINE
6 rows selected.
rman copy表空间
登录到需要迁移的pdb里
rman target sys/xxxxx@pdb1
backup as copy tablespace system format '+DATA1';
rman switch表空间
rman target sys/xxxxx@pdb1
switch tablespace sysaux to copy;
recover表空间
rman target sys/xxxxx@pdb1
recover tablespace sysaux;
online表空间
登录到需要迁移的pdb里
SQL> alter tablespace sysaux online;
Tablespace altered.
SQL> select con_id,file#,name,status from v$datafile;
CON_ID FILE# NAME STATUS
---------- ---------- ---------------------------------------------------------------------------------------------------- -------
4 15 +DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/system.276.1118572217 SYSTEM
4 16 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/sysaux.293.1118576615 ONLINE
4 17 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/undotbs1.292.1107279511 ONLINE
4 18 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/undo_2.291.1107279513 ONLINE
4 19 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/users.303.1107279547 ONLINE
4 32 +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/tbs_ogg.290.1109777737 ONLINE
6 rows selected.
删除旧的数据文件
此方法迁移，旧数据文件还存在，需要迁移后手工删除
ASMCMD [+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE] > ls -l +DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE OCT 20 11:00:00 Y SYSAUX.277.1118575553
DATAFILE UNPROT COARSE OCT 20 10:00:00 Y SYSTEM.276.1118572217
ASMCMD [+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE] > rm -f +DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/SYSAUX.277.1118575553
ASMCMD [+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE] >
在线迁移数据文件（12c+）
如果指定了KEEP子句，那么在移动操作之后将保留旧文件。如果源文件是OMF的文件，即使加了keep也会删除旧数据文件。
SQL> alter session set container=pdb1;
Session altered.
SQL> ALTER DATABASE MOVE DATAFILE '+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/system.304.1107279519' to '+DATA12C' keep;
Database altered.
SQL>
ALERT日志如下：
2022-10-20T10:30:16.058476+08:00
PDB1(4):ALTER DATABASE MOVE DATAFILE '+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/system.304.1107279519' to '+DATA12C' keep
2022-10-20T10:30:16.089800+08:00
Moving datafile +DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/system.304.1107279519 (15) to +DATA12C
2022-10-20T10:30:16.216200+08:00
NOTE: ASMB mounting group 1 (DATA12C)
NOTE: Assigned CGID 0x10004 for group 1
NOTE: ASMB process initiating disk discovery for grp 1 (reqid:0)
NOTE: Assigning number (1,0) to disk (/dev/asm-diske)
SUCCESS: mounted group 1 (DATA12C)
NOTE: grp 1 disk 0: DATA12C_0000 path:/dev/asm-diske
2022-10-20T10:30:16.619136+08:00
NOTE: dependency between database newdb and diskgroup resource ora.DATA12C.dg is established
2022-10-20T10:30:24.140706+08:00
Move operation committed for file +DATA12C/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/system.276.1118572217
2022-10-20T10:30:26.257841+08:00
PDB1(4):Completed: ALTER DATABASE MOVE DATAFILE '+DATA1/NEWDB/DFA6809F22DD5316E053C91FA8C0AC7B/DATAFILE/system.304.1107279519' to '+DATA12C' keep
参考文档
How to move ASM database files from one diskgroup to another ? (Doc ID 330103.1)
12C New Feature : Move a Datafile Online (Doc ID 1566797.1)
转文至此。
以下是个人微信公众号，欢迎关注：
文章转载自戏说数据那点事，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
