This discussion is archived
8 Replies Latest reply: Jan 10, 2010 12:01 PM by Lubiez Jean-Valentin RSS

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

745516 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points