How to move control files from one mount point to other location.

How to move control file from one mount point to different mount point.

Step 1: Set the environment for specific SID

[oracle@server3 ~]$ . oraenv

ORACLE_SID = [TEST] ?

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

Step 2: Connect to database

[oracle@server3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 13 14:59:34 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Step 3: Find the name, version and control files location.

SQL> select name from v$database;

NAME

---------

TEST

SQL> select banner from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE     11.2.0.4.0             Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> show parameter control_files

NAME                                                        TYPE VALUE

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

control_file_record_keep_time                     integer            7

control_files                                            string                /u01/app/oracle/oradata/TEST/c

                                                                                                 ontrol01.ctl, /u01/app/oracle/

                                                                                                 fast_recovery_area/TEST/contro

                                                                                                 l02.ctl

Step 4: Create new mount point and directory structure. In my case /u02 is the new  mount point

[oracle@server3 TEST]$ mkdir -p /u02/oradata/TEST

Step 5: Take backup of pfile and spfile.

[oracle@server3 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

[oracle@server3 dbs]$ mv initTEST.ora initTEST.ora_bkp

[oracle@server3 dbs]$ mv spfileTEST.ora spfileTEST.ora_bkp

Step 6: Shutdown the database

Step 7: Move the control files physically from existing location to new mount point.

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/control01.ctl /u02/oradata/TEST/

[oracle@server3 ~]$ mv /u01/app/oracle/fast_recovery_area/TEST/control02.ctl /u02/oradata/TEST/

Step 8: Start the database in nomount stage

Step 9: update the new location in control file with below command.

SQL> alter system set control_files='/u02/oradata/TEST/control01.ctl','/u02/oradata/TEST/control02.ctl' scope=spfile;

System altered.

Step 10: Shutdown database.

SQL> shut immediate

ORA-01507: database not mounted

ORACLE instance shut down.

Step 11: Start database in nomount point.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  221331456 bytes

Fixed Size                                2251856 bytes

Variable Size                        163578800 bytes

Database Buffers                50331648 bytes

Redo Buffers                         5169152 bytes

SQL>

Step 12: Check the control file for new location

SQL> show parameter control_files

NAME                                                        TYPE VALUE

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

control_files                                            string                /u02/oradata/TEST/control01.ct

                                                                                                 l, /u02/oradata/TEST/control02

                                                                                                 .ctl

Step 13: Start database in mount stage and then open it.

SQL> select name,open_mode from v$database;

NAME     OPEN_MODE

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

TEST         READ WRITE