Oracle – 第19页 – Database SOS

利用bbed找回ORACLE更新前值

模拟数据块更新

SQL> create table t_xifenfei(id number,name varchar2(10));
Table created.
SQL> insert into t_xifenfei values(1,'XFF');
1 row created.
SQL> insert into t_xifenfei values(2,'CHF');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select id,rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
  4  dbms_rowid.rowid_row_number(rowid) rowno
  5  from t_xifenfei;
        ID ROWID                 REL_FNO    BLOCKNO      ROWNO
---------- ------------------ ---------- ---------- ----------
         1 AAASc+AAEAAAACvAAA          4        175          0
         2 AAASc+AAEAAAACvAAB          4        175          1
SQL> select dump(1,'16') from dual;
DUMP(1,'16')
-----------------
Typ=2 Len=2: c1,2
SQL> select dump(2,'16') from dual;
DUMP(2,'16')
-----------------
Typ=2 Len=2: c1,3
SQL> select dump('XFF','16') FROM DUAL;
DUMP('XFF','16')
----------------------
Typ=96 Len=3: 58,46,46
SQL> SELECT DUMP('CHF','16') FROM DUAL;
DUMP('CHF','16')
----------------------
Typ=96 Len=3: 43,48,46
SQL> update t_xifenfei set name='XIFENFEI' where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select dump('XIFENFEI','16') from dual;
DUMP('XIFENFEI','16')
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49
SQL> alter system checkpoint;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- ----------
         1 XIFENFEI
         2 CHF

这里我们对数据库进行了一次更新操作,并且dump出来对应值,为了方便定位到相应记录

bbed查看相关值

[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Wed Aug 8 20:50:47 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 4 block 175
        FILE#           4
        BLOCK#          175
BBED> map
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175                                   Dba:0x010000af
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[2]                                @118
 ub1 freespace[8031]                        @122
 ub1 rowdata[35]                            @8153
 ub4 tailchk                                @8188
BBED> p kdbr
sb2 kdbr[0]                                 @118      8053
sb2 kdbr[1]                                 @120      8068
BBED> p *kdbr[1]
rowdata[15]
-----------
ub1 rowdata[15]                             @8168     0x2c
BBED> x /rnc
rowdata[15]                                 @8168
-----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170:    2
col    0[2] @8171: 2
col    1[3] @8174: CHF
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8153     0x2c
BBED> x /rnc
rowdata[0]                                  @8153
----------
flag@8153: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8154: 0x02
cols@8155:    2
col    0[2] @8156: 1
col    1[8] @8159: XIFENFEI
BBED> set count 64
        COUNT           64
 <32 bytes per line>
BBED> d /v
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175     Offsets: 8153 to 8191  Dba:0x010000af
-------------------------------------------------------
 2c020202 c1020858 4946454e 4645492c l ,......XIFENFEI,
 000202c1 03034348 462c0002 02c10203 l ......CHF,......
 58464602 068de8                     l XFF....
 <16 bytes per line>

使用bbed找回历史值

--准备工作,通过dump出来的值,推算出来第一条记录的起点02c10203584646,
--在这个值的基础上offset-3得到offset值为8078
BBED> p kdbr
sb2 kdbr[0]                                 @118      8053
sb2 kdbr[1]                                 @120      8068
--修改row directory指针位置
BBED> m /x 8e1f
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:  118 to  181           Dba:0x010000af
------------------------------------------------------------------------
 8e1f841f 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> p kdbr
sb2 kdbr[0]                                 @118      8078
sb2 kdbr[1]                                 @120      8068
BBED> sum apply
Check value for File 4, Block 175:
current = 0xdff8, required = 0xdff8
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: xaction header lock count mismatch
        trans=2 ilk=1 nlo=0     --提示事务锁错误
Block 175 failed with check code 6108
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> p *kdbr[0]
rowdata[25]
-----------
ub1 rowdata[25]                             @8178     0x2c
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8178 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 2c000202 c1020358 46460206 8de8
 <32 bytes per line>
BBED> x /rnc
rowdata[25]                                 @8178
-----------
flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8179: 0x00  --被更新前的记录事务锁标识为0,而更新后的事务锁标识为2
cols@8180:    2
col    0[2] @8181: 1
col    1[3] @8184: XFF
--修改事务锁标识为2
BBED> m /x 02 offset 8179
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8179 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 020202c1 02035846 4602068d e8
 <32 bytes per line>
BBED> set offset 8153
        OFFSET          8153
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8153 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 2c020202 c1020858 4946454e 4645492c 000202c1 03034348 462c0202 02c10203
 58464602 068de8
 <32 bytes per line>
--把更新后值的事务锁标识改为0
BBED> m /x 00 offset +1
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8154 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 000202c1 02085849 46454e46 45492c00 0202c103 03434846 2c020202 c1020358
 46460206 8de8
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddfa, required = 0xddfa
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: the amount of space used is not equal to block size
        used=42 fsc=0 avsp=8041 dtl=8088   -->提示块的空间使用不正确
Block 175 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> p ktbbh
struct ktbbh, 72 bytes                      @20
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24
      ub4 ktbbhsg1                          @24       0x0001273e
      ub4 ktbbhod1                          @24       0x0001273e
   struct ktbbhcsc, 8 bytes                 @28
      ub4 kscnbas                           @28       0x0000e88a
      ub2 kscnwrp                           @32       0x0002
   sb2 ktbbhict                             @36       2
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x010000a8
   struct ktbbhitl[0], 24 bytes             @44
      struct ktbitxid, 8 bytes              @44
         ub2 kxidusn                        @44       0x0006
         ub2 kxidslt                        @46       0x001e
         ub4 kxidsqn                        @48       0x000002c6
      struct ktbituba, 8 bytes              @52
         ub4 kubadba                        @52       0x00c000d9
         ub2 kubaseq                        @56       0x0086
         ub1 kubarec                        @58       0x2a
      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @62
         sb2 _ktbitfsc                      @62       2
         ub2 _ktbitwrp                      @62       0x0002
      ub4 ktbitbas                          @64       0x0000e550
   struct ktbbhitl[1], 24 bytes             @68
      struct ktbitxid, 8 bytes              @68
         ub2 kxidusn                        @68       0x0006
         ub2 kxidslt                        @70       0x0008
         ub4 kxidsqn                        @72       0x000002c7
      struct ktbituba, 8 bytes              @76
         ub4 kubadba                        @76       0x00c000da
         ub2 kubaseq                        @80       0x0086
         ub1 kubarec                        @82       0x12
      ub2 ktbitflg                          @84       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @86
         sb2 _ktbitfsc                      @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x0000e88d
--所有的_ktbitfsc修改为0
BBED> m /x 00 offset 62
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:   62 to  125           Dba:0x010000af
------------------------------------------------------------------------
 000050e5 00000600 0800c702 0000da00 c0008600 12000120 00008de8 00000000
 00000000 00000001 0200ffff 1600751f 691f691f 00000200 8e1f841f 00000000
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddf8, required = 0xddf8
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: the amount of space used is not equal to block size
        used=42 fsc=0 avsp=8041 dtl=8088
Block 175 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> p kdbh
struct kdbh, 14 bytes                       @100
   ub1 kdbhflag                             @100      0x00 (NONE)
   sb1 kdbhntab                             @101      1
   sb2 kdbhnrow                             @102      2
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      22
   sb2 kdbhfseo                             @108      8053
   sb2 kdbhavsp                             @110      8045
   sb2 kdbhtosp                             @112      8045
--修改kdbhtosp和kdbhavsp值
BBED>  m /x 6e1f  offset 112
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:  112 to  175           Dba:0x010000af
------------------------------------------------------------------------
 6e1f0000 02008e1f 841f0000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x  6e1f  offset 110
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:  110 to  173           Dba:0x010000af
------------------------------------------------------------------------
 6e1f6e1f 00000200 8e1f841f 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddf8, required = 0xddf8
--数据块验证通过
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

重启数据库

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             251661172 bytes
Database Buffers           54525952 bytes
Redo Buffers                6328320 bytes
Database mounted.
Database opened.
--找回更新前值
SQL> select * from chf.t_xifenfei;
        ID NAME
---------- ----------
         1 XFF
         2 CHF

ORACLE update 操作内部原理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE update 操作内部原理

对于oracle的update操作,在数据块中具体是如何出来,是直接更新原来值,还是通过插入新值修改指针的方法实现.下面通过证明:
模拟表插入数据

SQL> create table t_xifenfei(id number,name varchar2(10));
Table created.
SQL> insert into t_xifenfei values(1,'XFF');
1 row created.
SQL> insert into t_xifenfei values(2,'CHF');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select id,rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
  4  dbms_rowid.rowid_row_number(rowid) rowno
  5  from t_xifenfei;
        ID ROWID                 REL_FNO    BLOCKNO      ROWNO
---------- ------------------ ---------- ---------- ----------
         1 AAASc+AAEAAAACvAAA          4        175          0
         2 AAASc+AAEAAAACvAAB          4        175          1
SQL> alter system dump datafile 4 block 175;
System altered.
SQL>  select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_24625.trc

数据存储对应16进制值

SQL> select dump(1,'16') from dual;
DUMP(1,'16')
-----------------
Typ=2 Len=2: c1,2
SQL> select dump(2,'16') from dual;
DUMP(2,'16')
-----------------
Typ=2 Len=2: c1,3
SQL> select dump('XFF','16') FROM DUAL;
DUMP('XFF','16')
----------------------
Typ=96 Len=3: 58,46,46
SQL> SELECT DUMP('CHF','16') FROM DUAL;
DUMP('CHF','16')
----------------------
Typ=96 Len=3: 43,48,46

得出第一条记录对应值为:02c10203584646;第二条记录对应值为:02c10303434846

dump 数据块得到记录

bdba: 0x010000af
data_block_dump,data header at 0xb683c064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0xb683c064
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f84
avsp=0x1f6e
tosp=0x1f6e
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f8e    ---->8078
0x14:pri[1]     offs=0x1f84    ---->8068
block_row_dump:
tab 0, row 0, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 3]  58 46 46
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 3]  43 48 46
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175

bbed查看相关记录

BBED> p kdbr
sb2 kdbr[0]                                 @118      8078 <--第一条row directory指针位置
sb2 kdbr[1]                                 @120      8068 <--第二条row directory指针位置
BBED> p *kdbr[0]
rowdata[10]
-----------
ub1 rowdata[10]                             @8178     0x2c
BBED> x /rnc
rowdata[10]                                 @8178
-----------
flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8179: 0x01
cols@8180:    2
col    0[2] @8181: 1
col    1[3] @8184: XFF
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8168     0x2c
BBED> x /rnc
rowdata[0]                                  @8168
----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x01
cols@8170:    2
col    0[2] @8171: 2
col    1[3] @8174: CHF
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8168 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 2c010202 c1030343 48462c01 0202c102 03584646 010650e5
 <32 bytes per line>

这里可以得到结论如下:
1.数据是从块的底部开始往上存储
2.在每一条记录的头部分别有flag/lock/cols对应这里的2c0102
3.这里的偏移量和dump出来的数据可以看出来两条记录是连续在一起(偏移量分别为:8168和8178)

更新一条记录

SQL> update t_xifenfei set name='XIFENFEI' where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 175;
System altered.
SQL> select dump('XIFENFEI','16') from dual;
DUMP('XIFENFEI','16')
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49

我们可以但看到值有XFF改变为XIFENFEI,存储长度变大

dump数据块信息

bdba: 0x010000af
data_block_dump,data header at 0xb683c064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0xb683c064
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f75
avsp=0x1f69
tosp=0x1f69
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f75    ---->8053
0x14:pri[1]     offs=0x1f84    ---->8068
block_row_dump:
tab 0, row 0, @0x1f75
tl: 15 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 02
col  1: [ 8]  58 49 46 45 4e 46 45 49
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 03
col  1: [ 3]  43 48 46
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175

通过对比第一次dump出来的数据块发现:row 0的值和偏移量发生了变化

bbed查看相关记录

BBED> set file 4 block 175
        FILE#           4
        BLOCK#          175
BBED> map
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175                                   Dba:0x010000af
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[2]                                @118
 ub1 freespace[8031]                        @122
 ub1 rowdata[35]                            @8153
 ub4 tailchk                                @8188
BBED> p kdbr
sb2 kdbr[0]                                 @118      8053   <--第一条row directory指针位置
sb2 kdbr[1]                                 @120      8068   <--第二条row directory指针位置
BBED> p *kdbr[1]
rowdata[15]
-----------
ub1 rowdata[15]                             @8168     0x2c
BBED> x /rnc
rowdata[15]                                 @8168
-----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170:    2
col    0[2] @8171: 2
col    1[3] @8174: CHF
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8153     0x2c
BBED> x /r
rowdata[0]                                  @8153
----------
flag@8153: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8154: 0x02
cols@8155:    2
col    0[2] @8156:  0xc1  0x02
col    1[8] @8159:  0x58  0x49  0x46  0x45  0x4e  0x46  0x45  0x49
BBED> set count 64
        COUNT           64
 <32 bytes per line>
BBED> d /v
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175     Offsets: 8153 to 8191  Dba:0x010000af
-------------------------------------------------------
 2c020202 c1020858 4946454e 4645492c l ,......XIFENFEI,
 000202c1 03034348 462c0002 02c10203 l ......CHF,......
 58464602 068de8                     l XFF....
 <16 bytes per line>

从这里可以看到
1.这里可以看到三个值(XFF,CHF,XIFENFEI)均存在,但是通过p kdbr和dump block不能看到,因为row directory中无指针指定到该值上
2.也是通过row directory指针使得我们从原先看到的第一条记录处于数据块最底部变成了现在相对而言的数据部分最上层,
3.绝大多数情况:数据库更新一条记录,不是直接修改数据值,而是重新插入一条新记录,然后修改row directory指针指定到新的offset上
4.不是直接update,而是insert+指针来实现,这样做的好处:1)如果修改记录update值的长度发生变化(变大或者变小)那么该值之前的数据都要发生变动,对数据库来说成本太高.2)如果直接更新值可能导致其他数据变动,使得其他行受到影响.
5.由于是修改row directory指针,所以该处理方法的rowid值不会发生变化

v$datafile.enabled相关值说明

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：v$datafile.enabled相关值说明

自认为对v$datafile视图算比较了解,但是今天看到一网友的v$datafile.enabled=DISABLED,我一时没有想出来是为什么,这里通过试验对v$datafile.enabled各种情况进行了试验并简单说明scn的变化情况
模拟环境(READ WRITE)

SQL> alter tablespace users add datafile '/u01/oracle/oradata/XFF/users02.dbf' size 10m;
Tablespace altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ---------------------------------------
         1          0             456727 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458322 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
QL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              456727
         2 ONLINE              456727
         3 ONLINE              456727
         4 ONLINE              456727
         5 ONLINE              458322

加入数据文件scn不一样是因为:加入数据文件的时候,使用是当前scn,而数据库未做checkpoint,所以出现数据文件scn不一致现象

datafile offline(READ WRITE)

SQL> alter database datafile 5 offline;
Database altered.
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458392
         2 ONLINE              458392
         3 ONLINE              458392
         4 ONLINE              458392
         5 OFFLINE             458322
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ----------------------------------------
         1          0             458392 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458322 READ WRITE RECOVER /u01/oracle/oradata/XFF/users02.dbf

这里可以看出来数据文件offline之后,v$datafile.enabled依然是READ WRITE,但是该数据文件的scn不再变化

tablespace offline(DISABLED)

SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------
         1          0             458392 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458430 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458392
         2 ONLINE              458392
         3 ONLINE              458392
         4 ONLINE              458392
         5 ONLINE              458430
SQL> alter system checkpoint;
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ---------------------------------------
         1          0             458443 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458443
         2 ONLINE              458443
         3 ONLINE              458443
         4 ONLINE              458443
         5 ONLINE              458443
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------
         1          0             458497 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458497 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458497 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users01.dbf
         5          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458497
         2 ONLINE              458497
         3 ONLINE              458497
         4 OFFLINE                  0
         5 OFFLINE                  0
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458512 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users01.dbf
         5          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458512
         2 ONLINE              458512
         3 ONLINE              458512
         4 OFFLINE                  0
         5 OFFLINE                  0
SQL> alter tablespace users online;
Tablespace altered.
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458512
         2 ONLINE              458512
         3 ONLINE              458512
         4 ONLINE              458526
         5 ONLINE              458526
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458512 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458526 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458526 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> alter system checkpoint;
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458551 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458551
         2 ONLINE              458551
         3 ONLINE              458551
         4 ONLINE              458551
         5 ONLINE              458551

以上部分证明：
1.online datafile也不触发database checkpoint
2.tablespace offline后v$datafile.enabled为DISABLED
3.控制文件对应表空间scn不再变化,datafile header scn变为0
4.tablespace online不需要recover

tablespace read only(READ ONLY)

SQL> alter tablespace users read only;
Tablespace altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458551 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458551
         2 ONLINE              458551
         3 ONLINE              458551
         4 ONLINE              458567
         5 ONLINE              458567
SQL> alter system checkpoint;
System altered.
SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458581 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458581
         2 ONLINE              458581
         3 ONLINE              458581
         4 ONLINE              458567
         5 ONLINE              458567
SQL> alter tablespace users read write;
Tablespace altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458581 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458635 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458635 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458581
         2 ONLINE              458581
         3 ONLINE              458581
         4 ONLINE              458635
         5 ONLINE              458635
SQL> alter system checkpoint;
System altered.
SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458649 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458649
         2 ONLINE              458649
         3 ONLINE              458649
         4 ONLINE              458649
         5 ONLINE              458649

以上部分证明：
1.tablespace read only 对应的v$datafile.enabled为READ ONLY
2.tablespace read only与tablespace read write之间的转换也不会触发database checkpoint

补充说明
官方文档给出来的v$datafile.enabled有以下几种
DISABLED – No SQL access allowed
READ ONLY – No SQL updates allowed
READ WRITE – Full access allowed
UNKNOWN – should not occur unless the control file is corrupted
但是对于UNKNOWN我通过模拟重建控制文件,缺少数据文件(为READ WRITE),通过在线删除数据文件(为READ WRITE),都不能出现UNKNOWN状态,如果知道的朋友请告知我.

kill oracle session相关描述

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：kill oracle session相关描述

近期看到不少朋友都对kill session相关的参数不太熟悉,下面是摘自Oracle® Database SQL Reference

语法参考

DISCONNECT SESSION Clause

Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process
(or virtual circuit if the connection was made by way of a Shared Sever).
To use this clause, your instance must have the database open.
You must identify the session with both of the following values from the V$SESSION view:
•	For integer1, specify the value of the SID column.
•	For integer2, specify the value of the SERIAL# column.
If system parameters are appropriately configured, then application failover will take effect.
•	The POST_TRANSACTION setting allows ongoing transactions to complete before the session is disconnected.
        If the session has no ongoing transactions, then this clause has the same effect described for as KILL SESSION.
•	The IMMEDIATE setting disconnects the session and recovers the entire session state immediately,
        without waiting for ongoing transactions to complete.
o	If you also specify POST_TRANSACTION and the session has ongoing transactions, then the IMMEDIATE keyword is ignored.
o	If you do not specify POST_TRANSACTION, or you specify POST_TRANSACTION but the session has no ongoing transactions,
        then this clause has the same effect as described for KILL SESSION IMMEDIATE.

KILL SESSION Clause

The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions,
release all session locks, and partially recover session resources.
To use this clause, your instance must have the database open.
Your session and the session to be terminated must be on the same instance unless you specify integer3.
You must identify the session with the following values from the V$SESSION view:
•	For integer1, specify the value of the SID column.
•	For integer2, specify the value of the SERIAL# column.
•	For the optional integer3, specify the ID of the instance where the target session to be killed exists.
        You can find the instance ID by querying the GV$ tables.
If the session is performing some activity that must be completed,
such as waiting for a reply from a remote database or rolling back a transaction,
then Oracle Database waits for this activity to complete, marks the session as terminated,
and then returns control to you.
If the waiting lasts a minute, then Oracle Database marks the session to be terminated
and returns control to you with a message that the session is marked to be terminated.
The PMON background process then marks the session as terminated when the activity is complete.
Whether or not the session has an ongoing transaction, Oracle Database does not recover
the entire session state until the session user issues a request to the session
and receives a message that the session has been terminated.

IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions,
release all session locks, recover the entire session state, and return control to you immediately.

执行案例

--Oracle 级别kill
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
--Linux/Unix kill
kill -9 spid
--Windows kill
orakill ORACLE_SID spid

因非常规操作导致删除表空间提示ORA-01561解决办法

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：因非常规操作导致删除表空间提示ORA-01561解决办法

今天测试手工通过bbed修改undo$中回滚段状态(从status$=5修改为1)[NEEDS RECOVERY 修改为 DELETE],然后尝试删除表空间,发现不能删除
删除表空间提示ORA-01561

SQL> drop tablespace undotbs;
drop tablespace undotbs
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified
SQL> drop tablespace undotbs including contents;
drop tablespace undotbs including contents
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified

查询相关信息

SQL> select ts#,name from v$tablespace;
       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 SYSAUX
         4 USERS
         6 UNDOTBS
         3 TEMP
SQL> select name,ts#,status$ from undo$;
NAME                                  TS#    STATUS$
------------------------------ ---------- ----------
SYSTEM                                  0          3
_SYSSMU1_3138885392$                    2          1
_SYSSMU2_4228238222$                    2          1
_SYSSMU3_2210742642$                    2          1
_SYSSMU4_1455318006$                    2          1
_SYSSMU5_3787622316$                    2          1
_SYSSMU6_2460248069$                    2          1
_SYSSMU7_1924883037$                    2          1
_SYSSMU8_1909280886$                    2          1
_SYSSMU9_3593450615$                    2          1
_SYSSMU10_2490256178$                   2          1
NAME                                  TS#    STATUS$
------------------------------ ---------- ----------
_SYSSMU11_253524401$                    6          1
_SYSSMU12_842775869$                    6          1
_SYSSMU13_2794767139$                   6          1
_SYSSMU14_2067649841$                   6          1
_SYSSMU15_3270221471$                   6          1
_SYSSMU16_4094338609$                   6          1
_SYSSMU17_709661646$                    6          1
_SYSSMU18_699588262$                    6          1
_SYSSMU19_718640828$                    6          1
_SYSSMU20_3516920665$                   6          1
_SYSSMU21_793796797$                    6          1
NAME                                  TS#    STATUS$
------------------------------ ---------- ----------
_SYSSMU22_3988785920$                   6          1
_SYSSMU23_1828333848$                   6          1
_SYSSMU24_1223218862$                   6          1
_SYSSMU25_2939844199$                   6          1
_SYSSMU26_1317300205$                   6          1
_SYSSMU27_1654033223$                   6          1
_SYSSMU28_3748619502$                   6          1
_SYSSMU29_1868765904$                   6          1
_SYSSMU30_3379578723$                   6          1
31 rows selected.
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE

通过这里可以看出,通过bbed的修改,除system回滚段外,其他均已经被标志为delete状态,对于这样的情况,很本能的怀疑是extent或者segment未被清理掉导致

查询EXTENT和SEGMENT

SQL> select SEGMENT_NAME from dba_extents where TABLESPACE_NAME='UNDOTBS';
no rows selected
SQL> select segment_name from dba_segments where  TABLESPACE_NAME='UNDOTBS';
no rows selected
SQL> select count(*) from seg$ where ts#=6;
  COUNT(*)
----------
        10
SQL> select count(*) from seg$ where ts#=2;
  COUNT(*)
----------
         0
SQL> select file#,type# from  seg$ where ts#=6;
     FILE#      TYPE#
---------- ----------
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
10 rows selected.

通过查询我们发现SEG$中含有10条记录,而通过dbms_metadata.get_ddl分析DBA_SEGMENTS是的,得出type为10恰好是TYPE2 UNDO信息.

解决办法
删除掉这些因为手工修改undo$信息导致遗留下来的后遗症对象

SQL> delete from seg$ where ts#=6;
10 rows deleted.
SQL> commit;
Commit complete.
SQL> drop tablespace undotbs ;
Tablespace dropped.

这样的直接修改基表的做法,在一般的情况下非常不建议使用,可能带来系统不稳定.但是在数据库异常处理过程中,可能将成为一个法宝

ALERT_QUE表重建方法

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ALERT_QUE表重建方法

最近关注了下网络上,出现了很多AQ$_ALERT_QT_N的index SYS_IOT_TOP_NNNN坏块引起的数据库异常(主要是SYSAUX表空间),因为他们是IOT表和主键的关系,不能简单的rebuild.查询了一些资料,得到一些信息

ALERT_QUE表用途
The ALERT_QUE is used by the Grid Control and DB Control Management Agents to monitor server-generated alerts

ALERT_QUE表重建方法

--方法1
SQL> connect / as sysdba
SQL>alter system enable restricted session;
To drop server alert schema.
SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql
To re-create tables, sequence, type and queue for server alert
SQL>@$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
SQL>@$ORACLE_HOME/rdbms/admin/catalrt.sql
To recompile the invalid objects
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> alter system disable restricted session;
--方法2
SQL> connect / as sysdba
SQL>alter system enable restricted session;
To drop server alert schema.
SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql
Rerun catproc.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> alter system disable restricted session;

补充说明

By running the script up, the queue tables will be recreated and the messages
in the queue will be lost.
For 11g you can use catmwin.sql which has the steps to recreate the ALERT_QT.
Alternatively, for 11g you can use the catproc.sql to recreate.
If this option may leave DBSNMP.MGMT_BSLN_INTERNAL invalid.
To validate the same run catsnmp.sql [NOTE:603289.1]

Oracle 11g增加列,并带默认值的新特性

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 11g增加列,并带默认值的新特性

在11g以前,如果要在一个大表中增加一列,并设置默认值,那将是一个非常悲剧的事情.有些时候不得不选择在线重定义功能来实现该需求.而在11g中增加新列并设置默认值,只是简单的修改数据字典来实现该功能,大大提供效率
10g加列(默认值)

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table t_xifenfei
  2  as select object_id,object_name from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     49827
SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                          NUMBER
 OBJECT_NAME                                        VARCHAR2(128)
SQL> set timing on
SQL> alter table t_xifenfei add c_xff varchar2(100) default 'www.xifenfei.com' not null;
Table altered.
Elapsed: 00:00:06.13
--使用了6秒钟
SQL> select   rowid,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5  from t_xifenfei where object_name='OBJ$';
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAMwJAAEAAAAB8AAr          4        124         43
SQL> alter system dump datafile 4 block 124;
System altered.
Elapsed: 00:00:00.08

11g增加列(默认值)

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t_xifenfei
  2  as select object_id,object_name from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     74605
SQL> select   rowid,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5  from t_xifenfei where object_name='OBJ$';
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASpRAAEAAAACrAAu          4        171         46
SQL> alter system dump datafile 4 block 171;
System altered.
SQL> set timing on
SQL> alter table t_xifenfei add c_xff varchar2(100) default 'www.xifenfei.com' not null;
Table altered.
Elapsed: 00:00:00.19
--只是使用了0.19秒
SQL> select   rowid,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5  from t_xifenfei where object_name='OBJ$';
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASpRAAEAAAACrAAu          4        171         46
Elapsed: 00:00:00.04
SQL> alter system dump datafile 4 block 171;
System altered.

通过10g和11g的增加相同列和默认值的对比可以发现,11g的速度要比10g快很多很多,下面我们通过上面dump出来相关的数据块来分析原因
dump分析
11g增加列之前dump

tab 0, row 0, @0x1f74
tl: 12 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 15
col  1: [ 5]  49 43 4f 4c 24
tab 0, row 1, @0x1f66
tl: 14 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 2f
col  1: [ 7]  49 5f 55 53 45 52 31
tab 0, row 2, @0x1f5b
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 1d
col  1: [ 4]  43 4f 4e 24

11g增加列之后dump

tab 0, row 0, @0x1f74
tl: 12 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 15
col  1: [ 5]  49 43 4f 4c 24
tab 0, row 1, @0x1f66
tl: 14 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 2f
col  1: [ 7]  49 5f 55 53 45 52 31
tab 0, row 2, @0x1f5b
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 1d
col  1: [ 4]  43 4f 4e 24
tab 0, row 3, @0x1f4f
tl: 12 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 10
col  1: [ 5]  55 4e 44 4f 24

10g增加列之后dump
因为10g在没有增加列之前的dump和11g未增加列之前类似,所以未dump出来

tab 0, row 0, @0x1f63
tl: 29 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 15
col  1: [ 5]  49 43 4f 4c 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f44
tl: 31 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 2d
col  1: [ 7]  49 5f 55 53 45 52 31
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f28
tl: 28 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 1d
col  1: [ 4]  43 4f 4e 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 3, @0x1f0b
tl: 29 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 10
col  1: [ 5]  55 4e 44 4f 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d

对比发现11g在增加列之后,以前的数据dump出来的内容未有任何改变.也就是说:在10g中,我们增加一个列和默认值,会自动的增加到真实的数据中,而在11g中增加列和默认值并未真的加到11g的表中已经存在的数据中.
11g中插入新数据dump测试

SQL>  insert into chf.t_xifenfei(object_id,object_name)
  2  select object_id,object_name FROM DBA_OBJECTS;
74605 rows created.
SQL> commit;
Commit complete.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> /
System altered.
--建议刷新
SQL>
SQL>
SQL> select   rowid,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5  from chf.t_xifenfei where object_name='OBJ$';
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASpRAAEAAAACrAAu          4        171         46
AAASpRAAEAAAB5TAAu          4       7763         46
SQL> ALTER SYSTEM DUMP DATAFILE 4 BLOCK 7763;
System altered.
--dump内容
tab 0, row 0, @0x4e3
tl: 29 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 15
col  1: [ 5]  49 43 4f 4c 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x500
tl: 31 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 2f
col  1: [ 7]  49 5f 55 53 45 52 31
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x51f
tl: 28 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 1d
col  1: [ 4]  43 4f 4e 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 3, @0x53b
tl: 29 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 10
col  1: [ 5]  55 4e 44 4f 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d

通过这里看看出:在11g中后续插入的数据,默认值也插入到数据文件中

Oracle 11G的DDL_LOCK_TIMEOUT参数

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 11G的DDL_LOCK_TIMEOUT参数

今天kaums给客户做培训11g新特性,发现还真的有不少挺好的新东西,但是以前没有怎么去关注的他们,在后续的几篇中,陆续整理处理.
DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.
If a lock is not acquired before the timeout period expires, then an error is returned.
数据库ddl_lock_timeout参数

SQL> select * from v$version;
BANNER
---------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter ddl_lock_timeout;
NAME                                 TYPE       VALUE
------------------------------------ ---------- --------------
ddl_lock_timeout                     integer    0

ddl_lock_timeout默认值测试(同以前版本)

--会话1
SQL> set time on
10:27:57 SQL> create table t_xifenfei as
10:28:05   2  select * from dba_users;
Table created.
Elapsed: 00:00:00.08
10:28:17 SQL> delete from t_xifenfei where username='CHF';
1 row deleted.
--会话2
SQL> set timing on
SQL> ALTER TABLE T_XIFENFEI DROP COLUMN AUTHENTICATION_TYPE;
ALTER TABLE T_XIFENFEI DROP COLUMN AUTHENTICATION_TYPE
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:00.01

设置ddl_lock_timeout超时测试

--会话1
10:28:17 SQL> delete from t_xifenfei where username='CHF';
1 row deleted.
--会话2
SQL>  ALTER SESSION SET ddl_lock_timeout=10;
Session altered.
Elapsed: 00:00:00.00
SQL> ALTER TABLE T_XIFENFEI DROP COLUMN AUTHENTICATION_TYPE;
ALTER TABLE T_XIFENFEI DROP COLUMN AUTHENTICATION_TYPE
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:10.01

设置ddl_lock_timeout未超时测试

--会话2
SQL> ALTER SESSION SET ddl_lock_timeout=30;
Session altered.
Elapsed: 00:00:00.00
--会话1
10:30:00 SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
--会话2
SQL> ALTER TABLE T_XIFENFEI DROP COLUMN AUTHENTICATION_TYPE;
Table altered.
Elapsed: 00:00:07.91

在以前的版本中ddl操作是nowait等待,通过实验可以发现ddl_lock_timeout可以在一定程度上解决因为我们不清楚这个表是否有dml操作而导致ddl操作不能进行的情况,从一定程度上减少了自己去尝试ddl操作,或者查询相关视图然后找出相关会话,然后kill掉对应数据的情况,可以说是在修改表结构的时候一个很不错的新特性.

gv$视图不能查询所有节点信息

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：gv$视图不能查询所有节点信息

今天遇到诡异的事情,AIX 5.3 ORACLE 9I RAC的gv$视图只能查询到本地的记录,而不是所有节点.但是所有节点均运行正常,除gv$视图之外未发现其他异常.
异常时节点1信息

SQL> show parameter clu;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string      192.168.6.24
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> col host_name for a10
SQL> select inst_id,HOST_NAME,STATUS from gv$instance;
   INST_ID HOST_NAME  STATUS
---------- ---------- ------------
         1 zwq_crm1   OPEN
SQL> show parameter par;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
log_parallelism                      integer     1
parallel_adaptive_multi_user         boolean     FALSE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string
parallel_max_servers                 integer     5
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_threads_per_cpu             integer     2
partition_view_enabled               boolean     FALSE
recovery_parallelism                 integer     0
SQL> !ps -ef|grep p0
  oracrm 1929258       1   0 09:49:19      -  0:01 ora_p005_crm1
  oracrm  745844       1   0   Jun 26      -  0:20 ora_p004_crm1
  oraeye 2421272 3948648   0 21:53:49  pts/0  0:00 grep p0
  oracrm 3060406       1   0   Jun 26      -  0:20 ora_p002_crm1
  oracrm 3170868       1   0   Jun 20      -  2:13 ora_p000_crm1
  oracrm  787414       1   0   Jun 26      -  0:20 ora_p001_crm1
  oracrm 2552690       1   0   Jun 26      -  0:20 ora_p003_crm1

1.节点最大允许5个并发进程,现在已经启动并发进程到p005(6个)
2.gv$视图只能查询一个节点信息

异常时节点2信息
问题所有情况和1节点完全相似

SQL> col host_name for a10
SQL> select inst_id,HOST_NAME,STATUS from gv$instance;
   INST_ID HOST_NAME  STATUS
---------- ---------- ------------
         2 zwq_crm2   OPEN
SQL> show parameter par;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
log_parallelism                      integer     1
parallel_adaptive_multi_user         boolean     FALSE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string
parallel_max_servers                 integer     5
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_threads_per_cpu             integer     2
partition_view_enabled               boolean     FALSE
recovery_parallelism                 integer     0
SQL> !ps -ef|grep p0
  oracrm 1867938       1   0 15:17:25      -  0:00 ora_p004_crm2
  oracrm 2633748       1   0 09:49:19      -  0:01 ora_p005_crm2
  oraeye 3059876 1007714   0 21:54:01  pts/0  0:00 grep p0
  oracrm  323884       1 120   Jun 20      - 10692:47 ora_p000_crm2
  oracrm 1839818       1   0 06:16:32      -  0:00 ora_p003_crm2
  oracrm  459660       1 107   Jun 26      - 1857:00 ora_p001_crm2
  oracrm 2351894       1   0 16:52:52      -  0:00 ora_p002_crm2

在异常2节点上做10046

SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
Statement processed.
SQL> select * from gv$version;
   INST_ID BANNER
---------- ----------------------------------------------------------------
         2 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
         2 PL/SQL Release 9.2.0.8.0 - Production
         2 CORE 9.2.0.8.0       Production
         2 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
         2 NLSRTL Version 9.2.0.8.0 - Production
SQL> oradebug EVENT 10046 trace name context off
Statement processed.
SQL> oradebug TRACEFILE_NAME
/oracle9/app/admin/crm/udump/crm2_ora_517066.trc

分析10046内容发现

PARSING IN CURSOR #1 len=24 dep=0 uid=0 oct=3 lid=0 tim=41759005850609 hv=775381991 ad='21195808'
select * from gv$version
END OF STMT
PARSE #1:c=20000,e=33894,p=1,cr=59,cu=3,mis=1,r=0,dep=0,og=4,tim=41759005850607
BINDS #1:
kxfpg1srv
        could not start P006, inst 1
kxfpg1srv
        could not start local P006
EXEC #1:c=0,e=3540,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=41759005854254
FETCH #1:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=41759005854347
FETCH #1:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=4,dep=0,og=4,tim=41759005854678
STAT #1 id=1 cnt=5 pid=0 pos=1 obj=0 op='VIEW  '
STAT #1 id=2 cnt=5 pid=1 pos=1 obj=304 op='FIXED TABLE FULL X$VERSION '

通过这里观察10046可以看到:在2节点上查询gv$instance,需要通过使用并发进程去访问1节点,但是因为1节点的parallel_max_servers为5,而当前的并发进程已经达到最大数目,从而使得想在节点1上启动并发失败,进入使得gv$视图只能查询出来本节点数据

gv$视图异常解决方法

--重启两个节点,查询正常
SQL> col host_name for a10
SQL> select inst_id,HOST_NAME,STATUS from gv$instance;
   INST_ID HOST_NAME  STATUS
---------- ---------- ------------
         1 zwq_crm1   OPEN
         2 zwq_crm2   OPEN

针对这个问题,很可能是oracle bug(因为是9i版本,我无法深究),或者是并发进程僵死所致,当时有一个想法,kill 掉数据库并发进程,因客户不同意(采用稳妥重启方案),未能通过尝试验证我的猜想.

对gv$视图正常做10046

SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> select * from gv$version;
   INST_ID BANNER
---------- ----------------------------------------------------------------
         1 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
         1 PL/SQL Release 9.2.0.8.0 - Production
         1 CORE 9.2.0.8.0       Production
         1 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
         1 NLSRTL Version 9.2.0.8.0 - Production
         2 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
         2 PL/SQL Release 9.2.0.8.0 - Production
         2 CORE 9.2.0.8.0       Production
         2 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
         2 NLSRTL Version 9.2.0.8.0 - Production
10 rows selected.
SQL> oradebug EVENT 10046 trace name context off
Statement processed.
SQL> oradebug TRACEFILE_NAME
/oracle9/app/admin/crm/udump/crm1_ora_1708916.trc

分析10046内容

PARSING IN CURSOR #1 len=24 dep=0 uid=0 oct=3 lid=0 tim=41752681925071 hv=775381991 ad='92ef64f0'
select * from gv$version
END OF STMT
PARSE #1:c=0,e=6770,p=0,cr=12,cu=3,mis=1,r=0,dep=0,og=4,tim=41752681925070
BINDS #1:
WAIT #1: nam='PX Deq: reap credit' ela= 21 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 237 p1=268500992 p2=1 p3=504403208016510312
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 64 p1=268500992 p2=2 p3=504403208016510312
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 46 p1=268566528 p2=1 p3=504403208016502096
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 855 p1=268566528 p2=2 p3=504403208016502096
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 188 p1=268566528 p2=1 p3=504403208016502096
WAIT #1: nam='PX Deq: reap credit' ela= 1 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 406 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 7442 p1=200 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 1664 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
EXEC #1:c=0,e=11572,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=41752681936727
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1650815232 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 166 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 310 p1=200 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
FETCH #1:c=0,e=557,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=41752681937320
WAIT #1: nam='SQL*Net message from client' ela= 223 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1650815232 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 18 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='DFS lock handle' ela= 263 p1=1128857605 p2=9 p3=5
WAIT #1: nam='DFS lock handle' ela= 254 p1=1128857605 p2=9 p3=1
WAIT #1: nam='DFS lock handle' ela= 263 p1=1128857605 p2=9 p3=3
WAIT #1: nam='DFS lock handle' ela= 73 p1=1128857605 p2=9 p3=2
WAIT #1: nam='DFS lock handle' ela= 363 p1=1128857605 p2=9 p3=2
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 24 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Signal ACK' ela= 3 p1=0 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Signal ACK' ela= 155 p1=10 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 1 p1=0 p2=0 p3=0
FETCH #1:c=0,e=1735,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=4,tim=41752681939329
WAIT #1: nam='DFS lock handle' ela= 249 p1=1128857605 p2=9 p3=1
WAIT #1: nam='DFS lock handle' ela= 258 p1=1128857605 p2=9 p3=3
WAIT #1: nam='DFS lock handle' ela= 66 p1=1128857605 p2=9 p3=2
WAIT #1: nam='DFS lock handle' ela= 369 p1=1128857605 p2=9 p3=2
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 19 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 1 p1=0 p2=0 p3=0
WAIT #1: nam='enqueue' ela= 201 p1=1347616774 p2=2 p3=0
WAIT #1: nam='enqueue' ela= 41 p1=1347616774 p2=2 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 6981860 p1=1650815232 p2=1 p3=0
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='VIEW  '
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=304 op='FIXED TABLE FULL X$VERSION '

通过这里可以看出,rac在正常情况下gv$视图默认的查询就是并发方式进行

因未配置Hugepage会话数添增悲剧案例

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：因未配置Hugepage会话数添增悲剧案例

今天一朋友反馈他们的一个数据库hang住了,通过ssh也不能登录系统,他们没有办法重启系统解决问题,现在想让我帮忙找出问题原因
分析awr得出

询问朋友,他们的库一般session保持在200个左右,这次突然飙升到750以上,属于异常情况

分析监听日志

看到在截图的时间内,整体访问较频繁,某个ip访问异常频繁,通过这些信息,初步怀疑是用户的数据库内存使用完,导致系统数据库hang住.

查看系统日志

Jun 26 14:35:55 result01 kernel: [5613531.566617] Free swap  = 0kB
Jun 26 14:35:55 result01 kernel: [5613531.566618] Total swap = 2104504kB
Jun 26 14:35:55 result01 kernel: [5613531.566620] Free swap:            0kB
Jun 26 14:35:55 result01 kernel: [5613531.591073] 2359296 pages of RAM
Jun 26 14:35:55 result01 kernel: [5613531.591074] 318236 reserved pages
Jun 26 14:35:55 result01 kernel: [5613531.591075] 73353 pages shared
Jun 26 14:35:56 result01 kernel: [5613531.591076] 529 pages swap cached
Jun 26 14:35:56 result01 kernel: [5613531.591079] Out of Memory: Kill process 8904 (oracle) score 891 and children.
Jun 26 14:35:56 result01 kernel: [5613531.591201] Out of memory: Killed process 8904 (oracle).
Jun 26 14:35:56 result01 kernel: [5613531.592280] oracle invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0

通过这个日志看出系统内存和交换分区都使用完,因为内存不够,系统开始kill掉部分oracle进程.通过这些确定是系统内存使用完导致hang住可以理解.

分析hang住原因
为什么session意外的从200添增到750的时候,系统内存被使用完

cat /proc/meminfo
MemTotal:      8164240 kB
SwapTotal:     2104504 kB
PageTables:      69732 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB
sga_target=3674210304
pga_aggregate_target=1732247552

从这里得出几个信息:
1.数据库总内存8g,swap配置2g
2.数据库未使用Hugepage
3.数据库设置sga和pga信息

内存参数估算
数据库总计占用内存为:(3674210304+1732247552)/1024/1024=5156M(pga可能未使用完,也可能超过)
结合实际sga_target=3674210304,会话数.
保守估计下Oracle进程占用的系统内存3674210304/(4*1024)*1.5*750/1024/1024=960M
估算如果使用Hugepage Oracle进程占用系统内存为:3674210304/(2*1024*1024)*1.5*750/1024/1024=1.9M
通过这里分析Oracle总占用内存为:5156+960=6116M
通过保守计算留给系统的内存大概为:1.8G左右
因为系统的其他操作,最终导致该系统内存耗完,系统和数据库hang住

总结说明
这是一个实实在在因为linux中因为未配置Hugepage,因为用户突增,导致系统内存消耗光,从而使得系统和数据库hang住的例子.
这个库因为sga不是非常大,所以Oracle占用系统内存不是高到离谱,如果sga配置为32g,1000个session,那就会占用12g的系统内存
通过这些可以看出在linux中配置Hugepage的优点:Hugepage不光是为了减轻cpu的负担,还可以减少系统内存的消耗;在没有极端的情况下,建议linux的数据库系统配置Hugepage.

分类目录归档：Oracle