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 :)
Cool i was looking for a solution at the exact same problem. It's on Windows, but the solution is identical. Thanks a lot!
ReplyDeleteYou are welcome Ribibi
ReplyDeleteThank you! That worked but can you give a more vivid explanation behind the reason of this problem ?
ReplyDelete