• 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

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. Basics of Materialized Views in Oracle
  2. Extend rule sets by using factors in Oracle Database Vault
  3. PL/SQL Nested Blocks
  4. How to Restore and Recover files over network in Oracle 12c
  5. How to Migrate ASM Disk Groups to another Storage Online [non ASMLIB Devices]
  6. How To Disable the Oracle WebLogic Server Default Welcome Page
  7. How to limit access to oracle database so that only 1 user per schema is connected ( 1 Concurrent user per schema)
  8. Create an output file from GGSCI commands
  9. 12c ASM: PRCR-1001 : Resource ora.proxy_advm Does Not Exist (Flex ASM with Cardinality = ALL)
  10. How to list all the named events set for a database

You May Also Like

Primary Sidebar

Recent Posts

  • What are /dev/zero and /dev/null files in Linux
  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright