Oracle move datafile via rman .. ..
via RMAN this will work from 10g to 12c .
We have new feture in 12c move online . Demo is given below as well.
We can move ove SYSTEM and SYSAUX datafiles in database mount stage .
SQL> def
DEFINE _DATE = "09-JUL-17" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ora12c" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1201000200" (CHAR)
SQL> create tablespace test datafile '/home/oracle/test.dbf' size 1m ;
Tablespace created.
SQL> !ls -ltr /home/oracle/test.dbf
-rw-r----- 1 oracle oinstall 1056768 Jul 9 13:42 /home/oracle/test.dbf
set linesize 200
col FILE_NAME for a50
select tablespace_name, file_name, file_id file# from dba_data_files where tablespace_name = 'TEST';
TABLESPACE_NAME FILE_NAME FILE#
------------------------------ -------------------------------------------------- ----------
TEST /home/oracle/test.dbf 11
SQL> SELECT name FROM v$asm_diskgroup;
NAME
------------------------------
DATA
SQL> alter database datafile '/home/oracle/test.dbf' offline;
Database altered.
RMAN> copy datafile '/home/oracle/test.dbf' to '+DATA';
Starting backup at 09-JUL-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/home/oracle/test.dbf
output file name=+DATA/ORA12C/DATAFILE/test.306.948894431 tag=TAG20170709T134710 RECID=3 STAMP=948894431
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 09-JUL-17
Starting Control File and SPFILE Autobackup at 09-JUL-17
piece handle=+DATA/ORA12C/AUTOBACKUP/2017_07_09/s_948894432.308.948894433 comment=NONE
Finished Control File and SPFILE Autobackup at 09-JUL-17
RMAN> ALTER DATABASE RENAME FILE '/home/oracle/test.dbf' TO '+DATA/ORA12C/DATAFILE/test.306.948894431';
Statement processed
RMAN> switch datafile '+DATA/ORA12C/DATAFILE/test.306.948894431' to copy;
datafile 11 switched to datafile copy "+DATA/ORA12C/DATAFILE/test.306.948894431"
RMAN> recover datafile '+DATA/ORA12C/DATAFILE/test.306.948894431' ;
Starting recover at 09-JUL-17
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-JUL-17
No Idea why I am getting two output ..
RMAN> select tablespace_name, file_name, file_id file# from dba_data_files where tablespace_name = 'TEST';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
FILE#
----------
TEST
+DATA/ORA12C/DATAFILE/test.306.948894431
11
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
FILE#
----------
TEST
+DATA/ORA12C/DATAFILE/test.306.948894431
11
RMAN> alter database datafile '+DATA/ORA12C/DATAFILE/test.306.948894431' online;
Statement processed
RMAN> select tablespace_name, file_name, file_id file# from dba_data_files where tablespace_name = 'TEST';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
FILE#
----------
TEST
+DATA/ORA12C/DATAFILE/test.306.948894431
11
RMAN> select file_id, file_name, tablespace_name from dba_data_files;
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
1
+DATA/ORA12C/DATAFILE/system.258.938460923
SYSTEM
3
+DATA/ORA12C/DATAFILE/sysaux.257.938460879
SYSAUX
4
+DATA/ORA12C/DATAFILE/undotbs1.260.938460981
UNDOTBS1
6
+DATA/ORA12C/DATAFILE/users.259.938460979
USERS
11
+DATA/ORA12C/DATAFILE/test.306.948894431
TEST
[oracle@oraasm12c ~]$ pwd
/home/oracle
[oracle@oraasm12c ~]$ rm test.dbf
[oracle@oraasm12c ~]$
================================
Oracle 12C Online move datafile
Moving Datafiles Online in Oracle 12c
create tablespace test12c datafile '/home/oracle/test12c.dbf' size 1m ;
SQL> create tablespace test12c datafile '/home/oracle/test12c.dbf' size 1m ;
Tablespace created.
set linesize 200
col FILE_NAME for a50
select tablespace_name, file_name, file_id file# from dba_data_files where tablespace_name = 'TEST12C';
TABLESPACE_NAME FILE_NAME FILE#
------------------------------ -------------------------------------------------- ----------
TEST12C /home/oracle/test12c.dbf 12
SQL>
set linesize 200
col FILE_NAME for a50
select tablespace_name, file_name, file_id file# from dba_data_files where tablespace_name = 'TEST12C';
SQL> SQL> SQL>
TABLESPACE_NAME FILE_NAME FILE#
------------------------------ -------------------------------------------------- ----------
TEST12C /home/oracle/test12c.dbf 12
SQL> Alter database move datafile '/home/oracle/test12c.dbf' to '+DATA';
Database altered.
set linesize 200
col FILE_NAME for a50
select tablespace_name, file_name, file_id file# from dba_data_files where tablespace_name = 'TEST12C';
TABLESPACE_NAME FILE_NAME FILE#
------------------------------ -------------------------------------------------- ----------
TEST12C +DATA/ORA12C/DATAFILE/test12c.310.948895373 12
No old file . cool feature is in it
SQL> !ls -ltr /home/oracle/test12c.dbf
ls: cannot access /home/oracle/test12c.dbf: No such file or directory
