1 Reply Latest reply: Nov 27, 2012 7:24 AM by RaviPurbia RSS

    transportable tablespace using datapump

    user381384
      Hello,
      We are using transportable tablespace to recover a tablespace along with the contents as it was before dropping it.

      We are doing this as follows:

      -- At sys user:

      exec dbms_tts.transport_set_check('NEW_SCOTT_TS',TRUE,TRUE);

      select * from transport_set_violations; --Did not return any row

      alter tablespace NEW_SCOTT_TS read only;

      Then metadata is exported:
      ---------------------------------------------------------------------------------------------------------------
      [oracle10@testserver ~]$ expdp scott/scott@testdb parfile=exp_tts_par_filear

      Export: Release 10.2.0.1.0 - Production on Tuesday, 17 July, 2012 14:22:23

      Copyright (c) 2003, 2005, Oracle. All rights reserved.

      Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prtion
      With the Partitioning, OLAP and Data Mining options
      Starting "SCOTT"."SYS_EXPORT_TRANSPORTABLE_01": scott/********@testdb parfexp_tts_par_file17.par
      Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
      Processing object type TRANSPORTABLE_EXPORT/TABLE
      Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
      Master table "SCOTT"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloa
      ****************************************************************************
      Dump file set for SCOTT.SYS_EXPORT_TRANSPORTABLE_01 is:
      +DATA1/new_scott_ts170712.dmp
      Job "SCOTT"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:22:38

      [oracle10@testserver ~]$
      ---------------------------------------------------------------------------------------------------------------

      After this, we are dropping the tablespace (from sys user):

      drop tablespace NEW_SCOTT_TS including contents cascade constraints;

      Then trying to import the metadata in order to bring the tablespace back:
      ---------------------------------------------------------------------------------------------------------------
      [oracle10@testserver ~]$ impdp scott/scott@testdb parfile=imp_tts_par_filear

      Import: Release 10.2.0.1.0 - Production on Tuesday, 17 July, 2012 14:31:16

      Copyright (c) 2003, 2005, Oracle. All rights reserved.

      Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prtion
      With the Partitioning, OLAP and Data Mining options
      ORA-39005: inconsistent arguments
      ORA-39208: Parameter TTS_FULL_CHECK is invalid for IMPORT jobs.

      [oracle10@testserver ~]$
      ---------------------------------------------------------------------------------------------------------------

      We are using following parameter files for our purpose;

      [oracle10@testserver ~]$ cat exp_tts_par_file17.par
      DUMPFILE=new_scott_ts170712.dmp
      DIRECTORY=DIR_TTS
      LOGFILE=DIR_LOG:NEW_SCOTT_TS_EXP.log
      TRANSPORT_FULL_CHECK=N
      TRANSPORT_TABLESPACES=NEW_SCOTT_TS
      EXCLUDE=GRANT
      EXCLUDE=CONSTRAINT

      [oracle10@testserver ~]$ cat imp_tts_par_file17.par
      DIRECTORY=DIR_TTS
      DUMPFILE=new_scott_ts170712.dmp
      LOGFILE=DIR_LOG:NEW_SCOTT_TS_IMP.log
      TRANSPORT_TABLESPACES=NEW_SCOTT_TS
      TRANSPORT_FULL_CHECK=N
      TRANSPORT_DATAFILES='/home/oracle10/new_scott_ts'
      EXCLUDE=GRANT
      EXCLUDE=CONSTRAINT
      [oracle10@testserver ~]$

      --Note: The TRANSPORT_FULL_CHECK parameter is carrying the value 'N' instead of 'Y' in both cases. Before 'N' it was 'Y' in both parameter file.

      For the impdp parameter we've changed the parameters(NETWORK_LINK and omitted DUMPFILE)
      in the parameter to check if the problem get resolved but it was of not fruitful.

      Please advice how can it be possible to do using data pump.

      regards

      Edited by: user5813325 on Jul 17, 2012 3:07 AM