My Oracle Database is NOT using SPFILE

This is my 3rd encounter with this warning message since working fulltime as dba.

The first time was when i was performing a clone of a development instance in one of the client site.
The 3rd time was today while configuring the backup and recovery via EM for a 10gR2 db used
by EBS 11.5.10.2

While its easy to resolve, im wondering why in the first place this databases are not using spfile at all? I have a vague memory that i have read somewhere that this could be caused by creating the DB instance manually instead of DBCA.

I’ll try to research on this more ..

On the side note.. while doing a quick google of “oracle spfile pfile” i discovered that RMAN’s “CONFIGURE CONTROLFILE AUTOBACKUP ON” will include the SPFILE in backup runs and the poor PFILE will not be.

Converting between PFILES and SPFILES:

One can easily migrate from a PFILE to SPFILE or vice versa. Execute the following commands from a user with SYSDBA or SYSOPER privileges:

SQL> CREATE SPFILE FROM PFILE;

Bounce the database.

To know if our database is using a PFILE or SPFILE:

Execute the following query to see if your database was started with a PFILE or SPFILE:

SQL> SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File Type”
       FROM sys.v_$parameter WHERE name = ‘spfile’;

OR

SQL> SHOW PARAMETER SPFILE

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string

an empty Value indicates that our database is using pfile.

One more interesting bit that i found out is including a COMMENT in the ALTER SYSTEM SET to document the why/when the parameter was changed, like so:

SQL> ALTER SYSTEM SET timed_statistics=TRUE COMMENT=’Changed by joey on 13 March 2009 (testing)’

Of course we can always include comments in pfiles, but we want to use spfile 🙂

Advertisements

About this entry