Database Migration To A Different Filesystem On The Same Server With Minimal Downtime

The Objective is to move all Database files from old location [/csrprd/datafile/OEMDB] to the new location [/u02/oracle/oradata/OEMDB] within a minimal downtime window.

First Take an RMAN Backup As Copy to the new filesystem:
The below script will create a Copy Backup under the new filesystem /u02/oracle/oradata/OEMDB, if you re-run the same script for the second time it will refresh (sync) the already exist Backup Copy using a temporary incremental backup without the need to create a new Copy Backup from scratch which will save much time.

RMAN> run{
allocate channel F1 type disk format '/u02/oracle/oradata/OEMDB/%U';
allocate channel F2 type disk format '/u02/oracle/oradata/OEMDB/%U';
allocate channel F3 type disk format '/u02/oracle/oradata/OEMDB/%U';
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'DB_COPY_UPDTD_BKP'
DATABASE FORMAT '/u02/oracle/oradata/OEMDB/%d_%t_%s_%p'; # Incremental Level 1 Backup to recover the Image COPY.
RECOVER COPY OF DATABASE WITH TAG 'DB_COPY_UPDTD_BKP'; # Recover Image Copy with the Incr lvl1.
DELETE noprompt backup TAG 'DB_COPY_UPDTD_BKP'; # Delete [only] the incrmental bkp used for recovery.
#DELETE noprompt backup TAG 'arc_for_image_recovery' completed before 'sysdate-1'; # Delete Archive bkp for the previous recover.
DELETE noprompt copy   TAG 'ctrl_after_image_reco'; # Delete Controlfile bkp for the previous run.
BACKUP as copy current controlfile format '/u02/oracle/oradata/OEMDB/ctl_%U' tag 'ctrl_after_image_reco'; # Controlfile Copy Backup.
sql "ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ''/u02/oracle/oradata/OEMDB/controlfile.trc'' reuse"; # Controlfile Trace Backup.
sql "create pfile=''/u02/oracle/oradata/OEMDB/init${ORACLE_SID}.ora'' from spfile"; # Backup SPFILE.
}

In order to minimize the downtime during the migration, it's recommended to Sync the Copy Backup files using the same above command just before starting the actual migration.

Re-name the controlfile to point to the new location [/u02/oracle/oradata/OEMDB]:
Because I'm doing this practice on a test machine I'm keeping the control files on the same location which is not a best practice.

SQL> alter system set control_files='/u02/oracle/oradata/OEMDB/o1_mf_dmj045mz_.ctl','/u02/oracle/oradata/OEMDB/o1_mf_dmj045o9_.ctl' scope=spfile;

Generate the Shell command that will be used to move the controlfiles to the new filesystem during the actual migration:
SQL> sho parameter control
SQL> select 'cp '||name||'  /u02/oracle/oradata/OEMDB/'||SUBSTR(name, INSTR(name,'/', -1,1)+1) from v$controlfile;

Change all the parameters pointing to the old database files location to point to the new location:
Only if these parameters are already set:
SQL> ALTER SYSTEM SET db_create_file_dest='/u02/oracle/oradata/OEMDB'         SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_create_online_log_dest_1='/u02/oracle/oradata/OEMDB' SCOPE=SPFILE;
-- In case the Archivelogs and Flashback logs will be stored in the new location:
SQL> ALTER SYSTEM SET db_recovery_file_dest='/u02/oracle/oradata/OEMDB'       SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/oracle/oradata/OEMDB' SCOPE=SPFILE;

Actual Database Files Migration:
SQL> shu immediate
 -- Copy the controlfiles to new location:
 # cp  /csrprd/datafile/OEMDB/o1_mf_dmj045mz_.ctl         /u02/oracle/oradata/OEMDB/o1_mf_dmj045mz_.ctl
 # cp  /csrprd/datafile/OEMDB/o1_mf_dmj045o9_.ctl  /u02/oracle/oradata/OEMDB/o1_mf_dmj045o9_.ctl 
SQL> startup mount

Using RMAN, Switch the database to the Image Copy Backup:
RMAN> switch database to copy;

Copy the REDOLOG files to the new location:
SQL>  select 'cp '||member||'  /u02/oracle/oradata/OEMDB/'||SUBSTR(member, INSTR(member,'/', -1,1)+1) from v$logfile;
 # cp /u02/oracle/oradata/OEMDB/onlinelog/o1_mf_3_dmj0483s_.log  /csrprd/datafile/OEMDB/
 ...

-- Prepare and Execute the commands for renaming the REDOLOG files:
--prepare the script:
SQL> select 'ALTER DATABASE RENAME FILE '''||MEMBER||''' TO ''/u02/oracle/oradata/OEMDB/'||SUBSTR(member, INSTR(member,'/', -1,1)+1)||''' ;' from v$logfile ORDER BY 1;
--Execute:
SQL> ALTER DATABASE RENAME FILE '/csrprd/datafile/OEMDB/o1_mf_1_dmj0480w_.log'     TO '/u02/oracle/oradata/OEMDB/o1_mf_1_dmj0480w_.log' ;
...

RMAN> recover database;
RMAN> alter database open;

Re-create the Temporary tablespaces:
SQL> create temporary tablespace temp1 tempfile '/u02/oracle/oradata/OEMDB/temp1.dbf' size 10m;

SQL> alter database default temporary tablespace temp1;

-- Check & kill the sessions that still using the old TEMP tablespace:
set lines 170 pages 1000
      col sid_serial for a15
      col OSUSER for a20
      col module for a30
      col SPID for a10
      col TABLESPACE for a15
      select s.sid || ',' || s.serial# sid_serial, p.spid, s.username, s.osuser,
      s.module,sum (o.blocks) * t.block_size / 1024 / 1024 mb_used ,count(*) sorts, s.sql_id,o.tablespace
      from v$sort_usage o, v$session s, dba_tablespaces t, v$process p
      where o.session_addr = s.saddr and s.paddr = p.addr and o.tablespace = t.tablespace_name
      group by s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,s.program, t.block_size,s.sql_id, o.tablespace
      order by o.tablespace,mb_used,s.username;

SQL> drop tablespace temp including contents and datafiles;
SQL> alter tablespace temp1 rename to temp;

Make sure that All DB files are pointing to the new location:
SQL> select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile
union
select name from v$controlfile;

SQL> sho parameter dest

Well done. The database is now migrated to the new location.