Tuesday, August 16, 2016

ORA-32000: write to SPFILE requested but SPFILE is not modifiable

While trying to alter a parameter getting the error below

SQL> alter system set db_recovery_file_dest_size=7G scope=both sid='*';
alter system set db_recovery_file_dest_size=7G scope=both sid='*'
*
ERROR at line 1:
ORA-32000: write to SPFILE requested but SPFILE is not modifiable


SQL>


if you look at alert you will see an error like this

Using parameter settings in server-side spfile +DATA/spfilecdbrac1.ora
WARNING: spfile modifications are disabled because multiple spfiles were used
System parameters with non-default values:

or

Using parameter settings in server-side pfile /u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdbrac1.ora
WARNING: spfile modifications are disabled because multiple spfiles were used
System parameters with non-default values:

or

Using parameter settings in server-side spfile +DATA/cdbrac/spfilecdbracharv.ora
WARNING: spfile modifications are disabled because multiple spfiles were used
System parameters with non-default values:

depending upon your environment.

The first thing you should do is

[oracle@rac1 admin]$ srvctl config database -d cdbracp
Database unique name: cdbracp
Database name: cdbrac
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA/cdbrac/parameterfile/spfilecdbracharv.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: cdbrac1,cdbrac2
Configured nodes: rac1,rac2
Database is administrator managed
[oracle@rac1 admin]$

Now startup nomount the database to get the spfile contents. In mine case there were two different files so what I did was

SQL> create pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/my.ora' from spfile='+DATA/spfilecdbrac1.ora';

File created.

SQL>

[oracle@rac1 dbs]$ cat my.ora
*.SPFILE='+DATA/cdbrac/spfilecdbracharv.ora'
[oracle@rac1 dbs]$

SQL> create pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/my2.ora' from spfile='+DATA/cdbrac/spfilecdbracharv.ora';

File created.

SQL>

[oracle@rac1 dbs]$ cat my2.ora
*.SPFILE='+DATA/cdbrac/spfilecdbrac.ora'
[oracle@rac1 dbs]$


SQL> create pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/my3.ora' from spfile='+DATA/cdbrac/spfilecdbrac.ora';

File created.

SQL>

[oracle@rac1 dbs]$ cat my3.ora
cdbrac2.__data_transfer_cache_size=0
cdbrac1.__data_transfer_cache_size=0
cdbrac2.__db_cache_size=956301312
cdbrac1.__db_ca
.
.
.


You might have to do this till you see the file which holds the parameters.

Now we know that the parameter file is at +DATA/cdbrac/spfilecdbrac.ora which has all parameters.

Next thing you make sure that there is no other parameter file in use and configure this parameter file as main


[oracle@rac1 admin]$ srvctl modify database -d cdbracp -p +DATA/cdbrac/spfilecdbrac.ora
[oracle@rac1 admin]$ srvctl config database -d cdbracp
Database unique name: cdbracp
Database name: cdbrac
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA/cdbrac/spfilecdbrac.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: cdbrac1,cdbrac2
Configured nodes: rac1,rac2
Database is administrator managed
[oracle@rac1 admin]$

Now you can start the database without any issues and change parameter

[oracle@rac1 admin]$ srvctl start database -d cdbracp
[oracle@rac1 admin]$

SQL> alter system set db_recovery_file_dest_size=7G scope=both sid='*';

System altered.

SQL>