Moving Datafile using ASMCMD “cp” command – 11gR2

Few months back i wrote “Moving datafile from one diskgroup to another in ASM – 10.2.0.4” where i used RMAN and DBMS_FILE_TRANSFER methods to move the datafile between the diskgroups. From 11gR1, Oracle introduced “cp” command in asmcmd utility.

cp Command

Purpose

Enables you to copy files between ASM disk groups on local instances to and from remote instances. The file copy cannot be between remote instances. The local ASM instance must be either the source or the target of the operation. You can also use this command to copy files from ASM disk groups to the operating system.

Moving the datafiles between the diskgroups using asmcmp “cp” command —

SQL> select group_number, name, state, type, total_mb/1024 total_gb, free_mb/1024 free_gb, offline_disks from gv$asm_diskgroup;

GROUP_NUMBER NAME       STATE       TYPE     TOTAL_GB    FREE_GB OFFLINE_DISKS                                                                                                                                                        
------------ ---------- ----------- ------ ---------- ---------- -------------                                                                                                                                                        
           2 DATA02     MOUNTED     EXTERN          2 1.94921875             0                                                                                                                                                        
           1 DATA       CONNECTED   EXTERN          4 2.40136719             0                                                                                                                                                        

SQL> 
SQL> create tablespace test datafile size 10M;

Tablespace created.

SQL> select file#,name,(bytes/1024/1024)MB from v$datafile;

     FILE# NAME                                                       MB
---------- -------------------------------------------------- ----------
         1 +DATA/orcl/datafile/system.256.749749661                  680
         2 +DATA/orcl/datafile/sysaux.257.749749667                  550
         3 +DATA/orcl/datafile/undotbs1.258.749749667                 60
         4 +DATA/orcl/datafile/users.259.749749667                     5
         5 +DATA/orcl/datafile/example.265.749749897                 100
         6 +DATA/orcl/datafile/test.267.752424591                     10

6 rows selected.

The datafile test.267.752424591 is in +DATA diskgroup which will be moved to +DATA02 using “cp” command of asmcmd utility.

Make the datafile to be moved OFFLINE

SQL> alter system switch logfile;

System altered.

SQL> 
SQL> alter database datafile 6 offline;

Database altered.

Use ASMCMD to copy the file to another diskgroup

[oracle@anand-lab ~]$ asmcmd 
ASMCMD> cp +DATA/orcl/datafile/test.267.752424591 +DATA02/ORCL/DATAFILE/test.dbf
copying +DATA/orcl/datafile/test.267.752424591 -> +DATA02/ORCL/DATAFILE/test.dbf
ASMCMD>


ASMCMD [+DATA02/ORCL/DATAFILE] > ls -lt
Type      Redund  Striped  Time             Sys  Name
                                            N    test.dbf => +DATA02/ASM/DATAFILE/test.dbf.256.752424745

ASMCMD [+DATA02/ASM/DATAFILE] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   MAY 29 15:00:00  Y    test.dbf.256.752424745


Though i mentioned the location “+DATA02/ORCL/DATAFILE” , the system generated file got created in “+DATA02/ASM/DATAFILE” and a symbolic link with the name test.dbf is created pointing to original file “+DATA02/ASM/DATAFILE/test.dbf.256.752424745”.

To trace the asmcmd we can use DBI_TRACE. The trace shows

[oracle@anand-lab ~]$ export DBI_TRACE=1
[oracle@anand-lab ~]$ asmcmd 
ASMCMD> cp +DATA/orcl/datafile/test.267.752424591 +DATA02/ORCL/DATAFILE/test.dbf
    <- prepare('/* ASMCMD */ select group_number, state from v$asm_diskgroup_stat where name='DATA'')= DBI::st=HASH(0x8ce4678) at asmcmdshare.pm line 2572
    <- execute= '0E0' at asmcmdshare.pm line 2591
    <- fetchrow_hashref= HASH(0x8dbd918)2keys row1 at asmcmdshare.pm line 2617
    <- finish= 1 at asmcmdshare.pm line 2638
    <- DESTROY(DBI::st=HASH(8c9c3d8))= undef at asmcmdshare.pm line 1266
    <- prepare('/* ASMCMD */ select reference_index from v$asm_alias where group_number=2 and parent_index=33554432 and upper(name)='ORCL'')= DBI::st=HASH(0x8ce4788) at asmcmdshare.pm line 2572
    <- execute= '0E0' at asmcmdshare.pm line 2591
    <- fetchrow_hashref= HASH(0x8db7a40)1keys row1 at asmcmdshare.pm line 2617
    <- finish= 1 at asmcmdshare.pm line 2638
    <- DESTROY(DBI::st=HASH(8dbd8e8))= undef at asmcmdshare.pm line 1102
    <- prepare('/* ASMCMD */ select reference_index from v$asm_alias where group_number=2 and parent_index=33554591 and upper(name)='DATAFILE'')= DBI::st=HASH(0x8db7c10) at asmcmdshare.pm line 2572
    <- execute= '0E0' at asmcmdshare.pm line 2591
    <- fetchrow_hashref= HASH(0x8db7b00)1keys row1 at asmcmdshare.pm line 2617
    <- finish= 1 at asmcmdshare.pm line 2638
    <- DESTROY(DBI::st=HASH(8dbd818))= undef at asmcmdshare.pm line 1102
    <- prepare('/* ASMCMD */ select reference_index from v$asm_alias where group_number=2 and parent_index=33554644 and upper(name)='TEST.267.752424591'')= DBI::st=HASH(0x8dbd938) at asmcmdshare.pm line 2572
    <- execute= '0E0' at asmcmdshare.pm line 2591
    <- fetchrow_hashref= HASH(0x86148a8)1keys row1 at asmcmdshare.pm line 2617
    <- finish= 1 at asmcmdshare.pm line 2638
    <- DESTROY(DBI::st=HASH(8dbd868))= undef at asmcmdshare.pm line 1102
    <- prepare('/* ASMCMD */ select group_number, state from v$asm_diskgroup_stat where name='DATA02'')= DBI::st=HASH(0x8dbe5a8) at asmcmdshare.pm line 2572
    <- execute= '0E0' at asmcmdshare.pm line 2591
    <- fetchrow_hashref= HASH(0x8dbd848)2keys row1 at asmcmdshare.pm line 2617
    <- finish= 1 at asmcmdshare.pm line 2638
    <- DESTROY(DBI::st=HASH(8dbd9b8))= undef at asmcmdshare.pm line 1266
    <- prepare('/* ASMCMD */ select reference_index from v$asm_alias where group_number=1 and parent_index=16777216 and upper(name)='ORCL'')= DBI::st=HASH(0x8dbe588) at asmcmdshare.pm line 2572
    <- execute= '0E0' at asmcmdshare.pm line 2591
    <- fetchrow_hashref= HASH(0x8dbda18)1keys row1 at asmcmdshare.pm line 2617
    <- finish= 1 at asmcmdshare.pm line 2638
    <- DESTROY(DBI::st=HASH(86148e8))= undef at asmcmdshare.pm line 1102
    <- prepare('/* ASMCMD */ select reference_index from v$asm_alias where group_number=1 and parent_index=16777269 and upper(name)='DATAFILE'')= DBI::st=HASH(0x8ce4798) at asmcmdshare.pm line 2572
    <- execute= '0E0' at asmcmdshare.pm line 2591
    <- fetchrow_hashref= HASH(0x8dbd8e8)1keys row1 at asmcmdshare.pm line 2617
    <- finish= 1 at asmcmdshare.pm line 2638
    <- DESTROY(DBI::st=HASH(8db7a40))= undef at asmcmdshare.pm line 1102
    <- prepare('/* ASMCMD */ select reference_index from v$asm_alias where group_number=1 and parent_index=16777322 and upper(name)='TEST.DBF'')= DBI::st=HASH(0x8dbd8c8) at asmcmdshare.pm line 2572
    <- execute= '0E0' at asmcmdshare.pm line 2591
    <- fetchrow_hashref= undef at asmcmdshare.pm line 2617
    <- finish= 1 at asmcmdshare.pm line 2638
    <- DESTROY(DBI::st=HASH(8dbd818))= undef at asmcmdshare.pm line 1102
    <- prepare('
        begin
          dbms_diskgroup.getfileattr(:fileName, :fileType, :fileSz, :blkSz);
        end;
        ')= DBI::st=HASH(0x8dbd898) at asmcmdbase.pm line 3234
    <- bind_param(':fileName', '+DATA/orcl/datafile/test.267.752424591')= 1 at asmcmdbase.pm line 3241
    <- bind_param_inout(':fileType', SCALAR(0x89c35c0), ...)= 1 at asmcmdbase.pm line 3244
    <- bind_param_inout(':fileSz', SCALAR(0x89c35e0), ...)= 1 at asmcmdbase.pm line 3245
    <- bind_param_inout(':blkSz', SCALAR(0x89c3600), ...)= 1 at asmcmdbase.pm line 3246
    <- execute= '1' at asmcmdbase.pm line 3249
    <- prepare('
        begin
        dbms_diskgroup.copy('', '', '', :src_path, :src_ftyp, :src_blksz, 
                            :src_fsiz, '','','', :dst_path, 1);
        end;
        ')= DBI::st=HASH(0x8614908) at asmcmdbase.pm line 3347
    <- DESTROY(DBI::st=HASH(8607ea0))= undef at asmcmdbase.pm line 3354
    <- bind_param(':src_path', '+DATA/orcl/datafile/test.267.752424591')= 1 at asmcmdbase.pm line 3354
    <- bind_param(':src_ftyp', 12)= 1 at asmcmdbase.pm line 3355
    <- bind_param(':src_blksz', 8192)= 1 at asmcmdbase.pm line 3356
    <- bind_param(':src_fsiz', '1280')= 1 at asmcmdbase.pm line 3357
    <- bind_param(':dst_path', '+DATA02/ORCL/DATAFILE/test.dbf')= 1 at asmcmdbase.pm line 3358
copying +DATA/orcl/datafile/test.267.752424591 -> +DATA02/ORCL/DATAFILE/test.dbf
    <- execute= '1' at asmcmdbase.pm line 3361
     ...................
     ...................

Once the file is copied, rename the datafile

SQL> alter database rename file '+DATA/orcl/datafile/test.267.752424591' to '+DATA02/ORCL/DATAFILE/test.dbf';

Database altered.

The database alert log shows

Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_m000_6305.trc:
ORA-01135: file 6 accessed for DML/query is offline
ORA-01110: data file 6: '+DATA/orcl/datafile/test.267.752424591'
Sun May 29 15:45:54 2011
alter database rename file '+DATA/orcl/datafile/test.267.752424591' to '+DATA02/ORCL/DATAFILE/test.dbf'
Deleted Oracle managed file +DATA/orcl/datafile/test.267.752424591
Completed: alter database rename file '+DATA/orcl/datafile/test.267.752424591' to '+DATA02/ORCL/DATAFILE/test.dbf'

The previous datafile in +DATA diskgroup gets deleted automatically.Recover the datafile and bring it ONLINE.

SQL> alter database recover datafile 6;

Database altered.

SQL> alter database datafile 6 online;

Database altered.

SQL> select file#,name,(bytes/1024/1024)MB from v$datafile;

     FILE# NAME                                                       MB
---------- -------------------------------------------------- ----------
         1 +DATA/orcl/datafile/system.256.749749661                  680
         2 +DATA/orcl/datafile/sysaux.257.749749667                  550
         3 +DATA/orcl/datafile/undotbs1.258.749749667                 60
         4 +DATA/orcl/datafile/users.259.749749667                     5
         5 +DATA/orcl/datafile/example.265.749749897                 100
         6 +DATA02/orcl/datafile/test.dbf                             10

6 rows selected.

From ASM

[oracle@anand-lab ~]$ asmcmd lsof
DB_Name  Instance_Name  Path                                            
ORCL     ORCL           +data/orcl/controlfile/current.260.749749843    
ORCL     ORCL           +data/orcl/datafile/example.265.749749897       
ORCL     ORCL           +data/orcl/datafile/sysaux.257.749749667        
ORCL     ORCL           +data/orcl/datafile/system.256.749749661        
ORCL     ORCL           +data/orcl/datafile/undotbs1.258.749749667      
ORCL     ORCL           +data/orcl/datafile/users.259.749749667         
ORCL     ORCL           +data/orcl/onlinelog/group_3.263.749749861      
ORCL     ORCL           +data/orcl/tempfile/temp.264.749749887          
ORCL     ORCL           +data02/orcl/controlfile/current.257.752418055  
ORCL     ORCL           +data02/orcl/datafile/test.dbf                  
[oracle@anand-lab ~]$ 

One thought on “Moving Datafile using ASMCMD “cp” command – 11gR2

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