Solved

corrupt blocks in in sysman schema and can not drop or rebuild segments

Posted on 2009-06-30
30
1,416 Views
Last Modified: 2012-05-07
we have found few corrupt blocks;they came out to be few indexes in sysman when I tried to  
disable the them i got
alter table sysman.MGMT_DB_INIT_PARAMS_ECM disable primary key;
i got
ORA-25188: cannot drop/disable/defer the primary key constraint for index-organized tables or
sorted hash cluster

also alter table move is not working please advise
0
Comment
Question by:it-rex
  • 10
  • 10
  • 10
30 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 24748878
RMAN and DBMS_REPAIR can help

http://www.remote-dba.cc/teas_aegis_util19.htm

dbms_repair.FIX_CORRUPT_BLOCKS (
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL,
object_type IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
repair_table_name IN VARCHAR2 DEFAULT 'REPAIR_TABLE',
flags IN BINARY_INTEGER DEFAULT NULL,
fix_count OUT BINARY_INTEGER);

" schema_name - The name of the schema containing the object with corrupt blocks.
|
" object_name  The name of the object needing repair.

" partition_name  The name of the partition or subpartition to process. If none is specified and the object is partitioned, all partitions will be processed.

" object_type - Either table_object or index_object as specified as an enumeration.

" repair_table_name  The name of the repair table.

" flags  Not used.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24748890
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 250 total points
ID: 24748918
Are you confident that the corruption is simply a few logical blocks and not an underlying disk problem?

Do you have any RMAN backups?

If not, any indexes / IOTs will really not be recoverable.

If so, you can drop and recreate the EM repository.

emca -deconfig dbcontrol db -repos drop

emca -config dbcontrol db -repos recreate


However,  sometimes there are more steps, so I advise you to check Metalink, there are detailed articles on this.

See metalink article 278100.1
0
 
LVL 11

Author Comment

by:it-rex
ID: 24749401
schwertner: would you please give some insight how to filling for the proc parameters

dbms_repair.FIX_CORRUPT_BLOCKS (
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL,
object_type IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
repair_table_name IN VARCHAR2 DEFAULT 'REPAIR_TABLE',
flags IN BINARY_INTEGER DEFAULT NULL,
fix_count OUT BINARY_INTEGER);

i tried but it gave me error messages especially fix_count
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24749498
I'll leave you in schwertner's hands but please don't ignore my post above.

1) Blocks are often not recoverable without a backup, repairing does not bring them back, usually. It just markes them to be ignored, but you still lose blocks/data.

2) SYSAUX can be re-created without adversely affecting the core database, although you will lose historical AWR/ADDM reports.

Good luck. (unsubscribing)

0
 
LVL 11

Author Comment

by:it-rex
ID: 24749524
is it possible to issue the command blockrecover corruption list restore until time 'sysdate-5'
while the databse is running and without affecting the database incarnation...
0
 
LVL 11

Author Comment

by:it-rex
ID: 24749554
mrjoltcola: please stay around
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24750112
Hi I refreshed to check before closing the question. Yes, you can recover without affecting incarnation, but what I have done is simply restore the sysaux tablespace and recover it fully. Why do you need until time?

You can offline sysaux while the DB is open, restore / recover tablespace sysaux, then online it again.

Assuming you have all archivelogs.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24750142
Before doing ANYTHING though, make sure to take a FULL backup right now, including archivelogs.

RMAN> backup database plus archivelog tag 'BADBLOCKS';

That way you'll have a backup to go back to.

Then you can try restore until time (to get the previous backup), then full recover. (Don't use plain restore, or it'll get the backup you just took, not what you want)
0
 
LVL 11

Author Comment

by:it-rex
ID: 24750385
the problem is I have used the latest backup I have to clone the database to adiffernt host and it has the same problem with the corrub block as of friday 4 am as we had the whole server crashed"and it looks like it has caused this.
since Friday we can not take any backups as backups fail when u have corrupy blocks untill you increase the max corrupt blocks parmater..
so my only option is to use backups of thursday 4am as they are the only option .

so the question is can i use  blockrecover corruption list restore  while the db running and not to affect the incarnation and how this happens;
as i do not want to go back to thursday morning just recover these bad blocks
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24750648
Yes, recovering the sysaux tablespace or the datablocks is doable while the DB is up, see above, you still have to take SYSAUX tablespace offline before doing so.

If this were the SYSTEM tablespace, you'd have to actually shutdown and go to mount mode, but SYSAUX is possible to take offline without downing the DB.
0
 
LVL 11

Author Comment

by:it-rex
ID: 24751243
agin can I restore the corrupt blocks to 5 days ago without affecting the db incarnation.
as I know I can not do a a tablespace restoration to a point in time taht is why we do TSPITR in a differnt instance;
but here doing the blockrestoration is this different???

by the way as per metalink I can not use dbms_repair for an IOT.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24752290
There are some considerations in your not good case.
But please be critical to my advises because you
hit a hard problem.

If I were you i will immediatelly try to Export the nonsystem schemas and will try to reinstall Oracle.
If you ask me way then my point will be that RMAN is a closed system and it is a hard task to predict what
will RMAN do or not do. So instead traveling in the RMAN jungle I will chose the radical solution

The next advice is informative. SYSMAN plays auxiliary role in the Oracle functionality and I recal
that in the times of Oracle 10g R1 we simply recreate sysman like
http://www.idevelopment.info/data/Oracle/DBA_tips/Enterprise_Manager/OEM_3.shtml
So the hint is to figure out the scripts that drop and recreate sysman somewhere in the net.

Finally I guess you system is Linux based (excuse if I am not correct).
I hit many times index corruptions in Linux systems.
Linux file systems are worst amateur thing in the IT world and Oracle now
is trying (Oracle Linux kernel division) to enhance this functionality putting
strong check mechanism in the Linux kernel. But possibly this affect other OS.
You have to investigate what is the reason for bad blocks - OS, cheap disks, Oracle bug
You have also to make often backups of your instance.

I am very sorry that I cannot point you the 'silver bullet' - the button that will repair your
blocks .....
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24754560
>>So the hint is to figure out the scripts that drop and recreate sysman somewhere in the net.

Or you can use Metalink and do it per the Oracle supported way, it usually works for me just fine. I posted the Metalink note above (assuming you have support).


>>agin can I restore the corrupt blocks to 5 days ago without affecting the db incarnation.

Blockrecover uses the archivelogs. You need the archive logs to roll forward to bring it up to date. You cannot blockrecover TO a point in the past, blockrecover only works for full recovery.

I am not sure what you mean by "affecting the db incarnation" as you are simply applying redo logs.
0
 
LVL 11

Author Comment

by:it-rex
ID: 24756353
Guys sysman is the schema but the phyiscal location of the corrupt blocks is in sysaux tablespace ;
as I have cloned the db to a new box and I still have the problem I have dropped the sysman schema.
and I have checked the corrupt blocks they still exist ;the only difference is that when you rum metalink script to identify the affected segments they are not associated with any segments;
after I have created the em rep again they resurected from death...
please help!!!!!!!!!!!!!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24756396
So have you tried blockrecovery or not? I am confused, I thought you were choosing that route?

I would first try restore/recover of SYSAUX as suggested above, then if that fails due to not having old enough backup, then I would agree with schwertner, recreate the database and move the data via import.

You can also create a new empty database and them transport tablespaces. See Metalink note for some more ideas: 361172.1
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24756510
Recreate the database via Export/Import.
Do not enter into the RMAN gymnastic exercises.
keep it simple.
0
 
LVL 11

Author Comment

by:it-rex
ID: 24756693
the backups we have of tus ,mon.sun,sat,fri,thurs  all are backups including the corrupt blocks the only ones with non corrupt blocks are from wed;
If I try to reocver sysaus to that day I will lose a whole week of work and they will not accept that here.
and after each backup delete input is issued and the archives are deleted.....

export import for this huge DB will be very hard ;we need another way

thank you so much
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24756907
Seems it is logical corruption.
Try to recover using dbms_repair or RMAN repair
0
 
LVL 11

Author Comment

by:it-rex
ID: 24757387
would you guys please soem one answer my Q


the backups we have of tus ,mon.sun,sat,fri,thurs  all are backups including the corrupt blocks the only ones with non corrupt blocks are from wed;
If I try to reocver sysaus to that day I will lose a whole week of work and they will not accept that here.
and after each backup delete input is issued and the archives are deleted.....

export import for this huge DB will be very hard ;we need another way
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24759205
>>would you guys please soem one answer my Q

I already answered this (http:#24754560). Block recover will recover by applying the archive logs to an image. So it will restore from the older image, then recover by applying logs. If the corruption was logical, it may exist in the logs, but it is probable that it would fix it but you wont find out until you try!

I know you are frustrated, but I am also, we have been suggesting you try a recovery since yesterday and I cannot tell whether you have tried it or not.

I hope it turns out well.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24760930
Oracle 9i new RMAN features:

Obtain the datafile numbers and block numbers for the corrupted blocks.
pically, you obtain this output from the standard output, the alert.log, trace files, or a media management interface. For example, you may see the following in a trace file:

ORA-01578: ORACLE data block corrupted (file # 9, block # 13)
ORA-01110: data file 9: '/oracle/dbs/tbs_91.f'
ORA-01578: ORACLE data block corrupted (file # 2, block # 19)
ORA-01110: data file 2: '/oracle/dbs/tbs_21.f'

$rman target =rman/rman@rmanprod
RMAN> run {
       2> allocate channel ch1 type disk;
       3> blockrecover datafile 9 block 13 datafile 2 block 19;
       4> }


Recovering Data blocks Using Selected Backups:

# restore from backupset
BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
# restore from datafile image copy
BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 FROM DATAFILECOPY;
# restore from backupset with tag "mondayAM"
BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 199 FROM TAG = mondayAM;
# restore using backups made before one week ago
BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE
UNTIL 'SYSDATE-7';
# restore using backups made before SCN 100
BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE UNTIL SCN 100;
# restore using backups made before log sequence 7024
BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE
UNTIL SEQUENCE 7024;
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24761058
Please keep calm.
Step by step the solution will be found.
Corruption doesn't mean that the DB can not work.
In many cases the corrupted block(s) will be never used.
But give as more information.
Also investigate if the number of corrupted rows grows.
Sending email also helps because I will try to provide you
some documentation. You can found it in the net of course,
but it seems you prefer to use advices.
0
 
LVL 11

Author Comment

by:it-rex
ID: 24763309
mrjoltcola;schwertner
thank you so much Blockrecover from rman has fixed the problem I have actually posted a comment when I closed the thread ;and awarded you the points but looks like I mad a mistake and the comment did not post .
now the problem has been fixed thank you so much again both of you ;
you have been always a great help.

sorry for the typos ;and if I gave the feeling that I am frustrated ,I apologize.
now we are in the process of finding the cause for that corruption .
we were in bad luck as IOT are the worst segments to handle with corruption
thanks a lot again

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24763346
>>sorry for the typos ;and if I gave the feeling that I am frustrated ,I apologize.

No apologies needed, we are happy to help! :)

I was just concerned that you were not moving forward with either a full tablespace restore or a blockrecover. Maybe I have some grammar to work on if I was not communicating the message well.

Glad it finally worked!

0
 
LVL 47

Expert Comment

by:schwertner
ID: 24763404
Thank you for the good news.
0
 
LVL 11

Author Comment

by:it-rex
ID: 24765407
I have found out exactly what happened :
as per metalink notes
note 794705.1 and 7041254.8

after restoring the database from backups took by rman with check logical and compressed either or.
you will have corrupt blocks in sysaux table .
and each time you do check logical to the databse or backup
"ofcourse you can not backup a database unless you increase the maxcorrupt param"

your corrupt blocks will move and they will increase and decrease in random fashion till you do blockrecover restore corrupt list...
and keep a clean backup ..

thanks
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24765574
Not only this is the reason not to use RMAN before have
a goooooooooood experience and training.
The traps!
It is great! Possibly Metalink considered this as a bug,
not as normal situation.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24768148
I don't think it is a reason not to use RMAN, but it is definitely a reason not to use fancy features such as compression or encryption without really testing.

I agree with schwertner, new features are often flawed. In your case you combined compression with cloning, and it looks like an 11.1.0.7 only bug, according to Metalink. But even Datapump has had bugs, so its not just RMAN.

I do not use compression, so I've never run into a restore problem with RMAN.

I will add this valuable thread to my knowledgebase for the future.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24770812
Oracle is a very complex set of software.
Using sophisticated features and utilities
can cause big troubles.
So I see need of good experiments with a feature
before put it in production.
But I think there are more then enough bugs with RMAN
explained on Metalink that are not connected with compression.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Invalid Identifier Error 3 65
Oracle Database Upgrade 13 41
dates - loop 12 41
VB.Net - Oracle BulkCopy from CSV Date Format 7 25
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now