3
/ oracle数据文件迁移 | 从一个磁盘组到另一个磁盘组

oracle数据文件迁移 | 从一个磁盘组到另一个磁盘组 -m6米乐安卓版下载

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

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人已赞赏
皮
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

网站地图