Oracle12c新特性之online 移动datafile

online 移动datafile是12c中一个很好的特性之一，对我们做迁移来讲是太好不过了，比如存储更换，可以直接move，都不需要用rman去进行restore了.

那么多move datafile的实质到底如何呢 ？下面我们通过10046 event和strace命令来跟踪下move datafile的操作。

--Session 1

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

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

SQL> select sid from v$mystat where rownum < 2;          SID ----------         30   SQL> select spid from v$process where addr=(select paddr  from v$session where sid=30);   SPID ------------------------ 4192 SQL> show user USER is "SYS" SQL> oradebug setmypid Statement processed. SQL> oradebug event 10046 trace name context forever,level 12; Statement processed. SQL>  alter database move datafile 6 to '/oracle/oradata/orcl/users01.dbf';   Database altered.   SQL> oradebug event 10046 trace name context off Statement processed. SQL> oradebug close_trace Statement processed. SQL> oradebug tracefile_name /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4192.trc SQL>

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

1	----Session 3

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

1 2 3 4 5 6

[oracle@12c_single ~]$ ps -ef|grep -v grep|grep 4192 oracle    4192  4191  0 11:32 ?        00:00:02 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) [oracle@12c_single ~]$ strace -fr -o /tmp/4192.log -p 4192 Process 4192 attached - interrupt to quit ^CProcess 4192 detached [oracle@12c_single ~]$

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

1	----10046 trace event

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

1针对controlfile的读写：

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

1 2 3 4 5 6 7 8

[root@12c_single ~]# cat /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4192.trc |grep 'control' WAIT #139835385925424: nam='control file sequential read' ela= 317 file#=0 block#=1 blocks=1 obj#=442 tim=43780028339 WAIT #139835385925424: nam='control file sequential read' ela= 113 file#=1 block#=1 blocks=1 obj#=442 tim=43780029029 WAIT #139835385925424: nam='control file sequential read' ela= 196 file#=0 block#=16 blocks=1 obj#=442 tim=43780029935 ........ WAIT #139835385925424: nam='control file parallel write' ela= 3611 files=2 block#=89 requests=2 obj#=442 tim=43780090566 WAIT #139835385925424: nam='control file parallel write' ela= 3450 files=2 block#=23 requests=2 obj#=442 tim=43780094311 ........

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

1	从跟踪来看，move datafile的过程中会对controlfile进行顺序读和parallel write. 我们来看下对数据文件的读写情况：

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

[root@12c_single ~]# cat /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4192.trc |grep 'db file' WAIT #139835385925424: nam='db file sequential read' ela= 64 file#=6 block#=1 blocks=1 obj#=442 tim=43780046459 WAIT #139835385925424: nam='db file single write' ela= 65 file#=6 block#=1 blocks=1 obj#=442 tim=43780078849 WAIT #139835385925424: nam='db file sequential read' ela= 53941 file#=6 block#=2 blocks=127 obj#=442 tim=43780166313 WAIT #139835385925424: nam='db file single write' ela= 48912 file#=6 block#=2 blocks=127 obj#=442 tim=43780215734 WAIT #139835385925424: nam='db file sequential read' ela= 26990 file#=6 block#=129 blocks=128 obj#=442 tim=43780244920 WAIT #139835385925424: nam='db file single write' ela= 68495 file#=6 block#=129 blocks=128 obj#=442 tim=43780313982 WAIT #139835385925424: nam='db file sequential read' ela= 29335 file#=6 block#=257 blocks=128 obj#=442 tim=43780346831 WAIT #139835385925424: nam='db file single write' ela= 48586 file#=6 block#=257 blocks=128 obj#=442 tim=43780396042 WAIT #139835385925424: nam='db file sequential read' ela= 44739 file#=6 block#=385 blocks=128 obj#=442 tim=43780443595 ......... WAIT #139835385925424: nam='db file sequential read' ela= 48956 file#=6 block#=25345 blocks=128 obj#=442 tim=43798447850 WAIT #139835385925424: nam='db file single write' ela= 47854 file#=6 block#=25345 blocks=128 obj#=442 tim=43798496353 WAIT #139835385925424: nam='db file sequential read' ela= 27800 file#=6 block#=25473 blocks=128 obj#=442 tim=43798527302 WAIT #139835385925424: nam='db file single write' ela= 47581 file#=6 block#=25473 blocks=128 obj#=442 tim=43798575454 WAIT #139835385925424: nam='db file sequential read' ela= 272 file#=6 block#=1 blocks=1 obj#=442 tim=43798579382 WAIT #139835385925424: nam='db file single write' ela= 1417 file#=6 block#=1 blocks=1 obj#=442 tim=43798581335 WAIT #139835385925424: nam='db file sequential read' ela= 348 file#=6 block#=1 blocks=1 obj#=442 tim=43799611993 WAIT #139835385925424: nam='db file single write' ela= 4429 file#=6 block#=1 blocks=1 obj#=442 tim=43799617061

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

1

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

1 2 3

我们可以看到，trace文件的前部和尾部都是对该数据文件的header block进行读，然后写.中间部分内容是按照128 block单位进行读写. 可以发现,是会将整个datafile的block都读一遍。细心的朋友也行会发现,这里居然是db file single write,按理说应该是db file parallel write 才对，因为后面的p3=128. 如果是single write,那么p3应该是1才对.

那么这里只有一种解释,将每个block都写一次,每次读完后都写128次.

每次db file single write之前也是对应的db file sequential read,大家注意后的p3也是128,并不是1. 12c的官方文档是这样解释这个event的：

<%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-12c%E5%88%9D%E4%BD%93%E9%AA%8C.html>

1 2 3 4 5

file#   See "file#"   block#  See "block#"   blocks  This is the number of blocks that the session is trying to read (should be 1)

所以这里给人似乎有点难以理解,难道是每次读1个block,读128次?

另外,关于move datafile操作,我相信肯定也是会触发检查点的.本文转自cuug官网