Easiest way to move oracle database files from one location to another

In this we will only copy the datafiles from one location to another and then create the symbolic link ot the new location.
Basically we are bluffing database, database from OS layer. Database will see that the datafiles are still at the same location.
Physically datafiles are located at different location and symbolic link is making it possible.
This technique helps moving datafiles on different disks without issuing any command in database.

This will be done with an outage and the only two database commands issued are shutdown immediate and startup.

The example of achieving this is demonstrated below:


1. Shutdown the database.

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

2. Copy the files to new location but with the same name of the containing folder. I am copying because I want to keep the backup of the database.

$ pwd
/u01/app/oracle/oradata/new_location
$ ls -lah
total 3.9G
drwxr-xr-x 2 oracle oinstall 4.0K Feb  7 13:48 .
drwxr-x--- 5 oracle oinstall 4.0K Feb  7 11:12 ..
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 control01.ctl
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 example01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 example02.dbf
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 michael01.dbf
-rw-r----- 1 oracle oinstall 253M Feb 14 09:35 michael02.dbf
-rw-r----- 1 oracle oinstall  51M Feb 14 01:06 redo01.log
-rw-r----- 1 oracle oinstall  51M Feb 14 08:00 redo02.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:35 redo03.log
-rw-r----- 1 oracle oinstall 1.3G Feb 14 09:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 741M Feb 14 09:35 system01.dbf
-rw-r----- 1 oracle oinstall 501M Feb 14 09:35 undotbs01.dbf
-rw-r----- 1 oracle oinstall 826M Feb 14 09:35 users01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 users02.dbf
$ mkdir /u01/app/oracle/oradata/orcl/new_location
$ pwd
/u01/app/oracle/oradata/new_location
$ cp -p * ../orcl/new_location/
$ ls -lah ../orcl/new_location/*
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 ../orcl/new_location/control01.ctl
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 ../orcl/new_location/example01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 ../orcl/new_location/example02.dbf
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 ../orcl/new_location/michael01.dbf
-rw-r----- 1 oracle oinstall 253M Feb 14 09:35 ../orcl/new_location/michael02.dbf
-rw-r----- 1 oracle oinstall  51M Feb 14 01:06 ../orcl/new_location/redo01.log
-rw-r----- 1 oracle oinstall  51M Feb 14 08:00 ../orcl/new_location/redo02.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:35 ../orcl/new_location/redo03.log
-rw-r----- 1 oracle oinstall 1.3G Feb 14 09:35 ../orcl/new_location/sysaux01.dbf
-rw-r----- 1 oracle oinstall 741M Feb 14 09:35 ../orcl/new_location/system01.dbf
-rw-r----- 1 oracle oinstall 501M Feb 14 09:35 ../orcl/new_location/undotbs01.dbf
-rw-r----- 1 oracle oinstall 826M Feb 14 09:35 ../orcl/new_location/users01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 ../orcl/new_location/users02.dbf

3. Rename the original directory to something else

$ pwd
/u01/app/oracle/oradata
$ mv new_location new_location_backup

4. Create the symbolic link on the old location for the new location

$ pwd
/u01/app/oracle/oradata
$ ln -s /u01/app/oracle/oradata/orcl/new_location
$ ls -la
total 44
drwxr-x--- 5 oracle oinstall 4096 Feb 14 09:41 .
drwxr-xr-x 9 oracle oinstall 4096 Nov 24 13:10 ..
lrwxrwxrwx 1 oracle oinstall   41 Feb 14 09:41 new_location -> /u01/app/oracle/oradata/orcl/new_location
drwxr-xr-x 2 oracle oinstall 4096 Feb  7 13:48 new_location_backup
drwxr-x--- 3 oracle oinstall 4096 Feb 14 09:35 orcl

5. Startup database

SQL> startup
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             390071944 bytes
Database Buffers          138412032 bytes
Redo Buffers                5840896 bytes
Database mounted.
Database opened.
$ cd /u01/app/oracle/oradata/orcl/new_location
$ ls -lah
total 3.9G
drwxr-xr-x 2 oracle oinstall 4.0K Feb 14 09:38 .
drwxr-x--- 3 oracle oinstall 4.0K Feb 14 09:41 ..
-rw-r----- 1 oracle oinstall  11M Feb 14 09:42 control01.ctl
-rw-r----- 1 oracle oinstall 101M Feb 14 09:41 example01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:41 example02.dbf
-rw-r----- 1 oracle oinstall 101M Feb 14 09:41 michael01.dbf
-rw-r----- 1 oracle oinstall 253M Feb 14 09:41 michael02.dbf
-rw-r----- 1 oracle oinstall  51M Feb 14 09:41 redo01.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:41 redo02.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:42 redo03.log
-rw-r----- 1 oracle oinstall 1.3G Feb 14 09:41 sysaux01.dbf
-rw-r----- 1 oracle oinstall 741M Feb 14 09:41 system01.dbf
-rw-r----- 1 oracle oinstall 501M Feb 14 09:41 undotbs01.dbf
-rw-r----- 1 oracle oinstall 826M Feb 14 09:41 users01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:41 users02.dbf

Note that database assume that the files are still at the same location because of the symbolic link

TABLESPACE_NAME      FILE_NAME                                          
-------------------- --------------------------------------------------
EXAMPLE              /u01/app/oracle/oradata/new_location/example01.dbf
EXAMPLE              /u01/app/oracle/oradata/new_location/example02.dbf
MICHAEL              /u01/app/oracle/oradata/new_location/michael01.dbf
MICHAEL              /u01/app/oracle/oradata/new_location/michael02.dbf
SYSAUX               /u01/app/oracle/oradata/new_location/sysaux01.dbf
SYSTEM               /u01/app/oracle/oradata/new_location/system01.dbf
UNDOTBS1             /u01/app/oracle/oradata/new_location/undotbs01.dbf
USERS                /u01/app/oracle/oradata/new_location/users01.dbf  
USERS                /u01/app/oracle/oradata/new_location/users02.dbf