 Posted 6/15/2010 10:21:40 AM
|
|
|
|
| Ora Error Code ORA-01219: Database not open: queries allowed on fixed tables/views only You are trying to select from a dynamic/data dictionary table but the database is not yet open.
SQL> startup mount
ORACLE instance started.
Total System Global Area 126951228 bytes
Fixed Size 454460 bytes
Variable Size 109051904 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> select * from dba_users;
select * from dba_users
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only The data dictionary views are stored inside the Oracle database's datafiles (SYSTEM tablespace).
To be able to query the data dictionary views, the database need to be open.
Check the mode of the database by issuing following select, and if it is not open, open the database: SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select * from dba_users;
..
|
|
|
Posted 6/15/2010 10:21:57 AM
|
|
|
|
ORA-01219: | database not open: queries allowed on fixed tables/views only | | Cause: | A query was issued against an object not recognized as a fixed table or fixed view before the database has been opened.
| | Action: | Re-phrase the query to include only fixed objects, or open the database. |
|
|
|
Posted 6/15/2010 10:23:15 AM
|
|
|
|
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
|
|
|
Posted 6/15/2010 10:24:07 AM
|
|
|
|
So, your database is mounted. May you try the following:
recover database until cancel;
You apply the archived logs if you have some, then:
cancel
alter database open resetlogs;
Else, if you cannot open the database, because you need the lost redolog files for your recovery, then
you 'll have to restore the database from a Backup.
It's very important to duplex the redologs else, the lost of the CURRENT redolog may cause the need of
a backup and recovery.
Hope it can help
|
|
|
Posted 6/15/2010 10:28:05 AM
|
|
|
|
select * from all_users;
select * from all_users
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed
tables/views only
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>
To open the database for normal access, we can alter the database. SQL> alter database open; Database altered. The shutdown proccess is the simply opposite of the startup. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
|
|
|
|