20160526_oradebug_datablock_undoblockheader

最新推荐文章于 2023-10-23 21:30:03 发布

cuitan5937

最新推荐文章于 2023-10-23 21:30:03 发布

阅读量94

点赞数

####################################################

session 1

[oracle@test1 ~]$ sqlplus user1/oracle

SQL*Plus: Release 10.2.0.5.0 - Production on Thu May 26 13:03:17 2016

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from t1;

ID NAME

---------- -------------

1 shihaifeng

SQL> select sid from v$mystat where rownum=1;

SID

----------

142

SQL> update t1 set name='shihaijun' where rownum=1;

1 row updated.

--查看行所在文件号、块号

SQL> SELECT ROWID,

2 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS FNO,

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLKNO

FROM T1

4 5 WHERE ROWNUM = 1

6 /

ROWID FNO BLKNO

------------------ ---------- ----------

AAAM7VAAEAAAAGGAAA 4 390

#######################

s[oracle@test1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu May 26 13:05:04 2016

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system dump datafile 4 block 390;

System altered.

跟踪文件

SQL> SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||P.SPID || '.trc' TRACE_FILE_NAME

2 FROM (SELECT P.SPID

3 FROM V$MYSTAT M, V$SESSION S, V$PROCESS P

4 WHERE M.STATISTIC# = 1

5 AND S.SID = M.SID

AND P.ADDR = S.PADDR) P,

7 (SELECT T.INSTANCE

8 FROM V$THREAD T, V$PARAMETER V

9 WHERE V.NAME = 'thread'

10 AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,

11 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;

TRACE_FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/admin/db1/udump/db1_ora_11024.trc

SQL> select sid from v$mystat where rownum=1;

SID

----------

141

[oracle@test1 ~]$ tail -200 /u01/app/oracle/admin/db1/udump/db1_ora_11024.trc

Block header dump: 0x01000186

Object id on Block? Y

seg/obj: 0xced5 csc: 0x00.7cd18 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000181 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0009.01a.00000114 0x008000e4.0100.06 ---- 1 fsc 0x0001.00000000

0x02 0x0002.024.000000f6 0x00800e8c.00d5.04 C--- 0 scn 0x0000.0007c96f

data_block_dump,data header at 0x7c23664

===============

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x07c23664

bdba: 0x01000186

76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x1f77

avsp=0x1f73

tosp=0x1f74

0xe:pti[0] nrow=1 offs=0

0x12:pri[0] offs=0x1f77

block_row_dump:

tab 0, row 0, @0x1f77

tl: 16 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 02

col 1: [ 9] 73 68 69 68 61 69 6a 75 6e

end_of_block_dump

End dump data blocks tsn: 4 file#: 4 minblk 390 maxblk 390

007C25140 01130000 00000104 00000000 00040002 [................]

[oracle@test1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu May 26 13:08:46 2016

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

--查看事物xid

SQL> select xidusn,xidslot,xidsqn from v$transaction where addr=(select taddr from v$session where sid=142);

XIDUSN XIDSLOT XIDSQN

---------- ---------- ----------

9 26 276

--对比xid 0x0009.01a.00000114 16进制转换成10进制 9.26.276

--可以确认回滚段编号 22 事物表上的slot位置 38

SQL> select file_id,block_id from dba_rollback_segs where segment_id=9;

FILE_ID BLOCK_ID

---------- ----------

2 137

SQL> alter system dump datafile 2 block 137

2 /

System altered.

SQL> SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||P.SPID || '.trc' TRACE_FILE_NAME

FROM (SELECT P.SPID

FROM V$MYSTAT M, V$SESSION S, V$PROCESS P

WHERE M.STATISTIC# = 1

AND S.SID = M.SID

AND P.ADDR = S.PADDR) P,

(SELECT T.INSTANCE

FROM V$THREAD T, V$PARAMETER V

WHERE V.NAME = 'thread'

AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,

11 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;

TRACE_FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/admin/db1/udump/db1_ora_11070.trc

SQL>

撤销头块转储内容

[oracle@test1 ~]$ more /u01/app/oracle/admin/db1/udump/db1_ora_11070.trc

/u01/app/oracle/admin/db1/udump/db1_ora_11070.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1

System name: Linux

Node name: test1

Release: 2.6.32-431.el6.x86_64

Version: #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine: x86_64

Instance name: db1

Redo thread mounted by this instance: 1

Oracle process number: 28

Unix process pid: 11070, image: oracle@test1 (TNS V1-V3)

*** 2016-05-26 13:22:23.094

*** ACTION NAME:() 2016-05-26 13:22:23.094

*** MODULE NAME:(sqlplus@test1 (TNS V1-V3)) 2016-05-26 13:22:23.094

*** SERVICE NAME:(SYS$USERS) 2016-05-26 13:22:23.094

*** SESSION ID:(140.4) 2016-05-26 13:22:23.093

Starts tsn: 1 f dump data blockile#: 2 minblk 137 maxblk 137

buffer tsn: 1 rdba: 0x00800089 (2/137)

scn: 0x0000.0007cd20 seq: 0x01 flg: 0x04 tail: 0xcd202601

frmt: 0x02 chkval: 0xa141 type: 0x26=KTU SMU HEADER BLOCK

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x0000000008263600 to 0x0000000008265600

008263600 0000A226 00800089 0007CD20 04010000 [&....... .......]

。。。。。。。。。。。。。。。。。。。。。。。。。。。

0082655C0 00000000 00000000 00000000 00000000 [................]

Repeat 1 times

0082655E0 00000000 00000100 00170940 09400000 [........@.....@.]

0082655F0 01000017 02C10280 4A29C203 CD202601 [..........)J.& .]

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 15

last map 0x00000000 #maps: 0 offset: 4080

Highwater:: 0x008000e4 ext#: 1 blk#: 3 ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 1

Unlocked

Map Header:: next 0x00000000 #extents: 2 obj#: 0 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x0080008a length: 7

0x008000e1 length: 8

Retention Table

-----------------------------------------------------------

Extent Number:0 Commit Time: 1464238814

Extent Number:1 Commit Time: 1464238814

TRN CTL:: seq: 0x0100 chd: 0x0017 ctl: 0x0013 inc: 0x00000000 nfb: 0x0001

mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)

uba: 0x008000e4.0100.06 scn: 0x0000.0007bf89

Version: 0x01

FREE BLOCK POOL::

uba: 0x00000000.0100.05 ext: 0x1 spc: 0x1d94

uba: 0x008000e2.0100.02 ext: 0x1 spc: 0x1f06

uba: 0x00000000.00fb.01 ext: 0x0 spc: 0x1dbc

uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0

TRN TBL::--事物表

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

------------------------------------------------------------------------------------------------

0x00 9 0x00 0x0114 0x0012 0x0000.0007ca87 0x0080008d 0x0000.000.00000000 0x00000001 0x00000000 1464237663

0x01 9 0x00 0x0114 0x002d 0x0000.0007c4e2 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464234362

0x02 9 0x00 0x0114 0x0005 0x0000.0007c762 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464235562

0x03 9 0x00 0x0114 0x0007 0x0000.0007c780 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464235562

0x04 9 0x00 0x0114 0x0003 0x0000.0007c779 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464235562

0x05 9 0x00 0x0114 0x0004 0x0000.0007c773 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464235562

0x06 9 0x00 0x0114 0x0002 0x0000.0007c755 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464235562

0x07 9 0x00 0x0114 0x0009 0x0000.0007c8d5 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464236563

0x08 9 0x00 0x0114 0x000a 0x0000.0007c929 0x0080008d 0x0000.000.00000000 0x00000001 0x00000000 1464236762

0x09 9 0x00 0x0114 0x0008 0x0000.0007c91f 0x0080008d 0x0000.000.00000000 0x00000001 0x00000000 1464236761

0x0a 9 0x00 0x0114 0x000c 0x0000.0007ca7a 0x0080008d 0x0000.000.00000000 0x00000001 0x00000000 1464237663

0x0b 9 0x00 0x0114 0x0015 0x0000.0007cc27 0x0080008d 0x0000.000.00000000 0x00000001 0x00000000 1464238814

0x0c 9 0x00 0x0114 0x0000 0x0000.0007ca7e 0x0080008d 0x0000.000.00000000 0x00000001 0x00000000 1464237663

0x0d 9 0x00 0x0114 0x0013 0x0000.0007ccb0 0x008000e4 0x0000.000.00000000 0x00000003 0x00000000 1464238815

0x0e 9 0x00 0x0112 0x0023 0x0000.0007c24f 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464089415

0x0f 9 0x00 0x0114 0x0010 0x0000.0007cc59 0x008000e1 0x0000.000.00000000 0x00000001 0x00000000 1464238814

0x10 9 0x00 0x0114 0x0014 0x0000.0007cc63 0x008000e1 0x0000.000.00000000 0x00000001 0x00000000 1464238814

0x11 9 0x00 0x0114 0x000f 0x0000.0007cc51 0x008000e1 0x0000.000.00000000 0x00000001 0x00000000 1464238814

0x12 9 0x00 0x0114 0x002e 0x0000.0007cc23 0x008000e1 0x0000.000.00000000 0x00000001 0x00000000 1464238814

0x13 9 0x00 0x0114 0xffff 0x0000.0007ccb5 0x008000e4 0x0000.000.00000000 0x00000001 0x00000000 1464238816

0x14 9 0x00 0x0114 0x000d 0x0000.0007cc6a 0x008000e1 0x0000.000.00000000 0x00000001 0x00000000 1464238814

0x15 9 0x00 0x0114 0x0018 0x0000.0007cc4b 0x008000e1 0x0000.000.00000000 0x00000001 0x00000000 1464238814

0x16 9 0x00 0x0113 0x0021 0x0000.0007c2e3 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464233162

0x17 9 0x00 0x0113 0x001d 0x0000.0007bf8d 0x0080010b 0x0000.000.00000000 0x00000003 0x00000000 1464087659

0x18 9 0x00 0x0114 0x0011 0x0000.0007cc4f 0x008000e1 0x0000.000.00000000 0x00000001 0x00000000 1464238814

0x19 9 0x00 0x0113 0x001b 0x0000.0007bf99 0x0080008d 0x0000.000.00000000 0x00000008 0x00000000 1464087659

0x1a 10 0x80 0x0114 0x0001 0x0000.00000000 0x008000e4 0x0000.000.00000000 0x00000001 0x00000000 0 -- 0x1a 对应10进制slot 26 这是就是我们要观察的信息

0x1b 9 0x00 0x0113 0x0020 0x0000.0007c0e8 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464088513

0x1c 9 0x00 0x0113 0x000e 0x0000.0007c245 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464089414

0x1d 9 0x00 0x0113 0x002b 0x0000.0007bf92 0x0080010f 0x0000.000.00000000 0x00000003 0x00000000 1464087659

0x1e 9 0x00 0x0113 0x002f 0x0000.0007c4cd 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464234362

0x1f 9 0x00 0x0113 0x0025 0x0000.0007c101 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464088513

0x20 9 0x00 0x0113 0x001f 0x0000.0007c0ed 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464088513

0x21 9 0x00 0x0113 0x002c 0x0000.0007c2eb 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464233162

0x22 9 0x00 0x0113 0x001c 0x0000.0007c114 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464088513

0x23 9 0x00 0x0112 0x0024 0x0000.0007c2ae 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1464233162

0x24 9 0x00 0x0113 0x002a 0x0000.0007c2c2 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464233162

0x25 9 0x00 0x0113 0x0022 0x0000.0007c105 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464088513

0x26 9 0x00 0x0113 0x0006 0x0000.0007c5f8 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464234961

0x27 9 0x00 0x0113 0x001e 0x0000.0007c334 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464233228

0x28 9 0x00 0x0113 0x0016 0x0000.0007c2d9 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464233162

0x29 9 0x00 0x0113 0x0027 0x0000.0007c30b 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464233162

0x2a 9 0x00 0x0113 0x0028 0x0000.0007c2c5 0x0080008b 0x0000.000.00000000 0x00000001 0x00000000 1464233162

0x2b 9 0x00 0x0112 0x0019 0x0000.0007bf96 0x0080008b 0x0000.000.00000000 0x00000003 0x00000000 1464087659

0x2c 9 0x00 0x0113 0x0029 0x0000.0007c2ff 0x0080008e 0x0000.000.00000000 0x00000002 0x00000000 1464233162

0x2d 9 0x00 0x0113 0x0026 0x0000.0007c513 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464234362

0x2e 9 0x00 0x0113 0x000b 0x0000.0007cc26 0x008000e2 0x0000.000.00000000 0x00000003 0x00000000 1464238814

0x2f 9 0x00 0x0113 0x0001 0x0000.0007c4d9 0x0080008e 0x0000.000.00000000 0x00000001 0x00000000 1464234362

End dump data blocks tsn: 1 file#: 2 minblk 137 maxblk 137

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

转载于:http://blog.itpub.net/26179376/viewspace-2107491/

cuitan5937

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
20160526_oradebug_datablock_undoblockheader

#################################################### session 1 [oracle@test1 ~]$ sqlplus...
复制链接

扫一扫

20160526_oradebug_datablock_undoblockheader

“相关推荐”对你有帮助么？