Today I got chance To move datafile from one disk group to another Diskgroup. Using below plan (1) We can 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.
Option 1-
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
--------------------------------------------------- --------
+DATA/db/datafile/sysaux.260.699468081 3
+DATA/db/datafile/system.259.688768099 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=2157822341)
RMAN> REPORT SCHEMA;
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 560 SYSTEM *** +DATA/db/datafile/system.259.688768099
3 500 SYSAUX *** +DATA/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 "+DATA/db/datafile/sysaux.260.699468081" TO COPY;
SWITCH DATAFILE "+DATA/db/datafile/system.259.688768099" 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 "+DATA/db/datafile/sysaux.260.699468081";
DELETE DATAFILECOPY "+DATA/db/datafile/system.259.688768099";
}
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
------------------------------------------------------------------ --------
+DATA/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=2157822341)
Check...
RMAN> REPORT SCHEMA;
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DATA/db/datafile/users.258.699468081
Bring database file to offline
RMAN> SQL "ALTER DATABASE DATAFILE ' ' +DATA/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 "+DATA/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 "+DATA/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 "+DATA/db/datafile/users.258.699468081";
Option 2-
If not System Tablespace... We don't need stop database and We can offline tablespace... when online talblespace, we don't need recover...
SQL> select file_id, file_name from dba_data_files where tablespace_name='TEST_DATA';
FILE_ID FILE_NAME
---------- --------------------------------------------------
9 +DATA/orcl/datafile/test_data.1335.721759717
SQL> alter tablespace TEST_DATA read only;
Tablespace altered.
SQL> alter tablespace TEST_DATA offline;
Tablespace altered
RMAN> backup as copy datafile 9 format '+DATA2';
Starting backup at 26-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+DATA/orcl/datafile/test_data.1335.721759717
output file name=+DATA2/orcl/datafile/test_data.993.749500909 tag=TAG20110426T184147 RECID=38 STAMP=749500915
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 26-APR-11
RMAN> SWITCH DATAFILE 9 to copy;
datafile 9 switched to datafile copy "+DATA2/orcl/datafile/test_data.993.749500909"
SQL> alter tablespace TEST_DATA online;
Tablespace altered.
SQL> alter tablespace TEST_DATA read write;
Tablespace altered.
SQL> select file_id, file_name from dba_data_files where tablespace_name='TEST_DATA';
FILE_ID FILE_NAME
---------- --------------------------------------------------
9 +DATA2/orcl/datafile/test_data.993.749500909
