Re: Error creating control file

Howard,

The spfile for dev1 was incorrect. After re-creating spfile from pfile, everything works fine now. Thanks for your help.

--shirish
(http://www.shaydes.com)

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<ysqL9.4431$jM5.12760_at_newsfeeds.bigpond.com>...
> "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 Tue Dec 17 2002 - 12:55:03 CST