Move Datafiles Of Tablespaces With RMAN Set Newname in Oracle 11g

You can move datafiles of tablespaces. Scenarios can be asm to file system or file system to file system or file system to file system or asm to asm. Below operations show how to move datafiles. You have to take a backup and database has to be archivelog mode before this operations because of that you do restore and recover operation.

Steps

1 – Database has to be Archivelog Mode
2 – Take Full Rman Backup
3 – Execute Rman Run block

Run Block Description:
Offline Tablespace.
Set Newname.
Restore Datafile To New Location.
Write New Location information to controlfile with switch datafile all.
Recover Datafile.
Online Tablespace.

List Tablespaces

SQL> select tablespace_name from dba_tablespaces;

List Datafile Directories

SQL> select tablespace_name,file_name from dba_data_files;

Move Datafile From Asm To File System

RMAN> RUN {
SQL ‘ALTER TABLESPACE USERS OFFLINE IMMEDIATE’;
SET NEWNAME FOR DATAFILE ‘+DATA/dg/datafile/USERS.329.896267113’ TO ‘/tmp/USERS.329.896267113’;
RESTORE TABLESPACE USERS;
SWITCH DATAFILE ALL;
RECOVER TABLESPACE USERS;
SQL ‘ALTER TABLESPACE USERS ONLINE’;
}

Move Datafile From File System To From Asm

RMAN> RUN {
SQL ‘ALTER TABLESPACE USERS OFFLINE IMMEDIATE’;
SET NEWNAME FOR DATAFILE ‘/tmp/USERS.329.896267113’ TO ‘+DATA/dg/datafile/USERS.329.896267113’;
RESTORE TABLESPACE USERS;
SWITCH DATAFILE ALL;
RECOVER TABLESPACE USERS;
SQL ‘ALTER TABLESPACE USERS ONLINE’;
}

ASM to ASM and File System to File System operations are the same above operations.