How to manually commit a transaction using bbed in Oracle

Sometimes the database hangs up, the library will be ORA-00704 error, and the root cause of the ORA-00704 error is access to obj$, Oracle needs to roll back the data in the paragraph, and access to the rollback segment when the need for the undo data has been overwritten, At this point we can solve this problem by submitting the transaction manually through the Bbed tool.

The process of submitting a transaction test using bbed is as follows:

Jp@orcl>create table Jp_bbed_test as SELECT * from Hr.employees where rownum<=10;

Table created.

Jp@orcl>select rowID, Dbms_rowid.rowid_relative_fno (ROWID) Rel_fno,

Dbms_rowid.rowid_block_number (ROWID) Blockno,

Dbms_rowid.rowid_row_number (ROWID) rowno

From Jp_bbed_test; 2 3 4

ROWID rel_fno Blockno Rowno

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

AAAM7WAAEAAAAGCAAA 4 412 0

Aaam7waaeaaaagcaab 4 412 1

AAAM7WAAEAAAAGCAAC 4 412 2

Aaam7waaeaaaagcaad 4 412 3

AAAM7WAAEAAAAGCAAE 4 412 4

AAAM7WAAEAAAAGCAAF 4 412 5

Aaam7waaeaaaagcaag 4 412 6

Aaam7waaeaaaagcaah 4 412 7

AAAM7WAAEAAAAGCAAI 4 412 8

Aaam7waaeaaaagcaaj 4 412 9

Ten rows selected.

Jp@orcl>select last_name from Jp_bbed_test;

Last_Name

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

OConnell

Grant

Whalen

Hartstein

Fay

Mavris

Baer

Higgins

Gietz

King

Ten rows selected.

Jp@orcl>update jp_bbed_test set last_name= ' BADLY9 ';

Ten rows updated.

The transaction is not committed at this time, and the other session is unable to view the modification at this time.

Open a different window

[Oracle@jp bbed]$ Sqlplus/as SYSDBA

Sql*plus:release 10.2.0.1.0-production on Fri June 6 06:56:52 2014

Copyright (c) 1982, +, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production

With the partitioning, OLAP and Data Mining options

Sys@orcl>alter system checkpoint;

System altered.

Sys@orcl>alter System Dump datafile 4 block 412;

System altered.

Sys@orcl>oradebug Setmypid

Statement processed.

Sys@orcl>oradebug Tracefile_name

/u01/app/oracle/admin/orcl/udump/orcl_ora_17715.trc

View/U01/APP/ORACLE/ADMIN/ORCL/UDUMP/ORCL_ORA_17715.TRC Files

We can see the following:

Block Header dump:0x0100019c

Object ID on block? Y

SEG/OBJ:0XCEF0 csc:0x00.c3cf8 itc:3 flg:e typ:1-DATA

brn:0 bdba:0x1000199 ver:0x01 opc:0

inc:0 exflg:0

Itl Xid Uba Flag Lck SCN/FSC

0x01 0xffff.000.00000000 0x00000000.0000.00 C---0 SCN 0x0000.000c3cf8

0x02 0x0006.02b.000001a1 0x008000d0.00f6.2a----FSC 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00----0 FSC 0x0000.00000000

Here we can see that we lck the record in 10 tables

This is when we use bbed to commit the transaction:

First, using Session2 flush Buffer_pool, if you do not empty the buffer pool, you will not be able to read the block again, and you cannot see the modified results and overwrite our modifications:

Sys@orcl>alter system flush Buffer_cache;

System altered.

To modify using the Bbed tool:

Bbed> Set DBA 4,412

DBA 0x0100019c (16777628 4,412)

Bbed> Map

File:/U01/APP/ORACLE/ORADATA/ORCL/USERS01.DBF (4)

block:412 dba:0x0100019c

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

KTB Data block (table/cluster)

struct KCBH, Bytes @0

struct KTBBH, Bytes @20

struct KDBH, Bytes @124

struct kdbt[1], 4 bytes @138

SB2 Kdbr[10] @142

Ub1 freespace[6794] @162

Ub1 rowdata[1232] @6956

UB4 Tailchk @8188

Bbed> P KTBBH

struct KTBBH, Bytes @20

Ub1 Ktbbhtyp @20 0x01 (kddbtdata)

Union ktbbhsid, 4 bytes @24

UB4 KTBBHSG1 @24 0X0000CEF0

UB4 ktbbhod1 @24 0X0000CEF0

struct KTBBHCSC, 8 bytes @28

UB4 Kscnbas @28 0x000c3cf8

UB2 KSCNWRP @32 0x0000

B2 ktbbhict @36 3

Ub1 ktbbhflg @38 0x32 (NONE)

Ub1 KTBBHFSL @39 0x00

UB4 KTBBHFNX @40 0x01000199

struct ktbbhitl[0], bytes @44

struct Ktbitxid, 8 bytes @44

UB2 Kxidusn @44 0xFFFF

UB2 kxidslt @46 0x0000

UB4 kxidsqn @48 0x00000000

struct Ktbituba, 8 bytes @52

UB4 KUBADBA @52 0x00000000

UB2 kubaseq @56 0x0000

Ub1 Kubarec @58 0x00

UB2 ktbitflg @60 0x8000 (ktbfcom)

Union _ktbitun, 2 bytes @62

B2 _KTBITFSC @62 0

UB2 _KTBITWRP @62 0x0000

UB4 Ktbitbas @64 0x000c3cf8

struct ktbbhitl[1], bytes @68

struct Ktbitxid, 8 bytes @68

UB2 Kxidusn @68 0x0006

UB2 kxidslt @70 0x002b

UB4 kxidsqn @72 0X000001A1

struct Ktbituba, 8 bytes @76

UB4 KUBADBA @76 0x008000d0

UB2 kubaseq @80 0x00f6

Ub1 Kubarec @82 0x2a

UB2 ktbitflg @84 0x000a (NONE)

Union _ktbitun, 2 bytes @86

B2 _KTBITFSC @86 0

UB2 _KTBITWRP @86 0x0000

UB4 Ktbitbas @88 0x00000000

struct ktbbhitl[2], bytes @92

struct Ktbitxid, 8 bytes @92

UB2 Kxidusn @92 0x0000

UB2 kxidslt @94 0x0000

UB4 kxidsqn @96 0x00000000

struct Ktbituba, 8 bytes @100

UB4 KUBADBA @100 0x00000000

UB2 kubaseq @104 0x0000

Ub1 Kubarec @106 0x00

UB2 ktbitflg @108 0x0000 (NONE)

Union _ktbitun, 2 bytes @110

B2 _KTBITFSC @110 0

UB2 _KTBITWRP @110 0x0000

UB4 Ktbitbas @112 0x00000000