Moving datafile local disk to ASM

1.PURPOSE AND SCOPE

In some cases   we accidentally  forget and create datafiles on local disk while using.In this article i am going to explain how to move datafile local disk to ASM.

2.PRACTICE

2.1. I use 2 node RAC system for this practice. And create 2 tablespace on node 1 local disk.

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

FILE_NAME        TABLESPACE_NAME
--------------------------------------------------------------------------------

/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts     AHBS_DATA_TS

/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf    AHBS_INDEX_TS

2 rows selected.

2.2. We need to change the status of these tablespace to offline.

SQL> alter tablespace AHBS_INDEX_TS offline;
Tablespace altered.
SQL> alter tablespace AHBS_DATA_TS offline;
Tablespace altered.

2.3. For copying tablespaces to ASM i used rman copy command.

[oracle@sba5db07 ~]$ rman

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jun 30 18:10:00 2012

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

RMAN> connect target;

connected to target database: MERGE (DBID=3048845705)

RMAN> copy datafile '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts' to  '+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf';

Starting backup at 30-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 instance=MERGE1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts
output file name=+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf tag=TAG20120630T182127 RECID=4 STAMP=787342893
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 30-JUN-12

RMAN> copy datafile  '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf'  to  '+DATA_SBA5/merge/datafile/AHBS_INDEX_TS01.dbf' ;

Starting backup at 30-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf
output file name=+DATA_SBA5/merge/datafile/ahbs_index_ts01.dbf tag=TAG20120630T182136 RECID=5 STAMP=787342896
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JUN-12

2.4. Okey now we created datafiles on ASM but database doesnt aware of these tablespaces.So we need to rename datafiles local to ASM.

SQL> alter database rename file   '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts' to  '+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf' ;

Database altered.

SQL> alter database rename file  '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf'  to  '+DATA_SBA5/merge/datafile/AHBS_INDEX_TS01.dbf' ;

Database altered.

2.5. Lets change tablespaces status to online.

SQL> alter tablespace AHBS_INDEX_TS online;

Tablespace altered.

SQL> alter tablespace AHBS_DATA_TS online;

Tablespace altered.

2.6. Lets check path of tablespaces.

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

FILE_NAME        TABLESPACE_NAME
--------------------------------------------------------------------------------

+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf     AHBS_DATA_TS

+DATA_SBA5/merge/datafile/AHBS_INDEX_TS01.dbf    AHBS_INDEX_TS