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 modifiableThere 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.oraBingo! 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.ctlThank you :)