Home » RDBMS Server » Server Administration » MGMT_JOB  () 1 Vote
MGMT_JOB [message #467281] Fri, 23 July 2010 03:19 Go to next message
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

Report message to a moderator

Re: MGMT_JOB [message #467283 is a reply to message #467281] Fri, 23 July 2010 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I should say you would FIRST plan a good backup and recovery policy.

Regards
Michel

Report message to a moderator

Re: MGMT_JOB [message #467285 is a reply to message #467283] Fri, 23 July 2010 03:26 Go to previous messageGo to next message
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..

Report message to a moderator

Re: MGMT_JOB [message #467289 is a reply to message #467285] Fri, 23 July 2010 03:39 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ Any Idea which part of the data you have lost?
2/ if few then you can recover that much from by log mining!!

Report message to a moderator

Re: MGMT_JOB [message #467292 is a reply to message #467289] Fri, 23 July 2010 03:42 Go to previous messageGo to next message
prashanthgs
Messages: 89
Registered: May 2005
Location: chennai
Member
Not sure about that..Know only the block number in the datafile which got corrupted.

datafile 6 block 33275 ;

Report message to a moderator

Re: MGMT_JOB [message #467293 is a reply to message #467289] Fri, 23 July 2010 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
@rahulvb

Why this topic/answer is worth the 5 stars you gave?

Regards
Michel

Report message to a moderator

Re: MGMT_JOB [message #467295 is a reply to message #467292] Fri, 23 July 2010 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
prashanthgs wrote on Fri, 23 July 2010 10:42
Not 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 #467296 is a reply to message #467281] Fri, 23 July 2010 03:54 Go to previous messageGo to next message
prashanthgs
Messages: 89
Registered: May 2005
Location: chennai
Member
That is MGMT_JOB table. How we can perform recover using logmining.

Report message to a moderator

Re: MGMT_JOB [message #467297 is a reply to message #467296] Fri, 23 July 2010 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Database Utilities
Chapter 17 Using LogMiner to Analyze Redo Log Files

Regards
Michel

Report message to a moderator

Re: MGMT_JOB [message #467299 is a reply to message #467293] Fri, 23 July 2010 04:03 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Michel Cadot wrote on Fri, 23 July 2010 03:43
@rahulvb

Why this topic/answer is worth the 5 stars you gave?

Regards
Michel


Because I think this is one of the very interesting/ rare topic/Post.

Report message to a moderator

Re: MGMT_JOB [message #467306 is a reply to message #467281] Fri, 23 July 2010 04:28 Go to previous messageGo to next message
prashanthgs
Messages: 89
Registered: May 2005
Location: chennai
Member
Any way we can found out that able to recover till this date or scn (options)

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
John Watson
Messages: 8960
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 indexes
select 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.

Report message to a moderator

Previous Topic: refresh asa_recommendation
Next Topic: How do we check oracle database uses java or not
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 29 08:43:59 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.