title: Pfiles, spfiles and the post-install config of Oracle databases link: https://onemoretech.wordpress.com/2009/02/18/pfiles-spfiles-and-the-post-install-config-of-oracle-databases/ author: lembobro description: post_id: 374 created: 2009/02/18 16:20:33 created_gmt: 2009/02/18 16:20:33 comment_status: open post_name: pfiles-spfiles-and-the-post-install-config-of-oracle-databases status: publish post_type: post

Pfiles, spfiles and the post-install config of Oracle databases

While in the midst of building a new database for yet another test instance of Oracle’s Identity Manager I had some problems with getting the memory parameters right. This is something we’ve gone through before. On “real” database hardware we’d have 32 Gb (or more) of RAM to play with, but on my test virtual machine the most I can spare is 2 Gb (our hardware maxes out at 4 Gb). After mucking around with it for awhile I succeeded in hosing the db so it wouldn’t start. This was a pain because it had taken two days to get the db built (the process involved installing Oracle 10g 10.2.0.1 from media and then patching up to 10.2.0.4).

The answer to my tale of woe was to overwrite the existing spfile with the initial installed config and then restart the db so it could be reconfigured from there.

The name pfile comes from “ParameterFILE”, because that’s what it is. The name spfile comes from “SystemParameterFILE”. The pfile is in clear text. The spfile is binary. A database can be started using either at the command line. Things are usually set up to start automatically with the spfile if it exists.

To check for the existence of the spfile, do this query:

SQL> SHOW PARAMETER SPFILE;

If it’s there, you’ll get the path and filename.

On my system this was $ORACLE_HOME/dbs. The file is named spfile[SID].ora, or in my case, /u01/app/oracle/product/db/dbs/spfiletest01.ora.

A new spfile can be created (or the old one overwritten) by the info in a pfile.

An initial pfile is usually made during database creation, and located under $OH/admin/[SID]/pfile (in my case /u01/app/oracle/db/admin/testdb/pfile/init.ora.0272009111420).

To create a new spfile, or overwite an existing spfile, from that initial pfile, just do a

SQL> create spfile from pfile;

If you’ve got a pfile from somewhere else, you can use it to create a new spfile, or overwrite an existing one. Just be sure to change any values that need to be specific to the target database.

Here’s the command:

SQL> create spfile from pfile='/tmp/robspfile.ora';

The PATH and file name must be in quotes as above, or the function won’t know what you’re talking about.

To create a pfile from and existing spfile, you would do this:

SQL> create pfile='/tmp/mypfile.ora' from spfile;

So what does a pfile look like? I’m glad you asked. Here’s the one I used to reconfigure my OIM database (hint, hint, this contains a minimal database config for OIM):

*.aq_tm_processes=0
*.audit_file_dest=’/u01/app/oracle/product/db/admin/testdb/adump’
*.background_dump_dest=’/u01/app/oracle/product/db/admin/testdb/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u02/oradata/testdb/control01.ctl’,'/u02/oradata/testdb/control02.ctl’,'/u02/oradata/testdb/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/product/db/admin/testdb/cdump’
*.db_block_size=8192
*.db_domain='’
*.db_file_multiblock_read_count=16
*.db_name=’testdb’
*.db_recovery_file_dest=’/u01/app/oracle/product/db/flash_recovery_area’
*.db_recovery_file_dest_size=2G
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=testdbXDB)’
*.job_queue_processes=10
*.nls_length_semantics=’CHAR’
*.open_cursors=300
*.processes=300
*.query_rewrite_enabled=’TRUE’
*.query_rewrite_integrity=’TRUSTED’
*.remote_login_passwordfile=’EXCLUSIVE’
*.resource_manager_plan='’
*.undo_management=’AUTO’
*.undo_retention=900
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/product/db/admin/testdb/udump’
*.shared_pool_size=79691776
*.db_cache_size=75497472
*.large_pool_size=62914560
*.java_pool_size=62914560
*.pga_aggregate_target=136314880

Those last five lines are the ones that I needed to have set. The others were nls_length_semantics, qurery_rewrite_enabled and query_rewrite_integrity.

Copyright 2004-2019 Phil Lembo