Error creating control file

[Shirish Nilekar]

Hi,

I have two 9i databases with sid 'dev1' and 'dev2'. The control files for 'dev1'
got messed up, and I have to recreate them. When I try to create control files
for 'dev1' using CREATE CONTROLFILE ('dev2' is running while i am doing this),
I get the following error:

ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile: '/usr/local/dbms/oracle/oradata/dev2/control01.ctl'
ORA-27086: skgfglk: unable to lock file - already in use
Linux Error: 11: Resource temporarily unavailable
Additional information: 8

But if I shutdown 'dev2' and try to create control files for 'dev1',
everything goes fine, and I can start 'dev1'. But then 'dev2' fails to mount
with the following error:

ORA-01103: database name 'dev1' in controlfile is not 'dev2'

My command for creating control files is given at the end of this message.
Why does the CREATE CONTROLFILE for 'dev1' database try to access control files
of 'dev2' database? I recheked my ORACLE_SID env variable, and it is 'dev1'.
The initdev1.ora file also has correct file locations for control files.

What could have caused this dependency between databases? And how can I fix
it?

Thanks,
shirish
([url]http://www.shaydes.com[/url])

--
CREATE CONTROLFILE REUSE SET DATABASE "DEV1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
'/usr/local/dbms/oracle/oradata/dev1/redo03.log' SIZE 100M,
'/usr/local/dbms/oracle/oradata/dev1/redo02.log' SIZE 100M,
'/usr/local/dbms/oracle/oradata/dev1/redo01.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/usr/local/dbms/oracle/oradata/dev1/system01.dbf',
'/usr/local/dbms/oracle/oradata/dev1/undotbs01.dbf',
'/usr/local/dbms/oracle/oradata/dev1/cwmlite01.dbf',
'/usr/local/dbms/oracle/oradata/dev1/drsys01.dbf',
'/usr/local/dbms/oracle/oradata/dev1/example01.dbf',
'/usr/local/dbms/oracle/oradata/dev1/indx01.dbf',
'/usr/local/dbms/oracle/oradata/dev1/tools01.dbf',
'/usr/local/dbms/oracle/oradata/dev1/users01.dbf'
CHARACTER SET US7ASCII

[Howard J. Rogers]

I would suspect that you don't reset your ORACLE_SID when switching between
databases. If ORACLE_SID is set to DEV2, then the startup mount command will
be searching for an initDEV2.ora with which to build the instance. That
init.ora will contain a line which specifies
CONTROL_FILES=...blah/blah/blah/dev2...., so what you think is your DEV1
instance is going to try and create its control file on top of the existing
DEV2 one.

if that control file is already locked (as it will be if DEV2 is already
running), then the create controlfile statement will fail with the 'unable
to lock' command you experienced.

Shut down DEV2, and the re-creation of the controlfile will now work fine
(because the file is not locked). And you've then just overwritten a
perfectly good DEV2 controlfile with a DEV1 one. Now try and start DEV2, and
it will find the new control file which now belongs to database DEV1. Hence
there is a mismatch between the database name found in the controlfile
(DEV1) and the db_name found in the DEV2 init.ora (DEV2). Which explains the
second error message you describe.

Learn to switch between your instances by re-setting ORACLE_SID and this
won't be a problem.

Regards
HJR

"Shirish Nilekar" <shirish@shaydes.com> wrote in message
news:af54e0f8.0212151454.40eebada@posting.google.c om...

> Hi,
>
> I have two 9i databases with sid 'dev1' and 'dev2'. The control files for

'dev1'

> got messed up, and I have to recreate them. When I try to create control

files

> for 'dev1' using CREATE CONTROLFILE ('dev2' is running while i am doing

this),

> I get the following error:
>
> ERROR at line 1:
> ORA-01503: CREATE CONTROLFILE failed
> ORA-00200: controlfile could not be created
> ORA-00202: controlfile:

'/usr/local/dbms/oracle/oradata/dev2/control01.ctl'

> ORA-27086: skgfglk: unable to lock file - already in use
> Linux Error: 11: Resource temporarily unavailable
> Additional information: 8
>
> But if I shutdown 'dev2' and try to create control files for 'dev1',
> everything goes fine, and I can start 'dev1'. But then 'dev2' fails to

mount

> with the following error:
>
> ORA-01103: database name 'dev1' in controlfile is not 'dev2'
>
> My command for creating control files is given at the end of this message.
> Why does the CREATE CONTROLFILE for 'dev1' database try to access control

files

> of 'dev2' database? I recheked my ORACLE_SID env variable, and it is

'dev1'.

> The initdev1.ora file also has correct file locations for control files.
>
> What could have caused this dependency between databases? And how can I

fix

> it?
>
> Thanks,
> shirish
> ([url]http://www.shaydes.com[/url])
>
> --
> CREATE CONTROLFILE REUSE SET DATABASE "DEV1" RESETLOGS NOARCHIVELOG
> MAXLOGFILES 16
> MAXLOGMEMBERS 2
> MAXDATAFILES 30
> MAXINSTANCES 1
> MAXLOGHISTORY 226
> LOGFILE
> '/usr/local/dbms/oracle/oradata/dev1/redo03.log' SIZE 100M,
> '/usr/local/dbms/oracle/oradata/dev1/redo02.log' SIZE 100M,
> '/usr/local/dbms/oracle/oradata/dev1/redo01.log' SIZE 100M
> -- STANDBY LOGFILE
> DATAFILE
> '/usr/local/dbms/oracle/oradata/dev1/system01.dbf',
> '/usr/local/dbms/oracle/oradata/dev1/undotbs01.dbf',
> '/usr/local/dbms/oracle/oradata/dev1/cwmlite01.dbf',
> '/usr/local/dbms/oracle/oradata/dev1/drsys01.dbf',
> '/usr/local/dbms/oracle/oradata/dev1/example01.dbf',
> '/usr/local/dbms/oracle/oradata/dev1/indx01.dbf',
> '/usr/local/dbms/oracle/oradata/dev1/tools01.dbf',
> '/usr/local/dbms/oracle/oradata/dev1/users01.dbf'
> CHARACTER SET US7ASCII

[Howard J. Rogers]

"Shirish Nilekar" <shirish@shaydes.com> wrote in message
news:af54e0f8.0212161143.60f95f09@posting.google.c om...

> Does anyone have an answer for my real question: What can I do to fix this
> dependency?

What dependency? The only dependency in the 'create controlfile' command is
that between ORACLE_SID, the init.ora/spfile that is therefore used to start
the Instance, and the CONTROL_FILES parameter contained therein. That
doesn't need fixing, just understanding and sorting out.

> ORACLE_SID is *not* incorrect when I am trying to re-create control files.
> I would prefer a fix, if there is one.

If you are certain that ORACLE_SID is set correctly, then the only other
explanation is that in one or other of your init.oras, the CONTROL_FILES
parameter is set incorrectly. And from your description, it sounds like the
init.ora for the DEV1 database.

The 'create controlfile' command does not, in and of itself, specify where
the new controlfile should be created. Only the init.ora does that.

So you are either using the wrong init.ora (ie, wrong ORACLE_SID) or the
correct init.ora has an incorrect setting for CONTROL_FILES.

Given that you are on 9i, it is of course possible that you are using new
SPFILES, and not traditional init.oras. All the more reason to check their
contents carefully, since editing them directly is not an option.

Bear in mind that Oracle now uses SPFILE<SID>.ORA first, then SPFILE.ORA if
it can't find a SID-specific one, and only then uses the INIT<SID>.ORA.
What's more, the SPFILE must be in the ORACLE_HOME/dbs directory, unless
you've left behind a symbolic link to somewhere else, or unless an init.ora
is left behind in that location with the one line 'SPFILE=...'

If ORACLE_SID is not set as perfectly as you think it is, then it is
possible you are inadvertently using the default (SID-less) SPFILE, which
would contain a reference only to one CONTROL_FILES set of locations.

If ORACLE_SID is indeed set perfectly, but you've moved your SID-specific
SPFILE to a non-default location without any forwarding mechanism, then
again Oracle may be using a default SPFILE which is (perhaps) fine for DEV2
but not good for DEV1.

It's easy enough to diagnose. With both instances in NOMOUNT state, what do
you get for the command 'show parameter spfile'?

HJR

>My other option is exporting data,
> recreate databases, and import data.

Which is a bit like chopping off your head to cure a headache: a tad
drastic, though ultimately effective.

> Also, is there a tool to see contents of Oracle datafiles? Just curious.
> Thanks.
>
> --shirish
> ([url]http://www.shaydes.com[/url])
>
>
> "Howard J. Rogers" <howardjr2000@yahoo.com.au> wrote in message

news:<wd9L9.3845$jM5.10998@newsfeeds.bigpond.com>. ..

> > I would suspect that you don't reset your ORACLE_SID when switching

between

> > databases. If ORACLE_SID is set to DEV2, then the startup mount command

will

> > be searching for an initDEV2.ora with which to build the instance. That
> > init.ora will contain a line which specifies
> > CONTROL_FILES=...blah/blah/blah/dev2...., so what you think is your DEV1
> > instance is going to try and create its control file on top of the

existing

> > DEV2 one.
> >
> > if that control file is already locked (as it will be if DEV2 is already
> > running), then the create controlfile statement will fail with the

'unable

> > to lock' command you experienced.
> >
> > Shut down DEV2, and the re-creation of the controlfile will now work

fine

> > (because the file is not locked). And you've then just overwritten a
> > perfectly good DEV2 controlfile with a DEV1 one. Now try and start DEV2,

and

> > it will find the new control file which now belongs to database DEV1.

Hence

> > there is a mismatch between the database name found in the controlfile
> > (DEV1) and the db_name found in the DEV2 init.ora (DEV2). Which explains

the

> > second error message you describe.
> >
> > Learn to switch between your instances by re-setting ORACLE_SID and this
> > won't be a problem.
> >
> > Regards
> > HJR
> >
> > "Shirish Nilekar" <shirish@shaydes.com> wrote in message
> > news:af54e0f8.0212151454.40eebada@posting.google.c om...

> > > Hi,
> > >
> > > I have two 9i databases with sid 'dev1' and 'dev2'. The control files

for

> > 'dev1'

> > > got messed up, and I have to recreate them. When I try to create

control

> > files

> > > for 'dev1' using CREATE CONTROLFILE ('dev2' is running while i am

doing

> > this),

> > > I get the following error:
> > >
> > > ERROR at line 1:
> > > ORA-01503: CREATE CONTROLFILE failed
> > > ORA-00200: controlfile could not be created
> > > ORA-00202: controlfile:

> > '/usr/local/dbms/oracle/oradata/dev2/control01.ctl'

> > > ORA-27086: skgfglk: unable to lock file - already in use
> > > Linux Error: 11: Resource temporarily unavailable
> > > Additional information: 8
> > >
> > > But if I shutdown 'dev2' and try to create control files for 'dev1',
> > > everything goes fine, and I can start 'dev1'. But then 'dev2' fails to

> > mount

> > > with the following error:
> > >
> > > ORA-01103: database name 'dev1' in controlfile is not 'dev2'
> > >
> > > My command for creating control files is given at the end of this

message.

> > > Why does the CREATE CONTROLFILE for 'dev1' database try to access

control

> > files

> > > of 'dev2' database? I recheked my ORACLE_SID env variable, and it is

> > 'dev1'.

> > > The initdev1.ora file also has correct file locations for control

files.

> > >
> > > What could have caused this dependency between databases? And how can

I

> > fix

> > > it?
> > >
> > > Thanks,
> > > shirish
> > > ([url]http://www.shaydes.com[/url])
> > >
> > > --
> > > CREATE CONTROLFILE REUSE SET DATABASE "DEV1" RESETLOGS NOARCHIVELOG
> > > MAXLOGFILES 16
> > > MAXLOGMEMBERS 2
> > > MAXDATAFILES 30
> > > MAXINSTANCES 1
> > > MAXLOGHISTORY 226
> > > LOGFILE
> > > '/usr/local/dbms/oracle/oradata/dev1/redo03.log' SIZE 100M,
> > > '/usr/local/dbms/oracle/oradata/dev1/redo02.log' SIZE 100M,
> > > '/usr/local/dbms/oracle/oradata/dev1/redo01.log' SIZE 100M
> > > -- STANDBY LOGFILE
> > > DATAFILE
> > > '/usr/local/dbms/oracle/oradata/dev1/system01.dbf',
> > > '/usr/local/dbms/oracle/oradata/dev1/undotbs01.dbf',
> > > '/usr/local/dbms/oracle/oradata/dev1/cwmlite01.dbf',
> > > '/usr/local/dbms/oracle/oradata/dev1/drsys01.dbf',
> > > '/usr/local/dbms/oracle/oradata/dev1/example01.dbf',
> > > '/usr/local/dbms/oracle/oradata/dev1/indx01.dbf',
> > > '/usr/local/dbms/oracle/oradata/dev1/tools01.dbf',
> > > '/usr/local/dbms/oracle/oradata/dev1/users01.dbf'
> > > CHARACTER SET US7ASCII