ITPub博客

首页 > 数据库 > Oracle > [20150913]文件检查点-表空间offline.txt

[20150913]文件检查点-表空间offline.txt

原创 Oracle 作者:lfree 时间:2015-09-14 09:22:26 0 删除 编辑

[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更加合适一些.纯属个人理解,也许不对.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1798792/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292633