Move a tablespace to ASM from filesystem
Follow the below step to move tablespace to ASM from filesystemSteps are as below.
1. Offilne the tablespace
2. Copy using RMAN
3. Rename datafile
4. Make it online, recover if required
5. Delete the old datafile
SQL> set line 300
SQL> set pages 300
SQL> col FILE_NAME for a100
SQL> SELECT FILE_NAME , tablespace_name FROM DBA_DATA_FILES;
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------------------------------------- ------------------------------
/u01/app/oracle/product/12.1.0.2/orcltst/dbs/ORA_TEST ORA_TEST
SQL> ALTER TABLESPACE ORA_TEST offline;
Tablespace altered.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
localhost:/db/home/oracle > rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jan 25 09:48:59 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLTST (DBID=3881831134)
RMAN> copy datafile '/u01/app/oracle/product/12.1.0.2/orcltst/dbs/ORA_TEST' to '+ORCLTST';
Starting backup at 25-JAN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00022 name=/u01/app/oracle/product/12.1.0.2/orcltst/dbs/ORA_TEST
output file name=+ORCLTST/ORCLTST/DATAFILE/ora_test.463.934210303 tag=TAG20170125T095142 RECID=4 STAMP=934192303
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JAN-17
Starting Control File and SPFILE Autobackup at 25-JAN-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File and SPFILE Autobackup command on ORA_DISK_1 channel at 01/25/2017 09:51:45
ORA-07217: sltln: environment variable cannot be evaluated.
RMAN>
SQL> alter database rename file '/u01/app/oracle/product/12.1.0.2/orcltst/dbs/ORA_TEST' to '+ORCLTST/ORCLTST/DATAFILE/ora_test.463.934210303';
Database altered.
SQL> col FILE_NAME for a100
SQL> set line 300
SQL> set pages 300
SQL> SELECT FILE_NAME , tablespace_name FROM DBA_DATA_FILES;
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------------------------------------- ------------------------------
+ORCLTST/ORCLTST/DATAFILE/ora_test.463.934210303 ORA_TEST
22 rows selected.
SQL> select * from gv$recover_file;
INST_ID FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ---------- ------- ------- ----------------------------------------------------------------- ---------- --------- ----------
2 22 OFFLINE OFFLINE OFFLINE NORMAL 0 0
1 22 OFFLINE OFFLINE OFFLINE NORMAL 0 0
8 rows selected.
SQL> alter tablespace ORA_TEST online;
Tablespace altered.
SQL> select * from gv$recover_file;
no rows selected
SQL> select count(*) from
COUNT(*)
----------
1
SQL> alter system switch all logfile;
System altered.
SQL> SELECT FILE_NAME , tablespace_name FROM DBA_DATA_FILES;
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------------------------------------- ------------------------------
+ORCLTST/ORCLTST/DATAFILE/ora_test.463.934210303 ORA_TEST
SQL>
