Moving datafile from one diskgroup to another in ASM – 10.2.0.4

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 >
Advertisements

2 thoughts on “Moving datafile from one diskgroup to another in ASM – 10.2.0.4

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s