MGMT_JOB [message #467281] |
Fri, 23 July 2010 03:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
prashanthgs
Messages: 89 Registered: May 2005 Location: chennai
|
Member |
|
|
Hi,
In one of our grid qa database, one of the block got corrupted in SYSMAN.MGMT_JOB table.
As we dont have any good backups, as of now we recovered that table by skipping the corrupt blocks using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS package.
As below is not a permanaent solution (as Grid wont populate that db information present in the corrupted block), we are planning to recreating this table.
How we can perform this? Will there be any constraint issues and any options to skip that corrupted blocks in this new table.
oracle 10g, AIX
Thanks
Prashanth
|
|
|
|
Re: MGMT_JOB [message #467285 is a reply to message #467283] |
Fri, 23 July 2010 03:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
prashanthgs
Messages: 89 Registered: May 2005 Location: chennai
|
Member |
|
|
Thats True Michael. But Unfortunately we are unable to get the full QA Backup restore from Tape. Other options please..
|
|
|
|
|
|
Re: MGMT_JOB [message #467295 is a reply to message #467292] |
Fri, 23 July 2010 03:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
prashanthgs wrote on Fri, 23 July 2010 10:42Not sure about that..Know only the block number in the datafile which got corrupted.
datafile 6 block 33275 ;
Query DBA_EXTENTS to know to which object the block belongs:
select owner, segment_name, partition_name, segment_type
from dba_extents
where file_id = 6
and 33275 between block_id and block_id+blocks-1
/
Regards
Michel
[Updated on: Fri, 23 July 2010 03:45] Report message to a moderator
|
|
|
|
|
|
|
Re: MGMT_JOB [message #467332 is a reply to message #467306] |
Fri, 23 July 2010 08:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You could identify the rows that are in the corrupted block, and re-insert them. This query shows that many of the columns are in the table's indexesselect index_name,column_name from dba_ind_columns where index_owner='SYSMAN' and table_name='MGMT_JOB'; so if you query the table and save the results, projecting only those columns and using the INDEX_JOIN hint and nominating the four indexes, you will get the important columns of all rows, including the lost ones. Then rebuild the indexes, subtract the results of a full table scan from the saved result set, and insert the missing rows. Easy!
Of course, I don't know how critical the columns are that are not duplicated in index keys are. Or if it even worth the trouble.
|
|
|