7 Replies Latest reply: Feb 15, 2013 1:15 AM by vivalavida RSS

    ORA-39779 during import of types

    vivalavida
      Hi ,

      I have executed an export with the following parameters:
      DIRECTORY=DATAPUMP_DIR
      DUMPFILE=exportdp_FULL_%U.dmp
      LOGFILE=exportdp_FULL.log
      FLASHBACK_SCN=6070655498233
      FULL=yes
      CONTENT=ALL
      FILESIZE=2G
      PARALLEL=2

      The source database is of release 10.2.0.4.0 and installed on a HP-UX (HP-UX 11.23) environment.
      The target database is of release 10.2.0.5.0 and installed on Red Hat Linux (Red Hat Enterprise Linux Server release 5.8 ) environment.

      The parameterfile for the import has the following parameters:
      DUMPFILE=exportdp_FULL_%U.dmp
      LOGFILE=importdp_FULL.log
      SCHEMAS=schema_a, schema_b, schema_c, schema_d
      REMAP_TABLESPACE=DEFAULTUSER:DEFAULT_USER
      PARALLEL=4
      METRICS=yes

      During the import the following errors get raised:
      ORA-31693: Table data object "SCHEMA_A"."TEST" failed to load/unload and is being skipped due to error:
      ORA-02354: error in exporting/importing data
      ORA-39779: type "SCHEMA_A"."OT_TEST" not found or conversion to latest version is not possible

      Going through MOS and several forums none of the offered solutions (e.g. TRANSFORM=oid:n) solved this issue.

      Currently I really don't have idea how to solve this issue - it's not clear to me what causes the problem. Running the query
      SELECT o.oid$
      , t.hashcode
      , o.name
      FROM sys.obj$ o
      , sys.user$ u
      , sys.type$ t
      WHERE o.owner# = u.user#
      AND o.oid$ = t.toid
      AND o.name = 'OT_TEST';

      gives different results on source- and target-database (oid and hashcode do both differ).

      Any help will truly appreciated.

      Rgds
      Jan
        • 1. Re: ORA-39779 during import of types
          Richard Harrison .
          Hi,
          If you manually create the type and then manually create the table do you get any error?

          Cheers,
          Harry
          • 2. Re: ORA-39779 during import of types
            vivalavida
            Hi ,
            might be an idea. But since I want to import a complete schema (with all objects) it will be alot of work to create all types in advance.
            But thanks anyway - I will try your idea if it will work or not...

            Rgds
            Jan
            • 3. Re: ORA-39779 during import of types
              Srini Chavali-Oracle
              Pl post complete expdp and impdp commands used, along with the first 15 lines of the expdp and impdp logs.

              HTH
              Srini
              • 4. Re: ORA-39779 during import of types
                vivalavida
                Hi ,

                expdp command:
                expdp sys_admin parfile=exportdp_FULL.param;

                first 15 lines of exportdp_FULL.log:
                Export: Release 10.2.0.4.0 - 64bit Production on Mittwoch, 06 Februar, 2013 7:35:52

                Copyright (c) 2003, 2007, Oracle. All rights reserved.
                ;;;
                Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options
                Starting "SYS_ADMIN"."SYS_EXPORT_FULL_01": sys_admin/******** parfile=exportdp_FULL.param
                Estimate in progress using BLOCKS method...
                Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
                Total estimation using BLOCKS method: 199.2 GB
                Processing object type DATABASE_EXPORT/TABLESPACE
                Processing object type DATABASE_EXPORT/PROFILE
                Processing object type DATABASE_EXPORT/SYS_USER/USER
                Processing object type DATABASE_EXPORT/SCHEMA/USER
                Processing object type DATABASE_EXPORT/ROLE
                Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
                Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
                Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
                Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
                Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

                impdp command:
                impdp sys_admin parfile=importdp_FULL.param;

                first 15 lines of importdp_FULL.log:
                Import: Release 10.2.0.5.0 - 64bit Production on Thursday, 07 February, 2013 12:53:58

                Copyright (c) 2003, 2007, Oracle. All rights reserved.
                ;;;
                Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
                With the Partitioning, Real Application Clusters, OLAP, Data Mining
                and Real Application Testing options
                Master table "SYS_ADMIN"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
                Starting "SYS_ADMIN"."SYS_IMPORT_SCHEMA_02": sys_admin/******** parfile=importdp_FULL.param
                Processing object type DATABASE_EXPORT/SCHEMA/USER
                Failing sql is:
                CREATE USER "SCHEMA_A" IDENTIFIED BY VALUES 'A3E66F1F8E9EFE4E' DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "TEMP" PROFILE "SCHEMA_OWNER"
                ORA-31684: Object type USER:"SCHEMA_A" already exists
                ORA-31684: Object type USER:"SCHEMA_B" already exists
                ORA-39083: Object type USER failed to create with error:
                ORA-02380: profile TECHNICAL_USER does not exist

                The errors of the import are due to the fact that I have created all users before...

                Rgds
                Jan
                • 5. Re: ORA-39779 during import of types
                  Richard Harrison .
                  Hi,
                  Another thought. Is compatible set to the same version on both databases?

                  Cheers,
                  Harry
                  • 6. Re: ORA-39779 during import of types
                    Srini Chavali-Oracle
                    This looks good - I would suggest you open an SR with Support

                    HTH
                    Srini
                    • 7. Re: ORA-39779 during import of types
                      vivalavida
                      Hi,
                      No in source database it's set to 10.2.0.0.0 and in target database to 10.2.0.5.0.

                      Rgds
                      Jan