Google it ....

Monday, February 11, 2013

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

We have Database with ASM (Automatic Storage Management) and we want to add controlfile.
1) shutdown database with immediate option.
shutdown immediate;

2) start database in nomount mode.
startup nomount;

3) copy already existed controlfile to another location where you want to will be mirrored controlfile.
. oraenv
ORACLE_SID = [orcl] ? +ASM
The Oracle base for ORACLE_HOME=/u0/app/oracle/grid is /u0/app/oracle
-bash-3.2$ asmcmd
ASMCMD> cp +data/orcl/controlfile/Current.260.805917717 +data/orcl/controlfile/control01.ctl
ASMCMD> exit

4) change parameter control_files.
. oraenv
ORACLE_SID = [+ASM] ? orcl
The Oracle base for ORACLE_HOME=/u0/app/oracle/product/11.2.0/dbhome_1 is /u0/app/oracle
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 11 16:49:18 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter system set control_files='+DATA/orcl/controlfile/current.260.805917717', 
        '+DATA/orcl/controlfile/control01.ctl' scope=spfile;
 alter system set control_files='+DATA/orcl/controlfile/current.260.805917717', 
        '+DATA/orcl/controlfile/control01.ctl' scope=spfile
*
ERROR at line 1:
ORA-32000: write to SPFILE requested but SPFILE is not modifiable

There is question, What can we do?
first of all check parameter spfile, for this :
SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u0/app/oracle/product/11.2.0/
                                                 dbhome_1/dbs/spfileorcl.ora

Bingo! This is reason! because we have ASM and spfile must use from ASM.

5) shutdown database;
shutdown immediate;

6) delete this spfile from filesystem.
rm -rf /u0/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora

7) Now we have /u0/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora file with content
*.SPFILE='+DATA/orcl/spfileorcl.ora' and in ASM is +DATA/orcl/spfileorcl.ora file.

8) now we can change control_files parameter.
SQL> startup nomount;
ORACLE instance started.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileorcl.ora

SQL> alter system set control_files='+DATA/orcl/controlfile/current.260.805917717', 
        '+DATA/orcl/controlfile/control01.ctl' scope=spfile;

System altered.

shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 3190132736 bytes
Fixed Size                  2217584 bytes
Variable Size            2432698768 bytes
Database Buffers          738197504 bytes
Redo Buffers               17018880 bytes
Database mounted.
Database opened.
SQL>  select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/controlfile/current.260.805917717
+DATA/orcl/controlfile/control01.ctl

Thank you :)

3 comments:

  1. Cool i was looking for a solution at the exact same problem. It's on Windows, but the solution is identical. Thanks a lot!

    ReplyDelete
  2. Thank you! That worked but can you give a more vivid explanation behind the reason of this problem ?

    ReplyDelete