Shooting yourself in the spfile 2004-02-20 - By Niall Litchfield
> As for your example, even if you made these changes in a
> pfile, wouldn 't
> you get the same result?? So then you would fix it, either
> 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.
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Feb 20 15:40:07 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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\SPFILE%
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=50m scope =spfile;
System altered.
SQL > alter system set db_block_buffers=2000 scope=spfile;
System altered.
SQL > shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL > startup
ORA-00381 (See ORA-00381.ora-code.com): cannot use both new and old parameters for buffer cache size specification
SQL > alter system set db_block_buffers=0 scope=spfile;
alter system set db_block_buffers=0 scope=spfile
*
ERROR at line 1:
ORA-01034 (See ORA-01034.ora-code.com): ORACLE not available
SQL > alter system set db_block_buffers=null scope=spfile;
alter system set db_block_buffers=null scope=spfile
*
ERROR at line 1:
ORA-01034 (See ORA-01034.ora-code.com): ORACLE not available
SQL > alter system set db_cache_size=null scope=spfile;
alter system set db_cache_size=null scope=spfile
*
ERROR at line 1:
ORA-01034 (See ORA-01034.ora-code.com): ORACLE not available
SQL > alter system set db_cache_size=0 scope=spfile;
alter system set db_cache_size=0 scope=spfile
*
ERROR at line 1:
ORA-01034 (See ORA-01034.ora-code.com): ORACLE not available
So the only way to 'fix ' this problem that I can see is to have a text editable version of the spfile available, aka an old style init.ora.
I suspect that this (or something similar) is what jonathan meant.
> I still stand behind MHO. Spfiles are not pfiles, therefore
> you have to
> change your way of thinking and your "SOPs ".
Note I 'm not saying that spfiles are entirely bad, and your specific example would be a very good one. Equally where new features require the spfile like 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 alter database parameters dynamically is good and welcome, the implementation does have some drawbacks that the old system of text files didn 't. I 'd probably 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.
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.
Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805
**********************************************************************
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
