[20150913]文件检查点-表空间offline.txt
--oracle从8i以后基本使用增量检查点取代原来的检查点模式,但是如果一个表空间处于热备份模式以及offline时，要将这些表空间包含
--的数据文件所涉及到的脏块写数据文件.这个叫file level Checkpoint(感觉叫tablespace level checkpoint更加合适一些),如何验证
--这个过程,实际上很简单仅仅需要检查v$bh或者x$bh视图,或者转储脏块看看里面的内容是否存在变化,来验证这个过程,还是通过例子来
--说明:
--我个人喜欢使用bbed观察，效果与块转储一直。
1.运行环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> show parameter alert
NAME TYPE VALUE
------------------------- -------- --------
log_checkpoints_to_alert boolean TRUE
--设置log_checkpoints_to_alert=true,主要是为了测试需要，如果存在full checkpoint以及增量检查会写alert日志文件。
alter system set log_checkpoint_timeout=3600 scope=memory;
--设置的目的主要延长增量检查点的时间间隔(我的测试机器业务很小，我仅仅自己使用)，这样便于测试，参考链接：
--http://blog.itpub.net/267265/viewspace-1796999/
create table ta tablespace test as select 1 id1 ,cast('eeee' as varchar2(20)) name from dual ;
create table tb tablespace users as select 2 id1 ,cast('ffff' as varchar2(20)) name from dual ;
SCOTT@test> select rowid,ta.* from ta;
ROWID ID1 NAME
------------------ ---------- --------------------
AABMyVAAIAAAACDAAA 1 eeee
SCOTT@test> @rowid AABMyVAAIAAAACDAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
314517 8 131 0 8,131 alter system dump datafile 8 block 131 ;
SCOTT@test> select rowid,tb.* from tb;
ROWID ID1 NAME
------------------ ---------- --------------------
AABMyWAAEAAAAIzAAA 2 ffff
SCOTT@test> @rowid AABMyWAAEAAAAIzAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
314518 4 563 0 4,563 alter system dump datafile 4 block 563 ;
SCOTT@test> alter system checkpoint;
System altered.
--查看alert日志:
Mon Sep 14 08:48:49 2015
Beginning global checkpoint up to RBA [0xe2f.c498.10], SCN: 13201303348
Completed checkpoint up to RBA [0xe2f.c498.10], SCN: 13201303348
2.测试表空间offline：
--session 1:
SCOTT@test> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
206 629 19683 alter system kill session '206,629' immediate;
SCOTT@test> @bh 8 131
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC9BF878 8 131 1 data block xcur 1 0 0 0 0 0 000000009CD56000 TA
00000000BC9BF878 8 131 1 data block free 0 0 0 0 0 0 000000008F5C2000
00000000BC9BF878 8 131 1 data block free 0 0 0 0 0 0 000000008F5C0000
00000000BC9BF878 8 131 1 data block free 0 0 0 0 0 0 00000000A20D2000
00000000BC9BF878 8 131 1 data block free 0 0 0 0 0 0 000000008F5BA000
SCOTT@test> @bh 4 563
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC9B2358 4 563 1 data block xcur 1 0 0 0 0 0 000000009EAD8000 TB
00000000BC9B2358 4 563 1 data block free 0 0 0 0 0 0 000000009B390000
00000000BC9B2358 4 563 1 data block free 0 0 0 0 0 0 000000009CB96000
update ta set name=upper(name) where id1=1;
update tb set name=upper(name) where id1=2;
--不提交。STATE=free可能是先drop在建立的原因.
--session 2:
SCOTT@test> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
14 69 19673 alter system kill session '14,69' immediate;
SCOTT@test> select * from V$SESSION_EVENT where sid=14;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
14 log file sync 1 0 1 .62 1 6230 1328744198 3386400367 5 Commit
14 SQL*Net message to client 15 0 0 0 0 62 2067390145 2000153315 7 Network
14 SQL*Net message from client 15 0 29663 1977.53 28288 296629134 1421975091 2723168908 6 Idle
SCOTT@test> alter tablespace test offline ;
Tablespace altered.
SCOTT@test> select * from V$SESSION_EVENT where sid=14;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
14 Disk file operations I/O 5 0 0 .02 0 1188 166678035 1740759767 8 User I/O
14 control file sequential read 28 0 0 0 0 342 3213517201 4108307767 9 System I/O
14 control file parallel write 12 0 14 1.18 1 141835 4078387448 4108307767 9 System I/O
14 log file sync 3 0 3 1.06 1 31873 1328744198 3386400367 5 Commit
14 db file sequential read 2 0 0 0 0 25 2652584166 1740759767 8 User I/O
14 db file single write 1 0 1 .76 1 7645 1307477558 1740759767 8 User I/O
14 SQL*Net message to client 19 0 0 0 0 88 2067390145 2000153315 7 Network
14 SQL*Net message from client 18 0 33753 1875.16 28288 337528864 1421975091 2723168908 6 Idle
14 events in waitclass Other 6 0 7 1.1 6 65755 1736664284 1893977003 0 Other
9 rows selected.
--观察alert日志:
Mon Sep 14 08:48:49 2015
Beginning global checkpoint up to RBA [0xe2f.c498.10], SCN: 13201303348
Completed checkpoint up to RBA [0xe2f.c498.10], SCN: 13201303348
Mon Sep 14 08:51:30 2015
alter tablespace test offline
Completed: alter tablespace test offline
--session 1:
SCOTT@test> @bh 8 131
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC9BF878 8 131 1 data block free 0 0 0 0 0 0 000000009CD56000 TA
00000000BC9BF878 8 131 1 data block free 0 0 0 0 0 0 000000008F5C2000
00000000BC9BF878 8 131 1 data block free 0 0 0 0 0 0 000000008F5C0000
00000000BC9BF878 8 131 1 data block free 0 0 0 0 0 0 00000000A20D2000
00000000BC9BF878 8 131 1 data block free 0 0 0 0 0 0 000000008F5BA000
SCOTT@test> @bh 4 563
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC9B2358 4 563 1 data block xcur 1 0 0 0 0 0 000000009EAD8000 TB
00000000BC9B2358 4 563 1 data block free 0 0 0 0 0 0 000000009B390000
00000000BC9B2358 4 563 1 data block free 0 0 0 0 0 0 000000009CB96000
--表TA的状态是FREE,表示信息已经写盘,并且可以给别的人使用.
3.bbed观察：
BBED> set dba 8,131
DBA 0x02000083 (33554563 8,131)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: EEEE
BBED> set dba 4,563
DBA 0x01000233 (16777779 4,563)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2
col 0[2] @8180: 2
col 1[4] @8183: ffff
--可以确定TA表对应的脏块已经写盘.
4.继续测试:
--session 1:
rollback ;
--session 2:
alter tablespace test online ;
--通过bbed观察:
BBED> set dba 8,131
DBA 0x02000083 (33554563 8,131)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: EEEE
BBED> set dba 4,563
DBA 0x01000233 (16777779 4,563)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2
col 0[2] @8180: 2
col 1[4] @8183: ffff
--可以确定表空间online并不会发出file level Checkpoint.
5.其它说明:
--表空间offline还可以加immediate参数,这样情况并不写盘,出现这种情况无法online,必须执行recover tablespace test命令或者
--recover datafile xx命令.
SCOTT@test> alter tablespace test offline immediate;
Tablespace altered.
SCOTT@test> alter tablespace test online ;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u01/app/oracle11g/oradata/test/test01.dbf'
SCOTT@test> recover datafile 8;
Media recovery complete.
SCOTT@test> alter tablespace test online ;
Tablespace altered.
--另外数据文件offline也不会写盘.这样online必须需要一个恢复过程.
SCOTT@test> alter database datafile 8 offline ;
Database altered.
SCOTT@test> alter database datafile 8 online ;
alter database datafile 8 online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u01/app/oracle11g/oradata/test/test01.dbf'
SCOTT@test> recover tablespace test;
Media recovery complete.
SCOTT@test> alter database datafile 8 online ;
Database altered.
--所以file level Checkpoint叫tablespace level checkpoint更加合适一些.纯属个人理解,也许不对.
