Monday, October 17, 2011

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>

No comments: