Search My Oracle Blog

Custom Search

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016