RE: Shooting yourself in the spfile

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 20 Feb 2004 16:01:34 +0000
Message-Id: <s0362f77.034@bristol21.bristol.ac>

> As for your example, even if you made these changes in a=20
> pfile, wouldn't
> you get the same result?? So then you would fix it, either=20
> in the pfile
> or spfile, and bring the database up. Is that not true?

Fixing it in the spfile is somewhat harder than one might expect.=20

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Feb 20 15:40:07 2004

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> shutdown;
SQL> create spfile from pfile;

File created.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 135339844 bytes

Fixed Size                   454468 bytes
Variable Size             100663296 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes

Database mounted.
Database opened.
SQL> show parameter file

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------=
----
control_file_record_keep_time        integer     7

<snip>

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------=
----
spfile                               string      %ORACLE_HOME%\DATABASE\SPF=
ILE%
                                                 ORACLE_SID%.ORA

SQL> show parameter db_

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------=

<snip>

db_block_buffers                     integer     0
db_block_size                        integer     16384
db_cache_advice                      string      ON
db_cache_size                        big integer 25165824

SQL> alter system set db_cache_size=3D50m scope =3Dspfile;

System altered.

SQL> alter system set db_block_buffers=3D2000 scope=3Dspfile;

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00381: cannot use both new and old parameters for buffer cache size spe= cification
SQL> alter system set db_block_buffers=3D0 scope=3Dspfile; alter system set db_block_buffers=3D0 scope=3Dspfile *
ERROR at line 1:
ORA-01034: ORACLE not available

SQL> alter system set db_block_buffers=3Dnull scope=3Dspfile; alter system set db_block_buffers=3Dnull scope=3Dspfile *
ERROR at line 1:
ORA-01034: ORACLE not available

SQL> alter system set db_cache_size=3Dnull scope=3Dspfile; alter system set db_cache_size=3Dnull scope=3Dspfile *
ERROR at line 1:
ORA-01034: ORACLE not available

SQL> alter system set db_cache_size=3D0 scope=3Dspfile; alter system set db_cache_size=3D0 scope=3Dspfile *
ERROR at line 1:
ORA-01034: ORACLE not available

So the only way to 'fix' this problem that I can see is to have a text edit= able version of the spfile available, aka an old style init.ora.=20 I suspect that this (or something similar) is what jonathan meant.=20 =20
> I still stand behind MHO. Spfiles are not pfiles, therefore=20
> you have to
> change your way of thinking and your "SOPs".

Note I'm not saying that spfiles are entirely bad, and your specific exampl=
e would be a very good one. Equally where new features require the spfile l=
ike I believe RAC does for example, then obviously you need to go down the =
spfile path. All I think I am trying to say is that whilst the ability to a=
lter database parameters dynamically is good and welcome, the implementatio=
n does have some drawbacks that the old system of text files didn't. I'd pr=

obably change the change a parameter business procedure to read

Always issue create a named pfile from spfile before changing a parameter, = and keep a history of pfiles in case your database becomes unstartable.=20

What particularly grates is that oracle can check the set of parameters for= consistency as well as validity at startup, it ought to check them at the = point of change as well.=20

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805=20

This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 20 2004 - 10:01:34 CST