Environment Details:
Database Name: test
Current location:
/u01/app/oracle/oradata/test
New Location where files to be relocated :
/u02/BKP/test
Step 1: First source the environment variables and
capture below detail from the database.
- · DB name with version details
- · Data File Names and Locations
- · Control File Names and Locations
- · Temp File Names and Locations
- · Redo Log File Names and Locations
[oracle@dc test]$ . oraenv
ORACLE_SID = [test] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dc test]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 27
10:35:44 2018
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> select name,open_mode,log_mode,database_role from
v$database;
NAME OPEN_MODE LOG_MODE DATABASE_ROLE
--------- -------------------- ------------ ----------------
TEST READ WRITE NOARCHIVELOG PRIMARY
SQL> set lines 300 pages 3000
SQL> col name for a40
SQL> select name "Datafiles" from v$datafile;
Datafiles
--------------------------------------------------------------------
/u01/app/oracle/oradata/test/system01.dbf
/u01/app/oracle/oradata/test/sysaux01.dbf
/u01/app/oracle/oradata/test/undotbs01.dbf
/u01/app/oracle/oradata/test/users01.dbf
SQL> select name "Control Files" from
v$controlfile;
Control Files
--------------------------------------------------------------------
/u01/app/oracle/oradata/test/control01.ctl
/u01/app/oracle/oradata/test/control02.ctl
SQL> select member "Redo Log Files" from
v$logfile;
Redo Log Files
--------------------------------------------------------------------
/u01/app/oracle/oradata/test/redo03.log
/u01/app/oracle/oradata/test/redo02.log
/u01/app/oracle/oradata/test/redo01.log
SQL> select name " Temp Files" from v$tempfile;
Temp Files
--------------------------------------------------------------------
/u01/app/oracle/oradata/test/temp01.dbf
SQL> show parameter control_files
NAME TYPE
VALUE
------------------------------------ -----------
-------------------
control_files string
/u01/app/oracle/oradata/test/control01.ctl, /u01/app/oracle/oradata/test/control02.ctl
Step 2: Set below control file parameter in the
database and shut down the database.
SQL> alter system set
control_files='/u02/BKP/test/control01.ctl','/u02/BKP/test/control02.ctl'
scope=spfile;
System altered.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Step 3: Copy below files from
current location to new location which need to be relocated.
- · Data Files
- · Control Files
- · Temp Files
- · Redo Log Files
[oracle@dc test]$ cd
/u01/app/oracle/oradata/test
[oracle@dc test]$ ll
-rw-r-----. 1 oracle oinstall 9748480 Feb 27 10:39 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 Feb 27 10:39 control02.ctl
-rw-r-----. 1 oracle oinstall 52429312 Feb 27 10:35 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 Feb 27 10:39 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 Feb 27 10:35 redo03.log
-rw-r-----. 1 oracle oinstall 492838912 Feb 27 10:39
sysaux01.dbf
-rw-r-----. 1 oracle oinstall 775954432 Feb 27 10:39
system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Feb 27 10:23 temp01.dbf
-rw-r-----. 1 oracle oinstall 31465472 Feb 27 10:39 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Feb 27 10:39 users01.dbf
[oracle@dc test]$ cp *
/u02/BKP/test/
[oracle@dc test]$ cd /u02/BKP/test
[oracle@dc test]$ pwd
/u02/BKP/test
[oracle@dc test]$ ll
-rw-r-----. 1 oracle oinstall 9748480 Feb 27 10:56 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 Feb 27 10:56 control02.ctl
-rw-r-----. 1 oracle oinstall 52429312 Feb 27 10:56 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 Feb 27 10:56 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 Feb 27 10:56 redo03.log
-rw-r-----. 1 oracle oinstall 492838912 Feb 27 10:56
sysaux01.dbf
-rw-r-----. 1 oracle oinstall 775954432 Feb 27 10:56
system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Feb 27 10:56 temp01.dbf
-rw-r-----. 1 oracle oinstall 31465472 Feb 27 10:56 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Feb 27 10:56 users01.dbf
Step 4: Mount the database and
verify below file names locations.
- Data File Names and Locations
- Control File Names and Locations
- Temp File Names and Locations
- Redo Log File Names and Locations
[oracle@dc test]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 27
10:57:16 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area
588746752 bytes
Fixed Size 2255472 bytes
Variable Size 226493840 bytes
Database Buffers 356515840 bytes
Redo Buffers 3481600 bytes
SQL>
SQL>
SQL> show parameter control_files
NAME TYPE
VALUE
------------------------------------ ----------- -------------------
control_files string
/u02/BKP/test/control01.ctl,
/u02/BKP/test/control02.ctl
SQL> alter database mount;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST MOUNTED
SQL> col name for a40
SQL> col member for a40
SQL> set lines 300 pages 3000
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/test/system01.dbf
/u01/app/oracle/oradata/test/sysaux01.dbf
/u01/app/oracle/oradata/test/undotbs01.dbf
/u01/app/oracle/oradata/test/users01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/test/redo03.log
/u01/app/oracle/oradata/test/redo02.log
/u01/app/oracle/oradata/test/redo01.log
SQL> select name from v$controlfile;
NAME
--------------------------------------------------
/u02/BKP/test/control01.ctl
/u02/BKP/test/control02.ctl
SQL> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/test/temp01.dbf
Step 5: Now rename below files
with new location and verify the same.
SQL> alter database rename file
'/u01/app/oracle/oradata/test/system01.dbf' to '/u02/BKP/test/system01.dbf';
Database altered.
SQL> alter database rename file
'/u01/app/oracle/oradata/test/sysaux01.dbf' to '/u02/BKP/test/sysaux01.dbf';
Database altered.
SQL> alter database rename file
'/u01/app/oracle/oradata/test/undotbs01.dbf' to '/u02/BKP/test/undotbs01.dbf';
Database altered.
SQL> alter database rename file
'/u01/app/oracle/oradata/test/users01.dbf' to '/u02/BKP/test/users01.dbf';
Database altered.
SQL> alter database rename file
'/u01/app/oracle/oradata/test/redo01.log' to '/u02/BKP/test/redo01.log';
Database altered.
SQL> alter database rename file
'/u01/app/oracle/oradata/test/redo02.log' to '/u02/BKP/test/redo02.log';
Database altered.
SQL> alter database rename file
'/u01/app/oracle/oradata/test/redo03.log' to '/u02/BKP/test/redo03.log';
Database altered.
SQL> alter database rename file
'/u01/app/oracle/oradata/test/temp01.dbf' to '/u02/BKP/test/temp01.dbf';
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u02/BKP/test/system01.dbf
/u02/BKP/test/sysaux01.dbf
/u02/BKP/test/undotbs01.dbf
/u02/BKP/test/users01.dbf
SQL> select name from v$controlfile;
NAME
--------------------------------------------------
/u02/BKP/test/control01.ctl
/u02/BKP/test/control02.ctl
SQL> select name from v$tempfile;
NAME
--------------------------------------------------
/u02/BKP/test/temp01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/u02/BKP/test/redo03.log
/u02/BKP/test/redo02.log
/u02/BKP/test/redo01.log
Step 6: Now open the database.
SQL> alter database open;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------------------------------------------------
-----------------
TEST READ WRITE
Alert logfile snap:
Tue Feb 27 11:02:06 2018
alter database rename file
'/u01/app/oracle/oradata/test/system01.dbf' to '/u02/BKP/test/system01.dbf'
Completed: alter database rename file
'/u01/app/oracle/oradata/test/system01.dbf' to '/u02/BKP/test/system01.dbf'
Tue Feb 27 11:02:41 2018
alter database rename file
'/u01/app/oracle/oradata/test/sysaux01.dbf' to '/u02/BKP/test/sysaux01.dbf'
Completed: alter database rename file
'/u01/app/oracle/oradata/test/sysaux01.dbf' to '/u02/BKP/test/sysaux01.dbf'
Tue Feb 27 11:03:15 2018
alter database rename file
'/u01/app/oracle/oradata/test/undotbs01.dbf' to '/u02/BKP/test/undotbs01.dbf'
Completed: alter database rename file
'/u01/app/oracle/oradata/test/undotbs01.dbf' to '/u02/BKP/test/undotbs01.dbf'
Tue Feb 27 11:04:06 2018
alter database rename file
'/u01/app/oracle/oradata/test/users01.dbf' to '/u02/BKP/test/users01.dbf'
Completed: alter database rename file '/u01/app/oracle/oradata/test/users01.dbf'
to '/u02/BKP/test/users01.dbf'
Tue Feb 27 11:04:45 2018
alter database rename file
'/u01/app/oracle/oradata/test/redo01.log' to '/u02/BKP/test/redo01.log'
Completed: alter database rename file '/u01/app/oracle/oradata/test/redo01.log'
to '/u02/BKP/test/redo01.log'
Tue Feb 27 11:05:06 2018
alter database rename file
'/u01/app/oracle/oradata/test/redo02.log' to '/u02/BKP/test/redo02.log'
Completed: alter database rename file
'/u01/app/oracle/oradata/test/redo02.log' to '/u02/BKP/test/redo02.log'
Tue Feb 27 11:05:27 2018
alter database rename file
'/u01/app/oracle/oradata/test/redo03.log' to '/u02/BKP/test/redo03.log'
Completed: alter database rename file
'/u01/app/oracle/oradata/test/redo03.log' to '/u02/BKP/test/redo03.log'
Tue Feb 27 11:06:15 2018
alter database rename file
'/u01/app/oracle/oradata/test/temp01.dbf' to '/u02/BKP/test/temp01.dbf'
Completed: alter database rename file
'/u01/app/oracle/oradata/test/temp01.dbf' to '/u02/BKP/test/temp01.dbf'
Tue Feb 27 11:16:14 2018
alter database open
Tue Feb 27 11:16:14 2018
Thread 1 opened at log sequence 2
Current log# 2 seq#
2 mem# 0: /u02/BKP/test/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is
not set
Tue Feb 27 11:16:14 2018
SMON: enabling cache recovery
[30948] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:39796324
end:39796454 diff:130 (1 seconds)
Verifying file header compatibility for 11g tablespace
encryption..
Verifying 11g file header compatibility for tablespace
encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async
multimaster replication found)
Starting background process QMNC
Tue Feb 27 11:16:15 2018
QMNC started with pid=20, OS id=31201
Completed: alter database open
Tue Feb 27 11:16:17 2018
Starting background process CJQ0
Tue Feb 27 11:16:17 2018
CJQ0 started with pid=22, OS id=31213
Note: File have been relocated to new location without deleting existing one. It will
automatically delete all files located in source location i.e. /u01 which I have relocated, because I have renamed them.
[oracle@dc dbs]$ cd /u01/app/oracle/oradata/TEST
[oracle@dc TEST]$ ll
total 25544
-rw-r-----. 1 oracle oinstall 13058048 Jan 18 12:43
control01.ctl
-rw-r-----. 1 oracle oinstall 13058048 Jan 18 12:43
control02.ctl
[oracle@dc TEST]$
Thanks for reading this post ! Please comment if you like this post !
Thank you for your comment !