This discussion is archived
4 Replies Latest reply: Dec 24, 2012 10:17 AM by user6445925 RSS

impdp ORA-31693 and ORA-01917 issues

user6445925 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    >
    ...
    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 Newbie
    Currently Being Moderated
    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.

Legend

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