Saturday, September 15, 2012

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.

No comments: