Re: How to recover controlfile for OMF database?

"Peter" <peter_at_nomorenewsspammin.ca> wrote in message news:v0ribvg4urotbqblndnr8ig9n94v9i13uk_at_4ax.com...
> My whole database wascreated sing OMF oracle-managed files.
> After shutting down and restarting I got the following message:

Can I strongly recommend that you give OMF a very wide berth? It's completely screwed as it is (why on Earth is the default for datafiles to autoextend... and given they thought that was a good idea, why on Earth didn't they set a MAXSIZE. And, even worse, why is there not a NEXT clause specified?) The default sizes are utterly ridiculous (100MB for a datafile doesn't even pass for SYSTEM, as an example, but 10MB for an UNDO tablespace is almost certain to be ludicrously too small). All datafiles being created in the same directory is a recipe for performance disaster, and only works when the underlying hardware is a huge RAID volume. And so on.

But to answer your substantive point (and another reason the whole OMF debacle is half-baked). Just because Oracle knows where to *create* a controlfile doesn't mean it knows where to find it afterwards. Only the olde-fashioned CONTROL_FILES= init.ora parameter does the location job.

So, although you set DB_CREATE_FILE_DEST or DB_CREATE_LOG_DEST parameters to locate the controlfile when it's first created, you have to manually edit the init.ora after database creation to set the CONTROL_FILES parameter to remind Oracle where it put it.

The only exception to this is if you're smitten with the 'I use an SPFILE' school of thought. If you create a database using the Database Configuration Assistant with the SPFILE option selected (which it is by default), then Oracle will thoughtfully set the CONTROL_FILES parameter within the SPFILE for you to be the same location(s) where the files were created. That means a subsequent startup will work without further effort on your part. But it only works if you decide to use an SPFILE before database creation.

Allegedly, OMF makes cross-platform application development easier (because you don't embed wrong-way slashes in your application install scripts, for example). But otherwise, it's a (IMHO) poorly implemented attempt to make database creation and management more 'SQL Server-like', and it should be steered clear away from whenever possible.

But for now: just edit your init.ora by hand, and it should be OK. Make sure you get the control file name exactly right when you add it to the init.ora, though... which won't be that easy because of the wonderful naming convention that OMF files use!!

Best of luck,
HJR
>
> SQL> alter database mount;
> alter database mount
> *
> ERROR at line 1:
> ORA-00205: error in identifying controlfile, check alert log for more
> info
>
>
> How do you actualy recover controlfiles for an OMF database? I
> thought Oracle is doing everything and should be able to finds its own
> controlfiles.
>
>
>
>
Received on Thu May 08 2003 - 06:36:15 CDT