Oracle Spfile Location

Last updated on DECEMBER 27, 2019

  1. Pfile And Spfile
  2. Oracle Change Spfile Location
  3. Oracle Get Spfile Location
  4. Oracle Spfile Location Asm
  5. Create Pfile From Spfile 11g

Applies to:

The value of this parameter is the name of the current server parameter file (SPFILE) in use. Rotate video proshow producer. This parameter can be defined in a client side PFILE to indicate the name of the server parameter file to use. When the default server parameter file is used by the server, the value of SPFILE. This file is typically located under the $ ORACLEHOME/dbs directory and spfileORACLESID.ora format. We can say that this file is Oracle Instance configuration file that all parameters are kept for startup of Oracle instance. You can also find the location of this file from the database as follows.

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.1.0.2 [Release 11.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Pfile And Spfile

Goal

Chang Spfile location for ASM instance.

Found different spfile location in each ASM instance and gpnp profile find below case.

Oracle

ASM1:

SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /tmp/spfile.txt
ASM2:
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +VOTING/node103/asmparameterfile/registry.253.841165211

If we use command 'srvctl modify asm -pfile or -spfile <location>' to change location then will get below errors.

Errors:

PRKO-2207 : Warning:-spfile option has been deprecated and will be ignored.

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

1.Connect to target Database with RMAN.
i)If you have lost your spfile and dont have pfile then, follow the steps to recover spfile using RMAN.
RMAN>CONNECT TARGET /
ii)If your database is not up and you don't use recovery catalog then use,

Oracle Change Spfile Location


RMAN>CONNECT TARGET /
RMAN>SET DBID=3148849783
2)Start the instance with dummy parameter file.
OracleRMAN>STARTUP FORCE NOMOUNT
Example:
3)Restore server parameter file.
To restore in default location,
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
To restore in another location,
RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;

Oracle Get Spfile Location


RMAN> restore spfile to '/tmp/spfilejay.ora' from autobackup;
SpfileIf you want to restore to a pfile then use,
RMAN> restore spfile to pfile '/tmp/initjay.ora';

Oracle Spfile Location Asm


4)Restore Spfile from control file autobackup;
RMAN> run
Oracle spfile location asm2> { set controlfile autobackup format for device type disk to '/u01/app/oracle/backup/JAY_%F';
3> restore spfile from autobackup;
Location

Create Pfile From Spfile 11g

4> }
Note after restore spfile from RMAN shutdown and startup the database
else you will receive the error message
ORA-00205: error in identifying control file, check alert log for more info

Reference:
Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)