How to Move a Datafile From File System To ASM Disk Group?

Hi Friends,

In this article, I will write about moving a datafile from file system to ASM disk group with RMAN.

1- First, let’s create a tablespace which datafile locates on the file system (/data1).

SQL> CREATE TABLESPACE TOASM DATAFILE ‘/data1/toasm01.dbf’ SIZE 100M AUTOEXTEND ON NEXT 1M ;

2- Check for datafiles. So where are our datafiles?

SQL> select name from v$datafile;

NAME

——————————————————————————–

+DATA/talipdb/datafile/system.257.778261279

+DATA/talipdb/datafile/sysaux.258.778261375

+DATA/talipdb/datafile/undotbs1.259.778261441

+DATA/talipdb/datafile/users.260.778261447

/data1/toasm01.dbf

3- let’s connect to RMAN.

[oracle@DBTALIP /oracle/ora11g]# rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Sun Mar 18 16:05:09 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: TALIPDB (DBID=4043281188)

4- Take tablespace offline which you want to move datafiles.

RMAN> sql “alter tablespace toasm offline”;

using target database control file instead of recovery catalog

sql statement: alter tablespace toasm offline

5- Copy datafile with RMAN Copy command to +DATA disk group.

RMAN> copy datafile ‘/data1/toasm01.dbf’ to ‘+DATA’;

Starting backup at 18-MAR-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=58 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/data1/toasm01.dbf

output file name=+DATA/talipdb/datafile/toasm.263.778262769 tag=TAG20120318T160609 RECID=13 STAMP=778262779

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 18-MAR-12

RMAN> exit

Recovery Manager complete.

6- Connect to SQL*Plus and rename the old datafile to created ASM file .

[oracle@DBTALIP /oracle/ora11g]# sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 18 16:09:12 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> alter database rename file ‘/data1/toasm01.dbf’ to ‘+DATA/talipdb/datafile/toasm.263.778262769’;

Database altered.

7- Take tablespace again online.

SQL> alter tablespace toasm online;

Tablespace altered.

8- Query v$datafile view to see location of our datafile.

SQL> select name from v$datafile;

NAME ——————————————————————————–

+DATA/talipdb/datafile/system.257.778261279

+DATA/talipdb/datafile/sysaux.258.778261375

+DATA/talipdb/datafile/undotbs1.259.778261441

+DATA/talipdb/datafile/users.260.778261447

+DATA/talipdb/datafile/toasm.263.778262769

That is finished. Our Tablepsace is moved to ASM disk group with RMAN copy command. 🙂

Talip Hakan Öztürk