A method to fix a corrupted SPFILE in ASM

So you’ve made some changes to your SPFILE, and since the parameters you want change cannot be made to a running instance, you used the scope=spfile clause.

Now you go to restart your database, and you find the instance won’t start!

[oracle@sio01-mgmt sql]$ srvctl start database -d dbbench
PRCR-1079 : Failed to start resource ora.dbbench.db
CRS-5017: The resource action "ora.dbbench.db start" encountered the following error:
ORA-01078: failure in processing system parameters
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/sio02-mgmt/crs/trace/crsd_oraagent_oracle.trc".

In this case I set the SGA_MAX_SIZE to 8GB, but forgot to change the SGA_TARGET.  When the target is greater than the max, the instance won’t start.

There are several ways to fix this problem, this is my approach.

First let’s find out where the SPFILE is located.

[oracle@sio01-mgmt dbs]$ srvctl config database -d dbbench
Database unique name: dbbench
Database name: dbbench
Oracle home: /u01/app/db/product/12.2.0.1/db_1
Oracle user: oracle
Spfile: +DGDATA/dbbench/PARAMETERFILE/spfile.331.949851399

Next, let’s copy that SPFILE out of ASM so that we can fix it.

ASMCMD> pwd
+DGDATA/dbbench/PARAMETERFILE
ASMCMD> ls
spfile.331.949851399
ASMCMD> cp spfile.331.949851399 /tmp/sptemp.ora
copying +DGDATA/dbbench/PARAMETERFILE/spfile.331.949851399-> /tmp/sptemp.ora

I now have a copy of the SPFILE as /tmp/sptemp.ora.  Using the UNIX/Linux strings command I can extract an almost usable ASCII version

[oracle@sio01-mgmt dbs]$ strings /tmp/sptemp.ora > initdbbench1.bak

Using your favorite editor, edit the new file and fix any obvious syntax problems. Several lines may have extra carriage returns that will trip up Oracle so join them back together and remove any superfluous spaces and such.

If you are successful, you should be able to startup mount your instance.

SQL> startup mount pfile=initdbbench1.bak
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size 12180200 bytes
Variable Size 6123685144 bytes
Database Buffers 2415919104 bytes
Redo Buffers 38150144 bytes
Database mounted.

Okay good, Oracle is able to start.  Now we need to delete the damaged SPFILE in ASM:

ASMCMD> rm spfile.331.949851399

And create a new one.

SQL> create spfile from pfile='initdbbench1.bak'
File created.

Great, now shutdown the instance.

Check in ASM and see what name was assigned to the new SPFILE we just created  It will be in the same directory as the one we just deleted:

ASMCMD> ls
spfile.331.949851667

Since we create a new SPFILE in ASM, we need to update the location of the SPFILE in the database repository:

[oracle@sio01-mgmt dbs]$ srvctl modify database -d dbbench -p '+DGDATA/dbbench/PARAMETERFILE/spfile.331.949851667';

Now we should be able to restart our database using the repaired SPFILE:

[oracle@sio01-mgmt dbs]$ srvctl start database -d dbbench

 
Now I can set my parameters correctly:

SQL> alter system set sga_max_size=8G scope=spfile sid='*';

System altered.

SQL> alter system set sga_target=8G scope=spfile sid='*';

System altered.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s