Tuesday, June 1, 2010

ORA-00470: LGWR process terminated with error in 11g on Windows

In one of my test servers, I am using Oracle 11.1.6 on Windows Server 2008. The services of listener and database are automatic. Normally when I boot the machine, the listener starts, and as the database starts, the PMON automatically registers the database with the listener.

This morning when I booted the machine, both services were up, listener was also up,but the database was in mount state, and when I did:

SQL> conn sys as syssdba
SQL> alter database open;

it gave following error:

ORA-00470: LGWR process terminated with error

I resolved the error by doing following steps, when the database was at mount state:

SQL> recover database until cancel;
SQL> alter database open resetlogs;

Now the database was opened fine. The problem seems to be caused by an I/O error
reading/writing to
the redo log that causes Oracle to not be able to startup with the current state of the redo thread.