Thursday, July 19, 2007


Block Browser and Editor on Linux with Oracle 10g R2 database



Article [1] has been written in French, contains two interesting examples .
The posting bellow is supposed to be a translation final part from [1] to English,
dealing with other tables from sample schemas loaded during standard installation.
However , the way to obtain oracle's file number and block number for particular
record from selected table is different from [1].
We also focus attention at index corruption after changing field's value or record
recovery by BBED.


1.Modifying field's value for particular record in hr.departments table.


Get file number and header block for table hr.departments:-



select owner,segment_name,header_file,header_block,blocks
from dba_segments
where owner='HR' and segment_name='DEPARTMENTS';


OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
------- ------------------- -------------------- ---------------------- -------------
SCOTT EMP 5 55 8




Getting block number of record, supposed to be changed, with dbms_rowid package:-




Searching for offset of the pattern:-




Run "modify /c Abcdefg" to replace "Support" :-




Update block's checksum:-




Verify data with select statement:-



SQL> alter system flush buffer_cache;




To force Oracle perform reading block from disk device,not from buffer cache.




2.Recovering deleted record from scott.emp.


Get file number and header block for table scott.emp:-



select owner,segment_name,header_file,header_block,blocks
from dba_segments
where owner='SCOTT' and segment_name='EMP';


OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
------ ----------- ------------- -------------- ------
SCOTT EMP 4 27 8




Get the block number contains record with ename='JAMES' like in previous example.
Then search for word 'JAMES' to get it's offset.



Make dump with offset=offset-16 and look for first byte of the record (0x2c)
Watch the value of first byte of record when it got deleted.




First byte of deleted record appears to be changed to hexadecimal 0x3c.
The intend is to put 0x2c back on it's place as the first byte of deleted record.



SQL> delete from emp where ename='JAMES';

1 row deleted.

SQL> commit;

Commit complete.




Run "modify /x 2c" in bbed:-




Update block's checksum:-




Restart Oracle instance:-



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 83887720 bytes
Database Buffers 192937984 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.




Or just:-



SQL> alter system flush buffer_cache;




To force Oracle perform reading block from disk device,not from buffer cache.


Check for undeleted record:-



SQL> conn scott/tiger@orcl;
Connected.

SQL> select * from emp where ename='JAMES';

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7900 JAMES CLERK 7698 03-DEC-81 950
30

SQL> select * from emp where empno=7900;
no rows selected




At this point it became quite clear , that we have to recreate any indexes on table,
which records have been recovered by BBED.They are no longer valid.
Same issue will be in place regarding indexes based on columns have been modified by BBED.
Recreate index on empno field of emp table:-



SQL> alter table emp drop constraint PK_EMP;

Table altered.

SQL> alter table emp add constraint PK_EMP primary key(empno);

Table altered.

SQL> select * from emp where empno=7900;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7900 JAMES CLERK 7698 03-DEC-81 950
30




References.


1.http://mbouayoun.developpez.com/bbed/
2.http://orafaq.com/papers/dissassembling_the_data_block.pdf

Sunday, July 15, 2007


Block browse and edit utility (BBED) in Oracle 10g R2 on Linux



First login as oracle to compile bbed:



$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
$ls -la bbed
$ cp $ORACLE_HOME/rdbms/lib/bbed $ORACLE_HOME/bin




Use any hexadecimal editor on Linux to discover password
needed to start bbed:-



[oracle@ServerORCL orcl]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sun Jul 15 14:17:39 2007

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

************* !!! For Oracle Internal Use only !!! ***************

BBED> show
FILE# 0
BLOCK# 1
OFFSET 0
DBA 0x00000000 (0 0,1)
FILENAME
BIFILE bifile.bbd
LISTFILE
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No




Next run as sysdba:-




SQL> select owner,segment_name,header_file,header_block,blocks
2 from dba_segments
3 where owner='HR' and segment_name='DEPARTMENTS';

OWNER
------------------------------
SEGMENT_NAME
-----------------------------------
HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
HR
DEPARTMENTS
5 51 8




Tune BBED properly to perform block corruption for hr.departments table.



BBED> set listfile '/home/oracle/bbed.log'
LISTFILE /home/oracle/bbed.log
BBED> set blocksize 8192
BLOCKSIZE 8192
BBED> set filename '/u02/oradata/orcl/example01.dbf'
BBED> set dba 5,52
BBED> set mode edit
BBED> show
FILE# 5
BLOCK# 52
OFFSET 0
DBA 0x01400034 (20971572 5,52)
FILENAME /u02/oradata/orcl/example01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/bbed.log
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 64
LOGFILE log.bbd
SPOOL No


[oracle@ServerORCL ~]$ cat bbed.log
1 /u02/oradata/orcl/system01.dbf 503316480
2 /u02/oradata/orcl/undotbs01.dbf 41943040
3 /u02/oradata/orcl/sysaux01.dbf 262144000
4 /u02/oradata/orcl/users01.dbf 5242880
5 /u02/oradata/orcl/example01.dbf 104857600




Replace second block of table



BBED> set offset 0
OFFSET 0

BBED> copy dba 5,10 to dba 5,52
File: /u02/oradata/orcl/example01.dbf (5)
Block: 52 Offsets: 0 to 63 Dba:0x01400034
------------------------------------------------------------------------
21a20000 0a004001 0ed00600 00000104 414e0000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000




Restart Oracle instance.



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 75499112 bytes
Database Buffers 201326592 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.




Connect as HR to get block corruption error
when SELECT * FROM DEPARTMENTS.
Then start rman:-



RMAN> connect target /
RMAN> run {BACKUP VALIDATE DATABASE;}
Starting backup at 15-JUL-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u02/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/u02/oradata/orcl/example01.dbf
input datafile fno=00002 name=/u02/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/orcl/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 15-JUL-07




Run query as SYSDBA



select * from V$backup_corruption;


RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK#
---------- ---------- ---------- ---------- ---------- ---------- ----------
BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO
---------- ------------------ --- ---------
7 628005346 628005315 9 1 5 52
1 0 YES CORRUPT




Proceed with RMAN block recovery



RMAN> run {blockrecover datafile 5 block 52;}

Starting blockrecover at 15-JUL-07
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_07_15/
o1_mf_nnndf_TAG20070715T105801_39mkctc7_.bkp
channel ORA_DISK_1: restored block(s) from backup piece 1 piece
handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_07_15/
o1_mf_nnndf_TAG20070715T105801_39mkctc7_.bkp
tag=TAG20070715T105801
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:07
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 15-JUL-07




Connect as HR to perform SELECT * FROM DEPARTMENTS without errors.