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

The Geek Diary

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

What is spfile in Oracle Database

by admin

What is an SPFILE?

A server parameter file (SPFILE) is a new feature introduced in Oracle9i. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This eliminates the need to manually update initialization parameters in an init.ora and have the instance restarted to get changes effective.

When is the SPFILE used?

When the database is created with the Database Configuration Assistant the service is created without pointing out a specific parameter file. An SPFILE is created and placed under $ORACLE_HOME/database.

In this case the file that is used during startup of the database via a service or the command startup with no pfile argument will be the binary file spfile located in the $ORACLE_HOME/database directory. If you don’t want the service to use the spfile you must recreate the service or edit the service to point out a specific pfile.

On the other hand if you have created a service with the oradim command pointing out the pfile and you want to use the spfile instead. Edit the registry in HKEY_LOCAL_MACHINE/SOFTWARE/oRACLE/HOMEX by removing the key ORA_SID_PFILE which is created when the service was created with the oradim command.

Does the changes of the parameter file (pfile) get reflected in the database after restart?

It is very important to know that any changes in the init.ora file will not be reflected in the database when restarted unless you start the database pointing out the pfile explicitly when issuing the startup command. If you later on startup the database with the service it will again pick the values from the spfile.

How do I change the parameter values in an SPFILE?

The following command changes the sort_area_size setting in the SPFILE only.

SQL> alter system set sort_area_size=65536 scope=spfile;

The following command changes the sort_area_size setting in memory and in the SPFILE.

SQL> alter system set sort_area_size=65536 scope=both;

Can I create a SPFILE if I don’t have any?

The following command creates an SPFILE from a PFILE:

SQL> create SPFILE='d:\oracle\ora92\database\spfilep902.ora' from Pfile='d:\oracle\ora92\database\initp902.ora';

If I have created a new service which is using the pfile how do I change this to use the SPFILE instead?

Remove the entry ORA_[SID]_PFILE parameter from your HOME in the registry.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Understanding Oracle Database Recyclebin Features and How to Disable it
  2. How to Create a Physical Standby from ASM Primary
  3. How to Install/Uninstall/Upgrade Oracle SQLTXPLAIN (SQLT) Tool
  4. How to set custom device names using udev in CentOS/RHEL 7
  5. How to determine which user is using what rollback segment?
  6. Basics of client connectivity in Oracle Data Guard configuration
  7. Oracle Database Storage Architecture – Overview
  8. How to move ASM spfile to a different disk group
  9. How to delete archives from only 1 archive destination when 2 or more are in use
  10. When to Use Startup/Shutdown Database and Alter Database Command in Oracle 12c

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright