Search This Blog

Monday, August 1, 2011

Explain Data Block Addres

+-------------------------------------------------------------------------------------------+
| |
| Explain Data Block Address |
| |
+-------------------------------------------------------------------------------------------+
| Techonology : Oracle database 10.2.0 |
+-------------------------------------------------------------------------------------------+
| Created : 29/7/2007 |
+-------------------------------------------------------------------------------------------+
| Document Scope: |
The scope of the document is to understand and use Data Block Address .
The Second argument of ORA-600 [3374] corresponds to the expected Data Block Address.
They are 11206682 and 2617431000. The blocks in these DBA are corrupted.
Let's convert DBA to corresponding (file#,block#)

Example1 :ORA-00600: internal error code, arguments: [3374], [16777298], [825371952],[875376697], [11824], [], [], []

SQL> SELECT dbms_utility.data_block_address_block(16777298)
2 FROM dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777298)
-----------------------------------------------
82

SQL>
SQL> SELECT dbms_utility.data_block_address_file(16777298)
2 FROM dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777298)
----------------------------------------------
4

The corruption block is the block_id 82 in file_id 4. To find which database objects
belong the corrupted block,use the following query.

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 4
and 82 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME SEGMENT_TYPE OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
USERS TABLE SYS
T3


The example below , shows how to find the data block addreess base on file_id and some block_id.


SQL> select block_id,block_id + blocks - 1,relative_fno,file_id from dba_extents where segment_name='T3';

BLOCK_ID BLOCK_ID+BLOCKS-1 FILE_ID
---------- ----------------- ----------
81 88 4

SQL> SELECT dbms_utility.make_data_block_address(4,82)
2 FROM dual;

DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(4,82)
------------------------------------------
16777298


Relate Note :
Ref : http://sysdba.wordpress.com/2006/04/05/how-to-check-for-and-repair-block-corruption-with-rman-in-oracle-9i-and-oracle-10g/