Fractured block in Oracle

Fractured Block in Oracle

Fractured block is a block in which header and footer are not consistent. If your database has any fractured block then you RMAN backup is failed.

1. For checking the Oracle Database from corruption/Fractured block.


From RMAN:
Rman> backup validate check logical database;

From OS:
dbv file='E:\oracle\oradata\sysauz01.dbf'

2. Following block show the block id,file id, type of corruption after RMAN command

Select * from v$database_block_corruption;

3. Find the object_name,object_type from following view:

select * from dba_extents where file_id = &DATA_FILE_ID and &CORRUPTED_BLOCK_ID between block_id AND block_id + blocks - 1;

Note: IF no object is part of corrupted/Fractured block upper query will return no rows. We have following query to verify it.

Note: Not need to specify the blockid and fileid in following query:

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id = c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id = c.block#
order by file#, corr_start_block#;

4. If you find fractured block in free space then you follow oracle(Doc ID 336133.1)
According to this doc id, for remove the fractured block in free space. you need
to write on that block then it will automatically fixed the issue.

1. After run the RMAN validate command.

Rman> backup validate check logical database;

2. Having fractured block in sysaux file.

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 FAILED 0 23534 85762 353506490

File Name: E:\ORACLE\ORADATA\IC\SYSAUX01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 18166
Index 1 16146
Other 0 27914

3. On checking with above query 3 found data block is empty/freelist

4. create a table with Scott user
Note: Not create with sys or system users

create table s(
n number,
c varchar2(4000)
) nologging tablespace sysaux pctfree 99;
Table created.
Note: Mentioned tablespace which having fractured block

5. Create trigger with Scott user
Note: enter the corrupted block id and file id

CREATE OR REPLACE TRIGGER corrupt_trigger
AFTER INSERT ON scott.s
REFERENCING OLD AS p_old NEW AS new_p
FOR EACH ROW
DECLARE
corrupt EXCEPTION;
BEGIN
IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
RAISE corrupt;
END IF;
EXCEPTION
WHEN corrupt THEN
RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
END;
/
Enter value for blocknumber: 77810
old 8: IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
new 8: IF (dbms_rowid.rowid_block_number(:new_p.rowid)=77810)
Enter value for filenumber: 2
old 9: and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
new 9: and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=2) THEN

Trigger created.

6. Start file the tablespace having fractured block

BEGIN
FOR i IN 1..1000000000 LOOP
INSERT /*+ APPEND */ INTO scott.s select i, lpad('REFORMAT',3092, 'R') from dual;
commit ;
END LOOP;
END;
/

Following error occurred if you overwrite the corrupted block otherwise start repeat the insert operation until you got following error, Its depend upon size of tablespace

BEGIN
*
ERROR at line 1:
ORA-20000: Corrupt block has been formatted
ORA-06512: at "SCOTT.CORRUPT_TRIGGER", line 10
ORA-04088: error during execution of trigger 'SCOTT.CORRUPT_TRIGGER'
ORA-06512: at line 3

7. After getting error rerun the validate command for rman for verify

Rman> backup validate check logical database;

Advertisements

One thought on “Fractured block in Oracle

  1. Pingback: Identify the Corruption using RMAN | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s