Wednesday, July 1, 2009

Data Dictionary corruption on noarchive log mode database - Is restore the only solution?

For any corruption case the first solution given( by even support) is to do a restore and recover or do an Rman Block media recovery(BMR). BMR is a feature I like very much !. In fact I have co-authored a note on metalink (Note 342972.1How to Do block media recovery when backups are not taken by rman) It was an out come of my experiments with RMAN and Backup and recovery in initial days in Oracle support- Co-authored with Ramkumar).

Ok all these things are good, if we are lucky to have backup and archive logs. What if we hit a data dictionary corruption when we are not that lucky to have them? Is it end of world? Definitely not. Here is a case I encountered last day.
Got a call from a friend and their 400GB database (noarchive log mode) got a block corruption. Issue was clarified as below after few iterations.
1) A query on one table fails with ORA-01578. The block which is being reports as corrupted is part of dependency$.
2) The ORA-1578 happens only when they use a synonym to access table.
3)Directly querying the table works well.
4)Attempt to drop the synonym also fails.
Ok so that make it clear that the corrupted block of dependency$ holds information for the synonym. Application is a packaged one and it is not possible to change the synonym it uses. (Otherwise simply we could have created another synonym and used that).

So what is the next solution? They are OK for any workaround. The DB will be refreshed from a production copy weeks later. So as long as the current issue is solved any further risk is acceptable. Good, then we can take any risk :)
Where is the name of synonym stored? Can we change it just there so that we would be able to create a new synonym with same original name?
Yes , possible. This is what we did finally..
Updated table obj$ to change name of synonym to originalname_old.
(Its a data dictionary update, which need to be followed by a commit and shutdown Abort)
That did the trick. They were able to create synonym with original name and application started functioning smoothly!!.