Oracle SPFILE Missing in V. 7.0 upgrade

book

Article ID: 160406

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

Oracle throws Error ORA-32001 if the SPFILE is missing from the database.

Resolution

Relevant versions:  7.0

The SPFILE file is missing from the Vontu 7.0 release upgrade. This file is required to start the Oracle database. To resolve this issue, check the upgrade.log file for an ORA-32001 error, create the file if it does not exist, and restart Oracle.

To identify the problem:

  1. Search the upgrade.log file for an ORA-32001 error: 
    1. Run the following SQL query:
      SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_CACHING = 90 SCOPE = both; ALTER SYSTEM SET OPTIMIZER_INDEX_CACHING = 90 SCOPE = both

      Output example:
      ERROR at line 1:
      ORA-32001: write to SPFILE requested but no SPFILE specified at startup
      Elapsed: 00:00:00.03 
    2. Run the following SQL query:
      SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 25 SCOPE = both; ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 25 SCOPE = both

      Output example:
      ERROR at line 1:
      ORA-32001: write to SPFILE requested but no SPFILE specified at startup 
  2. Have your DBA create the SPFILE.
    Note: Replace <ORACLE_HOME> with the location of ORACLE _HOME (e.g. C:\oracle\product\10.2.0\db_1). 
    1. Log in to SQL*plus as sysdba. 
    2. Find the location of the SPFILE. Most likely, the file does not currently exist. 
    3. From the command line, show the parameter SPFILE. 
    4. Create a text parameter file using the following:
      set linesize 131
      set trimspool on
      set pagesize 0
      spool <ORACLE_HOME>\database\init.ora
      select name||' = ' || value
      from v$parameter
      where isdefault = 'FALSE'; spool off 
    5. Using a text editor, edit the file to make it look clean. 
    6. Create an SPFILE from the parameter file created above:
      CREATE SPFILE FROM PFILE = '<ORACLE_HOME>\database\init.ora'; 
  3. Restart Oracle.