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/18.104.22.168/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.