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>

ORA-00205, ORA-00202 and ORA-27086

I heard of a problem which occurred after a UNIX server crashed. The server hosted several databases whose datafiles, control files and redo logs were on attached storage. The attached storage unit, a Celerra, did not fail. When the server was rebooted, the databases would not start because Oracle could not lock their control files. An ORA-00205 message was displayed and in the alert log there were ORA-00202 and ORA-27086 messages. The problem was caused because the attached storage was still holding locks which had been created before the server crashed. It was not possible to reboot the attached storage because it also had datafiles, control files and redo logs belonging to databases on other UNIX servers. To cure the problem the server's file systems had to be unmounted and mounted again. I decided to try to simulate this problem on a PC at home which runs Red Hat Linux and has a database called ANDREW. First I located one of its control files:

[oracle@localhost ANDREW]$ pwd
/home/oracle/andrew/ANDREW
[oracle@localhost ANDREW]$ ls control01.ctl
control01.ctl
[oracle@localhost ANDREW]$

Then I wrote the following simple C program to lock the control file for an hour:

#include <fcntl.h>
#include <unistd.h>
int main ()
{
int fildes;
int status;
fildes = open("control01.ctl", O_RDWR);
status = lockf(fildes, F_TLOCK, (off_t)10);
system ("sleep 3600");
}

And compiled and ran it in the directory containing the control file. When I tried to open the database, I saw the same ORA-00205 message (the date and time on the PC are wrong):

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 1 02:13:10 2002

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL>

And the alert log showed an ORA-00202 and ORA-27086:

Tue Jan  1 02:13:16 2002
ORA-00202: control file: '/home/oracle/andrew/ANDREW/control01.ctl'
ORA-27086: unable to lock file - already in use
Linux Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 4643
Tue Jan  1 02:13:16 2002
ORA-205 signalled during: ALTER DATABASE   MOUNT...
MMNL started with pid=12, OS id=4698

I used Ctrl + C to stop the C program which was locking the control file then I was able to open the database:

[oracle@localhost bdump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 1 02:28:48 2002

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>