online data oracle,Oracle12c新特性之online 移动datafile

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官网