昨日与维照的技术交流中，说起oracle的诸多online操作，所以决定写一个oracle online操作的系列文章。
先从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
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操作
|
1 |
SQL> alter database move datafile 12 to '/oracle/app/oracle/oradata/ORA12C/B727E6DDCF847CEDE0555D384BFF4A69/datafile/12_mv.dbf'; |
通过分析发现其内部操作如下：
第一步：调用kcffo_movefile函数开始对数据文件进行online move，这个move期间session都会持有对应数据文件的MV锁
|
1 2 3 4 5 6 7 8 9 |
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函数对数据文件进行初始化。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
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的句柄
|
1 2 3 |
[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 继续拷贝。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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的根本所在。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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锁，此时在切换完成之前任何检查点都将无法完成。这保证了切换前数据文件的一致性。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
*** 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的结束动作。主要操作有两步：
- 清理控制文件中的 secondary file(如果没有加keep子句的话)
- 清理原数据文件(如果没有加keep子句的话)