7 Replies Latest reply on Jan 14, 2013 7:23 PM by L-MachineGun

    Datapump transortable tablespace - where are my indexes?

    L-MachineGun
      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
          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?
          1 person found this helpful
          • 2. Re: Datapump transortable tablespace - where are my indexes?
            jgarry
            Are they in obj$?
            1 person found this helpful
            • 3. Re: Datapump transortable tablespace - where are my indexes?
              L-MachineGun
              +++
              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
                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
                  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
                    >
                    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.
                    1 person found this helpful
                    • 7. Re: Datapump transortable tablespace - where are my indexes?
                      L-MachineGun
                      OK...
                      That was it. Objects should not exist in target database.
                      Wooohooo!!!
                      Migrate/Upgrade to 11g -- Here we come!
                      ;)