Check objects for hard disk corruption in datafile for oracle database

Check objects for hard disk corruption in datafile for oracle database

Suppose hard disk block is corrupted and your data file is using that block then you want to resize your data file to remove the block from that data file. The following query will provide you the list of objects for moving to another tablespace.

Put your block no as highlighted got from alert log or V$database_block_corruption query with data file id.

Query provide you the list of object present upto that block. so you can move this object to another tablespace to override from harddisk corruption.

select (OWNER ||'.'|| SEGMENT_NAME ||'- OBJECT TYPE = '||SEGMENT_TYPE) ONAME, (block_id+blocks-1), (866688 + blocks - 1) * 8192 "objectblockpresent" from dba_extents where file_id = 14 and (block_id+blocks-1) > 866688 order by 2;

After that you will resize the datafile upto size which release the corrupted block back to Operating system.

Then set the autoextend parameter of datafile to off.

Start moving the object to another tablespace.

Advertisements

One thought on “Check objects for hard disk corruption in datafile for oracle database

  1. Pingback: Check objects for hard disk corruption in datafile for oracle database — Smart way of Technology – Tech Stuff

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