Moving Datafiles Between ASM Disk Groups

This blog came about as a result of a situation in a production system. The ASM disks in the DATA diskgroup were full (should have monitored) and data files had no where to extend. Only space left is in the FLASH diskgroup. To keep the system running create an additional datafile for the tablespace in that location. Later when new ASM disk space is added to the DATA diskgroup move the datafiles created in FLASH group to DATA group.

First running out of space situation

1. Create a tablespace with few mega bytes and grant the user the quotas on it

create tablespace test datafile '+DATA(datafile)' SIZE 2m autoextend on next  1m maxsize 5m;

2. Create a table in that tablespace

create table fult (a char(2000),b char(2000), c char(2000), d char(2000)) tablespace test;

3. Run the following PL/SQL code. Above created tablespace ran out of space after inserting 248 rows. Inserting the 249th row raised an error

begin
    for i in 1 .. 248
    loop
    insert into fult values ('a'||i,'a'||i,'a'||i,'a'||i);
    end loop;
end;
/

Inserting the 249th

insert into fult values ('a','a','a','a')

insert into fult values ('a','a','a','a')
*
ERROR at line 1:
ORA-01653: unable to extend table ASANGA.FULT by 128 in tablespace TEST

4. Add another datafile into the tablespace (assuming diskgroup is filled)

 alter tablespace test add datafile '+flash(datafile)' SIZE 5m autoextend on next 1m maxsize 10m;

After this step it will be possible to continue to add more rows.

Once new ASM disks are added move the newly created datafile from DATA ASM Disk group to FLASH Disk group.

5. List current datafiles in rman

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name RAC11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    730      SYSTEM               ***     +DATA/rac11g/datafile/system.256.720805879
2    980      SYSAUX               ***     +DATA/rac11g/datafile/sysaux.257.720805881
3    3365     UNDOTBS1             ***     +DATA/rac11g/datafile/undotbs1.258.720805881
4    897      USERS                ***     +DATA/rac11g/datafile/users.259.720805881
5    100      REDUNTBS             ***     +DATA/rac11g/datafile/reduntbs.334.727582971
6    100      ASMBKP               ***     +DATA/rac11g/datafile/asmbkp.479.729141249
7    100      MSSM                 ***     +DATA/rac11g/datafile/mssm.520.729490583
8    5        TEST                 ***     +DATA/rac11g/datafile/test.618.741548757
9    7        TEST                 ***     +FLASH/rac11g/datafile/test.266.741549549

6. Take the datafile in FLASH diskgroup offline

SQL> alter database datafile '+FLASH/rac11g/datafile/test.266.741549549' offline;
Database altered.

New rows cannot be inserted while the datafile is offline

SQL> insert into fult values ('a','a','a','a');
insert into fult values ('a','a','a','a')
            *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 9: +FLASH/rac11g/datafile/test.266.741549549'

7. Create a copy of the datafile

RMAN> copy datafile '+FLASH/rac11g/datafile/test.266.741549549' to '+DATA';

Starting backup at 27-Jan-2011 18:02:55
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 instance=rac11g1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+FLASH/rac11g/datafile/test.266.741549549
output file name=+DATA/rac11g/datafile/test.609.741549777 tag=TAG20110127T180255 RECID=1 STAMP=741549777
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-Jan-2011 18:02:57

8. Rename the datafile to the newly created copy

SQL> alter database rename file '+FLASH/rac11g/datafile/test.266.741549549' to '+DATA/rac11g/datafile/test.609.741549777';
Database altered. 

9. Switch to the copy and recover the datafile

RMAN> switch datafile '+DATA/rac11g/datafile/test.609.741549777' to copy;

datafile 9 switched to datafile copy "+DATA/rac11g/datafile/test.609.741549777"

RMAN> recover datafile 9;  

10. Bring the datafile online

SQL> alter database datafile '+DATA/rac11g/datafile/test.609.741549777' online;
Database altered. 

11. After this new rows could be inserted

SQL>  insert into fult values ('a','a','a','a');

1 row created.

12.The old datafile (one creatd in flash group is no longer part of the tablespace).

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name RAC11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    730      SYSTEM               ***     +DATA/rac11g/datafile/system.256.720805879
2    980      SYSAUX               ***     +DATA/rac11g/datafile/sysaux.257.720805881
3    3365     UNDOTBS1             ***     +DATA/rac11g/datafile/undotbs1.258.720805881
4    897      USERS                ***     +DATA/rac11g/datafile/users.259.720805881
5    100      REDUNTBS             ***     +DATA/rac11g/datafile/reduntbs.334.727582971
6    100      ASMBKP               ***     +DATA/rac11g/datafile/asmbkp.479.729141249
7    100      MSSM                 ***     +DATA/rac11g/datafile/mssm.520.729490583
8    5        TEST                 ***     +DATA/rac11g/datafile/test.618.741548757
9    7        TEST                 ***     +DATA/rac11g/datafile/test.609.741549777 

SQL> select d.name dname,t.name tname from v$datafile d, v$tablespace t where d.ts#=t.ts#;
Datafile Name      Tablespace
---------------------------------------------           -----------
+DATA/rac11g/datafile/system.256.720805879  SYSTEM
+DATA/rac11g/datafile/sysaux.257.720805881  SYSAUX
+DATA/rac11g/datafile/undotbs1.258.720805881  UNDOTBS1
+DATA/rac11g/datafile/users.259.720805881  USERS
+DATA/rac11g/datafile/reduntbs.334.727582971  REDUNTBS
+DATA/rac11g/datafile/asmbkp.479.729141249  ASMBKP
+DATA/rac11g/datafile/mssm.520.729490583  MSSM
+DATA/rac11g/datafile/test.618.741548757  TEST
+DATA/rac11g/datafile/test.609.741549777   TEST

Datafile in the flash diskgroup is automatically removed.