This discussion is archived
7 Replies Latest reply: Jan 14, 2013 11:23 AM by L-MachineGun RSS

Datapump transortable tablespace - where are my indexes?

L-MachineGun Pro
Currently Being Moderated
Source version: 10.2.0.1 OS: AIX 5.2
Target version: 11.2.0.3 OS: AIX 7.1

I created new 11g database and want to "add" the data from old database:

1) All data/indexes contained in single tablespace.
2) Exported data as "Transportable Tablespace":
expdp / DIRECTORY=EXPORTS DUMPFILE=USERS_TS.DMP \
      TRANSPORT_TABLESPACES=users TRANSPORT_FULL_CHECK=Y    
3) Copied the files and imported (attached) to new database:
impdp / DUMPFILE=EXPORTS:USERS_TS.DMP \
    TRANSPORT_DATAFILES='/u01/oracle/orcl/datafile/users1.dbf' \
    LOGFILE=DATA_PUMP_DIR:impdp_tts.log
4) Import was successful, but...all indexes are missing!

Did I* miss something?
:?
  • 1. Re: Datapump transortable tablespace - where are my indexes?
    rp0428 Guru
    Currently Being Moderated
    You don't show a logfile for the export - did you create one? If so are there any issues reported in it?

    What about the import log file; any issues reported in it?

    Is it possible that indexes with those names already existed so the new ones could not be created?
  • 2. Re: Datapump transortable tablespace - where are my indexes?
    jgarry Guru
    Currently Being Moderated
    Are they in obj$?
  • 3. Re: Datapump transortable tablespace - where are my indexes?
    L-MachineGun Pro
    Currently Being Moderated
    +++
    No issues, it records "Processing object type TRANSPORTABLE_EXPORT/INDEX":
    ;;;
    Export: Release 10.2.0.1.0 - 64bit Production on Friday, 11 January, 2013 15:52:46
    
    Copyright (c) 2003, 2005, Oracle.  All rights reserved.
    ;;;
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    Starting "OPS$ORACLE"."SYS_EXPORT_TRANSPORTABLE_01":  /******** dumpfile=USERS_data.dmp directory=EXPORTS transport_tablespaces=USERS
    Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    Processing object type TRANSPORTABLE_EXPORT/TABLE
    Processing object type TRANSPORTABLE_EXPORT/INDEX
    Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
    Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
    Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
    Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
    Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    Master table "OPS$ORACLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for OPS$ORACLE.SYS_EXPORT_TRANSPORTABLE_01 is:
      /u01/oracle/nfsbackup2/oracle/vertex/exports/USERS_data.dmp
    Job "OPS$ORACLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:52:58
    ???
  • 4. Re: Datapump transortable tablespace - where are my indexes?
    L-MachineGun Pro
    Currently Being Moderated
    Nope.
    But I did find the issue, seems the imports skipped the indexes:
    ;;; 
    Import: Release 11.2.0.3.0 - Production on Fri Jan 11 16:52:31 2013
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    ;;; 
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "OPS$ORACLE"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
    Starting "OPS$ORACLE"."SYS_IMPORT_TRANSPORTABLE_01":  /******** DUMPFILE=USERS_data.dmp DIRECTORY=exports TRANSPORT_DATAFILES=/u01/oracle/vertex/datafile/users101.dbf LOGFILE=DATA_PUMP_DIR:impdpUSERS.log 
    Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    Processing object type TRANSPORTABLE_EXPORT/TABLE
    ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
    ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
    ORA-39151: Table "SCOTT"."BONUS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
    ORA-39151: Table "SCOTT"."SALGRADE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
    Processing object type TRANSPORTABLE_EXPORT/INDEX
    Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
    Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
    Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
    Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
    Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    Job "OPS$ORACLE"."SYS_IMPORT_TRANSPORTABLE_01" completed with 4 error(s) at 16:52:33
    Bummer...
    The purpose of this exercise is that we need to migrate the "real" database to target server and at the same time upgrade from 10g to 11g.
    We already tried installing 10.2.0.1 on the target server, but it did not work, only 10.2.0.3 worked but after cloning the database it requires a startup upgrade not allowed after the create controlfile and db cannot be open resetlogs.

    Trying to avoid an expdp/impdp of full database we are trying: a) create empty 11g database + b) transport the tablespaces.

    Any ideas/suggestions greatly appreciated.
    ;)
  • 5. Re: Datapump transortable tablespace - where are my indexes?
    P.Forstmann Guru
    Currently Being Moderated
    When transporting tablespaces objects transported in these tablespaces should not exist in target database: try to drop in target database objects that are transported.
  • 6. Re: Datapump transortable tablespace - where are my indexes?
    rp0428 Guru
    Currently Being Moderated
    >
    ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
    >
    The default action is SKIP: try using TABLE_EXISTS_ACTION=REPLACE.

    See the TABLE_EXISTS_ACTION parameter in the Data Pump doc
    http://docs.oracle.com/cd/E11882_01/server.112/e10701/dp_import.htm#sthref299
    >
    •REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
  • 7. Re: Datapump transortable tablespace - where are my indexes?
    L-MachineGun Pro
    Currently Being Moderated
    OK...
    That was it. Objects should not exist in target database.
    Wooohooo!!!
    Migrate/Upgrade to 11g -- Here we come!
    ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points