DBA Knowledge Base

Move Oracle datafiles from filesystem to ASM using RMAN

1. Connect to the database using RMAN and move database into mount mode:

$ rman target /
RMAN> shutdown immediate
RMAN> startup mount

2. Get information about target datafiles and tempfiles:

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name PRSH01

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1020     SYSTEM               YES     /u01/app/oracle/oradata/PRSH01/system01.dbf
3    490      SYSAUX               NO      /u01/app/oracle/oradata/PRSH01/sysaux01.dbf
4    850      UNDOTBS1             YES     /u01/app/oracle/oradata/PRSH01/undotbs01.dbf
7    5        USERS                NO      /u01/app/oracle/oradata/PRSH01/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    114      TEMP                 32767       /u01/app/oracle/oradata/PRSH01/temp01.dbf

3. Copy datafiles from filesystem to ASM diskgroup, in our case to +DATA:

RMAN> copy datafile 1 to '+DATA';
RMAN> copy datafile 3 to '+DATA';
RMAN> copy datafile 4 to '+DATA';
RMAN> copy datafile 7 to '+DATA';
RMAN> switch datafile 1 to copy;
RMAN> switch datafile 3 to copy;
RMAN> switch datafile 4 to copy;
RMAN> switch datafile 7 to copy;

4. Place tempfile on +DATA:

RMAN> run{
2> set newname for tempfile 1 to '+DATA';
3> switch tempfile 1;
4> }

executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file

5. Double check that all files have a new name

RMAN> report schema;

Report of database schema for database with db_unique_name PRSH01

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1020     SYSTEM               ***     +DATA/PRSH01/DATAFILE/system.256.1122674191
3    570      SYSAUX               ***     +DATA/PRSH01/DATAFILE/sysaux.257.1122674225
4    910      UNDOTBS1             ***     +DATA/PRSH01/DATAFILE/undotbs1.258.1122674235
7    5        USERS                ***     +DATA/PRSH01/DATAFILE/users.259.1122674253

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    114      TEMP                 32767       +DATA

6. Open database:

RMAN> alter database open;

7. Check new file names. Note tempfile name will also be generated:

RMAN> report schema;

Report of database schema for database with db_unique_name PRSH01

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1020     SYSTEM               YES     +DATA/PRSH01/DATAFILE/system.256.1122674191
3    570      SYSAUX               NO      +DATA/PRSH01/DATAFILE/sysaux.257.1122674225
4    910      UNDOTBS1             YES     +DATA/PRSH01/DATAFILE/undotbs1.258.1122674235
7    5        USERS                NO      +DATA/PRSH01/DATAFILE/users.259.1122674253

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    114      TEMP                 32767       +DATA/PRSH01/TEMPFILE/temp.260.1122674673