Yesterday our companies datawarehouse guy made a mistake and removed the current redo log file of his 1.2 terabyte database while tens of sessions were running. He didn't multiplex the redo logs, and didn't have backups either, though the database was running in archivelog mode. After Oracle support told him they couldn't help, he went to us for help.
As everything else has been tried, as last option, I decided to use the
unsupported _allow_resetlogs_corruption= true parameter to open the database. After an evening and a morning's try, I can't recall exactly what happened, but somehow we managed to open the database, first with that parameter, then without it. As database is opened without that parameter, I assume that it is now somehow consistent, though there is no way to check, and there is no alternative anyway. The only alternative is to reimport all the data and it could take weeks. Having his job saved is one thing, I was more interested in reconstructing the whole event.
So I created a small 8.1.7 database , let it run in archive log mode, then
run some transactions, then remove the current redo logs, try to recover it the same way as I did earlier. After some tries, I found that the steps are not so complicated at all, and repeated several times successfully. I would like to have some other people test these steps, and would like to hear some comments.
Here are the steps:
Database running, current redo log group is 3, and all memebers are removed.
1. shutdown immediate(if instance not crashed yet)
2. Set _allow_resetlogs_corruption=true (_allow_read_only_corruption) in init.ora
3. startup mount
4. recover database until cancel;
5. alter database open resetlogs;
6. remove _allow_resetlogs_corruption=true from init.ora
7. shutdown
8. startup mount;
10.recover database;
11. alter database open ;
I am suprised that this looks so simple, yet I thought before that this was not possible. Have no idea about the potential logical data corruption though. With our own production database, I would never use this procedure as we have backups.
Your comments are welcomed.
|