V$DATABASE OPEN_MODE

This was tested on Oracle 11.1.0.6.0 on Windows XP. You can see if your database is open or closed by querying open_mode from v$database:

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Sep 15 23:18:58 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SQL> startup nomount
ORACLE instance started.

Total System Global Area  535678976 bytes
Fixed Size                  1334320 bytes
Variable Size             201327568 bytes
Database Buffers          327155712 bytes
Redo Buffers                5861376 bytes
SQL> select open_mode from v$database;

select open_mode from v$database
                      *
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount
  2  /

Database altered.


SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL>

I will look at what all these open_mode values mean in another post.

ORA-01507

I had to put a database into archivelog mode today. The problem when I do this is that I can never remember if the database has to be mounted or not. The answer is that it does. If it isn't, you get an ORA-01507:

ORACLE10 > sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 6 15:23:22 2011

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

Connected to an idle instance.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  176160768 bytes

Fixed Size                  2028688 bytes

Variable Size             134220656 bytes

Database Buffers           33554432 bytes

Redo Buffers                6356992 bytes

SQL> alter database archivelog

  2  /

alter database archivelog

*

ERROR at line 1:

ORA-01507: database not mounted

 

SQL> alter database mount

  2  /

 

Database altered.

 

SQL> alter database archivelog

  2  /

 

Database altered.

 

SQL> alter database open

  2  /

 

Database altered.

 

SQL> select log_mode from v$database

  2  /

 

LOG_MODE

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

ARCHIVELOG

 

SQL>