RDBMS-- Migrating Oracle Database from disk to disk

This post was prepared just to put down the migration operation on paper.
Sometimes, you need to migrate(move) you database file in to a new storage...
This operation is often required when you buy a new SAN with fast disks (mounted to local, using ext3/ext4 filesystem).. Usually, we move our whole database in to these kind devices, and expect an increase in the I/O performance..
Note that, you may need to migrate some of your datafiles in to a new storage, too..
Altough there are several options to make such a migration; this post will explain how to migrate some of the datafiles in to a new storage using Linux copy and Oracle 's datafile rename commands.. The method that we will use does not include creation of controlfile operation, because rename datafile option is easier to implement in this kind of scenario;

Example:

Scenario: Your datafiles are on /u1 mount, and you need to move some of your datafiles(names starting with ERMAN%) to  new storage mounted on /u2

The method;

1) Identifiy the datafiles to be moved and prepare the copy script.
You can use the following sql to build the script for you.(name this script as copy.sh, we will use it on Step 4)

select 'cp '|| name||' /u2/newmount/'from v$datafile where name like '%ERMAN%'

This sql will create the cp commands like below;

cp /u1/prod/ERMAN.dbf /u2/prod/
cp /u1/prod/ERMAN_INDEX.dbf /u2/prod/
cp /u1/prod/ERMAN2.dbf /u2/prod/
cp /u1/prod/ERMAN3.dbf /u2/prod/
cp /u1/prod/ERMAN4.dbf /u2/prod/

2) Create the sql script for rename operations; You can use the following sql for this. (name this script as rename.sql, we will use it on Step 5)

select 'alter database rename datafile '''||name||''' to '''||replace(name,'/u1','/u2')||'''' from v$datafile where name like '%ERMAN%';

This sql will create the rename commands like below;

alter database rename file '/u1/prod/ERMAN.dbf' to '/u2/prod/ERMAN.dbf';
alter database rename file '/u1/prod/ERMAN_INDEX.dbf' to '/u2/prod/ERMAN.dbf';
alter database rename file '/u1/prod/ERMAN2.dbf' to '/u2/prod/ERMAN2.dbf';
alter database rename file '/u1/prod/ERMAN3.dbf' to '/u2/prod/ERMAN3.dbf';
alter database rename file '/u1/prod/ERMAN4.dbf' to '/u2/prod/ERMAN4.dbf';

3) Shutdown your application and database.

4) Connect you your database server (supposing it s Linux or Unix) and run the copy script that we created in Step 1;
as database owner> sh copy_script

5)When Step4 is finished, connect to sqlplus using sysdba, startup mount your database and execute the rename script that we have created in step 2.
as database owner > sqlplus "/as sysdba"
SQL> startup mount;
SQL>rename.sql;

6)Once Step 5 is finished, open your database..
SQL> alter database open;

7)Check your datafiles are actually migrated.. check using lsof and v$datafile to ensure the database is using the datafiles from the new storage..

8)If everything is expected, delete the old files using the output script that the dynamic sql below will produce..
select 'rm -f '||replace(name,'/u2','/u1')||';' from v$datafile where name like '%ERMAN%';

That's it.. The datafiles named with ERMAN prefix, have been migrated..