測試環境

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手記
墨天輪：http://m.sunline.cc/u/231198