8 Replies Latest reply: Jan 10, 2010 2:01 PM by Lubiez Jean-Valentin RSS

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

    745516
      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