How to move oracle datafile from Non ASM to ASM

How to move datafile from Non ASM (file system) to ASM

Solution: Verify the Name and location of datafile and in which tablespace the datafile resides

SQL> select tablespace_name "Tablespace",file_id "ID",file_name "File", bytes/1024/1024 "Size" from dba_data_files where TABLESPACE_NAME='TEST_TBS';

Tablespace ID File                                 Size
---------- --- -------------------------------------- ---------
TEST_TBS    8 /u01/oradata/orcl/test_tbs01.dbf         2016

Verify if you have sufficient space available on the ASM Disk Group

SQL> select name,total_mb,free_mb, state from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB STATE
----- -------- ---------- ---------
DATA 101355 59017 MOUNTED


Take the tablespace offline

SQL> alter tablespace TEST_TBS offline;
Tablespace altered.


SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='TEST_TBS';

TABLESPACE_NAME STATUS
--------------- -------
TEST_TBS OFFLINE

Use the RMAN command to copy the datafile from the file system to asm disk
[oracle@linux01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Feb 18 06:15:45 2014

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

connected to target database: ORCL (DBID=2195477165)

RMAN> copy datafile 8 to '+DATA';

Starting backup at 18-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=212 instance=ORCL2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u01/oradata/orcl/test_tbs01.dbf
output file name=+DATA/orcl/datafile/test_tbs01.259.123 tag=TAG20140218T061630 RECID=1 STAMP=839831287
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:11:45
Finished backup at 18-FEB-14

RMAN> switch datafile 8 to copy;

datafile 8 switched to datafile copy "+DATA/orcl/datafile/test_tbs01.259.123"


Bring back the tablespace online

[oracle@linux01 ~]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 18 06:41:36 2014

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

Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter tablespace TEST_TBS online;
Tablespace altered.

Check the new location of the data file and the status

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='TEST_TBS';

TABLESPACE_NAME STATUS
--------------- -------
TEST_TBS ONLINE

SQL> select tablespace_name "Tablespace",file_id "ID",file_name "File", bytes/1024/1024 "Size" from dba_data_files where TABLESPACE_NAME='TEST_TBS';

Tablespace ID File                                 Size
---------- --- -------------------------------------- ---------
TEST_TBS    8  +DATA/orcl/datafile/test_tbs01.259.123  2016


Remove the file from the OS file system:
rm /u01/oradata/orcl/test_tbs01.dbf

SP2-1503: Unable to initialize Oracle call interface

Issue: In one my environment where multiple databases of different versions(9i, 10gR2, 11gR1, 11gR2) are running simultaneously, after I have set the ORACLE_HOME & PATH variables I received the below error while logging into the server.

oracle@linux01=> export ORACLE_HOME=/u01/app/oracle/product/11.1.0.7.0
oracle@linux01=> export PATH=$ORACLE_HOME/bin:$PATH
oracle@linux01=> sqlplus sys as sysdba

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

Solution: After carefully analyzing the environment variables(initial troubleshooting), I observed that the LD_LIBRARY_PATH variable was pointing to a different Oracle Home. Corrected that and was successfully able to login to the database.

Previous Value:
oracle@linux01=> echo $LD_LIBRARY_PATH
/u01/app/oracle/product/11.2.0.3.0/lib

Setting to correct value:
oracle@linux01=> export LD_LIBRARY_PATH=/u01/app/oracle/product/11.1.0.7.0/lib

After Modifying:
oracle@linux01=> echo $LD_LIBRARY_PATH
/u01/app/oracle/product/11.1.0.7.0/lib