There is a work around to get the BBED ulitility in 11g.
Step 1: Copy the necessary files
We could copy the following library files from any of the 10g version ( please ensure the source 10g and target 11g binary are in the same os flavour/architecture)
$10G_HOME/rdbms/lib/sbbdpt.o to $11G_HOME/rdbms/lib/sbbdpt.o
$10G_HOME/rdbms/mesg/bbedus.msb to $11G_HOME/rdbms/mesg/bbedus.msb
$10G_HOME/rdbms/mesg/bbedus.msg to $11G_HOME/rdbms/mesg/bbedus.msg
Step3: link the library using make command
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
Step4: invoke bbed and explore the blocks
( please dont edit blocks without oracle's advice , infact oracle would not advice us to edit .....)
oracle@xxxxxx$ $ORACLE_HOME/bin/bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sun Nov 4 11:36:46 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> help
HELP [ <bbed command> | ALL ]
BBED> help all
SET DBA [ dba | file#, block# ]
SET FILENAME 'filename'
SET FILE file#
SET BLOCK [+/-]block#
SET OFFSET [ [+/-]byte offset | symbol | *symbol ]
SET BLOCKSIZE bytes
SET LIST[FILE] 'filename'
SET WIDTH character_count
SET COUNT bytes_to_display
SET IBASE [ HEX | OCT | DEC ]
SET OBASE [ HEX | OCT | DEC ]
SET MODE [ BROWSE | EDIT ]
SET SPOOL [ Y | N ]
SHOW [ <SET parameter> | ALL ]
INFO
MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]
DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ]
PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
</Nuf>:
N - a number which specifies a repeat count.
u - a letter which specifies a unit size:
b - b1, ub1 (byte)
h - b2, ub2 (half-word)
w - b4, ub4(word)
r - Oracle table/index row
f - a letter which specifies a display format:
x - hexadecimal
d - decimal
u - unsigned decimal
o - octal
c - character (native)
n - Oracle number
t - Oracle date
i - Oracle rowid
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]
MODIFY[/x|d|u|o|c] numeric/character string
[ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
ASSIGN[/x|d|u|o] <target spec>=<source spec>
<target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
<source spec> : [ value | <target spec options> ]
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]
POP [ALL]
REVERT [ DBA | FILE | FILENAME | BLOCK ]
UNDO
HELP [ <bbed command> | ALL ]
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
CORRUPT [ DBA | FILE | FILENAME | BLOCK ]
Now let us how to usiltise this tool for some cases
I How to amend a value through this tool
1. Find out the relative file no and block no
select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno, dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where rowid='AAACfBAAGAAACTyAAY'
SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno, dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where rowid='AAACfBAAGAAACTyAAY'
2 ;
JEYA1 AAACfBAAGAAACTyAAY 6 9458
2. Find the file_name for the relative file no ( here it is 6)
SQL> select file_name,bytes from dba_data_files where file_id=6;
/data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf 2097152000
3. capture the detail in a file in the following format strictly otherwise you mey get "BBED-00310: no datafile specified" when do set list
( please use running sequence if you want to mention more than 1 datafiles in this list,
the no 1 is not the file_id for the datafile, it is just a running sequence in this filesystem, otherwise you may get
"BBED-00310: no datafile specified" erorr when you do set dba)
SQL> !cat /tmp/bbed.log
1 /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf 2097152000
4. set the listfile
BBED> set list '/tmp/bbed.log'
LISTFILE /tmp/bbed.log
5. verify if this is set properly
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf 2097152000
6.now, mark the block by setting file running sequence number,rowid_blockNumber ( taken from step 1)
BBED> set dba 1,9458
DBA 0x004024f2 (4203762 1,9458)
7. seek the offset for the search string using find ( here it is 6366)
BBED> find /c JEYA
File: /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf (1)
Block: 9458 Offsets: 6366 to 6429 Dba:0x004024f2
------------------------------------------------------------------------
4a455941 3107786d 05040101 01065359 5354454d 02c20b03 c2053e03 c2062803
c12f0c03 4d4f4e04 52616a61 1973696e 70626366 696e6562 64627031 2e73672e
<32 bytes per line>
8.dump the block (/v is for verbose mode)
BBED> dump /v dba 1,9458 offset 6366 count 64
File: /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf (1)
Block: 9458 Offsets: 6366 to 6429 Dba:0x004024f2
-------------------------------------------------------
4a455941 3107786d 05040101 01065359 l JEYA1.xm......SY
5354454d 02c20b03 c2053e03 c2062803 l STEM.Â..Â.>.Â.(.
c12f0c03 4d4f4e04 52616a61 1973696e l Á/..MON.Raja.sin
70626366 696e6562 64627031 2e73672e l abctest.sg.
9.modify the value /C for character
BBED> modify /c HARIS dba 1,9458 offset 6366
BBED-00215: editing not allowed in BROWSE mode
vow BBEDIT is in browse mode by default. now , let me change into edit mode.
BBED> SET MODE EDIT
MODE Edit
BBED> modify /c HARIS dba 1,9458 offset 6366
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf (1)
Block: 9458 Offsets: 6366 to 6429 Dba:0x004024f2
------------------------------------------------------------------------
48415249 5307786d 05040101 01065359 5354454d 02c20b03 c2053e03 c2062803
c12f0c03 4d4f4e04 52616a61 1973696e 70626366 696e6562 64627031 2e73672e
<32 bytes per line>
10.Dump the file to see the changes
BBED> dump /v dba 1,9458 offset 6366 count 64
File: /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf (1)
Block: 9458 Offsets: 6366 to 6429 Dba:0x004024f2
-------------------------------------------------------
48415249 5307786d 05040101 01065359 l HARIS.xm......SY
5354454d 02c20b03 c2053e03 c2062803 l STEM.Â..Â.>.Â.(.
c12f0c03 4d4f4e04 52616a61 1973696e l Á/..MON.Raja.sin
70626366 696e6562 64627031 2e73672e l abctest.sg.
11. Check the block checksum and apply
BBED> sum dba 1,9458
Check value for File 1, Block 9458:
current = 0x4dca, required = 0x26c6
BBED> sum dba 1,9458 apply
Check value for File 1, Block 9458:
current = 0x26c6, required = 0x26c6
12. verify the data from another sqlplus window Please flush the buffer_cache to see the latest changes
SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno, dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where rowid='AAACfBAAGAAACTyAAY';
JEYA1 AAACfBAAGAAACTyAAY 6 9458
SQL> alter system flush buffer_cache;
System altered.
SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno, dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where rowid='AAACfBAAGAAACTyAAY';
HARIS AAACfBAAGAAACTyAAY 6 9458
II . How to restore a deleted record by updating directly the block using BBED tool.
(A simple way of recovering the deleted record is the flashback query option). This method is just
explore the efficiency of BBED tool.
1. Simulate the deletion
SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno, dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where ROWID='AAACfBAAMAAAj8nABV';
DB_NAME ROWID FNO BNO
-------------------- ------------------ ---------- ----------
JEYA AAACfBAAMAAAj8nABV 12 147239
SQL> delete from space_mon where ROWID='AAACfBAAMAAAj8nABV';
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> alter system flush BUFFER_CACHE
2 ;
System altered.
2. Find out the segment header details
SQL> select HEADER_FILE,HEADER_BLOCK,BLOCKS from dba_segments where SEGMENT_NAME = 'SPACE_MON';
HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
12 147212 39936
3. dump the whole segment to find out the deleted rows but still stay in the block
alter system dump datafile 12 block min 147212 block max 187148;
4.open the trace file and check for the CC:3 which is for deleted records
tab 0, row 0, @0x1f79 ---> hexa block number ( convert into decimal by select to_number('1f79','xxxx') from dual )
4 tl: 31 fb: --H-FL-- lb: 0x0 cc: 3
5. create a listfile with the necessary datafiles
BBED> !cat /tmp/bbed.log
1 /data/oracle/MYTEST/data01/MYTEST_perf_mon02.dbf 2097152000
2 /data/oracle/MYTEST/data01/MYTEST_perf_mon01.dbf 2097152000
6. set the listfile
BBED> set list '/tmp/bbed.log'
LISTFILE /tmp/bbed.log
7. mark the DBA which was found at step 4
BBED> set dba 2,147239
DBA 0x00823f27 (8535847 2,147239)
BBED> find /c JEYA
File: /data/oracle/MYTEST/data01/MYTEST_perf_mon01.dbf (2)
Block: 147239 Offsets: 1087 to 1150 Dba:0x00823f27
------------------------------------------------------------------------
4a455941 07786b0a 10010101 10444254 4d5f4c41 5247455f 494e4445 5802c229
03c21e21 03c20b45 03c14a1f 05204556 45200620 52616a61 20092064 62747062
<32 bytes per line>
8. dump the block with little head of the offset ( 1087 is the exact offset) to find out the row header.
'<' sympbol shows that the row header marker which is 1084
BBED> dump /v dba 2,147239 Offset 1080
File: /data/oracle/MYTEST/data01/MYTEST_perf_mon01.dbf (2)
Block: 147239 Offsets: 1080 to 1143 Dba:0x00823f27
-------------------------------------------------------
03c2033c 010a044a 45594107 786b0a10 l .Â.<...JEYA.xk..
01010110 4442544d 5f4c4152 47455f49 l ....DBTM_LARGE_I
4e444558 02c22903 c21e2103 c20b4503 l NDEX.Â).Â.!.Â.E.
c14a1f05 20455645 20062052 616a6120 l ÁJ.. EVE . Raja
9. Confirm if it is row header marker
BBED> p kdbr
sb2 kdbr[0] @118 5652
sb2 kdbr[1] @120 5735
sb2 kdbr[2] @122 5814
sb2 kdbr[3] @124 5892
sb2 kdbr[4] @126 5966
sb2 kdbr[5] @128 6043
sb2 kdbr[6] @130 6121
sb2 kdbr[7] @132 6198
sb2 kdbr[8] @134 6286
sb2 kdbr[9] @136 6374
sb2 kdbr[10] @138 6460
sb2 kdbr[11] @140 6546
sb2 kdbr[12] @142 6635
sb2 kdbr[13] @144 6723
sb2 kdbr[14] @146 6800
sb2 kdbr[15] @148 6872
sb2 kdbr[16] @150 6946
sb2 kdbr[17] @152 7023
sb2 kdbr[18] @154 7102
sb2 kdbr[19] @156 7179
sb2 kdbr[20] @158 7258
sb2 kdbr[21] @160 7333
sb2 kdbr[22] @162 7405
sb2 kdbr[23] @164 7480
sb2 kdbr[24] @166 7557
sb2 kdbr[25] @168 7630
sb2 kdbr[26] @170 7709
sb2 kdbr[27] @172 7786
sb2 kdbr[28] @174 7864
sb2 kdbr[29] @176 7941
sb2 kdbr[30] @178 8012
sb2 kdbr[31] @180 1054
sb2 kdbr[32] @182 1130
sb2 kdbr[33] @184 1211
sb2 kdbr[34] @186 1290
sb2 kdbr[35] @188 1364
sb2 kdbr[36] @190 1440
sb2 kdbr[37] @192 1523
sb2 kdbr[38] @194 1606
sb2 kdbr[39] @196 1689
sb2 kdbr[40] @198 1771
sb2 kdbr[41] @200 1847
sb2 kdbr[42] @202 1914
sb2 kdbr[43] @204 1983
sb2 kdbr[44] @206 2049
sb2 kdbr[45] @208 2120
sb2 kdbr[46] @210 2191
sb2 kdbr[47] @212 2260
sb2 kdbr[48] @214 2331
sb2 kdbr[49] @216 2402
sb2 kdbr[50] @218 2473
sb2 kdbr[51] @220 2544
sb2 kdbr[52] @222 2615
sb2 kdbr[53] @224 2686
sb2 kdbr[54] @226 2757
sb2 kdbr[55] @228 2828
sb2 kdbr[56] @230 2900
sb2 kdbr[57] @232 2972
sb2 kdbr[58] @234 3044
sb2 kdbr[59] @236 3115
sb2 kdbr[60] @238 3187
sb2 kdbr[61] @240 3259
sb2 kdbr[62] @242 3331
sb2 kdbr[63] @244 3401
sb2 kdbr[64] @246 3465
sb2 kdbr[65] @248 3532
sb2 kdbr[66] @250 3596
sb2 kdbr[67] @252 3665
sb2 kdbr[68] @254 3732
sb2 kdbr[69] @256 3800
sb2 kdbr[70] @258 3865
sb2 kdbr[71] @260 3931
sb2 kdbr[72] @262 4000
sb2 kdbr[73] @264 4071
sb2 kdbr[74] @266 4146
sb2 kdbr[75] @268 4215
sb2 kdbr[76] @270 4290
sb2 kdbr[77] @272 4357
sb2 kdbr[78] @274 4419
sb2 kdbr[79] @276 4480
sb2 kdbr[80] @278 4546
sb2 kdbr[81] @280 4609
sb2 kdbr[82] @282 4684
sb2 kdbr[83] @284 4758
sb2 kdbr[84] @286 4831
sb2 kdbr[85] @288 983 ====> close to 1084)
sb2 kdbr[86] @290 4978
sb2 kdbr[87] @292 5052
sb2 kdbr[88] @294 5124
sb2 kdbr[89] @296 5195
sb2 kdbr[90] @298 5259
sb2 kdbr[91] @300 5321
sb2 kdbr[92] @302 5382
sb2 kdbr[93] @304 5447
sb2 kdbr[94] @306 5514
sb2 kdbr[95] @308 5577
BBED> p *kdbr[85]
BBED> rowdata[0]
----------
ub1 rowdata[0] @1083 0x3c =====> confirm it is deleted record
10. change the value to 2c
BBED>
BBED> modify /x 2c offset 1083
File: /data/oracle/MYTEST/data01/MYTEST_perf_mon01.dbf (2)
Block: 147239 Offsets: 1083 to 1146 Dba:0x00823f27
------------------------------------------------------------------------
2c010a04 4a455941 07786b0a 10010101 10444254 4d5f4c41 5247455f 494e4445
5802c229 03c21e21 03c20b45 03c14a1f 05204556 45200620 52616a61 20092064
<32 bytes per line>
11. verify the changes
BBED> p *kdbr[85]
rowdata[0]
----------
ub1 rowdata[0] @1083 0x2c
12. check sum and apply the changes
BBED> sum dba 2,147239
Check value for File 2, Block 147239:
current = 0x65e3, required = 0x65f3
BBED> sum dba 2,147239 apply
Check value for File 2, Block 147239:
current = 0x65f3, required = 0x65f3
13. verify from the sqlplus
SQL> alter system checkpoint;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno, dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where ROWID='AAACfBAAMAAAj8nABV';
no rows selected
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> select db_name, rowid, dbms_rowid.rowid_relative_fno(ROWID) fno, dbms_rowid.rowid_block_number(ROWID) bno FROM space_mon where ROWID='AAACfBAAMAAAj8nABV';
DB_NAME ROWID FNO BNO
-------------------- ------------------ ---------- ----------
JEYA AAACfBAAMAAAj8nABV 12 147239
II. How to restore datafile which is restored from a old file where SCN is out of sync.
kscnbas – last change SCN
kcvcptim – time of the last change
kcvfhcpc – checkpoint count
kcvfhccc – a checkpoint checker value, which is one less than kcvfhcpc
BBED>p kcvfh --list out the structure of a datafile
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6:
'/data/oracle/MYTEST/data/MYTEST_data02.dbf'
column checkpoint_change# format 99999999999999999
SQL> select file#, checkpoint_change#, to_char(checkpoint_time, 'dd/mm/yyyy hh24:mi:ss') from v$datafile;
FILE# CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_
---------- ------------------ -------------------
6 6977 25/10/2012 11:41:42 =====> target datafile.
SQL> select file#, change#, to_char(time, 'dd/mm/yyyy hh24:mi:ss') from v$recover_file;
FILE# CHANGE# TO_CHAR(TIME,'DD/MM
---------- ---------- -------------------
6 6602 25/10/2012 11:42:42
===== Open the file 6 and 1 ( whichever datafile has latest SCN)
Target DATAFILE 6 - need to be recovered. ( 6 should be running sequnence in the listfile)
BBED> p dba 6,1 kcvfhckp
struct kcvfhckp, 36 bytes @142
struct kcvcpscn, 8 bytes @142
ub4 kscnbas @142 0x000019ca => SCN
ub2 kscnwrp @145 0x0000
ub4 kcvcptim @149 0x240bcfef => timestamp
BBED> p dba 6,1 kcvfhcpc
ub4 kcvfhcpc @178 0x00000004 => Checkpoint count
BBED> p dba 6,1 kcvfhccc
ub4 kcvfhccc @186 0x00000003 => Checkpoint counter which is less than checkpoint count
Source datafile 1
BBED> p dba 1,1 kcvfhckp
struct kcvfhckp, 36 bytes @142
struct kcvcpscn, 8 bytes @142
ub4 kscnbas @142 0x00001b51
ub2 kscnwrp @145 0x0000
ub4 kcvcptim @149 0x240bd56e
BBED> p dba 1,1 kcvfhcpc
ub4 kcvfhcpc @178 0x00000012
BBED> p dba 1,1 kcvfhccc
ub4 kcvfhccc @186 0x00000008
#select checkpoint_change# from v$database
modify /x 0x00001b51 dba 6,1 offset 142
modify /x 0x240bd56e dba 6,1 offset 149
modify /x 0x00000012 dba 6,1 offset 178
modify /x 0x00000008 dba 6,1 offset 186
BBED>sum dba 1,1
------
BBED> sum dba 1,1 apply
Check value for File 6, Block 1:
current = 0xc097, required = 0xc097
================================
No comments:
Post a Comment