我要评论[20181031]12c 在线移动数据文件.txt
--//12c以前,移动或者改名数据文件是一项比较麻烦的事情,至少要停一下业务.而12c支持在线移动或者改名数据文件,并且有点不可思议
--//的是这个操作可以在非归档模式下完成.链接有人问这个安全性的问题,链接http://www.itpub.net/thread-2106077-1-1.html，自己也测试看看.
--//没有在linux下安装12c,而且linux下有许多分析工具strace,gdb等等.windows下使用systeminternals的procmon.exe跟踪看看，直接
--//跟踪tid。
1.环境:
scott@test01p> @ver1
port_string version banner con_id
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
ibmpc/win_nt64-9.1.0 12.2.0.1.0 oracle database 12c enterprise edition release 12.2.0.1.0 - 64bit production 0
2.建立测试环境:
create tablespace lfree datafile
'd:\app\oracle\oradata\test\test01p\lfree01.dbf' size 100m autoextend on next 1280k maxsize unlimited
logging
online
extent management local autoallocate
blocksize 8k
segment space management auto
flashback on;
scott@test01p> select * from v$dbfile ;
file# name con_id
---------- ------------------------------------------------ ------
8 d:\app\oracle\oradata\test\test01p\system01.dbf 3
9 d:\app\oracle\oradata\test\test01p\sysaux01.dbf 3
10 d:\app\oracle\oradata\test\test01p\undotbs01.dbf 3
11 d:\app\oracle\oradata\test\test01p\users01.dbf 3
36 d:\app\oracle\oradata\test\test01p\lfree01.dbf 3
--//确定fule#=36
scott@test01p> create table t tablespace lfree as select rownum id,'test' name from dual connect by level<=100;
table created.
3.建立一个存储过程：
--//以sys用户执行:
sys@test01p> grant execute on dbms_lock to scott;
grant succeeded.
--//再建立存储过程：
create or replace procedure test_proc as
begin
for j in 1..26 loop
for i in 1..100 loop
update t set name=chr(64+j)||i where id=i;
commit;
dbms_lock.sleep(0.1);
end loop;
end loop;
end;
/
procedure created.
--//脚本执行至少需要26*100*0.1 = 260秒。
--//session 1:
scott@test01p> @ spid
sid serial# process server spid pid p_serial# c50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
88 30472 5628:2772 dedicated 6624 9 15 alter system kill session '88,30472' immediate;
--//记录下spid=6624,然后先输入命令注意先不要回车执行,执行procmon.exe跟踪tid=6624：
scott@test01p> alter database move datafile 36 to 'd:\app\oracle\oradata\test\test01p\lfree01x.dbf';
--//sesson 2,执行如下,马上切换到session 1，执行以上命令：
scott@test01p> exec test_proc
--//在procmon.exe 界面上选择过滤 path contains lfree的条件：
"time of day","process name","pid","operation","path","result","detail","tid"
"21:35:46.2364632","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","creationtime: 2018/10/30 21:24:16, lastaccesstime: 2018/10/30 21:24:16, lastwritetime: 2018/10/30 21:27:37, changetime: 2018/10/30 21:27:37, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.2366668","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","creationtime: 2018/10/30 21:24:16, lastaccesstime: 2018/10/30 21:24:16, lastwritetime: 2018/10/30 21:27:37, changetime: 2018/10/30 21:27:37, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.2368618","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","creationtime: 2018/10/30 21:24:16, lastaccesstime: 2018/10/30 21:24:16, lastwritetime: 2018/10/30 21:27:37, changetime: 2018/10/30 21:27:37, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.2370088","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","creationtime: 2018/10/30 21:24:16, lastaccesstime: 2018/10/30 21:24:16, lastwritetime: 2018/10/30 21:27:37, changetime: 2018/10/30 21:27:37, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.2372062","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","desired access: generic read/write, disposition: open, options: write through, no buffering, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.2374521","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","desired access: generic read/write, disposition: open, options: no buffering, synchronous io non-alert, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.2578635","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","offset: 8,192, length: 8,192, i/o flags: non-cached, priority: normal","6624"
--//读旧数据文件头。
"21:35:46.2751434","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name not found","","6624"
"21:35:46.2753602","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name not found","","6624"
"21:35:46.2755355","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name not found","","6624"
"21:35:46.2758586","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name not found","desired access: generic read, disposition: open, options: no buffering, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a","6624"
"21:35:46.2761353","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name not found","","6624"
"21:35:46.2763101","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name not found","","6624"
"21:35:46.2764895","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name not found","","6624"
"21:35:46.2766853","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name not found","","6624"
"21:35:46.2768467","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name not found","","6624"
--//检测文件是否存在。
"21:35:46.2770729","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: generic write, read attributes, disposition: openif, options: no buffering, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: 0, openresult: created","6624"
"21:35:46.2776825","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.2778262","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.2780766","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.2782051","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.2784432","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io alert, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.2786538","oracle.exe","6808","deviceiocontrol","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","invalid parameter","control: ioctl_mountdev_query_device_name","6624"
"21:35:46.2787145","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.2789604","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name invalid","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, open reparse point, attributes: n, sharemode: read, write, allocationsize: n/a","6624"
"21:35:46.2888654","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","offset: 0, length: 8,192, i/o flags: non-cached, priority: normal","6624"
---//写新数据文件的os块。offset: 0, length: 8,192
"21:35:46.2896819","oracle.exe","6808","setpositioninformationfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","position: 104,865,792","6624"
"21:35:46.2897575","oracle.exe","6808","querypositioninformationfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","position: 104,865,792","6624"
"21:35:46.2898133","oracle.exe","6808","setendoffileinformationfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","endoffile: 104,865,792","6624"
"21:35:46.3006965","oracle.exe","6808","setallocationinformationfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","allocationsize: 104,865,792","6624"
--//分配空间。
"21:35:46.3008295","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.3014917","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.3018669","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.3020677","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.3022306","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.3024618","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: generic read, disposition: open, options: no buffering, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.3026732","oracle.exe","6808","querystandardinformationfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","allocationsize: 104,865,792, endoffile: 104,865,792, numberoflinks: 1, deletepending: false, directory: false","6624"
"21:35:46.3028325","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.3029478","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.3031436","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.3032475","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.3035123","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io alert, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.3037237","oracle.exe","6808","deviceiocontrol","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","invalid parameter","control: ioctl_mountdev_query_device_name","6624"
"21:35:46.3037738","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.3039897","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","name invalid","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, open reparse point, attributes: n, sharemode: read, write, allocationsize: n/a","6624"
"21:35:46.3117120","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","offset: 0, length: 512, i/o flags: non-cached, priority: normal","6624"
"21:35:46.3120699","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.3123072","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.3124443","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.3126726","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.3128220","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.3130026","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: generic read/write, disposition: open, options: no buffering, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.3132067","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: generic read/write, disposition: open, options: no buffering, synchronous io non-alert, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.3134501","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","offset: 8,192, length: 8,192, i/o flags: non-cached, priority: normal","6624"
--//写新数据文件的块头。offset: 8,192, length: 8,192
"21:35:46.3151172","oracle.exe","6808","flushbuffersfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.3157203","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.3159666","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","","6624"
"21:35:46.4123677","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.4124547","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.4125446","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.4126349","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","creationtime: 2018/10/30 21:35:46, lastaccesstime: 2018/10/30 21:35:46, lastwritetime: 2018/10/30 21:35:46, changetime: 2018/10/30 21:35:46, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:46.4127380","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: generic read/write, disposition: open, options: write through, no buffering, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
"21:35:46.4128751","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","desired access: generic read/write, disposition: open, options: no buffering, synchronous io non-alert, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6624"
--//从这里开始以下偏移16,384,读1m，写1m。
"21:35:46.4364983","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","offset: 16,384, length: 1,040,384, i/o flags: non-cached, priority: normal","6624"
"21:35:46.4421101","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","offset: 16,384, length: 1,040,384, i/o flags: non-cached, write through, priority: normal","6624"
--//从这里开始偏移16,384+1,040,384=1056768,读1m，写1m。
"21:35:46.4494547","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","offset: 1,056,768, length: 1,048,576, i/o flags: non-cached, priority: normal","6624"
"21:35:46.4729876","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","offset: 1,056,768, length: 1,048,576, i/o flags: non-cached, write through, priority: normal","6624"
------------------------------------------------------------------
--//太长截断，奇怪length: 1,048,576,难道中间过程写脏数据的情况吗？
------------------------------------------------------------------
"21:35:49.9299089","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","offset: 102,768,640, length: 1,048,576, i/o flags: non-cached, priority: normal","6624"
"21:35:49.9365770","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","offset: 102,768,640, length: 1,048,576, i/o flags: non-cached, write through, priority: normal","6624"
"21:35:49.9427614","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","offset: 103,817,216, length: 1,048,576, i/o flags: non-cached, priority: normal","6624"
"21:35:49.9508470","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","offset: 103,817,216, length: 1,048,576, i/o flags: non-cached, write through, priority: normal","6624"
--//再次出现写新数据文件的块头。offset: 8,192, length: 8,192，我估计这个跟以前测试rman的backup as copy一样。
--//参考链接:http://blog.itpub.net/267265/viewspace-2147540/ =>[20171121]rman backup as copy 2.txt
"21:35:49.9696618","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","offset: 8,192, length: 8,192, i/o flags: non-cached, priority: normal","6624"
"21:35:49.9935072","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","offset: 8,192, length: 8,192, i/o flags: non-cached, write through, priority: normal","6624"
--//再次出现写新数据文件的块头。offset: 8,192, length: 8,192，不过这个过程反过来，从新文件读，写旧数据文件。
"21:35:51.0114127","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree01x.dbf","success","offset: 8,192, length: 8,192, i/o flags: non-cached, priority: normal","6624"
"21:35:51.0119214","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","offset: 8,192, length: 8,192, i/o flags: non-cached, write through, priority: normal","6624"
"21:35:51.0202771","oracle.exe","6808","flushbuffersfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","","6624"
"21:35:51.0220320","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","","6624"
"21:35:51.0223452","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","","6624"
"21:35:52.0700080","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","creationtime: 2018/10/30 21:24:16, lastaccesstime: 2018/10/30 21:24:16, lastwritetime: 2018/10/30 21:35:51, changetime: 2018/10/30 21:35:51, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:52.0702186","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","creationtime: 2018/10/30 21:24:16, lastaccesstime: 2018/10/30 21:24:16, lastwritetime: 2018/10/30 21:35:51, changetime: 2018/10/30 21:35:51, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6624"
"21:35:52.0703976","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","desired access: read attributes, delete, disposition: open, options: non-directory file, open reparse point, attributes: n/a, sharemode: read, write, delete, allocationsize: n/a, openresult: opened","6624"
"21:35:52.0705479","oracle.exe","6808","queryattributetagfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","attributes: a, reparsetag: 0x0","6624"
"21:35:52.0706144","oracle.exe","6808","setdispositioninformationfile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","delete: true","6624"
--//删除旧文件,如果执行时没有keep参数。
"21:35:52.0707449","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree01.dbf","success","","6624"
--//我看到都是读1m，写1m，并没有看到dml相关操作啊！！仔细思考明白过来，我执行的存储过程仅仅commit,并不一定触发脏数据写盘操作。
4.继续测试：
create or replace procedure test_proc as
begin
for j in 1..26 loop
for i in 1..100 loop
update t set name=chr(64+j)||i where id=i;
commit;
dbms_lock.sleep(0.1);
execute immediate 'alter system flush buffer_cache';
--// execute immediate 'alter system checkpoint';
end loop;
end loop;
end;
/
--//注意上面要执行execute immediate 'alter system checkpoint';，必须显示授权,否者执行报错。
sys@test01p> grant alter system to scott;
grant succeeded.
--//重复前面的测试：
--//session 1,先输入命令注意先不要回车执行,执行procmon.exe跟踪tid=6624：
scott@test01p> alter database move datafile 36 to 'd:\app\oracle\oradata\test\test01p\lfree02x.dbf';
--//sesson 2,执行如下,马上切换到session 1，执行以上命令：
scott@test01p> exec test_proc
--//依旧不行。测试看到的结果与上面一样，难道是通过dbwr进程实现的。
5.继续测试：
scott@test01p> select spid,pname from v$process where pname='dbw0' ;
spid pname
-------------------- --------------------
6280 dbw0
--//跟踪spid = 6280看看。
--//重复前面的测试：
--//session 1,先输入命令注意先不要回车执行,执行procmon.exe跟踪tid=6280：
scott@test01p> alter database move datafile 36 to 'd:\app\oracle\oradata\test\test01p\lfree05.dbf';
--//sesson 2,执行如下,马上切换到session 1，执行以上命令：
scott@test01p> exec test_proc
--//在procmon.exe 界面上选择过滤 path contains lfree的条件：
"time of day","process name","pid","operation","path","result","detail","tid"
"22:47:25.8836412","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
--//这样就对上了。
scott@test01p> select rowid,t.* from t where rownum=1;
rowid id name
------------------ ---------- --------------------
aaafl+aakaaaacdaaa 1 d1
scott@test01p> @ rowid aaafl+aakaaaacdaaa
object file block row rowid_dba dba text
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
22910 36 131 0 0x9000083 36,131 alter system dump datafile 36 block 131
scott@test01p> select 8192*131 from dual ;
8192*131
----------
1073152
--//偏移在1073152dbw0写脏块。
"22:47:26.1317439","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:26.3033216","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:26.4949103","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:26.6944914","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:26.8921476","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:27.0585687","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:27.2483195","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:27.4866166","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:27.6899560","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:27.8798858","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:28.1655702","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:28.5559149","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","creationtime: 2018/10/30 22:47:28, lastaccesstime: 2018/10/30 22:47:28, lastwritetime: 2018/10/30 22:47:28, changetime: 2018/10/30 22:47:28, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6280"
--//开始执行alter database move datafile 36 to 'd:\app\oracle\oradata\test\test01p\lfree05.dbf';
"22:47:28.5560114","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","creationtime: 2018/10/30 22:47:28, lastaccesstime: 2018/10/30 22:47:28, lastwritetime: 2018/10/30 22:47:28, changetime: 2018/10/30 22:47:28, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6280"
"22:47:28.5561132","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","creationtime: 2018/10/30 22:47:28, lastaccesstime: 2018/10/30 22:47:28, lastwritetime: 2018/10/30 22:47:28, changetime: 2018/10/30 22:47:28, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6280"
"22:47:28.5561973","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","creationtime: 2018/10/30 22:47:28, lastaccesstime: 2018/10/30 22:47:28, lastwritetime: 2018/10/30 22:47:28, changetime: 2018/10/30 22:47:28, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6280"
"22:47:28.5563242","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","desired access: generic read, disposition: open, options: no buffering, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6280"
"22:47:28.5564153","oracle.exe","6808","querystandardinformationfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","allocationsize: 104,865,792, endoffile: 104,865,792, numberoflinks: 1, deletepending: false, directory: false","6280"
"22:47:28.5565208","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6280"
"22:47:28.5566066","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","","6280"
"22:47:28.5567417","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6280"
"22:47:28.5568082","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","","6280"
"22:47:28.5569322","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io alert, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6280"
"22:47:28.5570270","oracle.exe","6808","deviceiocontrol","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","invalid parameter","control: ioctl_mountdev_query_device_name","6280"
"22:47:28.5570545","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","","6280"
"22:47:28.5571813","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","name invalid","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, open reparse point, attributes: n, sharemode: read, write, allocationsize: n/a","6280"
"22:47:28.5621531","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 0, length: 512, i/o flags: non-cached, priority: normal","6280"
"22:47:28.5624503","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","","6280"
"22:47:28.5625928","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","creationtime: 2018/10/30 22:47:28, lastaccesstime: 2018/10/30 22:47:28, lastwritetime: 2018/10/30 22:47:28, changetime: 2018/10/30 22:47:28, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6280"
"22:47:28.5626843","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","creationtime: 2018/10/30 22:47:28, lastaccesstime: 2018/10/30 22:47:28, lastwritetime: 2018/10/30 22:47:28, changetime: 2018/10/30 22:47:28, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6280"
"22:47:28.5627857","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","creationtime: 2018/10/30 22:47:28, lastaccesstime: 2018/10/30 22:47:28, lastwritetime: 2018/10/30 22:47:28, changetime: 2018/10/30 22:47:28, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6280"
"22:47:28.5628732","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","creationtime: 2018/10/30 22:47:28, lastaccesstime: 2018/10/30 22:47:28, lastwritetime: 2018/10/30 22:47:28, changetime: 2018/10/30 22:47:28, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6280"
"22:47:28.5629881","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","desired access: generic read/write, disposition: open, options: write through, no buffering, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6280"
"22:47:28.5631269","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","desired access: generic read/write, disposition: open, options: no buffering, synchronous io non-alert, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6280"
"22:47:28.5633243","oracle.exe","6808","queryopen","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","creationtime: 2018/10/30 22:47:28, lastaccesstime: 2018/10/30 22:47:28, lastwritetime: 2018/10/30 22:47:28, changetime: 2018/10/30 22:47:28, allocationsize: 104,865,792, endoffile: 104,865,792, fileattributes: a","6280"
"22:47:28.5634225","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, non-directory file, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6280"
"22:47:28.5634898","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","","6280"
"22:47:28.5636109","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6280"
"22:47:28.5636725","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","","6280"
"22:47:28.5637890","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","desired access: read attributes, synchronize, disposition: open, options: synchronous io alert, attributes: n/a, sharemode: read, write, allocationsize: n/a, openresult: opened","6280"
"22:47:28.5638699","oracle.exe","6808","deviceiocontrol","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","invalid parameter","control: ioctl_mountdev_query_device_name","6280"
"22:47:28.5638954","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","","6280"
"22:47:28.5640144","oracle.exe","6808","createfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","name invalid","desired access: read attributes, synchronize, disposition: open, options: synchronous io non-alert, open reparse point, attributes: n, sharemode: read, write, allocationsize: n/a","6280"
--//写数据文件os块。
"22:47:28.5686767","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 0, length: 8,192, i/o flags: non-cached, priority: normal","6280"
"22:47:28.5690026","oracle.exe","6808","readfile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 0, length: 8,192, i/o flags: non-cached, priority: normal","6280"
--//可以看出一些细节，先写新数据文件，在写旧数据文件。间隔0.1秒。
"22:47:30.4859339","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:30.4859819","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:31.4623837","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:31.4624679","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:33.1933155","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:33.1933573","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:33.6612641","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:33.6614131","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:34.2625288","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:34.2626437","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:34.7037038","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:34.7038352","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:35.0569077","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:35.0570132","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:35.3842675","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:35.3843631","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:35.7080824","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:35.7081653","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:35.8961936","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:35.8962799","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:36.0512627","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:36.0513522","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:36.2413785","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:36.2414672","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:36.3961598","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:36.3962423","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:36.5580874","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:36.5581325","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:36.6095170","oracle.exe","6808","flushbuffersfile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","","6280"
"22:47:36.6107383","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","","6280"
"22:47:36.6108529","oracle.exe","6808","closefile","d:\app\oracle\oradata\test\test01p\lfree04x.dbf","success","","6280"
--//这里关闭旧数据文件，删除操作tid=6624完成，业务继续，这时仅仅修改新数据文件d:\app\oracle\oradata\test\test01p\lfree05x.dbf。
"22:47:36.7366151","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:36.9199684","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:37.0837297","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:37.2505211","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
"22:47:37.4077499","oracle.exe","6808","writefile","d:\app\oracle\oradata\test\test01p\lfree05x.dbf","success","offset: 1,073,152, length: 8,192, i/o flags: non-cached, write through, priority: normal","6280"
总结：
--//还是基本概念不清楚，实际上了解脏块写盘由dbwr进程完成，就没有必要走这么一堆弯路了。
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论