Re: Error creating control file

Does anyone have an answer for my real question: What can I do to fix this dependency?
ORACLE_SID is *not* incorrect when I am trying to re-create control files. I would prefer a fix, if there is one. My other option is exporting data, recreate databases, and import data.

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 - 13:43:07 CST