DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, December 29, 2016

Moving Data Files Between Oracle ASM Disk Groups Using RMAN

1. Start RMAN and connect to the target database.

$ rman target /

connected to target database: ORCL (DBID=1217369048)

2. Generate a report that shows the names of the data files.

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------- ------- ------------------------
1    740      SYSTEM         ***     +DATA/orcl/datafile/system.258.689589737
2    570      SYSAUX         ***     +DATA/orcl/datafile/sysaux.259.689589785
3    55       UNDOTBS1       ***     +DATA/orcl/datafile/undotbs1.260.689589831
4    5        USERS          ***     +DATA/orcl/datafile/users.261.689589837

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------- ----------- --------------------
1    20       TEMP           32767       +DATA/orcl/tempfile/temp.262.689589851


3. Back up the data file to the new Oracle ASM disk group.

Run the BACKUP AS COPY command to back up the data file on DATA to USERDATA.

For example:

RMAN> BACKUP AS COPY
        DATAFILE "+DATA/orcl/datafile/users.261.689589837"
        FORMAT   "+USERDATA";

Starting backup at 16-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/users.261.689589837
output file name=+USERDATA/orcl/datafile/users.256.689682663
  tag=TAG20090616T103101 RECID=13 STAMP=689682663
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-DEC-16

4. Offline the data file that you intend to move to a new disk group.

Run the following SQL command in the RMAN client. Use two single quotation marks around the name of the data file, not double quotation marks.

For example:

RMAN> SQL "ALTER DATABASE DATAFILE
       ''+DATA/orcl/datafile/users.261.689589837'' OFFLINE";

sql statement: ALTER DATABASE DATAFILE
     ''+DATA/orcl/datafile/users.261.689589837''  OFFLINE

5. Point the control file to the newly created copy of the data file.

Run the SWITCH...TO COPY command in the RMAN client. The TO COPY option of SWITCH switches the data file to the most recent copy of the data file.

For example:

RMAN> SWITCH DATAFILE "+DATA/orcl/datafile/users.261.689589837" TO COPY;

datafile 4 switched to datafile copy
    "+USERDATA/orcl/datafile/users.256.689682663"

The output of this command displays the new name of the data file.

6. Recover the renamed data file.
Run the RECOVER command in the RMAN client.

For example:

RMAN> RECOVER DATAFILE "+USERDATA/orcl/datafile/users.256.689682663";

Starting recover at 16-DEC-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-DEC-16

7. Bring the data file online.

Run the SQL command in the RMAN client. Use two single quotation marks around the name of the data file, not double quotation marks.

For example:

RMAN> SQL "ALTER DATABASE DATAFILE
      ''+USERDATA/orcl/datafile/users.256.689682663'' ONLINE";

sql statement: ALTER DATABASE DATAFILE
   ''+USERDATA/orcl/datafile/users.256.689682663'' ONLINE

8. Delete the data file copy from the original Oracle ASM disk group.

In this scenario, +DATA/orcl/datafile/users.261.689589837 is the original data file in DATA. Because you issued SET NEWNAME and SWITCH commands for this data file, the original file is now recorded in the RMAN repository as a data file copy. Run a DELETE command in the RMAN client to remove this file.

For example:

RMAN> DELETE DATAFILECOPY "+DATA/orcl/datafile/users.261.689589837";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
List of Datafile Copies
=======================
Key     File S Completion Time Ckp SCN    Ckp Time      
------- ---- - --------------- ---------- ---------------
14      4    A 16-DEC-16       864471     16-JUN-09     
        Name: +DATA/orcl/datafile/users.261.689589837
        Tag: TAG20161215T084217

Do you really want to delete the above objects (enter YES or NO)? y
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/users.261.689589837 RECID=14 STAMP=689683255
Deleted 1 objects


Done..!!


No comments: