• 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. How to quiesce an Oracle 12c RAC database
  2. Create an output file from GGSCI commands
  3. How to Delete ASM Disk on Multipath Device in CentOS/RHEL
  4. Oracle Tablespace Transport for a Single Partition
  5. Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In Oracle RAC
  6. How to Modify spfile in Oracle Database
  7. What is the difference between PRIMARY Key and UNIQUE Key
  8. 12c ASM: PRCR-1001 : Resource ora.proxy_advm Does Not Exist (Flex ASM with Cardinality = ALL)
  9. How to change max_string_size value from STANDARD to EXTENDED
  10. ORA-01666: control file is for a standby database – failover over standby as primary

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright