Saturday, September 15, 2012

Oracle Controlfile move or rename.

If you need to move/rename Oracle controlfile this is how to do it:
#Check current controlfiles from sqlplus (as oracle user in database server):
sqlplus / as sysdba
SQL>  select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------

/data/oradata/testdb/CONTROL01.CTL
/arch/oradata/testdb/CONTROL02.CTL
/redo/oradata/testdb/CONTROL03.CTL



#Check parameter which set's the controlfiles:
SQL> show parameter control_files

NAME                                                             TYPE                                                         VALUE
-----------------------------------------------------  -------------------------------------------------  -----------------
----------------------------
control_files                                                    string                                                           /data/oradata/
testdb/CONTROL01.CTL,  /arch/oradata/testd
b/CONTROL02.CTL, /redo/oradat
a/testdb/CONTROL03.CTL



# Change control_files value in SPFILE (I change CONTROL03.CTL to CONTROL03_NEW.CTL):
SQL> alter system set control_files='/data/oradata/testdb/CONTROL01.CTL', '/arch/oradata/testdb/CONTROL02.CTL', '/redo/oradata/testdb/CONTROL03_NEW.CTL' SCOPE=SPFILE;

#Shutdown database changes take effect after you restart the database:
SQL> shutdown immediate;
SQL> exit


#Make same change from OS side (as oracle user):
In unix:
mv /redo/oradata/testdb/CONTROL03.CTL /redo/oradata/testdb/CONTROL03_NEW.CTL
In Windows you can do this change from Windows explorer.


#Restart database from sqlplus (as oracle user):
sqlplus / as sysdba
SQL> startup


#Check that controlfiles changes are ok:
SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------

/data/oradata/testdb/CONTROL01.CTL
/arch/oradata/testdb/CONTROL02.CTL
/redo/oradata/testdb/CONTROL03_NEW.CTL

No comments:

Post a Comment