MOVING TABLESPACE/DATAFILES ACROSS DISK GROUP IN ASM (ONLINE & OFFLINE)

1. MOVING SYSTEM TABLESPACE FROM ONE DISK GROUP TO ANOTHER DISK GROUP

$sqlplus / as sysdba

SQL> startup mount

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

RMAN> switch tablespace system to copy;

SQL> alter database open;

  

2. MOVING SYSTEM DATAFILE FROM ONE DISK GROUP TO ANOTHER DISK GROUP

STEP 1: Start the database in the mount stage

STEP 2: connect to the rman and copy the datafile

RMAN> copy datafile 1 to '+SYSTEM';

Starting backup at 08-SEP-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=388 instance=BE1_2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA1/nikhil/datafile/psapundo.259.756901419

output file name=+SYSTEM/nikhil/datafile/undo.263.761315651 tag=TAG20110908T123410 RECID=21 STAMP=761315652

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

Finished backup at 08-SEP-11

STEP 3: switch the datafile to the new disk group

RMAN> switch datafile 1 to copy;

datafile 1 switched to datafile copy "+SYSTEM/nikhil/datafile/undo.263.761315651"

STEP 4: open the database

SQL> alter database open;

3. MOVING NON-SYSTEM TABLESPACE FROM ONE DISK GROUP TO ANOTHER DISK GROUP    (WITH OFFLINE OPTION)

STEP 1: Offline the tablespace

RMAN>  sql ‘alter tablespace <TBS_NAME> offline’;

STEP 2: connect to the rman and copy the tablespace

RMAN> backup as copy tablespace <TBS_NAME>  format  ‘<+NEW_DISK>’;

STEP 3: Switch the tablespace to the new location

RMAN> switch tablespace <TBS_NAME>  to copy;

STEP 4: Online the tablespace

RMAN> sql ‘alter tablespace <TBS_NAME>  online’;

 4. MOVING NON-SYSTEM TABLESPACE FROM ONE DISK GROUP TO ANOTHER DISK GROUP
    (WITH ONLINE OPITION)

STEP 1: connect to the rman and copy the tablespace

RMAN> backup as copy tablespace  <TBS_NAME>  format  ‘<+NEW_DISK>’;

STEP 2: Offline the tablespace

RMAN> sql ‘alter tablespace <TBS_NAME> offline’;

STEP 3: Switch the tablespace to the new location

RMAN> switch tablespace <TBS_NAME>  to copy;

STEP 4: Recover the tablespace

RMAN> recover tablespace <TBS_NAME>;

STEP 4: Online the tablespace

RMAN> sql ‘alter tablespace <TBS_NAME> online’;

5. MOVING NON-SYSTEM DATAFILE FROM ONE DISK GROUP TO ANOTHER DISK GROUP    (ONLINE & OFFLINE)

STEP 1: Offline the datafile

RMAN> sql ‘alter datafile <datafile_number or name> offline’;

STEP 2: connect to the rman and copy the tablespace

RMAN> COPY DATAFILE <datafile_number or name> to ‘<+NEW_DISK’>;

STEP 3: Switch the tablespace to the new location

RMAN> switch datafile <datafile_number or name> to copy;

STEP 4: Recover the datafile

RMAN> recover datafile <datafile_number or name>;

STEP 5: Online the tablespace

RMAN> ‘alter datafile <datafile_number or name> online’;

VISIT for MORE