4 Replies Latest reply: Dec 24, 2012 12:17 PM by user6445925 RSS

    impdp ORA-31693 and ORA-01917 issues

    user6445925
      So if I am doing an expdp with full=y and then doing a impdp with full=y into a new database with all the tablespaces created it should recreate all my non-sys schema owners and objects right?

      So why do I see these errors (both expdp/impdp run as system).

      ORA-31684: Object type ROLE:"ORDADMIN" already exists
      Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
      Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
      ORA-39083: Object type SYSTEM_GRANT failed to create with error:
      ORA-01917: user or role 'EL_DBO' does not exist
      Failing sql is:
      GRANT CREATE VIEW TO "EL_DBO"
      ORA-39083: Object type SYSTEM_GRANT failed to create with error:
      ORA-01917: user or role 'EL_DBO' does not exist
      Failing sql is:
      GRANT CREATE PUBLIC SYNONYM TO "EL_DBO"
      ORA-39083: Object type SYSTEM_GRANT failed to create with error:
      ORA-01917: user or role 'EL_DBO' does not exist
      GRANT UNLIMITED TABLESPACE TO "EL_OWNER"
      Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
      ORA-39083: Object type ROLE_GRANT failed to create with error:
      ORA-01917: user or role 'EL_DBO' does not exist

      Plus a lot of my tables didn't import.
      ORA-31693: Table data object "UIMSMGR"."UABOPEN" failed to load/unload and is being skipped due to error:
      ORA-01403: no data found
      ORA-01403: no data found
      -- look for rows...
      . . imported "CWASMGR"."UWARCCRD" 0 KB 0 rows
      . . imported "CWASMGR"."UWATCCRD" 0 KB 0 rows
      . . imported "CWASMGR"."UWCBUSER" 0 KB 0 rows
      . . imported "CWASMGR"."UWCRACCM" 0 KB 0 rows
      . . imported "CWASMGR"."UWCRCCCM" 0 KB 0 rows

      If I check my export log file it does show data being exported.
      Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY
      . . exported "UIMSMGR"."UABOPEN" 6.932 GB 35779259 rows


      Basically just using dumpfile=xxx logfile=xxx directory=xxx and full=y for both expdp and impdp.
      So am I missing some extra paramters?
        • 1. Re: impdp ORA-31693 and ORA-01917 issues
          user9944600
          Please post the first 25 lines from the logfile=xxx.... it could help to diagnose.
          • 2. Re: impdp ORA-31693 and ORA-01917 issues
            user6445925
            See if this helps:
            ;;;
            Import: Release 11.2.0.3.0 - Production on Fri Dec 21 16:21:46 2012

            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
            Master table "SYSTEM"."CISP2_IMPORT" successfully loaded/unloaded
            Starting "SYSTEM"."CISP2_IMPORT": system/******** dumpfile=dpump_exp1:cisd_export.dmp logfile=dpump_exp1:cisp2_import job_name=cisp2_import full=y
            Processing object type DATABASE_EXPORT/TABLESPACE
            ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
            ORA-31684: Object type TABLESPACE:"TEMP" already exists
            ORA-31684: Object type TABLESPACE:"BGEN_DATA" already exists
            ORA-31684: Object type TABLESPACE:"BGEN_INDEX" already exists
            ORA-31684: Object type TABLESPACE:"BGEN_TEMP" already exists
            ORA-31684: Object type TABLESPACE:"BYLAW_DATA" already exists
            ORA-31684: Object type TABLESPACE:"BYLAW_INDEX" already exists
            ORA-31684: Object type TABLESPACE:"CIS" already exists
            ORA-31684: Object type TABLESPACE:"CIS_LRG_01" already exists
            ORA-31684: Object type TABLESPACE:"CIS_LRG_02" already exists
            ORA-31684: Object type TABLESPACE:"CIS_LRG_03" already exists
            ORA-31684: Object type TABLESPACE:"CIS_LRG_IDX_01" already exists
            ORA-31684: Object type TABLESPACE:"CIS_LRG_IDX_02" already exists
            ORA-31684: Object type TABLESPACE:"CIS_LRG_IDX_03" already exists
            ORA-31684: Object type TABLESPACE:"CIS_MED" already exists
            ORA-31684: Object type TABLESPACE:"CIS_MED_IDX" already exists
            ORA-31684: Object type TABLESPACE:"CIS_SM" already exists
            ORA-31684: Object type TABLESPACE:"CIS_SM_IDX" already exists
            ORA-31684: Object type TABLESPACE:"DEVELOPMENT" already exists
            ORA-31684: Object type TABLESPACE:"GSL_DATA" already exists
            ORA-31684: Object type TABLESPACE:"IS_USER" already exists
            ORA-31684: Object type TABLESPACE:"TAR_DATA" already exists
            ORA-31684: Object type TABLESPACE:"TOOLS" already exists
            ORA-31684: Object type TABLESPACE:"USERS" already exists
            ORA-31684: Object type TABLESPACE:"PERFSTAT" already exists
            Processing object type DATABASE_EXPORT/PROFILE
            Processing object type DATABASE_EXPORT/SYS_USER/USER
            Processing object type DATABASE_EXPORT/SCHEMA/USER
            ORA-31684: Object type USER:"OUTLN" already exists
            ORA-31684: Object type USER:"SYSMAN" already exists
            ORA-31684: Object type USER:"MGMT_VIEW" already exists
            ORA-39083: Object type USER failed to create with error:
            ORA-10615: Invalid tablespace type for temporary tablespace
            Failing sql is:
            CREATE USER "EL_DBO" IDENTIFIED BY VALUES 'S:4F057C5EC07B1C3C81519C4035F527BBCBBF5B6E3AF2655ED35F4D2D4D55;DB483783171DAC0C' DEFAULT TABLESPACE "BGEN_DATA" TEMPORARY TABLESPACE "BGEN_TEMP" PROFILE "APPL_ADMIN_USER"
            ORA-39083: Object type USER failed to create with error:
            ORA-10615: Invalid tablespace type for temporary tablespace
            Failing sql is:
            CREATE USER "EL_OWNER" IDENTIFIED BY VALUES 'S:3DE2E6A402CEC6D9BEB6F660F84B5BD342A7CA8B1498CA99766C8F0F2629;34984013404B8BF0' DEFAULT TABLESPACE "BGEN_DATA" TEMPORARY TABLESPACE "BGEN_TEMP" PROFILE "APPL_ADMIN_USER"
            ORA-39083: Object type USER failed to create with error:
            ORA-10615: Invalid tablespace type for temporary tablespace
            Failing sql is:
            • 3. Re: impdp ORA-31693 and ORA-01917 issues
              Srini Chavali-Oracle
              >
              ...
              ORA-31684: Object type TABLESPACE:"BGEN_TEMP" already exists
              ...
              ... TEMPORARY TABLESPACE "BGEN_TEMP" PROFILE "APPL_ADMIN_USER"
              ORA-39083: Object type USER failed to create with error:
              ORA-10615: Invalid tablespace type for temporary tablespace
              ...
              >

              Looks like BGEN_TEMP is not a tablespace of type temporary. Pl post output of
              SQL> select CONTENTS from DBA_TABLESPACES where tablespace_name='BGEN_TEMP';
              HTH
              Srini
              • 4. Re: impdp ORA-31693 and ORA-01917 issues
                user6445925
                Yes it is permanent and I didn't really take notice of that initial info until I posted that in response to the earlier suggestion.

                SQL> select CONTENTS from DBA_TABLESPACES where tablespace_name='BGEN_TEMP';

                CONTENTS
                ---------
                PERMANENT

                So I'll re-create it as a temporary tablespace.
                Maybe too early to tell but would that suggest all my errors are because of that issue?
                I guess I start the process of dropping and recreating that database and define the tablespace as TEMPORARY.