Short Circuit=>Power Down=>SAN Down=>DB crashed=>DB Started fine=>but BLAB BLAB BLAB ...SAP Application not working properly
User complained some modules had issues
Database Alert Log recorded ORA-600 internal error with different first actual code argument. search into Meta link told DB had problem in executing DMLs on some tables. As some of indices on these tables had logical corruption. So its obvious that when DB crashed DMLS on these tables were in progress,causing update of index entries. so BAD row mismatch occured as key-rowid information was not consistent.
automatic Crash Recovery of Database[without intervention of DBA] was successful, there was no corruption in logs nor in any datafile block. So when Db started after power SAN was made up again after restoring power supply it was opened successfully. But it had logical corruption hidden inside bad leaf blocks of indices. and crash recovery was not able to fix index logical corruption. And its obvious behavivour. We can't expect so hight from Database technology.
solution :
1. identify such indices which are in bad shape and
a) drop and recreated OR b) rebuild them online
2. restore from backup and recover
later was not feasible as DB had size 1TB and prod database was on RAID 5. It would take 3 days to restore!!!
also server did not have enough space to store all archive logs at same time.
User complained some modules had issues
Database Alert Log recorded ORA-600 internal error with different first actual code argument. search into Meta link told DB had problem in executing DMLs on some tables. As some of indices on these tables had logical corruption. So its obvious that when DB crashed DMLS on these tables were in progress,causing update of index entries. so BAD row mismatch occured as key-rowid information was not consistent.
automatic Crash Recovery of Database[without intervention of DBA] was successful, there was no corruption in logs nor in any datafile block. So when Db started after power SAN was made up again after restoring power supply it was opened successfully. But it had logical corruption hidden inside bad leaf blocks of indices. and crash recovery was not able to fix index logical corruption. And its obvious behavivour. We can't expect so hight from Database technology.
solution :
1. identify such indices which are in bad shape and
a) drop and recreated OR b) rebuild them online
2. restore from backup and recover
later was not feasible as DB had size 1TB and prod database was on RAID 5. It would take 3 days to restore!!!
also server did not have enough space to store all archive logs at same time.
Posts
