online 移动datafile是12c中一个很好的特性之一,对我们做迁移来讲是太好不过了,比如存储更换,可以直接move,都不需要用rman去进行restore了.
那么多move datafile的实质到底如何呢 ?下面我们通过10046 event和strace命令来跟踪下move datafile的操作。
--Session 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
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>
1
----Session 3
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
~]$
1
----10046 trace event
1针对controlfile的读写:
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 ........
1
从跟踪来看,move datafile的过程中会对controlfile进行顺序读和parallel write.我们来看下对数据文件的读写情况:
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
1
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的:
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官网