Relocating Datafiles from one ASM Disk to Another using RMAN:

Relocating Datafiles from one ASM Disk to Another using RMAN:

Relocating Datafiles from one ASM Disk to Another using RMAN

1. Place the datafile to be moved in offline

SQL>  alter database datafile '+<DGNAME>/<SID>/datafile/ebsdata.287.790435127' offline;

Database altered.

2. Copy the datafile to target ASM disk and don’t mention any directory structure.

RMAN> copy datafile '+<DGNAME>/<SID>/datafile/ebsdata.287.790435127' to  '+PPRDATA';

Starting backup at 03-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00013 name=+<DGNAME>/<SID>/datafile/ebsdata.287.790435127

output file name=+PPRDATA/<SID>/datafile/ebsdata.286.803747693 tag=TAG20130103T151453 RECID=20 STAMP=803749077

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:23:05

Finished backup at 03-JAN-13

Starting Control File and SPFILE Autobackup at 03-JAN-13

piece handle=/dbbackup/<SID>/c-2626060668-20130103-04 comment=NONE

Finished Control File and SPFILE Autobackup at 03-JAN-13

3. Make note of input and outfile name run the following command.

RMAN>  run {

2> set newname for datafile '+<DGNAME>/<SID>/datafile/ebsdata.287.790435127' to '+PPRDATA/<SID>/datafile/ebsdata.286.803747693'; ?Output file name here from step 2.

3> switch datafile all;

4> }

executing command: SET NEWNAME

datafile 13 switched to datafile copy

input datafile copy RECID=20 STAMP=803749077 file name=+PPRDATA/<SID>/datafile/ebsdata.286.803747693

4. Once done recover the new datafile

SQL> recover datafile '+PPRDATA/<SID>/datafile/ebsdata.286.803747693';

Media recovery complete.

5. Make the datafile online.

SQL> alter database datafile '+PPRDATA/<SID>/datafile/ebsdata.286.803747693' online;

Database altered.

We can’t use the same method for SYSTEM and SYSAUX datafiles as we can’t put the datafiles of system and sysaux in offline. Use the following method to move the datafiles of system and sysaux.

6. Take backup as copy for system tablespace.

RMAN> backup as copy tablespace system format '+PPRDATA';

Starting backup at 03-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00019 name=+PPRDATA/<SID>/datafile/system01.dbf

output file name=+PPRDATA/<SID>/datafile/system.293.803756671 tag=TAG20130103T174431 RECID=34 STAMP=803756736

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+<DGNAME>/<SID>/datafile/system.297.790435247

output file name=+PPRDATA/<SID>/datafile/system.294.803756737 tag=TAG20130103T174431 RECID=35 STAMP=803756774

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

Finished backup at 03-JAN-13

Starting Control File and SPFILE Autobackup at 03-JAN-13

piece handle=/dbbackup/<SID>/c-2626060668-20130103-0f comment=NONE

Finished Control File and SPFILE Autobackup at 03-JAN-13

7. Take backup as copy for sysaux tablespace.

RMAN> backup as copy tablespace sysaux  format '+PPRDATA';

Starting backup at 03-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+<DGNAME>/<SID>/datafile/sysaux.293.790435173

output file name=+PPRDATA/<SID>/datafile/sysaux.295.803756811 tag=TAG20130103T174650 RECID=36 STAMP=803756930

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05

channel ORA_DISK_1: starting datafile copy

input datafile file number=00020 name=+PPRDATA/<SID>/datafile/sysaux01.dbf

output file name=+PPRDATA/<SID>/datafile/sysaux.296.803756935 tag=TAG20130103T174650 RECID=37 STAMP=803756994

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05

Finished backup at 03-JAN-13

Starting Control File and SPFILE Autobackup at 03-JAN-13

piece handle=/dbbackup/<SID>/c-2626060668-20130103-10 comment=NONE

Finished Control File and SPFILE Autobackup at 03-JAN-13

8. Once backup of system and sysaux is completed stop the database.

[oracle@bgaodb21 ~]$ srvctl stop database -d <SID>

[oracle@bgaodb21 ~]$ ps -ef|grep pmon

oracle    7617     1  0  2012 ?        00:18:31 asm_pmon_+ASM1

oracle   32162 22710  0 18:06 pts/1    00:00:00 grep pmon

9. Start the database in mount   on one node if this is RAC database.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 8551575552 bytes

Fixed Size                  2230472 bytes

Variable Size            3942647608 bytes

Database Buffers         4596957184 bytes

Redo Buffers                9740288 bytes

Database mounted.

SQL> exit

10. Connect to RMAN and switch both system and sysaux tablespaces by issues the following command.

 [oracle@bgaodb21 ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 3 18:07:04 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: <SID> (DBID=2626060668, not open)

RMAN> switch tablespace system to copy;

using target database control file instead of recovery catalog

datafile 1 switched to datafile copy "+PPRDATA/<SID>/datafile/system.294.803756737"

datafile 19 switched to datafile copy "+PPRDATA/<SID>/datafile/system.293.803756671"

RMAN> switch tablespace sysaux to copy;

datafile 2 switched to datafile copy "+PPRDATA/<SID>/datafile/sysaux.295.803756811"

datafile 20 switched to datafile copy "+PPRDATA/<SID>/datafile/sysaux.296.803756935"

11. Recover the database.

Rman>Recover database.

12. Once recovery is completed shutdown the database and start the database.

Srvctl start database –d <SID>.