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".
I have been running into some problems recently with 12cR2 databases and Kevin Clossons’ SLOB tool.
The SLOB setup.sh script allows for the concurrent loading of multiple schemas, and if you are loading a large amount of data, being able to load concurrently is a significant time saver.
With LOAD_PARALLEL_DEGREE set to 8, I got the following error:
ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 3
Additional information: 128
Additional information: 140728056780720
These servers were new Dell R630s with plenty of horsepower, so the idea that just 8 parallel threads would cause this type of a failure was puzzling.
Further investigation of the trace file showed that the problem occured on the index shrink command:
SQL> SQL> ALTER INDEX i_cf1 SHRINK SPACE COMPACT
ERROR at line 1:
ORA-12801: error signaled in parallel query server P13L, instance
After some time investigating, it seems Oracle 12c has a much higher target for PARALLEL_MAX_SERVERS and PARALLEL_SERVERS_TARGET. In my case, PARALLEL_MAX_SERVERS had defaulted to 2240.
Since the SLOB data load uses the parallel query option, Oracle was spawning thousands of slave processes all trying to issue ASYNC IO.
So I set the numbers to what I considered more reasonable:
SQL> alter system set parallel_max_servers=400 sid='*';
SQL> alter system set parallel_min_servers=40 sid='*';
SQL> alter system set parallel_servers_target=400 sid='*';
Now SLOB was able to load data with eight concurrent processes.