Using IFILE and SPFILE with parameter files in alternate locations
——————————————————————

In Part 1 of this blog we briefly described what SPFILEs are and where they are usually located.  In this and later parts, we’ll go through different
location scenarios for these files.

For the examples used in the blogs, we’ll use the alternate location of “C:\Alternate_Location”, but this location could be any directory on the server of your choice - assuming that the user you’re logged in as, has permissions to create files in this area.

For these notes, the test database name is ORCL11. You will obviously need to replace these entries with the name of your database.

Note: When using these notes, if you have any other files called spfile$ORACLE_SID.ora, spfile.ora or init$ORACLE_SID.ora in the default location of $ORACLE_HOME/dbs on Unix or %ORACLE_HOME%\database on windows, then you should re-name these files during any testing, otherwise their presence may
mean that Oracle will reference these files, instead of the ones used in the scenarios.  Double-check any symbolic link settings on Unix and any registry/windows service/oradim settings on Windows before carrying out this work, otherwise you may find that you get unexpected results. Refer to this and later blogs for more information on how the files can be configured. (Or refer to the Oracle documentation as normal).

USING THE IFILE PARAMETER TO REFERENCE A PFILE
———————————————-
a) In the default location create a file called initORCL11.ora
b) The only entry that needs to be in this file is the single line:    IFILE=C:\Alternate_Location\DBPARAMS.ora
c) Create a file called C:\Alternate_Location\DBPARAMS.ora and store the database parameters in this file.
d) Re-start your database and it should now pick up the settings from the C:\Alternate_Location\DBPARAMS.ora

A new 11g feature is to write details of the parameter file that was used to start the instance to the database alert log. 
In this case the entry was:

Using parameter settings in server-side pfile C:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\INITORCL11.ORA
…
ifile                    = “C:\Alternate_Location\DBPARAMS.ora”

Note: The extract above was taken from the text-based version of the alert log, stored under C:\app\Administrator\diag\rdbms\orcl11\orcl11\trace\alert_orcl11.log

Notice that the alert log displays the fact that you have used the IFILE parameter.

Some sites use this feature to enable them to use a parameter file that contains settings that are shared between several instances (e.g. CURSOR_SHARING, DB_BLOCK_SIZE), whilst also using another parameter file that contains any settings that are unique to an individual instance. (e.g. DB_NAME, CONTROL_FILES) 

This means that any amendments to any shared parameters can be made in one central location. The obvious drawback to this procedure is that any failure or corruption of the shared file will also affect any instances that reference the file.  This may not be a great problem, as the file is only referenced at startup, so unless you’re re-starting several databases at the same time, the issue will usually only affect the database that you’re working on. 
Note: You can’t use the IFILE parameter to point to an SPFILE, as Oracle expects an SPFILE to be in a binary format. If you try, you see the errors: “ORA-01078: failure in processing system parameters” and “LRM-00123: invalid character 0 found in the input file”.
 
USING THE SPFILE PARAMETER TO REFERENCE AN SPFILE
————————————————–

a) Create a new spfile by using one of the following options or similar:
 
     create spfile=’C:\Alternate_Location\SPFILE.ora’ from pfile; 
     or use the new 11g option - create spfile=’C:\Alternate_Location\SPFILE.ora’ from memory;
     or just move an existing spfile to C:\Alternate_Location\SPFILE.ora.   

Note: You can’t use the commands create spfile=’C:\Alternate_Location\SPFILE.ora’ from  spfile; - ORA-00922: missing or invalid option
b) Once the spfile is in the alternate location, create a file in the default location called initORCL11.ora which just contains the single entry:  
    
SPFILE=C:\Alternate_Location\SPFILE.ora

c) Re-start your database and it should now pick up settings from the SPFILE. 
Entries in the alert log are:

Using parameter settings in server-side pfile C:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\INITAPPEXDEV.ORA
…
spfile                   = “C:\ALTERNATE_LOCATION\SPFILE.ORA”

Note: You can’t use “SPFILE=” to point to a pfile, because Oracle expects the file type to be a binary file - not a text file.

Errors returned if you do try this are:

ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file ‘C:\app\Administrator\product\11.1.0\db_1\database\pfiletest\INITorcl11.ORA’
ORA-27046: file size is not a multiple of logical block size/OSD-04012: file size mismatch (OS 1182)
 

This entry was posted on Monday, September 14th, 2009 at 3:48 pm and is filed under - Global Oracle Contractors Network, Oracle Database. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.