• 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 Modify spfile in Oracle Database

By admin

SPFILE: The Server Parameter File

Beginning with Oracle9i, Oracle introduced the concept of the server parameter file. This file, known as SPFILE , differs from the standard INIT.ORA file in a number of ways:

  • It is a binary file, rather than a text-based file.
  • It is stored on the server, rather than on a client machine.
  • It can maintain changes to parameter values over the shutdown and startup of an instance.

This last point is the important part of SPFILE. If your database is running under Oracle9i, any changes you make to configuration parameters via the ALTER SYSTEM statement will be saved as part of the permanent configuration file. That means that if you change any of your database parameter values for tuning purposes, you won’t also have to change one or more INIT.ORA files so that the new values will persist. You also have the option of making dynamic changes to parameters without making them a part of the SPFILE.

How to Modify the Content of an SPFILE File

In order to modify the spfile in oracle database, do one of the following:

1. Use the ALTER SYSTEM command to modify the SPFILE currently in use

1. To determine if an spfile is being used:

SQL> select value from v$parameter where name='spfile'

2. To modify the contents of the spfile:

SQL> alter system set undo_management=auto scope=spfile;
System altered.

This sets undo_management to auto.

SQL> alter system set resource_manager_plan='' scope=spfile; 
System altered.

This sets the resource_manager_plan parameter to null.

NOTE: Parameters starting with an underscore must be enclosed with “” (double quotes) to prevent an ORA-00911: invalid character

2. Use the export method.

1. Export the SPFILE to a PFILE. The resulting PFILE will be an editable ASCII file. Examples:

SQL> create pfile [= 'pfile_name' ] from spfile [= 'spfile_name'];
SQL> create pfile from spfile;

The PFILE is created in directory $ORACLE_HOME/dbs and named init[SID].ora.

SQL> create pfile = '/testcasesADC/137483.1/initV1023U.ora' from spfile;

The PFILE is created as ‘/testcasesADC/137483.1/initV1023U.ora’

SQL> create pfile = '/testcasesADC/137483.1/initV1023U_test.ora' from spfile = '/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora';

The PFILE is created as ‘/testcasesADC/137483.1/initV1023U_test.ora’ and the location of the spfile was explicit.

2. Modify the PFILE parameter value with a text editor and save the file:

undo_management=auto
resource_manager_plan=''

3. Recreate the SPFILE from the modified PFILE:

– If the instance is down:

SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';

– If the instance is up and you try to recreate the spfile the database was started with a naming conflicts occurs. You will recieve error ORA-32002.

SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';

ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance

To overcome this error specify a different file name and then rename the spfile once the instance is shutdown:

SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U_new.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';

Filed Under: oracle

Some more articles you might also be interested in …

  1. Upgrading to Oracle 12c using RMAN DUPLICATE with “NOOPEN” clause and “BACKUP LOCATION”
  2. Script to verify the Oracle DataPump Data Dictionary Catalog
  3. Interview Questions : Oracle Flex ASM 12c
  4. How to Recover DROPPED PDB After Flashback of CDB
  5. Oracle RMAN 11g New Feature – MultiSection Backups
  6. How to Create the Oracle Password File using orapwd Command
  7. Oracle 11g – New ASM features
  8. How to move ASM spfile from External Redundancy To Normal Redundancy in version 12.1.0.2 and above
  9. How to Find OCR Master Node
  10. How to Restore a Dropped Pluggable Database (PDB) in Multitenant Environment

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary