[20150414]只读表空间与没有提交事务.txt

[20150414]只读表空间与没有提交事务.txt

--如果1个表空间在设置只读前,存在没有提交的事务,会出现什么情况呢?自己做1个测试:

1.建立测试环境:
SCOTT@test> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

CREATE TABLESPACE MSSM DATAFILE
  '/mnt/ramdisk/test/mssm01.dbf' SIZE 16M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

create table scott.deptx tablespace mssm as selct * from scott.dept ;

RMAN> report schema ;
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    510      SYSTEM               ***     /mnt/ramdisk/test/system01.dbf
2    350      UNDOTBS1             ***     /mnt/ramdisk/test/undotbs01.dbf
3    370      SYSAUX               ***     /mnt/ramdisk/test/sysaux01.dbf
4    100      USERS                ***     /mnt/ramdisk/test/users01.dbf
5    100      EXAMPLE              ***     /mnt/ramdisk/test/example01.dbf
6    16       MSSM                 ***     /mnt/ramdisk/test/mssm01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /mnt/ramdisk/test/temp01.dbf

2.开始测试:
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688070213           10 ACCOUNTING     new york
AAAO1FAAGAAAAAKAAB  12688070213           20 RESEARCH       dallas
AAAO1FAAGAAAAAKAAC  12688070213           30 SALES          chicago
AAAO1FAAGAAAAAKAAD  12688070213           40 OPERATIONS     boston

SCOTT@test> @ &r/lookup_rowid AAAO1FAAGAAAAAKAAA
      OBJECT         FILE        BLOCK          ROW DBA                  TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
       60741            6           10            0 6,10                 alter system dump datafile 6 block 10 ;

SCOTT@test> update deptx set loc=upper(loc) ;
4 rows updated.

--打开另外会话执行.
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> alter system flush buffer_cache ;
System altered.

SYS@test> @ &r/bh 6 10
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
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006C6CA000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006C6E8000 DEPTX

SYS@test> alter tablespace mssm read only ;
--挂起,也就是有事务没有提交的情况下,无法设置read only.

SYS@test> select p1,p2,p1raw,p2raw,sid,serial#,seq#,event,state,seconds_in_wait from v$session where wait_class'Idle' order by event ;
          P1           P2 P1RAW            P2RAW                     SID      SERIAL#         SEQ# EVENT                                    STATE               SECONDS_IN_WAIT
------------ ------------ ---------------- ---------------- ------------ ------------ ------------ ---------------------------------------- ------------------- ---------------
  1650815232            1 0000000062657100 0000000000000001          140            3           47 SQL*Net message to client                WAITED SHORT TIME                 0
  1415053316       655383 0000000054580004 00000000000A0017          145           21          145 enq: TX - contention                     WAITING                         604

SYS@test> @ &r/ev_name 'enq: TX - contention'
      EVENT#     EVENT_ID NAME                                     PARAMETER1           PARAMETER2           PARAMETER3           WAIT_CLASS_ID  WAIT_CLASS# WAIT_CLASS
------------ ------------ ---------------------------------------- -------------------- -------------------- -------------------- ------------- ------------ --------------------
         580   1629782133 enq: TX - contention                     name|mode            usn

SYS@test> @ &r/tx 655383
old   1: select trunc(&&1/65536) XIDUSN,mod(&&1,65536)  XIDSLOT from dual
new   1: select trunc(655383/65536) XIDUSN,mod(655383,65536)  XIDSLOT from dual
      XIDUSN      XIDSLOT
------------ ------------
          10           23

SYS@test> @ &r/xid

X
------------------------------

XIDUSN      XIDSLOT       XIDSQN       UBAFIL       UBABLK       UBAREC       UBASQN STATUS              USED_UBLK    USED_UREC XID              ADDR             START_DATE
------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- -------------------
      9           20          796            2         3516            7         1146 ACTIVE                      1            1 090014001C030000 000000007A6F5888 2015-04-14 08:05:56
     10           23         8259            2          216           11         3917 ACTIVE                      1            1 0A00170043200000 000000007A702768 2015-04-14 08:04:53
--正好对上.

3.设置成offline看看是否可行.
--在offline前,执行如下:
SYS@test> select count(*) from x$bh where class in (3, 5);
    COUNT(*)
------------
           0

SYS@test> alter tablespace mssm offline ;
Tablespace altered.

--即使存在没有提交的事务,表空间也可以offline.
SYS@test> select count(*) from x$bh where class in (3, 5);
    COUNT(*)
------------
           2

--存在class=3,5的缓存块2个.
SYS@test> @&r/pt 'select * from x$bh where class in (3, 5)'
old  10:              passing xmltype(cursor( &1 ))
new  10:              passing xmltype(cursor( select * from x$bh where class in (3, 5) ))
     ROW_NUM COL_NAME         COL_VALUE
------------ ---------------- ----------------------------------------------------------------------------------------------------
           1 ADDR             00007FF6613934A8
             INDX             2016
             INST_ID          1
             HLADDR           000000007B74F578
             BLSIZ            8192
             NXT_HASH         000000007B74F810
             PRV_HASH         000000007B74F810
             NXT_REPL         000000006D7E8418
             PRV_REPL         000000006DFE2258
             FLAG             33562625
             RFLAG            0
             SFLAG            0
             LRU_FLAG         0
             TS_x0023_        0
             FILE_x0023_      1
             DBARFIL          1
             DBABLK           63218
             CLASS            3
             STATE            1
             MODE_HELD        0
             CHANGES          1
             CSTATE           0
             LE_ADDR          00
             DIRTY_QUEUE      0
             SET_DS           000000007B03E070
             OBJ              4294967295
             BA               000000006DCB6000
             CR_SCN_BAS       0
             CR_SCN_WRP       0
             CR_XID_USN       0
             CR_XID_SLT       0
             CR_XID_SQN       0
             CR_UBA_FIL       0
             CR_UBA_BLK       0
             CR_UBA_SEQ       0
             CR_UBA_REC       0
             CR_SFL           0
             CR_CLS_BAS       0
             CR_CLS_WRP       0
             LRBA_SEQ         107
             LRBA_BNO         64713
             HSCN_BAS         4098191743
             HSCN_WRP         2
             HSUB_SCN         1
             US_NXT           000000006DFE2128
             US_PRV           000000006DFE2128
             WA_NXT           000000006DFE2138
             WA_PRV           000000006DFE2138
             OBJ_FLAG         2
             TCH              1
             TIM              1428971234
Press Enter to view next row...

     ROW_NUM COL_NAME         COL_VALUE
------------ ---------------- ----------------------------------------------------------------------------------------------------
           2 ADDR             00007FF6613934A8
             INDX             3819
             INST_ID          1
             HLADDR           000000007B7D7730
             BLSIZ            8192
             NXT_HASH         000000007B7D7858
             PRV_HASH         000000007B7D7858
             NXT_REPL         000000006C7EB4F8
             PRV_REPL         000000006C7EB2D8
             FLAG             33562625
             RFLAG            0
             SFLAG            0
             LRU_FLAG         0
             TS_x0023_        0
             FILE_x0023_      1
             DBARFIL          1
             DBABLK           63217
             CLASS            5
             STATE            1
             MODE_HELD        0
             CHANGES          1
             CSTATE           0
             LE_ADDR          00
             DIRTY_QUEUE      0
             SET_DS           000000007B03D068
             OBJ              4294967295
             BA               000000006C5CA000
             CR_SCN_BAS       0
             CR_SCN_WRP       0
             CR_XID_USN       0
             CR_XID_SLT       0
             CR_XID_SQN       0
             CR_UBA_FIL       0
             CR_UBA_BLK       0
             CR_UBA_SEQ       0
             CR_UBA_REC       0
             CR_SFL           0
             CR_CLS_BAS       0
             CR_CLS_WRP       0
             LRBA_SEQ         107
             LRBA_BNO         64713
             HSCN_BAS         4098191740
             HSCN_WRP         2
             HSUB_SCN         1
             US_NXT           000000006C7EB3C8
             US_PRV           000000006C7EB3C8
             WA_NXT           000000006C7EB3D8
             WA_PRV           000000006C7EB3D8
             OBJ_FLAG         2
             TCH              1
             TIM              1428971234

102 rows selected.

SYS@test> @ &r/which_obj.sql 1 63217
OWNER  SEGMENT_NAME         PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                   EXTENT_ID      FILE_ID     BLOCK_ID        BYTES       BLOCKS RELATIVE_FNO
------ -------------------- ------------------------------ ------------------ ------------------------------ ------------ ------------ ------------ ------------ ------------ ------------
SYS    1.63217                                             DEFERRED ROLLBACK  SYSTEM                                    0            1        63217        65536            8            1

SYS@test> @ &r/which_obj.sql 1 63218
OWNER  SEGMENT_NAME         PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                   EXTENT_ID      FILE_ID     BLOCK_ID        BYTES       BLOCKS RELATIVE_FNO
------ -------------------- ------------------------------ ------------------ ------------------------------ ------------ ------------ ------------ ------------ ------------ ------------
SYS    1.63217                                             DEFERRED ROLLBACK  SYSTEM                                    0            1        63217        65536            8            1

--将回滚信息移到了system表空间.
--回到会话1:
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
select rowid,ora_rowscn,deptx.* from deptx
                                     *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'

--看看是否能提交.
SCOTT@test> commit ;
Commit complete.
--居然可以.

SYS@test> select count(*) from x$bh where class in (3, 5);
    COUNT(*)
------------
           2

SYS@test> alter system checkpoint ;
System altered.

--因为offline,执行 alter system dump datafile 6 block 10 ;没有输出,只能使用bbed观察.

BBED> set dba 6,10
        DBA             0x0180000a (25165834 6,10)

BBED> p ktbbh.ktbbhitl[0]
struct ktbbhitl[0], 24 bytes                @44
   struct ktbitxid, 8 bytes                 @44
      ub2 kxidusn                           @44       0x000a
      ub2 kxidslt                           @46       0x0016
      ub4 kxidsqn                           @48       0x00001ff2
   struct ktbituba, 8 bytes                 @52
      ub4 kubadba                           @52       0x00800038
      ub2 kubaseq                           @56       0x0ed0
      ub1 kubarec                           @58       0x0f
   ub2 ktbitflg                             @60       0xa000 (KTBFUPB, KTBFCOM)
   union _ktbitun, 2 bytes                  @62
      b2 _ktbitfsc                          @62       2
      ub2 _ktbitwrp                         @62       0x0002
   ub4 ktbitbas                             @64       0xf4449645

BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68
   struct ktbitxid, 8 bytes                 @68
      ub2 kxidusn                           @68       0x000a
      ub2 kxidslt                           @70       0x0017
      ub4 kxidsqn                           @72       0x00002043
   struct ktbituba, 8 bytes                 @76
      ub4 kubadba                           @76       0x008000d8
      ub2 kubaseq                           @80       0x0f4d
      ub1 kubarec                           @82       0x0b
   ub2 ktbitflg                             @84       0x0004 (NONE)
   union _ktbitun, 2 bytes                  @86
      b2 _ktbitfsc                          @86       0
      ub2 _ktbitwrp                         @86       0x0000
   ub4 ktbitbas                             @88       0x00000000

--应该事务发生在ktbbh.ktbbhitl[1]上,kxidusn=0x000a(10),kxidslt=0x0017(23).可以发现并没有些提交的scn.实际上会话1的提交相当于延迟块提交.

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8096     0x2c

BBED> x /rnccc
rowdata[0]                                  @8096
----------
flag@8096: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8097: 0x02
cols@8098:    3

col    0[2] @8099: 10
col   1[10] @8102: ACCOUNTING
col    2[8] @8113: NEW YORK

4.设置表空间read only看看.
--必须先online,在设置成read only.
SYS@test> alter tablespace mssm online  ;
Tablespace altered.

SYS@test> alter tablespace mssm read only ;
Tablespace altered.

SYS@test> alter system dump datafile 6 block 10 ;
System altered.

Block header dump:  0x0180000a
Object id on Block? Y
seg/obj: 0xed45  csc: 0x02.f44497e6  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.016.00001ff2  0x00800038.0ed0.0f  C-U-    0  scn 0x0002.f4449645
0x02   0x000a.017.00002043  0x008000d8.0f4d.0b  ----    4  fsc 0x0000.00000000

--可以确定这个时候读取会出现链接的情况http://blog.itpub.net/267265/viewspace-1561104/
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688136414           10 ACCOUNTING     NEW YORK
AAAO1FAAGAAAAAKAAB  12688136414           20 RESEARCH       DALLAS
AAAO1FAAGAAAAAKAAC  12688136414           30 SALES          CHICAGO
AAAO1FAAGAAAAAKAAD  12688136414           40 OPERATIONS     BOSTON

SYS@test> select count(*) from x$bh where class in (3, 5);
    COUNT(*)
------------
           0

SYS@test> @ &r/bh 6 10
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
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8            6           10            1 data block         cr                    3   4294967295        65535            0            0            0 0000000064ECC000 DEPTX
000000007B6FDFD8            6           10            1 data block         cr                    1   4294967295        65535            0            0            0 0000000064F7E000 DEPTX
000000007B6FDFD8            6           10            1 data block         cr                    1   4294967295        65535            0            0            0 0000000064CF0000 DEPTX
000000007B6FDFD8            6           10            1 data block         cr                    1   4294967295        65535            0            0            0 0000000064E46000 DEPTX
000000007B6FDFD8            6           10            1 data block         cr                    1   4294967295        65535            0            0            0 00000000652F4000 DEPTX

--可以发现state=CR.关闭数据库看看.

SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;

ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688136477           10 ACCOUNTING     NEW YORK
AAAO1FAAGAAAAAKAAB  12688136477           20 RESEARCH       DALLAS
AAAO1FAAGAAAAAKAAC  12688136477           30 SALES          CHICAGO
AAAO1FAAGAAAAAKAAD  12688136477           40 OPERATIONS     BOSTON

--ORA_ROWSCN 会变.视乎这个的ORA_ROWSCN来之哪里呢?

SCOTT@test> alter tablespace mssm read write ;
Tablespace altered.

SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688136483           10 ACCOUNTING     NEW YORK
AAAO1FAAGAAAAAKAAB  12688136483           20 RESEARCH       DALLAS
AAAO1FAAGAAAAAKAAC  12688136483           30 SALES          CHICAGO
AAAO1FAAGAAAAAKAAD  12688136483           40 OPERATIONS     BOSTON

BBED> set dba 6,10
        DBA             0x0180000a (25165834 6,10)

BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68
   struct ktbitxid, 8 bytes                 @68
      ub2 kxidusn                           @68       0x000a
      ub2 kxidslt                           @70       0x0017
      ub4 kxidsqn                           @72       0x00002043
   struct ktbituba, 8 bytes                 @76
      ub4 kubadba                           @76       0x008000d8
      ub2 kubaseq                           @80       0x0f4d
      ub1 kubarec                           @82       0x0b
   ub2 ktbitflg                             @84       0xa000 (KTBFUPB, KTBFCOM)
   union _ktbitun, 2 bytes                  @86
      b2 _ktbitfsc                          @86       2
      ub2 _ktbitwrp                         @86       0x0002
   ub4 ktbitbas                             @88       0xf4459923

SCOTT@test> @&r/16to10 f4459923
16 to 10 DEC
------------
  4098201891

SCOTT@test> select 2*power(2,32)+4098201891 from dual ;
2*POWER(2,32)+4098201891
------------------------
             12688136483

--正好对上.

5.重复测试,不过这次rollback看看情况如何.

--session 1:

SCOTT@test> update deptx set loc=lower(loc) ;
4 rows updated.

--修改为小写.
SYS@test> alter tablespace mssm offline ;
Tablespace altered.

SCOTT@test> @ &r/xid
X
------------------------------
8.45.697

      XIDUSN      XIDSLOT       XIDSQN       UBAFIL       UBABLK       UBAREC       UBASQN STATUS              USED_UBLK    USED_UREC XID              ADDR             START_DATE
------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- -------------------
           8           45          697            2          759           23         1212 ACTIVE                      1            1 08002D00B9020000 000000007A702768 2015-04-14 08:57:29

SYS@test> select count(*) from x$bh where class in (3, 5);

    COUNT(*)
------------
           2

SYS@test> select DBARFIL ,DBABLK   from x$bh where class in (3, 5);
     DBARFIL       DBABLK
------------ ------------
           1        63218
           1        63217

SYS@test> @ &r/bh 1 63217
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
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B7D7730            1        63217            5 save undo header   xcur                  1            0            0            0            0            0 000000006AEF6000

SYS@test> @ &r/bh 1 63218
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
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B74F578            1        63218            3 save undo block    xcur                  1            0            0            0            0            0 000000006AF58000

--回到会话1:
SCOTT@test> rollback ;
Rollback complete.

BBED> set dba 6,10
        DBA             0x0180000a (25165834 6,10)

BBED> p ktbbh.ktbbhitl[0]
struct ktbbhitl[0], 24 bytes                @44
   struct ktbitxid, 8 bytes                 @44
      ub2 kxidusn                           @44       0x0008
      ub2 kxidslt                           @46       0x002d
      ub4 kxidsqn                           @48       0x000002b9
   struct ktbituba, 8 bytes                 @52
      ub4 kubadba                           @52       0x008002f7
      ub2 kubaseq                           @56       0x04bc
      ub1 kubarec                           @58       0x17
   ub2 ktbitflg                             @60       0x0004 (NONE)
   union _ktbitun, 2 bytes                  @62
      b2 _ktbitfsc                          @62       0
      ub2 _ktbitwrp                         @62       0x0000
   ub4 ktbitbas                             @64       0x00000000

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8096     0x2c

BBED> x /rnccc
rowdata[0]                                  @8096
----------
flag@8096: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8097: 0x01
cols@8098:    3

col    0[2] @8099: 10
col   1[10] @8102: ACCOUNTING
col    2[8] @8113: new york

--可以发现磁盘里面的内容loc字段已经是小写.(注意我没有提交事务).

SYS@test> select count(*) from x$bh where class in (3, 5);
    COUNT(*)
------------
           2

alter tablespace mssm online ;
alter tablespace mssm read only ;

BBED> set dba 6,10
        DBA             0x0180000a (25165834 6,10)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8096     0x2c

BBED> p ktbbh.ktbbhitl[0]
struct ktbbhitl[0], 24 bytes                @44
   struct ktbitxid, 8 bytes                 @44
      ub2 kxidusn                           @44       0x000a
      ub2 kxidslt                           @46       0x0016
      ub4 kxidsqn                           @48       0x00001ff2
   struct ktbituba, 8 bytes                 @52
      ub4 kubadba                           @52       0x00800038
      ub2 kubaseq                           @56       0x0ed0
      ub1 kubarec                           @58       0x0f
   ub2 ktbitflg                             @60       0xa000 (KTBFUPB, KTBFCOM)
   union _ktbitun, 2 bytes                  @62
      b2 _ktbitfsc                          @62       2
      ub2 _ktbitwrp                         @62       0x0002
   ub4 ktbitbas                             @64       0xf4449645

BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68
   struct ktbitxid, 8 bytes                 @68
      ub2 kxidusn                           @68       0x000a
      ub2 kxidslt                           @70       0x0017
      ub4 kxidsqn                           @72       0x00002043
   struct ktbituba, 8 bytes                 @76
      ub4 kubadba                           @76       0x008000d8
      ub2 kubaseq                           @80       0x0f4d
      ub1 kubarec                           @82       0x0b
   ub2 ktbitflg                             @84       0xa000 (KTBFUPB, KTBFCOM)
   union _ktbitun, 2 bytes                  @86
      b2 _ktbitfsc                          @86       2
      ub2 _ktbitwrp                         @86       0x0002
   ub4 ktbitbas                             @88       0xf4459923

BBED> x /rnccc
rowdata[0]                                  @8096
----------
flag@8096: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8097: 0x00
cols@8098:    3

col    0[2] @8099: 10
col   1[10] @8102: ACCOUNTING
col    2[8] @8113: NEW YORK
--可以发现在设置成only是已经应用rollback.

SYS@test> select count(*) from x$bh where class in (3, 5);
    COUNT(*)
------------
           1

SYS@test> select count(*) from x$bh where class in (3, 5);
    COUNT(*)
------------
           1

SYS@test> select DBARFIL ,DBABLK   from x$bh where class in (3, 5);
     DBARFIL       DBABLK
------------ ------------
           1        63218

SYS@test> @ &r/bh   1        63218
old  23:   dbarfil = &1 and
new  23:   dbarfil = 1 and
old  24:   dbablk = &2
new  24:   dbablk = 63218
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
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B74F578            1        63218            3 save undo block    free                  0            0            0            0            0            0 000000006AF58000

--注意状态state=free.

SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688136483           10 ACCOUNTING     NEW YORK
AAAO1FAAGAAAAAKAAB  12688136483           20 RESEARCH       DALLAS
AAAO1FAAGAAAAAKAAC  12688136483           30 SALES          CHICAGO
AAAO1FAAGAAAAAKAAD  12688136483           40 OPERATIONS     BOSTON

SCOTT@test> @ &r/16to10 f4449645
16 to 10 DEC
------------
  4098135621

SCOTT@test> select power(2,33)+4098135621 from dual ;
POWER(2,33)+4098135621
----------------------
           12688070213
--奇怪不一致?

SYS@test> @&r/bh 6 10
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
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8            6           10            1 data block         xcur                  2            0            0            0            0            0 000000006A1E6000 DEPTX

--状态是xcur.

SCOTT@test> alter system dump datafile 6 block 10 ;
System altered.

Block header dump:  0x0180000a
Object id on Block? Y
seg/obj: 0xed45  csc: 0x02.f4459b72  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.016.00001ff2  0x00800038.0ed0.0f  C-U-    0  scn 0x0002.f4449645
0x02   0x000a.017.00002043  0x008000d8.0f4d.0b  C-U-    0  scn 0x0002.f4459923

--与这个0x0002.f4459923是对上的.

时间： 2024-09-21 17:33:31

[20150414]只读表空间与没有提交事务.txt的相关文章

[20150408]只读表空间以及数据库恢复2.txt

[20150408]只读表空间以及数据库恢复2.txt --昨天检查时发现1个小问题,就是有1个表空间设置只读,也许某个时间打开变成读写,又设置会只读,而备份仅仅在第1次设置只读时做过1次, --按照道理如果使用原来的备份恢复会存在问题的,自己测试1下,顺便看看有什么变通的方法解决这个问题. 1.建立测试环境: SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER ----------------

[20150408]只读表空间以及数据库恢复4.txt

[20150408]只读表空间以及数据库恢复4.txt 参考链接: http://blog.itpub.net/267265/viewspace-1544583/ http://blog.itpub.net/267265/viewspace-1548059/ http://blog.itpub.net/267265/viewspace-1548967/ --上午做了测试,通过新建控制文件的方式来恢复,实际上更常用的方式使用bbed,修改数据文件块1,保持与控制文件的记录一致. --通过对比来看看

[20150408]只读表空间以及数据库恢复3.txt

[20150408]只读表空间以及数据库恢复3.txt --昨天检查时发现1个小问题,就是有1个表空间设置只读,也许某个时间打开变成读写,又设置会只读,而备份仅仅在第1次设置只读时做过1次, --按照道理如果使用原来的备份恢复会存在问题的,自己测试1下,我上午已经测试了可以使用以前的做了来恢复,但是这里存在一些小问题, --我在第2次打开读写时,没有任何ddl,dml操作对这个表空间,如果存在这些操作会出现什么情况呢? --继续上午的测试. 1.建立测试环境: SCOTT@test> @ &

[20150409]只读表空间与延迟块清除.txt

[20150409]只读表空间与延迟块清除.txt --昨天测试只读表空间的数据库恢复问题,突然想到一种情况,如果只读表空间存在延迟块清除情况,这样在下次访问是会更新块的信息吗? --自己还是做1个测试: 1.首先在测试前,说明1点,设置表空间只读,仅仅阻止dml操作,并不能阻止ddl操作,ddl操作的是数据字典. SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER --------------

[20170623]利用传输表空间恢复部分数据.txt

[20170623]利用传输表空间恢复部分数据.txt --//昨天我测试使用传输表空间+dblink,上午补充测试发现表空间设置只读才能执行impdp导入原数据,这个也很好理解. --//这样的操作模式仅仅减少expdp生成原数据的过程. --//我想一下,rman也支持建立传输表空间的命令.我仔细看了以前的笔记,发现这样最大的有点不用设置只读,实际上它是通过建立辅组实 --//例来建立传输文件,理论讲可以恢复到特定的scn,这样可以利用它解决一些误操作的问题,还是通过例子来说明问题. 1.环

Oracle中如何设置EXCLUDE后STANDBY数据库只读表空间的恢复

在STANDBY数据库利用RMAN恢复主库上EXCLUDE的只读表空间,碰到了问题. 数据库恢复完成,但是恢复被主库EXCLUDE的只读表空间时,发现无法进行恢复: RMAN> restore tablespace clubstat2_bak; Starting restore at 14-FEB-11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=1094 devtype=DISK file 22 is excluded from

备份和恢复Oracle只读表空间的方法

--====================== -- 只读表空间的备份与恢复 --====================== 一.只读表空间的特性 使用只读表空间避免对静态数据的频繁备份 当使用alter tablespace tbs read only时,数据文件会执行检查点进程(将所有脏缓冲区的内容写至磁盘), 当前的SCN号会被标注,同时存储了SCN的数据文件头部被冻结.控制文件内也会记录该数据文件的冻结信息. 可以清除只读表空间的对象 二.只读表空间的备份 一般情况下,只读表空间只需

[20170623]利用传输表空间恢复数据库2.txt

[20170623]利用传输表空间恢复数据库2.txt --//继续上午的测试,测试truncate,是否可行,理论讲应该没有问题.我主要的目的测试是否要切换日志. --//参考链接 : http://blog.itpub.net/267265/viewspace-2141166/ 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER -------------------------

[20150113]系统管理表空间的疑问2.txt

[20150113]系统管理表空间的疑问2.txt --昨天探究系统管理表空间位图区分布的问题. --自己得到一些结论: http://blog.itpub.net/267265/viewspace-1399275/ 总结: 1.使用系统管理表空间,位图区不仅仅在块开始的2-8块(10g).11g没有问题,因为11g数据文件前面128块保留. 2.位图区除了位图信息,还有其他一些信息. 3.如果前面的位图区不够满足需要,从block=2的tail+1作为位图区 4.如果数据文件改变大小,如果尾部