• 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 Happens and What to Do when the SPFILE has been Manually Modified

by admin

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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to Enable Fast-Start Failover using Enterprise Manager
  2. How to switch roles in Oracle Data Guard
  3. How to rename Oracle-Managed Files (OMFs)
  4. How to Drop Undo Tablespace in Oracle Database
  5. ORA-00904: invalid identifier
  6. How to split BCV and open oracle ASM database
  7. How to change static parameters through SPFILE parameter file in Oracle Database
  8. Upgrading to Oracle 12c using RMAN DUPLICATE with “NOOPEN” clause and “BACKUP LOCATION”
  9. Oracle Database interview questions – Privileges And Roles
  10. How to reclaim entire space of an oracle database table with “Truncate Table” statement

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