Thursday, August 8, 2013

Changing Control File location on Database and Re-Creating Control Files


Changing the location of Control File:

SQL> ALTER SYSTEM SET control_files='E:\oracle\oradata\VINAYDEV\CONTROL02.CTL','
E:\ORADATA\VINAYDEV\CONTROL01.CTL' SCOPE=SPFILE;

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

move the physical file on OS-(differenet OS examples):
windows- cut , paste and rename the file accordingly.
SQL> HOST MOVE E:\oracle\oradata\VINAYDEV\CONTROL02.CTL E:\ORADATA\VINAYDEV\CONTROL01.CTL
$ mv E:\oracle\oradata\VINAYDEV\CONTROL02.CTLE:\ORADATA\VINAYDEV\CONTROL01.CTL

SQL> startup
ORACLE instance started.

Total System Global Area 3423965184 bytes
Fixed Size                  2257984 bytes
Variable Size            1862273984 bytes
Database Buffers         1543503872 bytes
Redo Buffers               15929344 bytes
Database mounted.
Database opened.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Re-Creating Control Files:

Since this file is heart of a Oracle Database, and unfortunately if anything goes wrong below are the steps to re-create the control files-

CREATE CONTROLFILE REUSE DATABASE "vinaydev" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 449
LOGFILE
  GROUP 1 'E:\oracle\oradata\VINAYDEV\REDO01.LOG'  SIZE 50M,
  GROUP 2 'E:\oracle\oradata\VINAYDEV\REDO02.LOG'  SIZE 50M,
  GROUP 3 'E:\oracle\oradata\VINAYDEV\REDO03.LOG'  SIZE 50M
# STANDBY LOGFILE
DATAFILE
  'E:\oracle\oradata\VINAYDEV\SYSAUX01.DBF',
  'E:\oracle\oradata\VINAYDEV\SYSTEM01.DBF',
  'E:\oracle\oradata\VINAYDEV\UNDOTBS01.DBF',
  'E:\oracle\oradata\VINAYDEV\USERS01.DBF'
CHARACTER SET WE8MSWIN1252;

SQL>SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';
- will show the updated paths.

No comments:

Post a Comment