17 April, 2016

Online Relocation of Database File : ASM to FileSystem and FileSystem to ASM

There have been few published examples of the online datafile relocation feature in 12c.  The examples I've seen are on filesystem.

Here I show online relocation to/from ASM and FileSystem.

SQL> connect system/oracle
Connected.
SQL> create tablespace test_relocate;

Tablespace created.

SQL> create table test_relocate_tbl                         
  2  tablespace test_relocate
  3  as select * from dba_objects;

Table created.

SQL> select tablespace_name, bytes/1024
  2  from user_segments
  3  where segment_name = 'TEST_RELOCATE_TBL';

TABLESPACE_NAME         BYTES/1024
------------------------------ ----------
TEST_RELOCATE       13312

SQL> select file_name, bytes/1024
  2  from dba_data_files
  3  where tablespace_name = 'TEST_RELOCATE';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024
----------
+DATA/NONCDB/DATAFILE/test_relocate.260.909444793
    102400


SQL> 
SQL> alter database move datafile                                                        
  2  '+DATA/NONCDB/DATAFILE/test_relocate.260.909444793'
  3  to '/oradata/NONCDB/test_relocate_01.dbf';

Database altered.

SQL> !ls -l /oradata/NONCDB
total 102408
-rw-r----- 1 oracle asmdba 104865792 Apr 17 23:39 test_relocate_01.dbf

SQL> 
SQL> alter database move datafile  
  2  '/oradata/NONCDB/test_relocate_01.dbf'
  3  to '+DATA';

Database altered.

SQL> select file_name, bytes/1024
  2  from dba_data_files
  3  where tablespace_name = 'TEST_RELOCATE';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024
----------
+DATA/NONCDB/DATAFILE/test_relocate.260.909445261
    102400


SQL>
SQL> !ls -l /oradata/NONCDB
total 0

SQL> 


Note that I was courageous enough to not use the KEEP keyword (which is optional !).
.
.
.

No comments: