Renaming/Relocating the Datafiles from one disk group to another one in ASM

In this post I'll show you to rename the existing datafile which resides into a ASM disk group.

I'll only put the respected datafile into offline mode inspite of putting the entire tablespace so that the other schema users which might belong to that tablespace will not hamper.

I'll consider two test case to check the behavior of the Oracle process when we put the datafile into ONLINE mode.

Case 1. Without Commiting the transaction.

SQL> create tablespace test
  2  datafile '+DATA' size 200M;

Tablespace created.



SQL> select a.NAME,a.STATUS,b.USERNAME,c.BYTES
  2  from v$datafile a, dba_users b, dba_data_files c
  3  where a.NAME=c.FILE_NAME
  4  and
  5  c.TABLESPACE_NAME=b.DEFAULT_TABLESPACE
  6  and b.USERNAME='TEST'
  7  /

NAME                                     STATUS  USERNAME                            BYTES
---------------------------------------- ------- ------------------------------ ----------
+DATA/node/datafile/test.283.829102823   ONLINE  TEST                            209715200



SQL> create user test identified by test
  2  default tablespace test;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test;
Connected.
SQL> create table xyz
  2  as select * from scott.emp;

Table created.


SQL> select count(*) from xyz;

  COUNT(*)
----------
        14

SQL> begin
  2  for i in 1..100000
  3  loop
  4  insert into xyz
  5  select * from scott.emp;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.



SQL> alter database datafile '+DATA/node/datafile/test.283.829102823' offline;

Database altered.



RMAN> copy datafile '+DATA/node/datafile/test.283.829102823' to '+DATA';

Starting backup at 18-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 instance=node1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/node/datafile/test.283.829102823
output file name=+DATA/node/datafile/test.284.829157163 tag=TAG20131018T172601 RECID=2 STAMP=829157170
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 18-OCT-13

RMAN> exit


Recovery Manager complete.

SQL> alter database rename file '+DATA/node/datafile/test.283.829102823' to '+DATA/node/datafile/test.284.829157163';

Database altered.

SQL> recover datafile '+DATA/node/datafile/test.284.829157163';
ORA-00279: change 14969378 generated at 10/18/2013 02:55:04 needed for thread 1
ORA-00289: suggestion : +BACKUP/node/archivelog/2013_10_18/thread_1_seq_295.257.829104939
ORA-00280: change 14969378 for thread 1 is in sequence #295


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 14969378 generated at 10/18/2013 02:13:08 needed for thread 2
ORA-00289: suggestion : +BACKUP/node/archivelog/2013_10_18/thread_2_seq_123.260.829116519
ORA-00280: change 14969378 for thread 2 is in sequence #123


ORA-00279: change 14971006 generated at 10/18/2013 02:55:08 needed for thread 1
ORA-00289: suggestion : +BACKUP/node/archivelog/2013_10_18/thread_1_seq_296.258.829105041
ORA-00280: change 14971006 for thread 1 is in sequence #296


ORA-00279: change 15013692 generated at 10/18/2013 02:57:18 needed for thread 1
ORA-00289: suggestion : +BACKUP/node/archivelog/2013_10_18/thread_1_seq_297.259.829105051
ORA-00280: change 15013692 for thread 1 is in sequence #297


Log applied.
Media recovery complete.
SQL> alter database datafile '+DATA/node/datafile/test.284.829157163' online;

Database altered.

SQL> conn test/test
Connected.
SQL> select count(*) from xyz;

  COUNT(*)
----------
         0

Case 2. Commiting the Transaction.

SQL> begin
  2  for i in 1..100000
  3  loop
  4  insert into xyz
  5  select * from scott.emp;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.



SQL> select count(*) from xyz;
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '+DATA/node/datafile/test.284.829157163'



SQL> alter database datafile '+DATA/node/datafile/test.284.829157163' offline;

Database altered.

SQL> select a.NAME,a.STATUS,b.USERNAME,c.BYTES
  2  from v$datafile a, dba_users b, dba_data_files c
  3  where a.NAME=c.FILE_NAME
  4  and
  5  c.TABLESPACE_NAME=b.DEFAULT_TABLESPACE
  6  and b.USERNAME='TEST'
  7  /

NAME                                     STATUS  USERNAME                            BYTES
---------------------------------------- ------- ------------------------------ ----------
+DATA/node/datafile/test.284.829157163   RECOVER TEST

SQL> host rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 18 18:02:58 2013

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

connected to target database: NODE (DBID=873107756)

RMAN> copy datafile '+DATA/node/datafile/test.284.829157163' to '+DATA';

Starting backup at 18-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 instance=node1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/node/datafile/test.284.829157163
output file name=+DATA/node/datafile/test.283.829159407 tag=TAG20131018T180326 RECID=3 STAMP=829159416
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 18-OCT-13


SQL> alter database datafile '+DATA/node/datafile/test.283.829159407' online;
alter database datafile '+DATA/node/datafile/test.283.829159407' online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '+DATA/node/datafile/test.283.829159407'


SQL> recover datafile '+DATA/node/datafile/test.283.829159407';
Media recovery complete.

SQL> alter database datafile '+DATA/node/datafile/test.283.829159407' online;

Database altered.


SQL> select count(*) from xyz;

  COUNT(*)
----------
   2800000