how to multiplex control files?

[aking]

Hi, really basic question from oracle newbie:
How do you multiplex control files? I have 3, all in the same place, and my initialization file is an SPfile (i.e. binary so i can't edit it manually).
Please don't just send me to the oracle documentation site, i've already read it. Instructions for multiplexing control files:
1.Shut down the database.
2.Copy an existing control file to a new location, using operating system commands.
3.Edit the CONTROL_FILES parameter in the database initialization parameter file to add the new control file name, or to change the existing control filename.
4.Restart the database.

But How do you edit the control_files parameter??
Hope this question not too stupid but nothing seems straight-forward on this database.

 

[SantaMufasa]

AKing,

(As a side note: I have discovered that, on balance, the hassle of using the SPFILE method of managing my database configurations heavily overshadows the benefits of using such.)

To modify the entries is both you init<SID>.ora file and the same entries in your SPFILE, you can use the following code at your SQL> prompt, connected as a DBA:

ALTER SYSTEM SET CONTROL_FILES=<new file specification(s) for control files> SCOPE=BOTH;

The "scope=both" specification means to modify the contents of BOTH your INIT<sid>.ora file and your spfile.


Let us know your outcomes.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[aking]

thanks for that Mufasa, wish i'd seen this earlier, i've managed to edit spfile by doing 'create pfile' editing that manually and then doing 'create spfile'. It's just worked so i'm happy and now i've got 3 control files on 3 different volumes
But your way looks quicker, i've got another database in the same state on another server so might try your technique on that.

 

[KenCunningham]

on balance, the hassle of using the SPFILE method

I couldn't agree more, Dave.

I want to be good, is that not enough?

 

[jimirvine]

scope = both will not work.
It MUST be set to scope = spfile otherwise oracle will try to write to a multiplexed controlfile that doesn't exist.

 

[SantaMufasa]

Thanks, Jim, for producing the correction. My answer in this case was an untested ("ivory tower") response.

This is yet another example of the "hassle" of using an spfile to make your life miserable.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Sorry Dave. It's not often that I disagree with you, but in this case I do. I do not see the above as being an example of the extra hassle of working with spfiles. All I see it as is not knowing the correct settings for a particular statement. If you mean hassle as 'having to learn the correct values for these statements' then maybe I could agree with you, but I'm fairly certain that that is not what you meant. I fully support your opinion (although I don't necessarily agree with it) that you do not like the spfile. I don't see where the dislike comes from, but there you go, everyone 'has the right' I would point out a couple of big advantages of the spfile over the pfile. (and would more than welcome your bad experiences of working with the spfile -all goes into the knowledge base )
1. Since Oracle maintains the spfile, even when we make changes to it, Oracle will check for the appropriate syntax and parameter values before making changes, reducing the likelihood of invalid parameter valaues.
2. RMan is able to backup the controlfile along with the standard backup strategy (not available in pfiles)
3. No need for an spfile on any remote machine from which you may want to startup the db.
4. The ability to make changes persistant across shutdown and startup.

Once again, however, I would like to restate that I am not trying to say that you should like spfiles better that pfiles, but I don't think that they are the 'monster' that you are implying them to be.
[/off soapbox]

 

[KenCunningham]

Points taken, Jim, but I'm firmly of the opinion that the spfile simply introduces an extra level of complexity/confusion for the DBA to cope with, something we could all do without.

I want to be good, is that not enough?

 

[jimirvine]

That is absolutely your right to do so(I actually think the opposite ) I would genuinely love to hear why you think this is so. As I say, I know that I definitely don't have all the answers (as is proven every day to me - generally by wifey ) and any input that you have on why you think it is more complex, I'm sure would stand us all in good stead (it certainly would help me).

 

[jimirvine]

PS Please note that I am in no way trying to sway your opinion on this, I don't think that it is important enough to be honest, I'm just interested in other perspectives.

 

[KenCunningham]

No problem, Jim. I think what I mainly feel is that the spfile provides an additional level of possible confusion for the DBA in divining exactly what his/her database is using to startup and where it's getting it's parameters from.

I think it's also important if using an spfile, to ensure that any changes made dynamically are also reflected in the pfile 'just in case' the latter is needed to start the database at any point (as has been the case here on at least one occasion). To me that rather negates any benefit accruing from use of an spfile.

Then again, it could just be that I'm lazy and can't be bothered to change my habits ;-)

I want to be good, is that not enough?

 

[jimirvine]

Thanks for getting back to me Ken.
As I see it, both of the issues that you mention can be resolved by simply not having a pfile anywhere on the system.
However, it is very, very easy to divine whether you are using an spfile

SELECT isspecified, count(*)
FROM v$spparameter

If there is a value for TRUE then you are using an spfile.
The only level of complexity that I can see is working out whether you can alter a parameter
1. solely in memory,
2. solely in the spfile
or
3. both spfile and memory
This can be worked out quickly by looking at the issys_modifiable column for the particular parameter. As for need ing a pfile to start up (maybe you didn't use rman to back it up for example)well you can get the last set of parameters used on the system by looking at the alert log.

Cheers
Jim

 

[KenCunningham]

Thanks for that Jim, and I do appreciate the points you make are sensible. Perhaps this will be the year I'll finally take the plunge to spfile-only setups!

I want to be good, is that not enough?

 

[jimirvine]

LOL, well good luck if you do Ken. Cheers for the discourse (it's better than actually having to do work )