<$BlogRSDUrl$>

Wednesday, May 05, 2004

9i introduced what is presumably intended to eventually replace the parameter file (or init.ora) for an instance. The server parameter file or SPFILE has a number of advantages over the old fashioned text file. From the docs


A server parameter file (SPFILE) can be thought of as a repository for initialization parameters that is maintained on the machine where the Oracle database server executes. It is, by design, a server-side initialization parameter file. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This eliminates the need to manually update initialization parameters to make changes effected by ALTER SYSTEM statements persistent. It also provides a basis for self tuning by the Oracle database server.


Sounds good doesn't it.

There are a number of drawbacks that I have found though.

  • you can store mutually incompatible settings in it that prevent database startup.


    • 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> startup force;
    ORA-00381: cannot use both new and old parameters for buffer cache size
    specification



    As a result you will want to backup your spfile regularly - this is obviously common sense, but if you forgot to do this with the text based file you could at least edit the file.

  • How to backup


    • The preferred option is to use RMAN which will handle this for you quite nicely, if you aren't using rman then you can export your server parameter file using the new sql command create pfile from spfile . However, I ran into this today


    SQL> create pfile from spfile;
    create pfile from spfile
    *
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [733], [538970160], [pga heap], [], [], [], [], []



    The 733 argument shows that is essentially an out of memory condition, the second argument is the amount of memory in bytes requested!

    In short unless you actually have to use a server parameter file - think RAC, you may consider the potential drawbacks to outweigh the advantages.



    Permalink posted by Niall @ 10:59 AM
    0 Comments
    0 Comments: Post a Comment