oracle01219,ORA-01219: database not open: queries allowed on fixed tables/views only

Hi all GURUS,

I am working in Oracle database 10G. My database was working fine till last night but today when i started my SQLPLUS session, it was showing me following error,

------------------------

ORA-01219: database not open: queries allowed on fixed tables/views only

--------------------------

So i did the following steps from command prompt,

-------------------------------------

sqlplus /nolog

connect / as sysdba

SQL> shutdown immediate

ORA-01109: database not open+_

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 209715200 bytes

Fixed Size 1248140 bytes

Variable Size 88081524 bytes

Database Buffers 117440512 bytes

Redo Buffers 2945024 bytes

Database mounted.

SQL> select open_mode from v$database;

OPEN_MODE

----------

MOUNTED

-----------------------------

Then I tried to open the database but i was getting following error,

----------------------------------

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1:

'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'

-------------------------------

So i did the following steps to check my redo log file but physically it is not available in the ORACLE HOME path

--------------------------

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS

---------- ---------- ----------------

1 1 UNUSED

3 1 UNUSED

2 1 CURRENT

SQL> alter system switch logfile;

alter system switch logfile

*

ERROR at line 1:

ORA-01109: database not open

------------------------------------------------------

I tried to drop the group2 and also tried to disable my thread for REDO02.LOG file but both steps give me error,

--------------------------------------

SQL> alter database drop logfile group 2;

alter database drop logfile group 2

*

ERROR at line 1:

ORA-01623: log 2 is current log for instance orcl (thread 1) - cannot drop

ORA-00312: online log 2 thread 1:

'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'

or

SQL> alter database disable thread 1;

alter database disable thread 1

*

ERROR at line 1:

ORA-01109: database not open

--------------------------------------------

So in any case i am not able start my database session and even not success in creating REDO02.LOG file. can any one help me out from this problem?

Thanks in advance

Bhavik