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

Using RMAN:

http://sysdba.wordpress.com/2006/04/05/how-to-check-for-and-repair-block-corruption-with-rman-in-oracle-9i-and-oracle-10g/

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

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)

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...

mrjoltcola: please stay around

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.

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)

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

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.

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.

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 .....

>>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.

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!!!!!!!!!!!!!

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

Recreate the database via Export/Import.
Do not enter into the RMAN gymnastic exercises.
keep it simple.

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

Seems it is logical corruption.
Try to recover using dbms_repair or RMAN repair

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

>>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.

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;

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.

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

>>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!

Thank you for the good news.

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

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.

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.

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.