本文共 9374 字，大约阅读时间需要 31 分钟。
[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 ProductionSCOTT@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;
--设置的目的主要延长增量检查点的时间间隔(我的测试机器业务很小，我仅仅自己使用)，这样便于测试，参考链接：
--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 eeeeSCOTT@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 ffffSCOTT@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: 132013033482.测试表空间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 000000008F5BA000SCOTT@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 000000009CB96000update ta set name=upper(name) where id1=1;
update tb set name=upper(name) where id1=2;--不提交。STATE=free可能是先drop在建立的原因.
--session 2:
SCOTT@test> @spidSID 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 IdleSCOTT@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 Other9 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 000000008F5BA000SCOTT@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 0x2cBBED> x /rncn
rowdata[0] @8177 ---------- flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8178: 0x02 cols@8179: 2col 0[2] @8180: 1
col 1[4] @8183: EEEEBBED> set dba 4,563 DBA 0x01000233 (16777779 4,563)
BBED> p *kdbr[0]
rowdata[0] ---------- ub1 rowdata[0] @8177 0x2cBBED> x /rncn
rowdata[0] @8177 ---------- flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8178: 0x00 cols@8179: 2col 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 0x2cBBED> x /rncn
rowdata[0] @8177 ---------- flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8178: 0x02 cols@8179: 2col 0[2] @8180: 1
col 1[4] @8183: EEEEBBED> set dba 4,563 DBA 0x01000233 (16777779 4,563)
BBED> p *kdbr[0]
rowdata[0] ---------- ub1 rowdata[0] @8177 0x2cBBED> x /rncn
rowdata[0] @8177 ---------- flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8178: 0x00 cols@8179: 2col 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更加合适一些.纯属个人理解,也许不对.
转载地址：http://kgwza.baihongyu.com/