Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use bbed to restore the rows of delete, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The experiments are as follows:

1. Create environment table DVD

SQL > conn hr/hr

Connected.

SQL >

SQL > create table dvd (job varchar2)

Table created.

SQL > insert into dvd values ('Dave is DBAs')

1 row created.

SQL > insert into dvd values ('Dave like orchestrations')

1 row created.

SQL > commit

Commit complete.

SQL > conn / as sysdba

Connected.

SQL >

SQL > set lines 200

SQL > select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where table_name='DVD'

OWNER TABLE_NAME TABLESPACE_NAME

-

HR DVD USERS

two。 Query dvd related file/block information

SQL > select rowid,dbms_rowid.rowid_relative_fno (rowid) rel_fno,dbms_rowid.rowid_block_number (rowid) blockno,dbms_rowid.rowid_row_number (rowid) rowno,a.* from dvd a

ROWID REL_FNO BLOCKNO ROWNO JOB

- -

AAAV8YAAEAAAAIPAAA 4 527 0 Dave is DBA!

AAAV8YAAEAAAAIPAAB 4 527 1 Dave like Oracle!

SQL >

-- A new window is opened to execute dump block query information

Oracle@wang ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 28 14:31:49 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

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

SQL > alter system dump datafile 4 block 527

System altered.

SQL > oradebug setmypid

Statement processed.

SQL >

SQL > oradebug tracefile_name

/ u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2013.trc

SQL >

-- View trace:

[oracle@wang ~] $more / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2013.trc

Trace file / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2013.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

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

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

System name: Linux

Node name: wang

Release: 3.10.0-327.el7.x86_64

Version: # 1 SMP Thu Oct 29 17:29:29 EDT 2015

Machine: x86_64

Instance name: DBdb

Redo thread mounted by this instance: 1

Oracle process number: 27

Unix process pid: 2013, image: oracle@wang (TNS V1-V3)

* 2017-11-28 14 14 Fringe 31purl 56.277

* SESSION ID: (40.13) 2017-11-28 14 purl 31 purl 56.277

* CLIENT ID: () 2017-11-28 14 31mer 56.277

* SERVICE NAME: (SYS$USERS) 2017-11-28 14 31 purl 56.277

* MODULE NAME: (sqlplus@wang (TNS V1-V3)) 2017-11-28 14 purl 31 purl 56.277

* ACTION NAME: () 2017-11-28 14 31mer 56.277

Start dump data blocks tsn: 4 file#:4 minblk 527 maxblk 527

Block dump from cache:

Dump of buffer cache at level 4 for tsn=4 rdba=16777743

BH (0x89ff27e8) file#: 4 rdba: 0x0100020f (4x527) class: 1 ba: 0x89eee000

Set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0

Dbwrid: 0 obj: 89880 objn: 89880 tsn: 4 afn: 4 hint: f

Hash: [0x8fe6a618,0x87fd9a18] lru: [0x89ff2a10,0x89ff27a0]

Ckptq: [NULL] fileq: [NULL] objq: [0x89ff2a38,0x8b4e5e60] objaq: [0x89ff2a48,0x8b4e5e50]

St: XCURRENT md: NULL fpin: 'ktspbwh3: ktspfmdb' tch: 5

Flags: block_written_once redo_since_read

LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]

Block dump from disk:

Buffer tsn: 4 rdba: 0x0100020f

Scn: 0x0000.0039530d seq: 0x01 flg: 0x06 tail: 0x530d0601

Frmt: 0x02 chkval: 0x9014 type: 0x06=trans data

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

Dump of memory from 0x00007F3C1C8B7A00 to 0x00007F3C1C8B9A00

7F3C1C8B7A00 0000A206 0100020F 0039530D 06010000 [.S9.]

7F3C1C8B7A10 00009014 00000001 00015F18 00395308 [. _... S9.]

7F3C1C8B7A20 00000000 00320002 01000208 00160002 [.2.]

7F3C1C8B7A30 000005FA 00C3D82B 00180272 00002002 [. +. R. ..]

7F3C1C8B7A40 0039530D 00000000 00000000 00000000 [.S9.]

7F3C1C8B7A50 00000000 00000000 00000000 [.]

7F3C1C8B7A60 00000000 00020100 0016FFFF 1F5D1F73 [.s.].]

7F3C1C8B7A70 00001F5D 1F880002 00001F73 00000000 [] .s.]

7F3C1C8B7A80 00000000 00000000 00000000 [.]

Repeat 500 times

7F3C1C8B99D0 00000000 2C000000 44110101 20657661 [.,... Dave]

7F3C1C8B99E0 656B696C 61724F20 21656C63 0C01012C [like Oracle!,...]

7F3C1C8B99F0 65766144 20736920 21414244 530D0601 [Dave is DBA!...S]

Block header dump: 0x0100020f

Object id on Block? Y

Seg/obj: 0x15f18 csc: 0x00.395308 itc: 2 flg: e typ: 1-DATA

Brn: 0 bdba: 0x1000208 ver: 0x01 opc: 0

Inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0002.016.000005fa 0x00c3d82b.0272.18-- U2 fsc 0x0000.0039530d

0x02 0x0000.000.00000000 0x00000000.0000.00-0 fsc 0x0000.00000000

Bdba: 0x0100020f

Data_block_dump,data header at 0x7f3c1c8b7a64

=

Tsiz: 0x1f98

Hsiz: 0x16

Pbl: 0x7f3c1c8b7a64

76543210

Flag=-

Ntab=1

Nrow=2

Frre=-1

Fsbo=0x16

Fseo=0x1f73

Avsp=0x1f5d

Tosp=0x1f5d

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

0x12:pri [0] offs=0x1f88

0x14:pri [1] offs=0x1f73

Block_row_dump:

Tab 0, row 0, @ 0x1f88

Tl: 16 fb:-- Hmurfrill-lb: 0x1 cc: 1

Col 0: [12] 44 61 76 65 20 69 73 20 44 42 41 21

Tab 0, row 1, @ 0x1f73

Tl: 21 fb:-- Hmurfrill-lb: 0x1 cc: 1

Col 0: [17] 44 61 76 65 20 6c 69 6b 65 20 4f 72 61 63 6c 65 21

End_of_block_dump

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

* 2017-11-28 14 14 Fringe 31purl 57.368

Processing Oradebug command 'setmypid'

* 2017-11-28 14 14 Fringe 31purl 57.368

Oradebug command 'setmypid' console output:

* 2017-11-28 14 14 15 32 purl 00.927

Processing Oradebug command 'tracefile_name'

* 2017-11-28 14 14 15 32 purl 00.927

Oradebug command 'tracefile_name' console output:

/ u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2013.trc

[oracle@wang ~] $

There are only 2 rows of records in our table dvd, so the row shown here is 2. 0.

Notice the fb here:-- Hmurf FLMI -. It has eight options, with each value corresponding to bitmask.

Therefore,columns that fit within a single block, are not chained, migrated or part of aclustered table and are not deleted will have the following attributes:

(1) Head of Row Piece

(2) First Data Piece

(3) Last Data Piece

If a row is not deleted, then it has the above three attributes, that is, Flag is represented as:-- Hmurf Murray. The letters here represent the initials of the attribute. The corresponding value is 32 + 8 + 4 = 44 or 0x2c.

If a row is delete, then the row flag is updated and the deleted in the bitmask is set to 16. 0. The row flag is 32 + 16 + 8 + 4 = 60 or 0x3c.

-- verify:

SQL > 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 dvd

ROWID REL_FNO BLOCKNO ROWNO

--

AAAV84AAEAAAAIPAAA 4 527 0

AAAV84AAEAAAAIPAAB 4 527 1

SQL > select * from dvd

JOB

Dave is DBA!

Dave like Oracle!

SQL > delete from dvd where rownum=1

1 row deleted.

SQL > commit

Commit complete.

SQL > select * from dvd

JOB

Dave like Oracle!

-- look at the tag of dump again:

[oracle@wang ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 28 14:36:29 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

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

-- flush buffer_cache is required to clear the buffer:

SQL > alter system flush buffer_cache

System altered.

SQL > alter system dump datafile 4 block 527

System altered.

SQL >

SQL > oradebug setmypid

Statement processed.

SQL >

SQL > oradebug tracefile_name

/ u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2065.trc

SQL >

SQL >

-- check the trace file again:

[oracle@wang ~] $more / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2065.trc

Omit.

Omit.

Omit.

Block_row_dump:

Tab 0, row 0, @ 0x1f88

Tl: 2 fb:-- HDFL-- lb: 0x2-notice the change from-H-FL to-HdFL

Tab 0, row 1, @ 0x1f73

Tl: 21 fb:-- Hmurfrill-lb: 0x0 cc: 1

Col 0: [17] 44 61 76 65 20 6c 69 6b 65 20 4f 72 61 63 6c 65 21

End_of_block_dump

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

* 2017-11-28 14 14 36 purl 42.754

Processing Oradebug command 'setmypid'

* 2017-11-28 14 14 36 purl 42.754

Oradebug command 'setmypid' console output:

* 2017-11-28 14 14 36 purl 48.261

Processing Oradebug command 'tracefile_name'

* 2017-11-28 14 14 36 Fraser 48.262

Oradebug command 'tracefile_name' console output:

/ u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2065.trc

[oracle@wang ~] $

The row 1flag here becomes-- HDFL--.

3.2 now we use bbed to retrieve the deleted row 1 content

-- Edit the PARFILE parameter file of BBED

Check the data file first

SQL > select file# | |''| | name | |''| | bytes from v$datafile

FILE# | |''| | NAME | |''| | BYTES

1/ u01/app/oracle/oradata/DBdb/system01.dbf 2936012800

2 / u01/app/oracle/oradata/DBdb/sysaux01.dbf 723517440

3 / u01/app/oracle/oradata/DBdb/undotbs01.dbf 2710568960

4 / u01/app/oracle/oradata/DBdb/users01.dbf 3207331840

5 / u01/app/oracle/oradata/DBdb/example01.dbf 355205120

-- enter the bbed window

-- configure the BBED parameter file parfile

-- Edit listfile

[oracle@wang ~] $vi filelist.txt

1/ u01/app/oracle/oradata/DBdb/system01.dbf 2936012800

2 / u01/app/oracle/oradata/DBdb/sysaux01.dbf 723517440

3 / u01/app/oracle/oradata/DBdb/undotbs01.dbf 2710568960

4 / u01/app/oracle/oradata/DBdb/users01.dbf 3207331840

5 / u01/app/oracle/oradata/DBdb/example01.dbf 355205120

"filelist.txt" [New] 6L, 275C written

[oracle@wang ~] $

-- Edit parfile:

[oracle@wang ~] $vi bbed.par

Blocksize=8192

Listfile=/home/oracle/filelist.txt

Mode=edit

~

"bbed.par" [New] 3L, 60C written

[oracle@wang ~] $

-- enter the bbed window

[oracle@wang ~] $bbed parfile=/home/oracle/bbed.par

Password: blockedit

BBED: Release 2.0.0.0.0-Limited Production on Tue Nov 28 15:02:40 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

*! For Oracle Internal Use only!! *

BBED > show all

FILE# 1

BLOCK# 1

OFFSET 0

DBA 0x00400001 (4194305 1)

FILENAME / u01/app/oracle/oradata/DBdb/system01.dbf

BIFILE bifile.bbd

LISTFILE / home/oracle/filelist.txt

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 80

COUNT 512

LOGFILE log.bbd

SPOOL No

-- modify the assignment to file 4, block 527:

BED > set dba 4527 offset 0

DBA 0x0100020f (16777743 4527)

OFFSET 0

BBED > show all

FILE# 4

BLOCK# 527

OFFSET 0

DBA 0x0100020f (16777743 4527)

FILENAME / u01/app/oracle/oradata/DBdb/users01.dbf

BIFILE bifile.bbd

LISTFILE / home/oracle/filelist.txt

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 80

COUNT 512

LOGFILE log.bbd

SPOOL No

-- find the location of Dave

BBED > find / c Dave

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

Block: 527 Offsets: 8155 to 8191 Dba:0x0100020f

44617665 206c696b 65204f72 61636c65 213c0201 0c446176 65206973 20444241

210206e0 53

-- translate its data

BBED > d / v dba 4527 offset 8155

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

Block: 527 Offsets: 8155 to 8191 Dba:0x0100020f

44617665 206c696b 65204f72 61636c65 l Dave like Oracle

213c0201 0c446176 65206973 20444241 l! D / v dba 4527 offset 8156

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

Block: 527 Offsets: 8156 to 8191 Dba:0x0100020f

61766520 6c696b65 204f7261 636c6521 l ave like Oracle!

3c02010c 44617665 20697320 44424121 l p kdbr

Sb2 kdbr [0] @ 118 8072

Sb2 kdbr [1] @ 120 8051

BBED > p * kdbr [0]

Rowdata [21]

-

Ub1 rowdata [21] @ 8172 0x3c

BBED > p * kdbr [1]

Rowdata [0]

-

Ub1 rowdata [0] @ 8151 0x2c

Through row directory, we can confirm that the row header of the corresponding row record is stored in the location of offset 8172, with a value of 3c. The purpose of our find string is to compare with offset in rowdirectory. When they are close, they can be sure.

Now let's change the 3c at @ 8172 to 2c. That is, from deleted to normal

BBED > modify / x 2c offset 8172

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

Block: 527 Offsets: 8172 to 8191 Dba:0x0100020f

2c02010c 44617665 20697320 44424121 0206e053

-- apply changes:

BBED > sum apply

Check value for File 4, Block 527:

Current = 0xf25d, required = 0xf25d

BBED >

-- flush buffer cache, then query

SQL > conn / as sysdba

Connected.

SQL > alter system flush buffer_cache

System altered.

SQL > conn hr/hr

Connected.

SQL > select * from dvd

JOB

Dave is DBA!

Dave like Oracle!

The above is all the contents of the article "how to use bbed to recover the rows of delete". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.