Relocate Oracle Database data files

In this tutorial we will se how to relocate database data files offline. This can be done when doing S.O. disk maintenance. This can be done offline and also online.

  • 1-Find the location of your datafiles :
    • SQL> set pagesize 600
SQL> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
/u00/oracle/oradata/RCAT/system01.dbf
/u00/oracle/oradata/RCAT/sysaux01.dbf
/u00/oracle/oradata/RCAT/undotbs01.dbf
/u00/oracle/oradata/RCAT/users01.dbf
/u00/oracle/oradata/RCAT/RMAN_CATALOG.dbf
/u00/oracle/oradata/RCAT/ruei.dbf
/u00/oracle/oradata/RCAT/users00.dbf
/u00/oracle/oradata/RCAT/users02.dbf
/u00/oracle/oradata/RCAT/users03.dbf
/u00/oracle/oradata/RCAT/users04.dbf
/u00/oracle/oradata/RCAT/users05.dbf
/u00/oracle/oradata/RCAT/users06.dbf
/u00/oracle/oradata/RCAT/users07.dbf
/u00/oracle/oradata/RCAT/users08.dbf
/u00/oracle/oradata/RCAT/users09.dbf
/u00/oracle/oradata/RCAT/users10.dbf
/u00/oracle/oradata/RCAT/users11.dbf
/u00/oracle/oradata/RCAT/users12.dbf
/u00/oracle/oradata/RCAT/users13.dbf
/u00/oracle/oradata/RCAT/users14.dbf
/u00/oracle/oradata/RCAT/users15.dbf
/u00/oracle/oradata/RCAT/users16.dbf
/u00/oracle/oradata/RCAT/users17.dbf
/u00/oracle/oradata/RCAT/users18.dbf
/u00/oracle/oradata/RCAT/users19.dbf
/u00/oracle/oradata/RCAT/users20.dbf
/u00/oracle/oradata/RCAT/users21.dbf
/u00/oracle/oradata/RCAT/users22.dbf
/u00/oracle/oradata/RCAT/users23.dbf
/u00/oracle/oradata/RCAT/users24.dbf
/u00/oracle/oradata/RCAT/users25.dbf
/u00/oracle/oradata/RCAT/users26.dbf
/u00/oracle/oradata/RCAT/users27.dbf
/u00/oracle/oradata/RCAT/users28.dbf
/u00/oracle/oradata/RCAT/users29.dbf
/u00/oracle/oradata/RCAT/uxconf01.dbf
/u00/oracle/oradata/RCAT/uxstat01.dbf
/u00/oracle/oradata/RCAT/uxtemp01.dbf

38 rows selected.
  • 2-Create the move script (this script will help you move your data files to the new location).

    • SQL> select ' mv  '||file_name|| '  /u01/oracle/oradata/RCAT/' from  dba_data_files;

'MV'||FILE_NAME||'/U01/ORACLE/ORADATA/RCAT/'
--------------------------------------------------------------------------------
 mv  /u00/oracle/oradata/RCAT/users01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/undotbs01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/sysaux01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/system01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/RMAN_CATALOG.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/ruei.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users00.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users02.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users03.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users04.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users05.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users06.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users07.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users08.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users09.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users10.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users11.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users12.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users13.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users14.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users15.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users16.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users17.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users18.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users19.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users20.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users21.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users22.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users23.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users24.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users25.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users26.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users27.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users28.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users29.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/uxconf01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/uxstat01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/uxtemp01.dbf  /u01/oracle/oradata/RCAT/

38 rows selected.
  • 3-Shutdown your database.
    • SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
  • 4-Run the "mv" script create at step 2.
    •  mv  /u00/oracle/oradata/RCAT/users01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/undotbs01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/sysaux01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/system01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/RMAN_CATALOG.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/ruei.dbf  /u01/oracle/oradata/RCAT/
 ..............
 ..............
 ..............
  • 5-Start you database in mount mode().
    • [oracle@DCG023 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 9 13:52:10 2013

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size            1006635120 bytes
Database Buffers          587202560 bytes
Redo Buffers                7360512 bytes
Database mounted.
SQL>
  • 6-Rename the file within the Oracle dictionary 
    • alter database rename file '/u00/oracle/oradata/RCAT/users01.dbf' to '/u01/oracle/oradata/RCAT/users01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/undotbs01.dbf' to '/u01/oracle/oradata/RCAT/undotbs01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/sysaux01.dbf' to '/u01/oracle/oradata/RCAT/sysaux01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/system01.dbf' to '/u01/oracle/oradata/RCAT/system01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/RMAN_CATALOG.dbf' to '/u01/oracle/oradata/RCAT/RMAN_CATALOG.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/ruei.dbf' to '/u01/oracle/oradata/RCAT/ruei.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users00.dbf' to '/u01/oracle/oradata/RCAT/users00.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users02.dbf' to '/u01/oracle/oradata/RCAT/users02.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users03.dbf' to '/u01/oracle/oradata/RCAT/users03.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users04.dbf' to '/u01/oracle/oradata/RCAT/users04.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users05.dbf' to '/u01/oracle/oradata/RCAT/users05.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users06.dbf' to '/u01/oracle/oradata/RCAT/users06.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users07.dbf' to '/u01/oracle/oradata/RCAT/users07.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users08.dbf' to '/u01/oracle/oradata/RCAT/users08.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users09.dbf' to '/u01/oracle/oradata/RCAT/users09.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users10.dbf' to '/u01/oracle/oradata/RCAT/users10.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users11.dbf' to '/u01/oracle/oradata/RCAT/users11.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users12.dbf' to '/u01/oracle/oradata/RCAT/users12.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users13.dbf' to '/u01/oracle/oradata/RCAT/users13.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users14.dbf' to '/u01/oracle/oradata/RCAT/users14.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users15.dbf' to '/u01/oracle/oradata/RCAT/users15.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users16.dbf' to '/u01/oracle/oradata/RCAT/users16.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users17.dbf' to '/u01/oracle/oradata/RCAT/users17.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users18.dbf' to '/u01/oracle/oradata/RCAT/users18.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users19.dbf' to '/u01/oracle/oradata/RCAT/users19.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users20.dbf' to '/u01/oracle/oradata/RCAT/users20.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users21.dbf' to '/u01/oracle/oradata/RCAT/users21.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users22.dbf' to '/u01/oracle/oradata/RCAT/users22.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users23.dbf' to '/u01/oracle/oradata/RCAT/users23.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users24.dbf' to '/u01/oracle/oradata/RCAT/users24.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users25.dbf' to '/u01/oracle/oradata/RCAT/users25.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users26.dbf' to '/u01/oracle/oradata/RCAT/users26.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users27.dbf' to '/u01/oracle/oradata/RCAT/users27.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users28.dbf' to '/u01/oracle/oradata/RCAT/users28.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users29.dbf' to '/u01/oracle/oradata/RCAT/users29.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/uxconf01.dbf' to '/u01/oracle/oradata/RCAT/uxconf01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/uxstat01.dbf' to '/u01/oracle/oradata/RCAT/uxstat01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/uxtemp01.dbf' to '/u01/oracle/oradata/RCAT/uxtemp01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/uxstat01.dbf' to '/u01/oracle/oradata/RCAT/uxstat01.dbf';
  • 7-Open your database
    • SQL>  ALTER DATABASE OPEN;
Database altered.
SQL>

    Ok, that was it , we just altered our data file locations using the offline way.

    Copyright 2020. All Rights Reserved