The Geek Diary

What Happens and What to Do when the SPFILE has been Manually Modified

by

The Problem

The SPFILE has previously been manually edited when the instance was opened.

$ ls -l $ORACLE_HOME/dbs/spfileRel14.ora
-rw-rw----   1 sme   dba    907 Mar 19 09:17 /ora9i/dbs/spfileRel14.ora

a. Once you attempt to store new parameter values, you get the following error:

SQL> alter system set processes=70 scope=spfile;
alter system set processes=70 scope=spfile
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

b. Once you attempt to start the database up whereas there is not any initSID.ora parameter file anymore, you get the following error because the instance expects to use the spfile by default, but it is not usable:

SQL> startup
ORA-01078: failure in processing system parameters

The Solution

The SPFILE is a binary file. It can only be modified by ALTER SYSTEM SQL command or recreated from an existing init.ora parameter file and you must not change/edit it directly. Follow the steps outlined below to recreate spfile.

Recreate a binary SPFILE from an old init.ora, if any exists:

SQL> create spfile from pfile='initRel14bis.ora';
File created.

If no init.ora file exists, recreate one and then create the binary SPFILE fromit then a new usable spfileRel14.ora will be available.

$ ls  -l sp*
-rw-r-----   1 sme      dba         2560 Mar 19 15:30 spfileRel14.ora
Note that the file size has changed, though it contains the same list of parameters and values.

If no old init.ora file exists, you can still try and retrieve the parameters from the corrupted spfile using the strings command (native on Unix and available with GNU utilities for Win32 on http://unxutils.sourceforge.net/ )

Example (on Linux/Unix)

$ cd $ORACLE_HOME/dbs 
$ strings spfileRel14.ora > initRel14.ora 
$ mv spfileRel14.ora spfileRel14.ora_CORRUPT

Using any suitable text editor assure/validate correctness of the contents of the initRel14.ora (for any irrelevant strings or characters – that might happen or exist due CR character conversions or any characters while conversion) 

$ export ORACLE_SID=Rel14 
$ sqlplus / as sysdba 
SQL> create spfile from pfile; 
SQL> exit

From now on you got the healthy spfile.

You May Also Like