Moving Oracle datafiles from Non-ASM to ASM

There are different ways to move datafile between non-asm to asm or vice-versa. Let me give some examples/methods that I've used quite often.

- RMAN backup/restore.
- RMAN convert command to move individual file.
- ASMCMD- cp command(11g onwards)

RMAN backup/restore.

following example shows how to move datafile from /backup2/ filesystem to ASM

SQL> startup mount;
ORACLE instance started.

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/backup2/TEST/convertdb/datafile/system.311.781438167
/backup2/TEST/convertdb/datafile/tools.ora
..
- Set the control_files parameter to ASM storage. for example, 

       CONTROL_FILES=+DATA/controlfiles/control01.ctl

Startup in nomount
  
RMAN> STARTUP NOMOUNT;
Restore controlfile

RMAN> RESTORE CONTROLFILE FROM '/backup2/TEST/control01.ctl'
mount database.

RMAN> ALTER DATABASE MOUNT;
Copy database into ASM, since you are copying from disk you can increase the parallelism


RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 03-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2361 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 
name=/backup2/TEST/scripts/convertdb/datafile/system.311.781438167
output file name=+DATA/TEST/datafile/system.304.782310947 tag=TAG20120503T123546 RECID=1 STAMP=782311049
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
input datafile file number=00004 name=/backup/TEST/convertdb/datafile/users.257.781439177
output file name=+DATA/datafile/users.269.782313859 tag=TAG20120503T123546 RECID=36 STAMP=782313896
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
name=/backup/TEST/convertdb/datafile/sysaux.270.781439487
output file name=+DATA/TEST/datafile/sysaux.267.782313951 tag=TAG20120503T123546 RECID=38 STAMP=782313974
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
...
...
...
...
Finished backup at 03-MAY-12
 RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/TEST/datafile/system.304.782310947"
datafile 2 switched to datafile copy "+DATA/TEST/datafile/sysaux.267.782313951"
datafile 3 switched to datafile copy "+DATA/TEST/datafile/undotbs1.289.782312249"
datafile 4 switched to datafile copy "+DATA/TEST/datafile/users.269.782313859"

Open the database.

RMAN> ALTER DATABASE OPEN;
database opened

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/TEST/datafile/system.304.782310947
+DATA/TEST/datafile/sysaux.267.782313951
+DATA/TEST/datafile/undotbs1.289.782312249
+DATA/TEST/datafile/users.269.782313859

- Now create online redo logs in ASM and drop them in non-asm filesystem.


SQL> show paramter db_create_file_dest

db_create_file_dest                  string      /backup/TEST/datafile

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/backup/TEST/datafile/onlinelog/o1_mf_9_7szzmj1q_.log
/backup/TEST/datafile/onlinelog/o1_mf_7_7szzmhr3_.log
/backup/TEST/datafile/onlinelog/o1_mf_5_7szzmhhb_.log
/backup/TEST/datafile/onlinelog/o1_mf_2_7szzmh6t_.log
/backup/TEST/datafile/onlinelog/o1_mf_1_7szzmgxg_.log

SQL>alter system set db_create_file_dest='+DATA';

SQL> show paramter db_create_file_dest

db_create_file_dest                  string      +DATA

SQL> alter database add logfile size 50m;
Database altered.

SQL> alter database add logfile size 50m;
Database altered.

SQL> alter database add logfile size 50m;
Database altered.

SQL>alter database drop logfile group 1;
Database altered.

SQL>alter database drop logfile group 2;
Database altered.

SQL>alter database drop logfile group 3;
Database altered.

SQL> select group#,member from v$logfile;
    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         2
+DATA/TEST/onlinelog/group_2.259.782314915
         3
+DATA/TEST/onlinelog/group_3.260.782314769
         4
+DATA/TEST/onlinelog/group_4.258.782314917

IN CASE OF INDIVIDUAL FILES.. YOU CAN DO WITH SIMILAR COMMANDS.

RMAN> target /


RMAN>backup as copy datafile 54 format '+DATA';

RMAN>switch datafile 54 to copy; 

RMAN convert command to move individual files.

I have created a test tablespace with one datafile which is on '/backup' filesystem.

SQL> select file#,name from v$datafile where name like ('%test%');

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         5
/backup/test.ora

Lets move this into ASM with convert command.

shutdown and mount the database and run convert command.

$rman target /

RMAN> convert datafile '/backup/test.ora' format '+DATA';

Starting conversion at target at 03-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2361 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/backup/test.ora
converted datafile=+DATA/TEST/datafile/test.256.782320709
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 03-MAY-12

change the file pointer to ASM with rename file.

SQL> alter database rename file '/backup/test.ora' to '+DATA/TEST/datafile/test.256.782320709';

Database altered.

SQL> alter database open;

SQL> select file#,name from v$datafile where name like ('%test%');

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         5
+DATA/TEST/datafile/test.256.782320709

ASMCMD- cp command

Place tablespace offline, or shutdown and mount the database.

SQL> alter tablespace test offline; 

Tablespace altered.

login to grid account to connect to ASM instance.

$ su - grid
Password: 

use ASMCMD cp command to copy files between OS and ASM.This is available only from 11g

$ asmcmd
ASMCMD> cp /backup/test.ora '+DATA'
copying /backup/test.ora -> +DATA/test.ora
ASMCMD> 

make changes to file pointer and open the database or place tablespace online

SQL> alter database rename file '/backup/test.ora' to '+DATA/test.ora';

Database altered.

SQL> alter tablespace test online;

Tablespace altered.

SQL> select file#,name from v$datafile where name like ('%test%');

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         5
+DATA/test.ora