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.
$ 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
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
$ 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
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.
Tags: ASM, asmcmd, copy, cp, dbms_tts, expdp, tablespace, Transportable, transport_set_check, transport_set_violations, TTS
