Moving a datafile from filesystem to ASM

I had a database where someone had created a datafile outside of ASM and on the Oracle Home filesystem. Whilst this would not cause a problem for backups as they are done via RMAN and it knows where all the files are thanks to the control file, it will have performance implications as my ASM area is on Exadata cells, and the Oracle Home filesystem is on an internal disk.

The method I am going to describe assumes that you can take the tablespace offline to carry out the maintenance.


Find out the filename name of your mis-located datafile

SQL> select tablespace_name, file_name from dba_data_files where file_name not like '%DATA%';


Alter the affected tablespace offline so no changes will be made to the datafile while we move it.

SQL> alter tablespace cmpind_xx offline;


Use RMAN to copy the file (in bold below).

oracle@mydbhost:~/mel$ rman target /
Recovery Manager: Release - Production on Tue Apr 1 08:11:53 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: mydb (DBID=3455727309)
RMAN> copy datafile '/u01/app/oracle/product/' to '+DATA_EXPA';
Starting backup at 01-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=856 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=916 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00172 name=/u01/app/oracle/product/
output file name=+DATA_EXPA/mydb/datafile/cmpind_xx.657.843725535 tag=TAG20140401T081214 RECID=511 STAMP=843725535
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 01-APR-14
Starting Control File and SPFILE Autobackup at 01-APR-14
piece handle=/stage/rman/12Mar14/c-3455727309-20140401-00 comment=NONE
Finished Control File and SPFILE Autobackup at 01-APR-14

Next step is to rename the datafile from the original name to the new OMF name that was generated during the RMAN copy (highlighted in red above) and bring the tablespace back online.

oracle@mydbhost:~/mel$ sqlplus '/ as sysdba'
SQL*Plus: Release Production on Tue Apr 1 08:12:46 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter database rename file '/u01/app/oracle/product/' to '+DATA_EXPA/mydb/datafile/cmpind_xx.657.843725535';
Database altered.
SQL> alter tablespace cmpind_xx online;
Tablespace altered.




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s