Moving datafile from non-asm to asm

[oracle@localhost ~]$ export ORACLE_SID=newdb

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL> select status from v$instance;

OPEN

SQL> select name from v$database;

NEWDB

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/newdb/system01.dbf

/u01/app/oracle/oradata/newdb/undotbs01.dbf

/u01/app/oracle/oradata/newdb/sysaux01.dbf

/u01/app/oracle/oradata/newdb/users01.dbf

/u01/app/oracle/oradata/newdb/example01.dbf

/u01/app/oracle/oradata/newdb/addm1.dbf

/u01/app/oracle/oradata/newdb/old1.dbf

+DGROUP1/newdb/datafile/tbsasm.256.729953755

8 rows selected.

FOR THIS PRACTICAL WE CREATED A NORMAL TABLESPACE IN NON ASM DATABASE.

SQL> create tablespace tbsasmmig datafile 'asmmig1.dbf' size 3M;

Tablespace created.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/newdb/system01.dbf

/u01/app/oracle/oradata/newdb/undotbs01.dbf

/u01/app/oracle/oradata/newdb/sysaux01.dbf

/u01/app/oracle/oradata/newdb/users01.dbf

/u01/app/oracle/oradata/newdb/example01.dbf

/u01/app/oracle/oradata/newdb/addm1.dbf

/u01/app/oracle/oradata/newdb/old1.dbf

+DGROUP1/newdb/datafile/tbsasm.256.729953755

/u01/app/oracle/product/10.2.0/db_1/dbs/asmmig1.dbf

9 rows selected.

SQL> create table l3(c number) tablespace tbsasmmig;

Table created.

SQL> insert into l3 values(1);

1 row created.

SQL> select * from l3;

C

----------

1

1

Non ASM to ASM shoaib.ansari@pathinfotech.com

SQL> host rman target / nocatalog

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 21 10:34:31 2010

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

connected to target database: NEWDB (DBID=2968639662)

using target database control file instead of recovery catalog

RMAN> sql "alter tablespace tbsasmmig offline";

sql statement: alter tablespace tbsasmmig offline

RMAN> backup as copy tablespace tbsasmmig format '+DGROUP1';

Starting backup at 21-SEP-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=148 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00009 name=/u01/app/oracle/product/10.2.0/db_1/dbs/asmmig1.dbf

output filename=+DGROUP1/newdb/datafile/tbsasmmig.257.730290999 tag=TAG20100921T103638 recid=4

stamp=730291001

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

Finished backup at 21-SEP-10

RMAN> switch tablespace tbsasmmig to copy;

datafile 9 switched to datafile copy "+DGROUP1/newdb/datafile/tbsasmmig.257.730290999"

RMAN> sql "alter tablespace tbsasmmig online";

sql statement: alter tablespace tbsasmmig online

RMAN> exit

Recovery Manager complete.

SQL> column file_name format a48

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

------------------------------ ------------------------------------------------

EXAMPLE /u01/app/oracle/oradata/newdb/example01.dbf

USERS /u01/app/oracle/oradata/newdb/users01.dbf

SYSAUX /u01/app/oracle/oradata/newdb/sysaux01.dbf

UNDOTBS1 /u01/app/oracle/oradata/newdb/undotbs01.dbf

SYSTEM /u01/app/oracle/oradata/newdb/system01.dbf

TBSADDM /u01/app/oracle/oradata/newdb/addm1.dbf

OLD /u01/app/oracle/oradata/newdb/old1.dbf

TBSASM +DGROUP1/newdb/datafile/tbsasm.256.729953755

TBSASMMIG +DGROUP1/newdb/datafile/tbsasmmig.257.730290999

9 rows selected.