Most of you would already be knowing the various methods of moving the datafiles from one diskgroup to another.From Oracle ASM 11g we have ‘cp’ command in asmcmd utility which makes things much easier.As this is more kind of a note i prepared for myself,lets get started.
The only way to copy files to or from an ASM diskgroup in 10g was either to use RMAN, or use the DBMS_FILE_TRANSFER package to configure XDB for FTP access,.Here i tried with :-
1. RMAN
2. DBMS_FILE_TRANSFER
1. RMAN
22:59:46 SYS at matrix >@filename
Enter value for tbs_name: main
old 1: select file_id,file_name,(bytes/1024/1024)MB,AUTOEXTENSIBLE, MAXBYTES/1024/1024 "MAX size Mb" from dba_data_files where tablespace_name=UPPER('&tbs_name')
new 1: select file_id,file_name,(bytes/1024/1024)MB,AUTOEXTENSIBLE, MAXBYTES/1024/1024 "MAX size Mb" from dba_data_files where tablespace_name=UPPER('main')
FILE_ID FILE_NAME MB AUT MAX size Mb
---------- ---------------------------------------------------------------------- ---------- --- -----------
6 +DATA1/matrix/datafile/main.267.732802425 250 NO 0
7 +DATA2/matrix/datafile/main.261.736639043 250 NO 0
Elapsed: 00:00:00.10
23:02:22 SYS at matrix >
23:04:52 SYS at matrix >select FILE_ID,FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where FILE_ID=7;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- ---------------------------------------------------------------------- ------------------------------ --------- -------
7 +DATA2/matrix/datafile/main.261.736639043 MAIN AVAILABLE ONLINE
Elapsed: 00:00:00.14
23:05:16 SYS at matrix >
23:10:07 SYS at matrix >col name for a50
23:10:17 SYS at matrix >select FILE#,name,STATUS,ENABLED,CHECKPOINT_CHANGE#,OFFLINE_CHANGE#,ONLINE_TIME from v$datafile where FILE#=7;
FILE# NAME STATUS ENABLED CHECKPOINT_CHANGE# OFFLINE_CHANGE# ONLINE_TI
---------- -------------------------------------------------- ------- ---------- ------------------ --------------- ---------
7 +DATA2/matrix/datafile/main.261.736639043 ONLINE READ WRITE 1632555 0
Elapsed: 00:00:00.20
SO, here i will move the datafile ‘main.261.736639043’ from +DATA2 diskgroup to +DATA1 using RMAN.I like doing things in trace mode.
D:\>rman target / debug trace=D:\asm_test\rman_trace.lst
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Dec 2 23:04:29 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06005: connected to target database: MATRIX (DBID=2272919429)
RMAN> run{
2> ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
3> copy datafile 7 to '+DATA1';
4> sql 'alter database datafile 7 offline';
5> release channel C1;
6> }
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: C1
RMAN-08500: channel C1: sid=159 devtype=DISK
RMAN-03090: Starting backup at 02-DEC-10
RMAN-08580: channel C1: starting datafile copy
RMAN-08522: input datafile fno=00007 name=+DATA2/matrix/datafile/main.261.736639043
RMAN-08586: output filename=+DATA1/matrix/datafile/main.270.736729913 tag=TAG20101202T231148 recid=15 stamp=736729947
RMAN-08581: channel C1: datafile copy complete, elapsed time: 00:00:38
RMAN-03091: Finished backup at 02-DEC-10
RMAN-03090: Starting Control File and SPFILE Autobackup at 02-DEC-10
RMAN-08503: piece handle=D:\DB_BACKUP\RMAN\C-2272919429-20101202-01 comment=NONE
RMAN-03091: Finished Control File and SPFILE Autobackup at 02-DEC-10
RMAN-06162: sql statement: alter database datafile 7 offline
RMAN-08031: released channel: C1
RMAN>
So output file is ‘+DATA1/matrix/datafile/main.270.736729913’ which gets physically created in the specified location.This can be checked using ASMCMD utility.The trace shows lots of PL/SQL codes being called and mainly sys.dbms_backup_restore being used.
Session 2 – SQLPLUS session
==============================
23:17:08 SYS at matrix >select FILE_ID,FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where FILE_ID=7;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- ---------------------------------------------------------------------- ------------------------------ --------- -------
7 +DATA2/matrix/datafile/main.261.736639043 MAIN AVAILABLE RECOVER
Elapsed: 00:00:00.11
23:17:10 SYS at matrix >select FILE#,name,STATUS,ENABLED,CHECKPOINT_CHANGE#,OFFLINE_CHANGE#,ONLINE_TIME from v$datafile where FILE#=7;
FILE# NAME STATUS ENABLED CHECKPOINT_CHANGE# OFFLINE_CHANGE# ONLINE_TI
---------- -------------------------------------------------- ------- ---------- ------------------ --------------- ---------
7 +DATA2/matrix/datafile/main.261.736639043 RECOVER READ WRITE 1633406 0
Elapsed: 00:00:00.04
23:17:15 SYS at matrix >
23:18:53 SYS at matrix >select file#,CHECKPOINT_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1629758
2 1629758
3 1629758
4 1629758
5 1629758
6 1629758
7 1633406
7 rows selected.
Elapsed: 00:00:00.15
23:19:17 SYS at matrix >
Session 1 – RMAN session
=======================
RMAN> switch datafile 7 to copy; RMAN-06570: datafile 7 switched to datafile copy "+DATA1/matrix/datafile/main.270.736729913" RMAN> recover datafile 7; RMAN-03090: Starting recover at 02-DEC-10 RMAN-08030: allocated channel: ORA_DISK_1 RMAN-08500: channel ORA_DISK_1: sid=159 devtype=DISK RMAN-08054: starting media recovery RMAN-08181: media recovery complete, elapsed time: 00:00:02 RMAN-03091: Finished recover at 02-DEC-10
Session 2 – SQLPLUS session
===================================
23:19:55 SYS at matrix >select FILE#,name,STATUS,ENABLED,CHECKPOINT_CHANGE#,OFFLINE_CHANGE#,ONLINE_TIME from v$datafile where FILE#=7;
FILE# NAME STATUS ENABLED CHECKPOINT_CHANGE# OFFLINE_CHANGE# ONLINE_TI
---------- -------------------------------------------------- ------- ---------- ------------------ --------------- ---------
7 +DATA1/matrix/datafile/main.270.736729913 RECOVER READ WRITE 1633406 0
Elapsed: 00:00:01.01
23:20:17 SYS at matrix >select FILE_ID,FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where FILE_ID=7;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- ---------------------------------------------------------------------- ------------------------------ --------- -------
7 +DATA1/matrix/datafile/main.270.736729913 MAIN AVAILABLE OFFLINE
Elapsed: 00:00:00.10
23:20:18 SYS at matrix >select FILE#,name,STATUS,ENABLED,CHECKPOINT_CHANGE#,OFFLINE_CHANGE#,ONLINE_TIME from v$datafile where FILE#=7;
FILE# NAME STATUS ENABLED CHECKPOINT_CHANGE# OFFLINE_CHANGE# ONLINE_TI
---------- -------------------------------------------------- ------- ---------- ------------------ --------------- ---------
7 +DATA1/matrix/datafile/main.270.736729913 OFFLINE READ WRITE 1633435 0
Elapsed: 00:00:00.20
23:20:23 SYS at matrix >
Session1 – RMAN session
RMAN> sql 'alter database datafile 7 online'; RMAN-06162: sql statement: alter database datafile 7 online RMAN>
Session 2 – SQLPLUS session
===========================
23:20:57 SYS at matrix >select FILE_ID,FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where FILE_ID=7;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- ---------------------------------------------------------------------- ------------------------------ --------- -------
7 +DATA1/matrix/datafile/main.270.736729913 MAIN AVAILABLE ONLINE
Elapsed: 00:00:00.09
23:20:58 SYS at matrix >select FILE#,name,STATUS,ENABLED,CHECKPOINT_CHANGE#,OFFLINE_CHANGE#,ONLINE_TIME from v$datafile where FILE#=7;
FILE# NAME STATUS ENABLED CHECKPOINT_CHANGE# OFFLINE_CHANGE# ONLINE_TI
---------- -------------------------------------------------- ------- ---------- ------------------ --------------- ---------
7 +DATA1/matrix/datafile/main.270.736729913 ONLINE READ WRITE 1633789 0
Elapsed: 00:00:00.03
23:20:59 SYS at matrix >select file#,CHECKPOINT_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1629758
2 1629758
3 1629758
4 1629758
5 1629758
6 1629758
7 1633789
7 rows selected.
Elapsed: 00:00:00.07
23:27:35 SYS at matrix >@filename
Enter value for tbs_name: main
old 1: select file_id,file_name,(bytes/1024/1024)MB,AUTOEXTENSIBLE, MAXBYTES/1024/1024 "MAX size Mb" from dba_data_files where tablespace_name=UPPER('&tbs_name')
new 1: select file_id,file_name,(bytes/1024/1024)MB,AUTOEXTENSIBLE, MAXBYTES/1024/1024 "MAX size Mb" from dba_data_files where tablespace_name=UPPER('main')
FILE_ID FILE_NAME MB AUT MAX size Mb
---------- ---------------------------------------------------------------------- ---------- --- -----------
6 +DATA1/matrix/datafile/main.267.732802425 250 NO 0
7 +DATA1/matrix/datafile/main.270.736729913 250 NO 0
Elapsed: 00:00:00.07
The datafile ‘+DATA2/matrix/datafile/main.261.736639043’ will be present in the location.You can remove the file either using the ‘rm’ command in asmcmd utility or using ‘alter diskgroup drop file’ in the command line.
2. DBMS_FILE_TRANSFER
Moving the datafile from ‘+DATA1’ to ‘+DATA2’ diskgroup using DBMS_FILE_TRANSFER package.
23:31:27 SYS at matrix >drop directory DEST_DIR;
Directory dropped.
Elapsed: 00:00:00.31
23:32:11 SYS at matrix >drop directory SOURCE_DIR;
Directory dropped.
Elapsed: 00:00:00.03
23:34:40 SYS at matrix >@filename
Enter value for tbs_name: main
old 1: select file_id,file_name,(bytes/1024/1024)MB,AUTOEXTENSIBLE, MAXBYTES/1024/1024 "MAX size Mb" from dba_data_files where tablespace_name=UPPER('&tbs_name')
new 1: select file_id,file_name,(bytes/1024/1024)MB,AUTOEXTENSIBLE, MAXBYTES/1024/1024 "MAX size Mb" from dba_data_files where tablespace_name=UPPER('main')
FILE_ID FILE_NAME MB AUT MAX size Mb
---------- ---------------------------------------------------------------------- ---------- --- -----------
6 +DATA1/matrix/datafile/main.267.732802425 250 NO 0
7 +DATA1/matrix/datafile/main.270.736729913 250 NO 0
Elapsed: 00:00:00.06
23:34:46 SYS at matrix >
23:34:47 SYS at matrix >create directory SOURCE_DIR as '+DATA1/matrix/datafile/';
Directory created.
Elapsed: 00:00:00.12
23:35:11 SYS at matrix >create directory DEST_DIR as '+DATA2/matrix/datafile/';
Directory created.
Elapsed: 00:00:00.04
23:36:33 SYS at matrix >
23:40:33 SYS at matrix >select FILE_ID,FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where FILE_ID=7;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- ---------------------------------------------------------------------- ------------------------------ --------- -------
7 +DATA1/matrix/datafile/main.270.736729913 MAIN AVAILABLE ONLINE
Elapsed: 00:00:00.09
23:40:46 SYS at matrix >select FILE#,name,STATUS,ENABLED,CHECKPOINT_CHANGE#,OFFLINE_CHANGE#,ONLINE_TIME from v$datafile where FILE#=7;
FILE# NAME STATUS ENABLED CHECKPOINT_CHANGE# OFFLINE_CHANGE# ONLINE_TI
---------- -------------------------------------------------- ------- ---------- ------------------ --------------- ---------
7 +DATA1/matrix/datafile/main.270.736729913 ONLINE READ WRITE 1633789 0
Elapsed: 00:00:00.03
23:40:46 SYS at matrix >alter database datafile 7 offline;
Database altered.
Elapsed: 00:00:00.14
23:40:55 SYS at matrix >BEGIN
23:41:02 2 DBMS_FILE_TRANSFER.COPY_FILE(
23:41:02 3 source_directory_object =>'SOURCE_DIR',
23:41:02 4 source_file_name =>'main.270.736729913',
23:41:02 5 destination_directory_object =>'DEST_DIR',
23:41:02 6 destination_file_name =>'main.270.736729913');
23:41:02 7 END;
23:41:02 8 /
BEGIN
*
ERROR at line 1:
ORA-19504: failed to create file "+DATA2/matrix/datafile//main.270.736729913"
ORA-17502: ksfdcre:4 Failed to create file +DATA2/matrix/datafile//main.270.736729913
ORA-15046: ASM file name '+DATA2/matrix/datafile//main.270.736729913' is not in single-file creation form
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 2
Elapsed: 00:00:00.73
23:41:03 SYS at matrix >
I tried to move file ‘main.270.736729913’ from the location defined by directory ‘SOURCE_DIR’ to a new location defined by ‘DEST_DIR’, using the same name with OMF format.So, the solution is
23:41:15 SYS at matrix > BEGIN 23:41:35 2 DBMS_FILE_TRANSFER.COPY_FILE( 23:41:35 3 source_directory_object =>'SOURCE_DIR', 23:41:35 4 source_file_name =>'main.270.736729913', 23:41:35 5 destination_directory_object =>'DEST_DIR', 23:41:35 6 destination_file_name =>'main_02.dbf'); 23:41:35 7 END; 23:41:36 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:33.78 23:42:10 SYS at matrix >
The destination name can not use OMF format. So,using an alias will create automatically the entry with the OMF format.The trace of the session would mostly show ‘dbms_file_transfer I/O’ wait event.
Session 2 – ASMCMD session
==================================
ASMCMD [+DATA1/MATRIX/DATAFILE] > ls -lrt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE DEC 02 22:00:00 Y EXAMPLE.265.732594843
DATAFILE UNPROT COARSE DEC 02 22:00:00 Y MAIN.267.732802425
DATAFILE UNPROT COARSE DEC 02 22:00:00 Y SYSAUX.257.732594585
DATAFILE UNPROT COARSE DEC 02 22:00:00 Y SYSTEM.256.732594585
DATAFILE UNPROT COARSE DEC 02 22:00:00 Y UNDOTBS1.258.732594587
DATAFILE UNPROT COARSE DEC 02 22:00:00 Y USERS.259.732594587
DATAFILE UNPROT COARSE DEC 02 23:00:00 Y MAIN.270.736729913
ASMCMD [+DATA1/MATRIX/DATAFILE] > cd +DATA2/MATRIX/DATAFILE
ASMCMD [+DATA2/MATRIX/DATAFILE] >
ASMCMD [+DATA2/MATRIX/DATAFILE] >
ASMCMD [+DATA2/MATRIX/DATAFILE] > ls -lrt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE DEC 02 23:00:00 Y COPY_FILE.261.736732333
N main_02.dbf => +DATA2/MATRIX/DATAFILE/COPY_FILE.261.736732333
ASMCMD [+DATA2/MATRIX/DATAFILE] >
ASMCMD [+DATA2/MATRIX/DATAFILE] >
Above we can see an OMF file (COPY_FILE.261.736732333) created which is SYSTEM generated and hence Sys column shows ‘Y’. Also an alias ‘main_02’ pointing to the actual file is created.
Interesting to note is the ‘Time’ column.Though the file was created around 23:42PM the time still shows 23:00:00.Atleast i have never seen minutes and seconds values other than zero 🙂
Session 1 – SQLPLUS session
=====================================
As the new datafile has been created in ‘+DATA2’ diskgroup, we need to rename the datafile 7.
23:46:34 SYS at matrix >select FILE_ID,FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where FILE_ID=7;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- ---------------------------------------------------------------------- ------------------------------ --------- -------
7 +DATA1/matrix/datafile/main.270.736729913 MAIN AVAILABLE ONLINE
23:53:50 SYS at matrix >alter database rename file '+DATA1/matrix/datafile/main.270.736729913' to '+DATA2/matrix/datafile/main_02.dbf';
Database altered.
Elapsed: 00:00:03.32
23:53:59 SYS at matrix >
23:54:02 SYS at matrix >select FILE_ID,FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where FILE_ID=7;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- ---------------------------------------------------------------------- ------------------------------ --------- -------
7 +DATA2/matrix/datafile/main_02.dbf MAIN AVAILABLE RECOVER
Elapsed: 00:00:00.07
23:54:08 SYS at matrix >select FILE#,name,STATUS,ENABLED,CHECKPOINT_CHANGE#,OFFLINE_CHANGE#,ONLINE_TIME from v$datafile where FILE#=7;
FILE# NAME STATUS ENABLED CHECKPOINT_CHANGE# OFFLINE_CHANGE# ONLINE_TI
---------- -------------------------------------------------- ------- ---------- ------------------ --------------- ---------
7 +DATA2/matrix/datafile/main_02.dbf RECOVER READ WRITE 1634583 0
Elapsed: 00:00:00.04
23:54:09 SYS at matrix >
23:54:10 SYS at matrix >recover datafile 7;
Media recovery complete.
23:54:20 SYS at matrix >
23:54:24 SYS at matrix >select FILE_ID,FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where FILE_ID=7;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS ONLINE_
---------- ---------------------------------------------------------------------- ------------------------------ --------- -------
7 +DATA2/matrix/datafile/main_02.dbf MAIN AVAILABLE OFFLINE
Elapsed: 00:00:00.04
23:54:28 SYS at matrix >select FILE#,name,STATUS,ENABLED,CHECKPOINT_CHANGE#,OFFLINE_CHANGE#,ONLINE_TIME from v$datafile where FILE#=7;
FILE# NAME STATUS ENABLED CHECKPOINT_CHANGE# OFFLINE_CHANGE# ONLINE_TI
---------- -------------------------------------------------- ------- ---------- ------------------ --------------- ---------
7 +DATA2/matrix/datafile/main_02.dbf OFFLINE READ WRITE 1634917 0
Elapsed: 00:00:00.04
23:54:29 SYS at matrix >alter database datafile 7 online;
Database altered.
Elapsed: 00:00:03.35
23:54:49 SYS at matrix >@filename
Enter value for tbs_name: main
old 1: select file_id,file_name,(bytes/1024/1024)MB,AUTOEXTENSIBLE, MAXBYTES/1024/1024 "MAX size Mb" from dba_data_files where tablespace_name=UPPER('&tbs_name')
new 1: select file_id,file_name,(bytes/1024/1024)MB,AUTOEXTENSIBLE, MAXBYTES/1024/1024 "MAX size Mb" from dba_data_files where tablespace_name=UPPER('main')
FILE_ID FILE_NAME MB AUT MAX size Mb
---------- ---------------------------------------------------------------------- ---------- --- -----------
6 +DATA1/matrix/datafile/main.267.732802425 250 NO 0
7 +DATA2/matrix/datafile/main_02.dbf 250 NO 0
Elapsed: 00:00:00.12
23:55:39 SYS at matrix >
2 thoughts on “Moving datafile from one diskgroup to another in ASM – 10.2.0.4”
Thanks for sharing this Anand!! However, is it possible for ou to provide a demo for the same in 11g…if at all you have free time in your hand….
Thanks,
Ajinkya
Hi,
Thanks for visiting the blog.
It would take some time for me to upload a demo.Meanwhile you can read a nice link
http://surachartopun.com/2009/10/moving-datafile-in-asm-by-asmcmdcp.html
Regards,
Anand