Moving Data files to Another Mount Point

Often, we are in need to move Oracle Datafiles from one mount point to another. Below are the steps to achieve the same using simple shell scripts. These can prepare required sub scripts that can be executed on target environment in runtime. Below steps only not limited to whole planned activity, only few that automate partial activity.

1. Spool the list of files to be moved to file named file_list (from column@table_name: name@v$datafile, name@v$controlfile,member@v$log). Ensure no column names, blank lines in between in the spool file.

2. Generate copy sub script from (below) gen_cp.sh, This will generate file named copy_script.sh

[oracle@my_server]-->cat gen_cp.sh
cat file_list|while read line
 do
 b=`basename $line`
 echo  "cp $line /u01/oracle/erpdev/$b"
done >copy_script.sh

3. Use linux “split” command to distribute N-lines to N/n-Files (N: total number of lines in file_list, n:number of parallel cp sessions you want to execute), so that you can run them in parallel.

Split -n10 file_list  # file_list is the file that was created in step-1

4. Above step will create multiple files with evenly distributed number of cp command lines into each file.
5. Either prepare single file which calls copy files (prepared in above) in nohup (or) execute them in VNC in outage window
6. Generate rename datafile SQL than can be executed in MOUNT stage after copy of datafiles from source mount to target mount was completed.

[oracle@my_server]-->cat gen_rename.sh
cat copy_script|while read line
do
        fname=`echo $line|awk '{print $2}'`
        dname=`echo $line|awk '{print $3}'`
        echo "alter database rename file '${fname}' to '${dname}';"
done>Rename_datafile.sql

7. After successfully copying the files to new destination, perform below

• STARTUP database to NOMOUNT with pfile (where control files are pointing to new location).
• Verify If the database instance started with control files from new location.
• MOUNT the database
• Execute Rename_datafile.sql
• Verify for any errors in DB Alert log, spool file
• Verify the location of datafile, controlfile, redolog file (using below SQL)
• OPEN database to read-write mode
• Add Temporary tablespaces and datafiles.

select distinct regexp_substr(name,'^.*/') "Distinct File Location" 
from v$datafile
union
select distinct regexp_substr(name,'^.*/') "Distinct File Location" 
from v$controlfile
union
select distinct regexp_substr(member,'^.*/') "Distinct File Location" 
from v$logfile
union
select distinct regexp_substr(name,'^.*/') "Distinct File Location" 
from v$tempfile
;