Search this blog for other interesting articles

Thursday 28 July 2011

Steps to be followed to rename or move control files location

This article answers the following queries :
·         How to find out the current location of control files?
·         Can we rename or move control file location? What is the parameter for that ?
·         What is the process to be followed to rename or move control file location?
-----------------------------------------------------------------------------------------
Query to find current location of control files :

SQL > select name from v$controlfile;

After executing the above query, System outputs  the location of all control files in the system
Usually there will be 3 control files in oracle database( for SAP applications).

Can we rename or move control files location? If so, what is that parameter?

Yes. If required, control files location can changed with the control_files instance parameter.
Please execute the below query to display the current value of control_files parameter
SQL > show parameter control_files
The above command outputs the current value. This value can be changed with the ALTER SYSTEM command. In other words the current location of the control files can be changed  using ALTER SYSTEM command.

Steps to be followed to rename or move control files location

1)      Alter the control_files parameter using the ALTER SYSTEM command
Syntax:
SQL> ALTER SYSTEM SET control_files=’<New controlfile1 path or newcontrol file name>’, <New controlfile2 path or newcontrol file name>’, <New controlfile13 path or newcontrol name>’ SCOPE=SPFILE;
 Eg: In the below command set the paths or names  for various control files  as per your requirement
SQL> ALTER SYSTEM SET control_files=’D:\ORACLE\..\..\..\Newname1.CTL’, 
’D:\ORACLE\..\..\..\Newname2.CTL’,  ’D:\ORACLE\..\..\..\Newname3.CTL’
SCOPE=SPFILE;

2)     Shutdown the database
SQL > shutdown immediate;

3)     Rename the physical file in the OS
SQL >  HOST  MOVE <old controlfile with path> <new controlfile with path >
 Eg:
SQL > HOST  MOVE  D:\ORACLE\ORADATA\CONTROL01.CTL  D:\ORACLE\ORADATA\NEW_CONTROL01.CTL
4)     Start the database
SQL > startup



Enter your email address:


Delivered by FeedBurner

No comments:

Search this blog for other interesting articles

Please subscribe & activate link received to your email id to receive latest articles

Enter your email address:

Delivered by FeedBurner