Alternate locations for database parameter files in 11g - Part 7 of 7
REFERENCE A SHARED PFILE FROM AN SPFILE
—————————————-
You can use the IFILE parameter within an SPFILE, to reference a PFILE.
a) Firstly you need to start the database with an SPFILE.
b) Then run the commandS:
alter system set ifile=”C:\Alternate_Location\INITDUMMY.ORA” scope=spfile;
c) Then re-start your database.
Following database startup, the alert log shows:
Using parameter settings in server-side spfile C:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILE.ORA
…
ifile = “C:\Alternate_Location\INITDUMMY.ORA”
I only ran a few brief tests of this configuration, but it looks like:
Same entry in spfile and pfile - spfile entry takes precedence. (remote_login_passwordfile=’EXCLUSIVE’ in spfile, ‘SHARED’ in pfile)
To check that the PFILE parameters were being read, I added the parameter query_rewrite_enabled=FALSE to the pfile and then bounced the instance. (The spfile didn’t contain this parameter and was thus defaulting to the 11g value of TRUE). Following the bounce the parameter was set to be FALSE in the database.
Note: You should be aware that if you’re running with this configuration then you could get unexpected results when running various commands:
“create pfile= … from spfile; - creates a new pfile and retains the settings from the spfile - including the IFILE parameter setting.
“create pfile= … from memory; - creates a new pfile, but adds in settings from the pfile that were not in the spfile.
(i.e. query_rewrite_enabled=FALSE - though this was set in the pfile, not the spfile).
The command also removed the ifile parameter.
Only in the first command is the IFILE command still present in the newly created pfile.
REFERENCE A SHARED SPFILE FROM AN SPFILE
—————————————-
I then started a database with an SPFILE and tried to run the commands:
alter system set spfile=”C:\ALTERNATE_LOCATION\SPFILE.ORA” scope=spfile;
- This just returned the following errors:
ORA-32017: failure in updating SPFILE
ORA-32019: The parameter SPFILE cannot be updated in the server parameter file.
This indicated that it’s not possible to use the SPFILE parameter within an SPFILE, in order to reference a secondary spfile.
If you really want to try this configuration, then the method that follows works:
a) Set the database to start using an SPFILE in the default location.
b) set the spfile to contain the parameter: ifile=’C:\Alternate_Location\INITDUMMY.ORA
c) Edit the file C:\Alternate_Location\INITDUMMY.ORA and remove all contents, apart from the single line entry:
SPFILE=C:\Alternate_Location\SPFILE2.ORA
This then references a second spfile called SPFILE2.ORA, in the location above.
d) Re-start the database and it should now start correctly.
The Alert log shows:
Using parameter settings in server-side spfile C:\ALTERNATE_LOCATION\SPFILE2.ORA
…
spfile = “C:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILE.ORA”
…
ifile = “C:\Alternate_Location\INITDUMMY.ORA”
As you can see, what’s happened is that the alert log entry puts our secondary SPFILE as the primary spfile being used and the spfile in the default
location is being referenced as an SPFILE parameter. (The ifile parameter for our dummy pfile is also shown).
Which SPFILE is being used?
log_archive_dest=’C:\Archive\ORCL11′ - in our SPFILE in the default location.
log_archive_dest=’C:\Alternate_Location’ - in the SPFILE in the alternate location.
sho parameter log_arch returns: C:\Archive\ORCL11 - so the setting from our primary spfile is taking precedence.
sho parameter spfile confirms this: C:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILE.ORA
To be honest, I wouldn’t have thought that you would run with this configuration in any real-life scenario, as it could be the cause of much confusion. The choice would be up to yourself though.
References:
Oracle Database Administrator’s Guide 11g Release 1 (11.1),232587.1,162491.1,811788.1,378021.1,135714.1,314422.1,273674.1