暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / Oracle数据文件迁移 | 从一个磁盘组到另一个磁盘组

Oracle数据文件迁移 | 从一个磁盘组到另一个磁盘组

原创 董宏伟 云和恩墨 2022-11-04
2578

测试环境

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
扫码_搜索联合传播样式白色版.png

墨力计划oracle
最后修改时间：2023-04-01 12:31:16
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
2人已赞赏
皮
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论