Spfile vs init.ora

[simply_dba]

Originally posted by stmontgo
ahhh, so u can like restore it?

Is it worth it?
I mean to say u can crete a new one anytime.

[davey23uk]

Originally posted by simply_dba
Is it worth it?
I mean to say u can crete a new one anytime.

surely restoring it is easier than having to create a new one

[DaPi]

Originally posted by grjohnson
And the advantages of using an spfile over an init.ora file are?

(I don't use spfile either.) I understood the main advantage is that you update the spfile via an alter system, so any changes are validated. If you edit init.ora, you can introduce cr*p that might prevent the db from starting.

[davey23uk]

spfile wont validate your changes

[oracle@LNCSTRTLDB03 oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Fri Jan 28 10:20:29 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  236000424 bytes
Fixed Size                   451752 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> alter system set shared_pool_size = 0 scope = spfile;

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00093: shared_pool_size must be between 300000 and 4026531840
SQL>

[DaPi]

. . . and if you use SCOPE=BOTH ?

[jmodic]

In the era of Oracle10g a question like "should I use spfile or not" has not much relevance any more. Of course you should. And in couple releases this will not be a relevant question any more - pfiles will be desupported anyway, I'm sure. And rightfully so.

[davey23uk]

wont let you do scope=both because shared pool cannot be changed woth immediate effect.

Bottom line is spfile does no checking and its only advantage is not having to remember to update init.ora on permanent system changes

[DaPi]

Oh! so it just protects you from smelling mystiques.

[davey23uk]

Originally posted by DaPi
Oh! so it just protects you from smelling mystiques.

precisely

[stmontgo]

Originally posted by simply_dba
Is it worth it?
I mean to say u can crete a new one anytime.

sure but why bother, if you have several enviornments to manage that's just another point of failure - disaster strikes and developers need their test data back in a hurry - like a champ you begin the restore and ..ge whiz, where did i put that init file, it was here somewhere, instead let rman do the lights out restore for you