Thursday, October 22, 2009

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";
}

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";
...

3 comments:

ocpdba said...

Great article. Have you tried the "cp" command of the Oracle 11g ASMCMD?
Take Care. LMC.

Surachart said...

Thank You,
I'll test it on 11g ASMCMD ;)

Be Careful.

Surachart said...

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