Search This Blog

Total Pageviews

Wednesday 12 July 2017

Oracle Move datafile via rman

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

Oracle DBA

anuj blog Archive