测试环境
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)
订阅号：DongDB手记
墨天轮：https://www.modb.pro/u/231198
最后修改时间：2023-04-01 12:31:16
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
