asmcmd cp command on the test with transportable tablespace

asmcmd cp command on the test with transportable tablespace

Category: Database — ittichai @ 7:37 am

Tweet

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.

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22

$ 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

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17

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