Moving datafile (system) from one ASM Diskgroup to Another

We have to move database file to NEW diskgroup... In case, we have to move system + sysaux database files as well.

With Oracle ASM, when we need to move database file from one diskgroup to another. we have to use "rman" to help.

About moving system + sysaux database files, we need database (mount)

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';

FILE_NAME FILE_ID

--------------------------------------------------- --------

+OLD/db/datafile/sysaux.260.699468081 3

+OLD/db/datafile/system.259.699468079 1

SQL> SELECT name FROM v$asm_diskgroup;

NAME

--------------------

OLD

NEW

We need to move database files to NEW diskgroup

SQL> shutdown immediate;

SQL> startup mount;

Connect target database by "rman" command-line :

$ rman target /

connected to target database: DB (DBID=1043389676)

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1 560 SYSTEM *** +OLD/db/datafile/system.259.699468079

3 500 SYSAUX *** +OLD/db/datafile/sysaux.260.699468081

Backup AS COPY "database files" to NEW diskgroup, then SWITCH DATAFILE to use NEW diskgroup

RMAN> run {

BACKUP AS COPY DATAFILE 1 FORMAT "+NEW";

BACKUP AS COPY DATAFILE 3 FORMAT "+NEW";

SWITCH DATAFILE "+OLD/db/datafile/sysaux.260.699468081" TO COPY;

SWITCH DATAFILE "+OLD/db/datafile/system.259.699468079" TO COPY;

}

Open database...

SQL> alter database open;

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';

FILE_NAME FILE_ID

--------------------------------------------------- --------

+NEW/db/datafile/sysaux.291.700906921 3

+NEW/db/datafile/system.294.700906903 1

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1 560 SYSTEM *** +NEW/db/datafile/system.294.700906903

3 500 SYSAUX *** +NEW/db/datafile/sysaux.291.700906921

After make sure...(no error and database files used in NEW diskgroup), then DELETE old datafilecopy(original)...

RMAN> run {

DELETE DATAFILECOPY "+OLD/db/datafile/sysaux.260.699468081";

DELETE DATAFILECOPY "+OLD/db/datafile/system.259.699468079";

}

reference: http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmasmmi.htm

If not "SYSTEM" database file. we can move database file while database open...

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';

FILE_NAME FILE_ID

------------------------------------------------------------------ --------

+OLD/db/datafile/users.258.699468081 4

SQL> SELECT name FROM v$asm_diskgroup;

NAME

--------------------

OLD

NEW

We need to move database file (4) to NEW diskgroup

Connect target database by "rman" command-line

$ rman target /

connected to target database: DB (DBID=1043389676)

Check...

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

4 5 USERS *** +OLD/db/datafile/users.258.699468081

Bring database file to offline

RMAN> SQL "ALTER DATABASE DATAFILE ' ' +OLD/db/datafile/users.258.699468081 ' ' OFFLINE";

or

RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';

Backup as copy database file to NEW diskgroup

RMAN> BACKUP AS COPY DATAFILE "+OLD/db/datafile/users.258.699468081" FORMAT "+NEW";

or

RMAN> BACKUP AS COPY DATAFILE 4 FORMAT "+NEW";

Switch database file to NEW diskgroup

RMAN> SWITCH DATAFILE "+OLD/db/datafile/users.258.699468081" TO COPY;

and we need "RECOVER"...

RMAN> RECOVER DATAFILE 4;

Bring database file to online:

RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';

Check...

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

4 5 USERS *** +NEW/db/datafile/users.257.700906939

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';

FILE_NAME FILE_ID

------------------------------------------------------------------ --------

+NEW/db/datafile/users.257.700906939 4

After Make sure... DELETE old datafilecopy(original):

RMAN> DELETE DATAFILECOPY "+OLD/db/datafile/users.258.699468081";

...