Change or Multiplex Control File Location

Change or Multiplex Control File Location. 1

Steps Changing the ControlFile Location. 1

Example. 1

Inspect the file location(s) 1

Prepare the ASCII pfile. 2

ShutDown the DB.. 2

Change the control_files Parameter 2

Relocate the ControlFile. 2

StartUp the DB.. 2

Save Change to Spfile. 3

Test the Spfile. 3

Steps Changing the ControlFile Location

1. Shut down the database.

2. Copy an existing control file to a different location, using operating system commands.

3. Edit the CONTROL_FILES parameter in the database's initialization parameter file to add the new control file's name, or to change the existing control filename.

4. Restart the database.

Example

Inspect the file location(s)

--Command prompt …

set ORACLE_SID=orcl

set ORACLE_HOME=E:\oracle\ora92

set PATH=E:\oracle\ora92\bin

sqlplus

Enter user-name: / as sysdba

SQL> Select * from v$controlfile;

STATUS NAME

-------------------------------------

E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL

E:\ORACLE\ORADATA\ORCL\CONTROL02.CTL

E:\ORACLE\ORADATA\ORCL\CONTROL03.CTL

--All control files are on one location !@#

Prepare the ASCII pfile

SQL> create pfile='E:\oracle\admin\orcl\pfile\init14022007.ora' from spfile;

ShutDown the DB

SQL> shutdown immediate;

Change the control_files Parameter

Using OS commands change the location of ex. CONTROL02.CTL from location E:\oracle\oradata\orcl to C:\OraData\orcl

a) Change and save the changes in ascii file E:\oracle\admin\orcl\pfile\init14022007.ora to have ex. line

*.control_files='E:\oracle\oradata\orcl\CONTROL01.CTL','C:\OraData\orcl\CONTROL02.CTL'

Relocate the ControlFile

Move using OS command the file E:\ORACLE\ORADATA\ORCL\CONTROL02.CTL to C:\OraData\orcl\CONTROL02.CTL

StartUp the DB

-- Start the database with new location of CONTROL02.CTL

SQL> startup pfile='E:\oracle\admin\orcl\pfile\init14022007.ora'

SQL> Select * from v$controlfile;

STATUS NAME

------------------------------------

E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL

C:\ORADATA\ORCL\CONTROL02.CTL

Save Change to Spfile

--Save the changes to spfile and check the changes

SQL> create spfile from pfile='E:\oracle\admin\orcl\pfile\init14022007.ora';

Test the Spfile

SQL> shutdown immediate

SQL> startup