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