• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Recover From Lost or Missing Database Parameter Files (PFILE or SPFILE)

By admin

This post explains the steps required to recover when the parameter files are lost. The parameter files are read at instance startup time to get instance specific characteristics.

Oracle database : Basics about pfile and spfile

Following are the various scenarios of recovering from the lost parameter file

SPFILE is lost but the pfile is present

1. Rename bad or corrupted spfile from the original location. Then shutdown and restart your database with pfile (init.ora)

SQL> STARTUP PFILE='location/init[SID].ora‎';

2. Then finally create spfile from this pfile. The below command will automatically create spfile with its original name.

SQL> Create spfile from pfile='location/init[SID].ora‎';

Both spfile, as well as pfile, is lost or corrupted and your database is up

1. Parameter files are read while starting up the instance. Hence losing parameter files when the database is up and running won’t have a huge impact (till next shutdown).

2. Query v$parameter for the non default parameters:

SQL> select name,value from v$parameter where ISDEFAULT=’FALSE’ order by name;

3. Create pfile with these parameters:

SQL> Create spfile from pfile='location/init[SID].ora‎';

4. Restart the database with this newly created spfile:

From 11g, you can directly create the pfile or spfile using the FROM MEMORY clause.

CREATE PFILE [= 'pfile_name' ] FROM { { SPFILE [= 'spfile_name'] } | MEMORY } ;
CREATE SPFILE [= 'spfile_name' ] FROM { { PFILE [= 'pfile_name' ] } | MEMORY } ;

Example:

SQL>create pfile='$ORACLE_HOME/dbs/initdb11g.txt' from memory;
File created.

Both spfile, as well as pfile, is lost or corrupted and your database is down

1. In that case, you have remaining option to create new pfile using the non-default parameters recorded in the alert.log file. You can find this in the previous successful startup entries.

2. You can then start the database using this pfile (init.ora):

SQL> STARTUP PFILE='location/init.ora‎';

3. Then finally create spfile from this pfile. The below command will automatically create spfile with its original name.

SQL> Create spfile from pfile='location/init[SID].ora‎';

4. IF RMAN autobackup is configured in any of the above situations, you can restore the same.

RMAN> RESTORE SPFILE FROM AUTOBACKUP;

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle Database – How to recover from a lost datafile with no backup
  2. How to Force ASM to Scan the Multipathed Device First using ASMLIB/oracleasm
  3. Oracle Database 12c New Feature – RMAN “RECOVER TABLE”
  4. Oracle Database Environment Variables and Their Functions
  5. Oracle RMAN Backup Shell Script Example
  6. Oracle Interview Questions : Recovery catalog for RMAN backup
  7. Which SPFILE Parameter File is Impacted when Issuing an ALTER SYSTEM Command
  8. How to Modify an Existing ASM Spfile in a RAC Environment
  9. Script to monitor RMAN Backup and Restore Operations
  10. How to Shrink the datafile of Undo Tablespace in Oracle Database

You May Also Like

Primary Sidebar

Recent Posts

  • What are different Oracle Database Vault Roles
  • Unable to export realm protected table using data pump
  • Beginners Guide to Oracle Database Vault
  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary