How to perform Oracle database recovery with a corrupt online redo log
Learn how to perform Oracle database recovery of an Oracle 9i database with a corrupt online redo log with this SYS from Oracle expert Brian Peasland.
I am running Oracle 9i database in CentOS 4 in NOARCHIVE mode. Recently, I tried to start it up but the database was not able to reach open state (it can only be in mount state). This is the output I've got:
SQL> ORACLE instance started.
Total System Global Area 823203616 bytes Fixed Size 452384 bytes Variable Size 318767104 bytes Database Buffers 503316480 bytes Redo Buffers 667648 bytes Database mounted. ORA-00355: change numbers out of order ORA-00353: log corruption near block 97 change 9353734646 time 10/06/2009 09:46:19 ORA-00312: online log 2 thread 1: '/oracle/oradata/hqdb/redo02.log'
I have a database export dump. I do not know much about recovery of the Oracle database. I would prefer to recover the database than create a new database and import. Can you help?
It looks like your online redo log is corrupt. You might be able to open the database with the following as SYS:
STARTUP MOUNT ALTER DATABASE OPEN RESETLOGS;
If that does not work, your only option is to drop the entire database, recreate it and then import the db's contents from the export dump file.
