asmcmd cp command on the test with transportable tablespace

As you all know, the transportable tablepsace (TTS) enables users to unplug a set of tablespaces from one database, and plug it into another database. It is very efficient to move bulk data because only metadata and data files are involved in file movement – this can be much faster than export/import. More detailed information can be found in Oracle document.

We’ve been using TTS in 10g for many scenarios especially in the database refresh. However, we’ve never used it in database with ASM. For pre-11g with ASM, RMAN or DBMS_FILE_TRANSFER has to be used to move data out of ASM into file system or vice versa. With new feature of asmcmd cp command in 11g, I’d like explore whether or not this could be successfully used to move data files in/out ASM on our 11.1.0.7 database for the TTS export/import purpose.

Notes below are straight-forward step-by-step for TTS configuration. However, when copying data files, I use asmcmd cp instead. In the initial run, I’ve encountered ORA-01200 error during import saying that the actual copied file size is smaller than the correct size. It took me awhile to discover that when copying a data file larger than 2GB, the file was actually shrunk! This was filed as a bug by Oracle support. I will talk more about this at the end of this post. So all samples below are done with files smaller than 2GB. I’ve tested with 100 MB and 1.1 GB, both cases are working fine.

Assumption

The test_user has tables in the TTSASMCP_TS tablespace. One of them is testtb.

SQL> conn testuser
SQL> select count(*) from testtb;
COUNT(*)
----------
1000

Prerequisite

1. Make sure that this tablespace is self-contained for transportable.

SQL> exec dbms_tts.transport_set_check(TS_LIST=>'TTSASMCP_TS', incl_constraints=>TRUE);
PL/SQL procedure successfully completed.

If there are multiple transportable tablespaces, they can be listed with comma-separated in TS_LIST.

2. View whether or not there is any violation.

SQL> select * from transport_set_violations;
no rows selected

Start Transportable Tablespace Export using expdp

1. Make tablespace read-only.

SQL> alter tablespace TTSASMCP_TS read only;
Tablespace altered.

2. Locate data files for this tablespace.

col tablespace_name format a10
col file_name format a50

SQL> select tablespace_name, file_name from dba_data_files
where tablespace_name = 'TTSASMCP_TS';

TABLESPACE   FILE_NAME
----------   --------------------------------------------------
TTSASMCP_TS  +PROD_DG1/ORA11PD/datafile/ttsasmcp_ts.314.680796267

3. Create a directory to be used for export data pump.

SQL> create directory expdir as '/opt/oracle/admin/ORA11PD/exp';
Directory created.

SQL> grant read, write on directory expdir to system;
Grant succeeded

4. Perform export data pump.

[source language=”sql”]$ expdp system directory=expdir dumpfile=ttsasmcp.dmp transport_tablespaces=TTSASMCP_TS include=triggers,constraints,grant

Export: Release 11.1.0.7.0 – 64bit Production on Friday, 06 March, 2009 9:51:12

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=expdir dumpfile=ttsasmcp.dmp
transport_tablespaces=TTSASMCP_TS
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/opt/oracle/admin/ORA11PD/exp/ttsasmcp.dmp
******************************************************************************
Datafiles required for transportable tablespace TTSASMCP_TS:
+PROD_DG1/ORA11PD/datafile/ttsasmcp_ts.314.680796267
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:10:08

[/source]

5. Once completed, move the export’s metadata file and data file over to target database.

ASMCMD> cp +PROD_DG1/ora11pd/datafile/TTSASMCP_TS.314.680796267 /tmp
copying +PROD_DG1/ora11pd/datafile/TTSASMCP_TS.314.680796267 -> /tmp/TTSASMCP_TS.314.680796267

$ scp /tmp/TTSASMCP_TS.314.680796267 serverB:/tmp
$ scp /opt/oracle/admin/ora11pd/exp/ttsasmcp.dmp serverB:/opt/oracle/admin/ora11dv/exp/

6. Put the original tablespace back to read write mode.

SQL> alter tablespace TTSASMCP_TS read write;
Tablespace altered.

In this case, since both of our systems are on the same platforms, there is no need for platform conversion. If you’re interested in, information about cross-platform transportable tablespace can be found here and here.

Start Transportable Tablespace Import

1. Create a needed directory object for import data pump.

SQL> create directory expdir as '/opt/oracle/admin/ora11dv/exp';
Directory created.

SQL> grant read, write on directory expdir to system;
Grant succeeded

2. Move the data file to the data file location under ASM.

ASMCMD> cp /tmp/TTSASMCP_TS.314.680796267 +DEV_DG1/ora11dv/datafile/TTSASMCP_TS
copying /tmp/TTSASMCP_TS.314.680796267 -> +DEV_DG1/ORA11DV/datafile/TTSASMCP_TS

However, since ASM will automatically append file/incarnation pair to ensure uniqueness, you will need to list to get a new data file name.

ASMCMD> ls -s +DEV_DG1/ora11dev/datafile/TT*
Block_Size  Blocks      Bytes      Space  Name
16384    6401  104873984  106954752  TTSASMCP_TS.271.680796929

Please note that the following syntaxes won’t work. This is very inconvenient because it is common way of typing cp syntax in Unix. Hopefully it will be fixed in the later release.

cp /tmp/TTSASMCP_TS.314.680796267 +DEV_DG1/ora11dv/datafile/

or

cp /tmp/TTSASMCP_TS.314.680796267 +DEV_DG1/ora11dv/datafile/TTSASMCP_TS.314.680796267

You will receive this error

ORA-15046: ASM file name ‘+DEV_DG1/ora11dv/datafile/TTSASMCP_TS.314.680796267′ is not in single-file creation form.

3. Rum impdp

[source language=”sql”]$ impdp system directory=expdir dumpfile=ttsasmcp.dmp transport_datafiles=’+DEV_DG1/ora11dv/datafile/TTSASMCP_TS.271.680796929’

Import: Release 11.1.0.7.0 – 64bit Production on Friday, 06 March, 2009 14:25:34

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=expdir dumpfile=ttsasmcp.dmp
transport_datafiles=+DEV_DG1/ORA11DV/datafile/TTSASMCP_TS.271.680796929
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 14:25:50[/source]

Validation

SQL> conn testuser
SQL> select count(*) from testtb;
COUNT(*)
----------
1000

A bug found with asmcmd cp command

Normally when copying file, you must get the same file size 🙂 Sample here is with 104 MB file.

ASMCMD> ls -s tts*
Block_Size  Blocks      Bytes      Space  Name
16384    6401  104873984  106954752  TTSASMCP_TS.314.680796267

ASMCMD> cp TTSASMCP_TS.314.680796267 TTSASMCP_TS.backup
copying +PROD_DG1/ORA11PD/datafile/TTSASMCP_TS.314.680796267 -> +PROD_DG1/ORA11PD/datafile/TTSASMCP_TS.backup

ASMCMD> ls -s tts*
Block_Size  Blocks      Bytes      Space  Name
16384    6401  104873984  106954752  TTSASMCP_TS.314.680796267
16384    6401  104873984  106954752  TTSASMCP_TS.backup.313.680798709

Same with 1.1 GB.

ASMCMD> ls -s npi*
Block_Size  Blocks       Bytes       Space  Name
16384   70401  1153449984  1155530752  NPI_DATA.298.677341631

ASMCMD> cp NPI_DATA.298.677341631 NPI_DATA.backup
copying +PROD_DG1/ORA11PD/datafile/NPI_DATA.298.677341631 -> +PROD_DG1/ORA11PD/datafile/NPI_DATA.backup

ASMCMD>  ls -s npi*
Block_Size  Blocks       Bytes       Space  Name
16384   70401  1153449984  1155530752  NPI_DATA.298.677341631
16384   70401  1153449984  1155530752  NPI_DATA.backup.315.680916403

But for a file  larger than 2GB (21 GB in this sample), as you can see, the file size is smaller.

ASMCMD> ls -s co*
Block_Size   Blocks        Bytes        Space  Name
16384  1327249  21745647616  21747466240  CO_DATA.276.673697925

ASMCMD> cp CO_DATA.276.673697925 CO_DATA.backup
copying +PROD_DG1/ORA11PD/datafile/CO_DATA.276.673697925 -> +PROD_DG1/ORA11PD/datafile/CO_DATA.backup

ASMCMD> ls -s co*
Block_Size   Blocks        Bytes        Space  Name
16384  1327249  21745647616  21747466240  CO_DATA.276.673697925
16384    16529    270811136    272629760  CO_DATA.backup.313.680798663

I was told by Oracle support that this is a bug (#7828187) : Asmcmd CP Command Can Not Copy Files Larger Than 2 GB. Currently there is no patch available yet. The recommended work-around is to either use RMAN or dbms_file_transfer.copy_file to copy file.

Even though I still can’t use the asmcmd cp for a larger file copy until it is fixed, it is still very useful for making a backup of smaller files such as spfile.

If anyone encounters the same issue I’ve had, please  feel free to share your experience.

9 thoughts on “asmcmd cp command on the test with transportable tablespace”

  1. Praveen Jindal

    Very good article with information to the point..

    Currently I am also exploring the possibiltiy of schema refresh using TTs and in fact faced the similar issue
    although the file copied is not exactly 2 GB.

    W.R.T file copying using RMAN or DBMS_FILE_TRANSFER, observing a strange behavior whereby a file pointer
    is getting created.
    Wondering if someone has faced this issue and how to workaround the same.

    ASMCMD > ls -ltr
    Type Redund Striped Time Sys Name
    DATAFILE UNPROT COARSE JUN 05 04:00:00 Y SYSAUX.1293.688708755
    DATAFILE UNPROT COARSE JUN 05 04:00:00 Y SYSTEM.1834.688708761
    DATAFILE UNPROT COARSE JUN 05 04:00:00 Y UNDOTBS1.1291.688708755
    DATAFILE UNPROT COARSE JUN 05 04:00:00 Y USERS.3923.688708755
    DATAFILE UNPROT COARSE JUN 08 21:00:00 Y LOM_ACT_DATA_OLD.1840.689031913
    N lom_act_data_old => +ORCL_DATA/RDCPACT/DATAFILE/LOM_ACT_DATA_OLD.1840.689031913

    1. Praveen,

      I believe the file pointer you mentioned is created from its normal behavior of copying file using, for example, DBMS_FILE_TRANSFER. It is an ASM file alias created when the “destination_file_name” is specified. The alias makes it easier to refer to a system-generated filename.

      SQL> create directory TARGETDIR as ‘+DG1/dbs/backup’;
      Directory created.

      SQL> BEGIN
      2 DBMS_FILE_TRANSFER.COPY_FILE(‘SOURCEDIR’,’file1.f’, ‘TARGETDIR’, ‘file1.f’);
      3 END;
      4 /
      PL/SQL procedure successfully completed.

      Note that the “file1.f” will become an alias.

      ASMCMD> cd DG1/dbs/backup
      ASMCMD> ls
      file1.f => +DISKGROUP1/ORCL/TEMPFILE/COPY_FILE.267.546546525

      See detail at http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_ftran.htm

      1. Praveen Jindal

        The pointer concept can be misleading. I understand w.r.t practical usage, it might be
        inconvenient during the maintenance time. What basically happening is that the file is
        physically present at the source directory location only and the pointer file is getting c reated
        in the target location. This arrangement definitelynot confortable.
        I believe that asmcmd cp command would not create file pointer
        but create physical file at target location only.

        There must be some workaround to overcome this inconvenience.

        Any insight/information is welcome.

        Cheers
        Praveen

        1. Praveen,
          Ah, I believe we’re talking different thing. 🙂 The DBMS_FILE_TRANSFER actually creates a physical file with a fully qualified name on the target directory first, and then additionally create an alias (pointer) to the newly-copied target file, not to the source file. So you can access the copied file with either actual file name or alias.

          Sample here shows that the DBMS_FILE_TRANSFER.COPY_FILE actually creates a new fully-qualified-name file COPY_FILE.319.689590417, and have TOOLS.dfbk alias pointing to the COPY_FILE, not the source file (TOOLS.309.677341647).

          SYS@DEDW AS SYSDBA> BEGIN
          DBMS_FILE_TRANSFER.COPY_FILE(‘SOURCEDIR’,’TOOLS.309.677341647′, ‘TARGETDIR’, ‘TOOLS.dfbk’);
          END;
          /
          PL/SQL procedure successfully completed.

          ASMCMD> ls -al +DEDW_DG1/DEDW/DATAFILE/TOOLS*
          Type Redund Striped Time Sys Name
          DATAFILE UNPROT COARSE JUN 09 13:00:00 Y none => TOOLS.309.677341647

          ASMCMD> ls -al +DEDW_DG1/DEDW/backup/
          Type Redund Striped Time Sys Name
          N TOOLS.dfbk => +DEDW_DG1/DEDW/DATAFILE/COPY_FILE.319.689590417

          The asmcmd cp behaves a little bit differently because it does not create an alias. It just simply create a new physical with with a fully qualified name even when you specify a simple name.

          ASMCMD> cp TOOLS.309.677341647 +DEDW_DG1/backup/toolbk
          copying +DEDW_DG1/DEDW/DATAFILE/TOOLS.309.677341647 -> +DEDW_DG1/backup/toolbk

          ASMCMD> ls -al +DEDW_DG1/backup/toolbk*
          Type Redund Striped Time Sys Name
          DATAFILE UNPROT COARSE JUN 15 09:00:00 Y none => toolbk.322.689593101

          Ittichai

  2. Hello,

    I’m executing the same steps as you in 11gR2 and the tables are not exported.
    When executing expdp I don’t see the line…

    13 Processing object type TRANSPORTABLE_EXPORT/TABLE

    Any idea why???

    Thanks.

      1. Hi all,

        If I copy a datafile called +DEDW_DG1/DEDW/DATAFILE/TOOLS.309.677341647 from the source DB, then I don’t need to copy the alias too?
        What should I do if in the source DB the datafile has an alias and wanted to created the datafile with the alias in the target DB?

        thanks

        1. You can simply create an alias on target db, for example, ALTER DISKGROUP disk_group_1 ADD ALIAS ‘+disk_group_1/my_dir/my_file.dbf’
          FOR ‘+disk_group_1/mydb/datafile/my_ts.342.3’;

          1. I have a process that execute the following command to copy the datafile from DB_1 to DB_2:

            $ asmcmd cp ‘+DATA/DB_1/DATAFILE/my_file.ora’ ‘+DATA/DB_2/DATAFILE/’

            After copying the datafile and importing the tablespace, I see in DB_2 that ASM is copying the datafile in ‘+DATA/ASM/DATAFILE’ and not in ‘+DATA/DB_2/DATAFILE/’.

            ASMCMD> pwd

            +DATA/DB_2/datafile

            ASMCMD> ls -l

            my_file.ora => +DATA/ASM/DATAFILE/my_file.ora.3209.313452345

            Any idea?

            thanks

Leave a Reply to dennis Cancel Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top