Re: howto drop a corrupt database ?

"Daniel Morgan" damorgan_at_x.washington.edu wrote in HTML yet again despite constant pleadings for him not to:

I have now modified by curriculum to specifically advise students to NEVER use this. This
is a horror worthy of a terrorist organization. The problem is that this bomb was fused by
Oracle itself.

It gets worse, you know! Much worse.

With OMF you can now issue the one-liner: create database blah;

So where do we stick the control files when you do that? In wherever db_create_file_dest is set to, that's where (unless you've also set the db_create_online_log_dest_n parameter, in which case it goes there, and since that parameter can take n= 1 to 5, it will multiplex the thing for you from the word go. Quite nice.) And also unless you've set CONTROL_FILES in the init.ora, because otherwise it will go there. Clear so far??

Right, now imagine you want to make use of this wonderful new one-liner-create-database feature, and therefore *don't* set CONTROL_FILES, but do set one or other of the db_create parameters.

Not comes the good bit.

When you re-start your instance after having successfully created your database, it will crash out in the NOMOUNT state with the error 'unable to locate the control files'. Why? Because db_create parameters only tell Oracle where to *create* the control files. Not where they're to be found after they've been created. So, because your init.ora is missing the CONTROL_FILES parameter, it looks (naturally) in ORACLE_HOME/dbs, which is almost certainly not where you set the db_create parameters to.

So the fix is: edit your init.ora to insert the CONTROL_FILES parameter after all. And you thought you'd managed to avoid that?? Fat chance! Oh, and set CONTROL_FILES to practically the same setting as the db_create parameters you've already got sitting in the init.ora. Nothing like a bit of duplication of effort, I always say.

Unless you were using an spfile, however. In that case, the system sets the CONTROL_FILES parameter for you automatically at the time of database creation, and everything just works.

Now it takes me about 5 minutes to explain this behaviour to students, and I swear ever man jack of them is sitting there blank-faced and incomprehending afterwards. That's if they're not slithering around the floor in fits of laughter.

If this is the SQL Server-ing of Oracle, I think I'd prefer the genuine article myself.

In short, I agree with you. Only a nutcase would want to make extensive use of OMF.

Regards
HJR Received on Mon Nov 10 2003 - 18:55:30 CST