[Oracle] Use bbed to submit transactions (2)

Based on the previous article, this article modifies the relevant transaction slot information in the undo segment header to prevent the smon rollback operation when the database is restarted or the process is abnormal, thus, the database transaction is submitted manually.

The experiment process is as follows:

Session 1

JP @ ORCL> select last_name from bbed_test;

LAST_NAME

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

OConnell

Grant

Whalen

Hartstein

Fay

Mavris

Baer

Higgins

Gietz

King

10 rows selected.

JP @ ORCL> update bbed_test set last_name = 'badly9 ';

10 rows updated.

Transaction not submitted

Session 2:

SYS @ 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 2 3 4

5 JP. BBED_TEST;

ROWID REL_FNO BLOCKNO ROWNO

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

AAAM8CAAEAAAAG8AAA 4 444 0

AAAM8CAAEAAAAG8AAB 4 444 1

AAAM8CAAEAAAAG8AAC 4 444 2

AAAM8CAAEAAAAG8AAD 444 3

AAAM8CAAEAAAAG8AAE 444 4

AAAM8CAAEAAAAG8AAF 4 444 5

AAAM8CAAEAAAAG8AAG 4 444 6

AAAM8CAAEAAAAG8AAH 4 444 7

AAAM8CAAEAAAAG8AAI 444 8

AAAM8CAAEAAAAG8AAJ 4 444 9

10 rows selected.

SYS @ ORCL> select xidusn, XIDSLOT, XIDSQN, UBAFIL, UBABLK, UBASQN, ubarec from v $ transaction;

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC

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

7 38 369 2 786 302 16

SYS @ ORCL> select segment_name, header_file, header_block

2 from dba_segments where segment_name = (

3 select name from v $ rollname where usn = (select xidusn from v $ transaction ));

SEGMENT_NAME HEADER_FILE HEADER_BLOCK

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

_ SYSSMU7 $2 105

SYS @ ORCL> alter system dump undo header "_ SYSSMU7 $ ";

System altered.

SYS @ ORCL> oradebug setmypid

Statement processed.

SYS @ ORCL> oradebug tracefile_name

/U01/app/oracle/admin/ORCL/udump/orcl_ora_8512.trc

View the/u01/app/oracle/admin/ORCL/udump/orcl_ora_8512.trc file, where you can see

0x25 9 0x00 0x0171 0x001d 0x0000. 000ee92e 0x00000000 0x0000. 000.00000000 0x00000000 0x00000000 1402237152

0x26 10 0x80 0x0171 0x0001 0x0000. 000f3981 0x00800312 0x0000. 000.00000000 0x00000001 0x00000000 0

0x27 9 0x00 0x0171 0x0022 0x0000. 000f38cc 0x00800311 0x0000. 000.00000000 0x00000001 0x00000000 1402238227

Combine the state of trn tbl in the dump undo header with 10 as the active transaction, and then combine the information such as scn/dba to determine which record needs to be modified. then, use the find command to quickly locate the 0x0b record and modify it.

Use the bbed dump command to analyze hexadecimal data and draw the following conclusions:

-- Index 0x26

7101 wrap #0000 12038000 dba 81390f00 00000000 scn 0a s tate 80 cflags 0100 uel 00000000 00000000 parent-xid 00000000 stmt_num 01000000 nub 00000000 cmt

BBED> f/x 0a80

File:/u01/app/oracle/oradata/ORCL/undotbs01.dbf (2)

Block: 105 Offsets: 7736 to 8191 Dba: 0x00800069

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

0a800100 00000000 00000000 00000000 01000000 00000000 71010000

<32 bytes per line>

BBED> m/x 0900

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

File:/u01/app/oracle/oradata/ORCL/undotbs01.dbf (2)

Block: 105 Offsets: 7736 to 8191 Dba: 0x00800069

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

09000100 00000000 00000000 00000000 01000000 00000000 71010000

<32 bytes per line>

BBED> sum apply

Check value for File 2, Block 105:

Current = 0x8e65, required = 0x8e65

Next, modify itl.

Struct ktbbhitl [1], 24 bytes @ 68

Struct ktbitxid, 8 bytes @ 68

Ub2 kxidusn @ 68 0x0007

Ub2 kxidserver Load balancer @ 70 0x0026

Ub4 kxidsqn @ 72 0x00000171

Struct ktbituba, 8 bytes @ 76

Ub4 kubadba @ 76 0x00800312

Ub2 kubaseq @ 80 0x012e

Ub1 kubarec @ 82 0x10

Ub2 ktbitflg @ 84 0x000a (NONE)

Union _ ktbitun, 2 bytes @ 86

B2 _ ktbitfsc @ 86 0

Ub2 _ ktbitwrp @ 86 0x0000

Ub4 ktbitbas @ 88 0x00000000

BBED> m/x 0080 offset 84

File:/u01/app/oracle/oradata/ORCL/users01.dbf (4)

Block: 444 Offsets: 84 to 595 Dba: 0x010001bc

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

00800000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply

Check value for File 4, Block 444:

Current = 0x17c1, required = 0x17c1

Session3 restart the database to query data:

SYS @ ORCL> shutdown abort

ORACLE instance shut down.

SYS @ ORCL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 75499088 bytes

Database Buffers 205520896 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SYS @ ORCL> select last_name from jp. bbed_test;

LAST_NAME

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

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

BADLY9

10 rows selected.

Experiment process reference Fei's article: http://www.orasos.com/4194.html