Re: Error creating control file

"Shirish Nilekar" <shirish_at_shaydes.com> wrote in message news:af54e0f8.0212161143.60f95f09_at_posting.google.com...
> 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
> (http://www.shaydes.com)
>
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:<wd9L9.3845$jM5.10998_at_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_at_shaydes.com> wrote in message
> > news:af54e0f8.0212151454.40eebada_at_posting.google.com...
> > > 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
> > > (http://www.shaydes.com)
> > >
> > > --
> > > 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
Received on Mon Dec 16 2002 - 14:22:01 CST