|
先从Online Move Datafile说起，Online Move Datafile是12.1的新特性，在12c之前如果想要move datafile通常需要下列步骤：
1.offline datafile 2.backup as copy数据文件到指定路径 3.switch datafile to copy切换控制文件中数据文件路径 4.由于offline datafile不会做检查点，所以在online之前需要recover datafile 5.online datafile
Oracle 12.1推出了Online Move Datafile的新特性，那么Oracle是如何通过一条命令就搞定了12c之前较为繁琐的步骤呢？下面将通过测试验证进行深入分析。 测试环境12.2，测试验证过程使用了gdb、10046、strace，gdb在kcffo_mv_prepare、kcffo_mv_copy_loop、kcffo_mv_remove_secondary等重要函数打上断点。10046跟踪Online Move Datafile的会话，strace跟踪dbwr进程。 发起数据文件12的online move操作： SQL> alter database move datafile 12 to '/oracle/app/oracle/oradata/ORA12C/B727E6DDCF847CEDE0555D384BFF4A69/datafile/12_mv.dbf';
通过分析发现其内部操作如下： 第一步：调用kcffo_movefile函数开始对数据文件进行online move，这个move期间session都会持有对应数据文件的MV锁。 Moving datafile /tmp/12_mvnew.dbf (12) to /oracle/app/oracle/oradata/ORA12C/B727E6DDCF847CEDE0555D384BFF4A69/datafile/12_mvnew.dbf
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000000066186B00 0000000066186B80 237 IS 0 0 4 0 1282 0 0
0000000066187968 00000000661879E8 237 TO 71021 1 3 0 31075 0 1
0000000066187B98 0000000066187C18 237 MR 12 0 4 0 1282 0 0
0000000066189EF8 0000000066189F78 237 MV 1 12 4 0 1282 0 0
0000000066186588 0000000066186608 237 MV 0 12 6 0 1282 0 0
第二步：调用kcffo_mv_prepare函数对数据文件进行初始化。 WAIT #140452576418240: nam='db file single write' ela= 34 file#=12 block#=1 blocks=1 obj#=0 tim=95452872153
WAIT #140452576418240: nam='Disk file operations I/O' ela= 11369 FileOperation=5 fileno=0 filetype=2 obj#=0 tim=95452883593
WAIT #140452576418240: nam='control file sequential read' ela= 12 file#=0 block#=1 blocks=1 obj#=0 tim=95452883736
WAIT #140452576418240: nam='control file sequential read' ela= 6 file#=1 block#=1 blocks=1 obj#=0 tim=95452883770
WAIT #140452576418240: nam='control file sequential read' ela= 6 file#=0 block#=15 blocks=1 obj#=0 tim=95452883788
WAIT #140452576418240: nam='control file sequential read' ela= 6 file#=0 block#=17 blocks=1 obj#=0 tim=95452883804
WAIT #140452576418240: nam='control file sequential read' ela= 6 file#=0 block#=89 blocks=1 obj#=0 tim=95452883826
WAIT #140452576418240: nam='control file sequential read' ela= 6 file#=0 block#=23 blocks=1 obj#=0 tim=95452883844
WAIT #140452576418240: nam='control file parallel write' ela= 1334 files=2 block#=90 requests=2 obj#=0 tim=95452885195
WAIT #140452576418240: nam='control file parallel write' ela= 1096 files=2 block#=24 requests=2 obj#=0 tim=95452886333
WAIT #140452576418240: nam='control file parallel write' ela= 862 files=2 block#=18 requests=2 obj#=0 tim=95452887230
WAIT #140452576418240: nam='control file parallel write' ela= 837 files=2 block#=16 requests=2 obj#=0 tim=95452888102
WAIT #140452576418240: nam='control file parallel write' ela= 833 files=2 block#=1 requests=2 obj#=0 tim=95452888977
kcffo_mv_prepare: the secondary file /oracle/app/oracle/oradata/ORA12C/B727E6DDCF847CEDE0555D384BFF4A69/datafile/12_mvnew.dbf is created with size 131072
从10046 trace也能看出来该步骤会做三个非常重要的操作： 在指定路径产生一个和原数据文件大小相同的数据文件称作secondary file，但这个文件只写入了数据文件头，整个文件都是空的。 将 secondary file 加入到控制文件DATA FILE RECORDS中，同样以datafile copy的形式存在 dbwr将打开secondary file的句柄，这是能实现online非常关键的一点
dump控制文件可以看到，secondary file被加入，并且 Online move state变成了1。 DATA FILE #12:
name #21: /tmp/12_mvnew.dbf
name #20: /oracle/app/oracle/oradata/ORA12C/B727E6DDCF847CEDE0555D384BFF4A69/datafile/12_mvnew.dbf ( second)
creation size=640 block size=8192 status=0xe flg=0x1 head=21 tail=20 dup=1
pdb_id 3, tablespace 5, index=14 krfil=12 prev_file_in_ts=0 prev_file_in_pdb=11
unrecoverable scn: 0x0000000000000000 01/01/1988 00:00:00
Checkpoint cnt:65 scn: 0x00000000003ff3ff 11/19/2021 22:09:10
Stop scn: 0xffffffffffffffff 11/19/2021 13:51:53
Creation Checkpointed at scn: 0x00000000001608f6 12/24/2020 03:43:36
thread:1 rba:(0x2.1c87f.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x00000000003ddac3 prev_range: 48
Online Checkpointed at scn: 0x00000000003f64d8 11/19/2021 13:54:21
thread:1 rba:(0x24.7b.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Hot Backup end marker scn: 0x0000000000000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000000000000000
Plugin resetlogs scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
Online move state: 1
查看dbwr持有的句柄发现dbwr持有了secondary file的句柄： [root@dbtest fd]# ls -l /proc/8355/fd |grep 12_mvnew
lrwx------ 1 oracle oinstall 64 Nov 19 22:51 267 -> /oracle/app/oracle/oradata/ORA12C/B727E6DDCF847CEDE0555D384BFF4A69/datafile/12_mvnew.dbf
lrwx------ 1 oracle oinstall 64 Nov 19 22:51 272 -> /tmp/12_mvnew.dbf
第三步：调用kcffo_mv_copy_loop函数对数据文件其他block进行拷贝，从2号块开始以多块读的方式做数据文件的拷贝。 为什么函数中有loop呢？个人认为是在此过程期间如果数据文件发生自动扩展或者resize，还会调用 kcffo_mv_copy_loop 继续拷贝。 WAIT #140452597414360: nam='db file scattered read' ela= 2723 file#=12 block#=2 blocks=127 obj#=0 tim=64444328091
WAIT #140452597414360: nam='db file parallel write' ela= 2992 requests=12 interrupt=2 timeout=127 obj#=0 tim=64444331227
WAIT #140452597414360: nam='db file scattered read' ela= 2298 file#=12 block#=129 blocks=128 obj#=0 tim=64444333571
WAIT #140452597414360: nam='db file parallel write' ela= 3640 requests=12 interrupt=129 timeout=128 obj#=0 tim=64444337409
WAIT #140452597414360: nam='db file scattered read' ela= 2760 file#=12 block#=257 blocks=128 obj#=0 tim=64444340251
WAIT #140452597414360: nam='db file parallel write' ela= 3509 requests=12 interrupt=257 timeout=128 obj#=0 tim=64444343842
WAIT #140452597414360: nam='db file scattered read' ela= 2507 file#=12 block#=385 blocks=128 obj#=0 tim=64444346388
WAIT #140452597414360: nam='db file parallel write' ela= 3211 requests=12 interrupt=385 timeout=128 obj#=0 tim=64444349715
WAIT #140452597414360: nam='db file scattered read' ela= 2583 file#=12 block#=513 blocks=128 obj#=0 tim=64444352382
WAIT #140452597414360: nam='db file parallel write' ela= 3236 requests=12 interrupt=513 timeout=128 obj#=0 tim=64444355779
WAIT #140452597414360: nam='db file scattered read' ela= 1935 file#=12 block#=641 blocks=128 obj#=0 tim=64444357826
WAIT #140452597414360: nam='db file parallel write' ela= 2767 requests=12 interrupt=641 timeout=128 obj#=0 tim=64444360665
WAIT #140452597414360: nam='db file scattered read' ela= 3931 file#=12 block#=769 blocks=128 obj#=0 tim=64444364681
WAIT #140452597414360: nam='db file parallel write' ela= 2514 requests=12 interrupt=769 timeout=128 obj#=0 tim=64444367978
WAIT #140452597414360: nam='db file scattered read' ela= 2204 file#=12 block#=897 blocks=128 obj#=0 tim=64444370384
WAIT #140452597414360: nam='db file parallel write' ela= 11184 requests=12 interrupt=897 timeout=128 obj#=0 tim=64444382079
WAIT #140452597414360: nam='db file scattered read' ela= 19043 file#=12 block#=1025 blocks=128 obj#=0 tim=64444401178
WAIT #140452597414360: nam='db file parallel write' ela= 2658 requests=12 interrupt=1025 timeout=128 obj#=0 tim=64444404082
...
WAIT #140452576418240: nam='db file scattered read' ela= 6702 file#=12 block#=130817 blocks=128 obj#=0 tim=96033679518
WAIT #140452576418240: nam='db file parallel write' ela= 3469 requests=12 interrupt=130817 timeout=128 obj#=0 tim=96033683562
WAIT #140452576418240: nam='db file scattered read' ela= 3211 file#=12 block#=130945 blocks=128 obj#=0 tim=96033687162
WAIT #140452576418240: nam='db file parallel write' ela= 4810 requests=12 interrupt=130945 timeout=128 obj#=0 tim=96033692734
WAIT #140452576418240: nam='rdbms ipc reply' ela= 1291 from_process=19 p2=0 p3=0 obj#=0 tim=96033694888
WAIT #140452576418240: nam='control file sequential read' ela= 7 file#=0 block#=1 blocks=1 obj#=0 tim=96033695382
WAIT #140452576418240: nam='control file sequential read' ela= 4 file#=1 block#=1 blocks=1 obj#=0 tim=96033695560
WAIT #140452576418240: nam='control file sequential read' ela= 8 file#=0 block#=16 blocks=1 obj#=0 tim=96033695629
WAIT #140452576418240: nam='control file sequential read' ela= 7 file#=0 block#=18 blocks=1 obj#=0 tim=96033695698
WAIT #140452576418240: nam='control file sequential read' ela= 9 file#=0 block#=24 blocks=1 obj#=0 tim=96033695798
WAIT #140452576418240: nam='db file sequential read' ela= 7 file#=12 block#=1 blocks=1 obj#=0 tim=96033695868
kcffo_mv_domove: Blocks copied for file /oracle/app/oracle/oradata/ORA12C/B727E6DDCF847CEDE0555D384BFF4A69/datafile/12_mvnew.dbf size 131072
在此过程中，发生了检查点，那么dbwr将实施“双写”，这一点就是Online Move Datafile的根本所在。 SQL> insert into test.t1 select * from dba_objects where rownum<10;
9 rows created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
8355 23:14:37.945194 (+ 0.000281) pwrite64(267, "!\242\0\0\211\0\0\3\32\376?\0\0\0\1\4\3\230\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1122304) = 8192
8355 23:14:37.945473 (+ 0.000279) pwrite64(272, "!\242\0\0\211\0\0\3\32\376?\0\0\0\1\4\3\230\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1122304) = 8192
8355 23:14:37.945893 (+ 0.000420) pwrite64(267, "#\242\0\0\212\0\0\3\32\376?\0\0\0\1\4\252\265\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1130496) = 8192
8355 23:14:37.946276 (+ 0.000382) pwrite64(272, "#\242\0\0\212\0\0\3\32\376?\0\0\0\1\4\252\265\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1130496) = 8192
8355 23:14:37.946604 (+ 0.000328) pwrite64(267, "\6\242\0\0\377\1\0\3-\314?\0\0\0\1\6\210\276\0\0\1\0\0\0\35\37\1\0)P3\0"..., 16384, 4186112) = 16384
8355 23:14:37.946919 (+ 0.000314) pwrite64(272, "\6\242\0\0\377\1\0\3-\314?\0\0\0\1\6\210\276\0\0\1\0\0\0\35\37\1\0)P3\0"..., 16384, 4186112) = 16384
8355 23:14:37.947218 (+ 0.000299) pwrite64(267, "\6\242\0\0\34\2\0\3K\2@\0\0\0\2\6~t\0\0\1\0\0\0\35\37\1\0\32\376?\0"..., 8192, 4423680) = 8192
8355 23:14:37.947488 (+ 0.000270) pwrite64(272, "\6\242\0\0\34\2\0\3K\2@\0\0\0\2\6~t\0\0\1\0\0\0\35\37\1\0\32\376?\0"..., 8192, 4423680) = 8192
8355 23:14:37.947766 (+ 0.000277) pwrite64(267, " \242\0\0H\24\0\3\32\376?\0\0\0\1\0044I\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 42532864) = 8192
8355 23:14:37.948091 (+ 0.000325) pwrite64(272, " \242\0\0H\24\0\3\32\376?\0\0\0\1\0044I\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 42532864) = 8192
8355 23:14:37.948412 (+ 0.000320) pwrite64(267, "\6\242\0\0V\24\0\3K\2@\0\0\0\2\6`D\0\0\2\0\0\0%\37\1\0J\2@\0"..., 8192, 42647552) = 8192
8355 23:14:37.948681 (+ 0.000268) pwrite64(272, "\6\242\0\0V\24\0\3K\2@\0\0\0\2\6`D\0\0\2\0\0\0%\37\1\0J\2@\0"..., 8192, 42647552) = 8192
第四步：切换控制文件中的数据文件，该过程中session将持有CF锁，此时在切换完成之前任何检查点都将无法完成。这保证了切换前数据文件的一致性。 *** 2021-11-19T23:14:37.730115+08:00 (CDB$ROOT(1))
WAIT #140452576418240: nam='db file single write' ela= 10529 file#=12 block#=1 blocks=1 obj#=0 tim=97508058188
WAIT #140452576418240: nam='control file sequential read' ela= 10 file#=0 block#=90 blocks=1 obj#=0 tim=97508058312
WAIT #140452576418240: nam='control file parallel write' ela= 1542 files=2 block#=23 requests=2 obj#=0 tim=97508059902
WAIT #140452576418240: nam='control file parallel write' ela= 1235 files=2 block#=89 requests=2 obj#=0 tim=97508061185
WAIT #140452576418240: nam='control file parallel write' ela= 1217 files=2 block#=18 requests=2 obj#=0 tim=97508062444
WAIT #140452576418240: nam='control file parallel write' ela= 1007 files=2 block#=16 requests=2 obj#=0 tim=97508063490
WAIT #140452576418240: nam='control file parallel write' ela= 989 files=2 block#=1 requests=2 obj#=0 tim=97508064520
Move operation committed for file /oracle/app/oracle/oradata/ORA12C/B727E6DDCF847CEDE0555D384BFF4A69/datafile/12_mvnew.dbf
WAIT #140452576418240: nam='rdbms ipc reply' ela= 6652 from_process=19 p2=0 p3=0 obj#=0 tim=97508071522
*** 2021-11-19T23:14:38.743441+08:00 (CDB$ROOT(1))
WAIT #140452576418240: nam='concurrent I/O completion' ela= 999936 p1=0 p2=0 p3=0 obj#=0 tim=97509071512
WAIT #140452576418240: nam='control file sequential read' ela= 13 file#=0 block#=1 blocks=1 obj#=0 tim=97509071675
WAIT #140452576418240: nam='control file sequential read' ela= 12 file#=1 block#=1 blocks=1 obj#=0 tim=97509071747
WAIT #140452576418240: nam='control file sequential read' ela= 5 file#=0 block#=15 blocks=1 obj#=0 tim=97509071776
WAIT #140452576418240: nam='control file sequential read' ela= 5 file#=0 block#=17 blocks=1 obj#=0 tim=97509071794
WAIT #140452576418240: nam='control file sequential read' ela= 5 file#=0 block#=24 blocks=1 obj#=0 tim=97509071827
WAIT #140452576418240: nam='db file sequential read' ela= 7 file#=12 block#=1 blocks=1 obj#=0 tim=97509071860
*** 2021-11-19T23:16:31.538225+08:00 (CDB$ROOT(1))
WAIT #140452576418240: nam='db file single write' ela= 9761 file#=12 block#=1 blocks=1 obj#=0 tim=97621866300
WAIT #140452576418240: nam='control file parallel write' ela= 1650 files=2 block#=23 requests=2 obj#=0 tim=97621868063
WAIT #140452576418240: nam='control file parallel write' ela= 1489 files=2 block#=18 requests=2 obj#=0 tim=97621869601
WAIT #140452576418240: nam='control file parallel write' ela= 937 files=2 block#=16 requests=2 obj#=0 tim=97621870594
WAIT #140452576418240: nam='control file parallel write' ela= 618 files=2 block#=1 requests=2 obj#=0 tim=97621871261
WAIT #140452576418240: nam='rdbms ipc reply' ela= 8300 from_process=19 p2=0 p3=0 obj#=0 tim=97621879661
Move operation completed for file /oracle/app/oracle/oradata/ORA12C/B727E6DDCF847CEDE0555D384BFF4A69/datafile/12_mvnew.dbf
第五步：调用kcffo_mv_remove_secondary函数清理secondary file，如果语句加了keep子句，将不会有这一步操作。该步骤是Online Move Datafile的结束动作。主要操作有两步：
|
