Results 1 to 10 of 10

Thread: Expdp/impdp

  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Unanswered:

    Dear All,

    I am new to Database administration i want to know whether it is possible to use export dump from Oracle 11.1.0.6 WIN 2003 SP2 Server to Oracle 11.1.0.6 Windows 7 standalone Machine (Test machine).If yes how to do it please let me know as i have tried but it is returning errors.So i tried to do it by pre-creating Tablespaces but even then its failing.

    Note : I am Using EXPDP and IMPDP

    Thanks in Advance
    MBY

    Dear All,

    I am new to Database administration i want to know whether it is possible to use export dump from Oracle 11.1.0.6 WIN 2003 SP2 Server to Oracle 11.1.0.6 Windows 7 standalone Machine (Test machine).If yes how to do it please let me know as i have tried but it is returning errors.So i tried to do it by pre-creating Tablespaces but even then its failing.

    Note : I am Using EXPDP and IMPDP

    Thanks in Advance
    MBY

    Dear All,

    I am new to Database administration i want to know whether it is possible to use export dump from Oracle 11.1.0.6 WIN 2003 SP2 Server to Oracle 11.1.0.6 Windows 7 standalone Machine (Test machine).If yes how to do it please let me know as i have tried but it is returning errors.So i tried to do it by pre-creating Tablespaces but even then its failing.

    Note : I am Using EXPDP and IMPDP

    Thanks in Advance
    MBY
    Last edited by healdem; 09-05-12 at 18:35.

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Why did you not post in the Oracle part of the forum?

    Maybe some kind moderator will relocate this . . .

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You are doing something, but we don't know what since you decided not to show.
    You are getting errors, but we don't know which since you decided not to show us.

    Yes, it is possible to expdp from one DB & impdp into different DB.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Sep 2012
    Posts
    6
    Hi,
    Thanks for the reply, find as below the command and output of expdp command.

    Note:I am having windows 2003 R2 with Oracle 11.1.0.6 on Source machine and Windows 7 with Oracle 11.1.0.6 on target machine.

    >expdp system/******** directory=my_dumpdir dumpfile=exp_dump.dmp logfile=exp_dump.log full=y;

    Output

    Master table "SYSTEM"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
    ************************************************** ****************************
    Dump file set for SYSTEM.SYS_EXPORT_FULL_02 is:
    E:\EXP\BACKUP\EXP_DUMP.DMP
    Job "SYSTEM"."SYS_EXPORT_FULL_02" successfully completed at 14:20:07


    >impdp system/******** DIRECTORY=my_dump_dir FULL=Y DUMPFILE='EXP_DUMP.DMP' logfile=imp_full.log



    ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
    ORA-31684: Object type TABLESPACE:"TEMP" already exists
    ORA-31684: Object type TABLESPACE:"USERS" already exists
    ORA-31684: Object type TABLESPACE:"EXAMPLE" already exists
    ORA-31684: Object type TABLESPACE:"MPP_DATA" already exists
    ORA-31684: Object type TABLESPACE:"MPP_INDX" already exists
    ORA-31684: Object type TABLESPACE:"ADMINU_DATA" already exists
    ORA-31684: Object type TABLESPACE:"ADMINU_INDX" already exists
    ORA-31684: Object type TABLESPACE:"PM_DATA" already exists
    ORA-31684: Object type TABLESPACE:"LIB_DATA" already exists
    ORA-31684: Object type TABLESPACE:"HR_DATA" already exists
    ORA-31684: Object type TABLESPACE:"ODM" already exists
    ORA-39083: Object type TABLESPACE failed to create with error:
    ORA-02236: invalid file name
    Failing sql is:
    CREATE TEMPORARY TABLESPACE "ADMINU_TEMP" TEMPFILE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
    ORA-39083: Object type TABLESPACE failed to create with error:
    ORA-02236: invalid file name
    Failing sql is:
    CREATE TEMPORARY TABLESPACE "LIB_TEMP" TEMPFILE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
    ORA-39083: Object type TABLESPACE failed to create with error:
    ORA-02236: invalid file name
    ORA-31684: Object type TABLESPACE:"DISCO_DATA" already exists
    ORA-31684: Object type TABLESPACE:"TEMP_NEW" already exists
    ORA-31684: Object type TABLESPACE:"OLD_MPP" already exists
    ORA-31684: Object type TABLESPACE:"OLD_MPP_IDX" already exists
    ORA-31684: Object type TABLESPACE:"UNDOTSB1" already exists
    ORA-31684: Object type TABLESPACE:"UNDOTBS3" already exists
    ORA-31684: Object type TABLESPACE:"UBS01" already exists
    Processing object type DATABASE_EXPORT/PROFILE
    ORA-31684: Object type PROFILE:"WKSYS_PROF" already exists
    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:"TSMSYS" already exists
    ORA-31684: Object type USER:"OLAPSYS" already exists
    ORA-31684: Object type USER:"MDDATA" already exists
    ORA-31684: Object type USER:"SPATIAL_WFS_ADMIN_USR" already exists
    ORA-31684: Object type USER:"SPATIAL_CSW_ADMIN_USR" already exists
    ORA-31684: Object type USER:"WKSYS" already exists
    ORA-31684: Object type USER:"WKPROXY" already exists
    ORA-31684: Object type USER:"SYSMAN" already exists
    ORA-31684: Object type USER:"MGMT_VIEW" already exists
    ORA-31684: Object type USER:"FLOWS_FILES" already exists
    ORA-31684: Object type USER:"APEX_PUBLIC_USER" already exists
    ORA-31684: Object type USER:"FLOWS_030000" already exists
    ORA-31684: Object type USER:"OWBSYS" already exists
    ORA-31684: Object type USER:"WK_TEST" already exists
    Processing object type DATABASE_EXPORT/ROLE
    ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
    ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
    ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
    ORA-31684: Object type ROLE:"LOGSTDBY_ADMINISTRATOR" already exists
    ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
    ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
    ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
    ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
    ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
    ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
    ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
    ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
    ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
    ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
    ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
    ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
    ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
    ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
    ORA-31684: Object type ROLE:"EJBCLIENT" already exists
    ORA-31684: Object type ROLE:"JMXSERVER" already exists
    ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
    ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
    ORA-31684: Object type ROLE:"CTXAPP" already exists
    ORA-31684: Object type ROLE:"XDBADMIN" already exists
    ORA-31684: Object type ROLE:"XDB_SET_INVOKER" already exists
    ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
    ORA-31684: Object type ROLE:"XDB_WEBSERVICES" already exists
    ORA-31684: Object type ROLE:"XDB_WEBSERVICES_WITH_PUBLIC" already exists
    ORA-31684: Object type ROLE:"XDB_WEBSERVICES_OVER_HTTP" already exists
    ORA-31684: Object type ROLE:"ORDADMIN" already exists
    ORA-31684: Object type ROLE:"OLAPI_TRACE_USER" already exists
    ORA-31684: Object type ROLE:"OLAP_XS_ADMIN" already exists
    ORA-31684: Object type ROLE:"OLAP_DBA" already exists
    ORA-31684: Object type ROLE:"CWM_USER" already exists
    ORA-31684: Object type ROLE:"OLAP_USER" already exists
    ORA-31684: Object type ROLE:"SPATIAL_WFS_ADMIN" already exists
    ORA-31684: Object type ROLE:"WFS_USR_ROLE" already exists
    ORA-31684: Object type ROLE:"SPATIAL_CSW_ADMIN" already exists
    ORA-31684: Object type ROLE:"CSW_USR_ROLE" already exists
    ORA-31684: Object type ROLE:"WKUSER" already exists
    ORA-31684: Object type ROLE:"MGMT_USER" already exists
    ORA-31684: Object type ROLE:"OWB$CLIENT" already exists
    ORA-31684: Object type ROLE:"OWB_DESIGNCENTER_VIEW" already exists
    ORA-31684: Object type ROLE:"OWB_USER" already exists
    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/PROXY
    Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
    Processing object type DATABASE_EXPORT/RESOURCE_COST
    Processing object type DATABASE_EXPORT/ROLLBACK_SEGMENT
    Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
    Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
    Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
    ORA-31684: Object type SEQUENCE:"SYSTEM"."MVIEW$_ADVSEQ_GENERIC" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."MVIEW$_ADVSEQ_ID" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_FLAVORS_S" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_FLAVOR_NAME_S" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_REPPROP_KEY" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT_LOG_SEQUENCE" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_REFRESH_TEMPLATES_S" already exists
    ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_USER_AUTHORIZATIONS_S" already exists
    ORA-31684: Object type TRIGGER:"SYSMAN"."MGMT_STARTUP" already exists
    Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
    ORA-31684: Object type MATERIALIZED_VIEW:"SYSMAN"."MGMT_ECM_MD_ALL_TBL_CO LUMNS" already exists
    Processing object type DATABASE_EXPORT/SCHEMA/JOB
    ORA-39083: Object type JOB failed to create with error:
    ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
    Failing sql is:
    BEGIN DBMS_JOB.ISUBMIT( JOB=> 4001, NEXT_DATE=> TO_DATE('2012-09-02 14:49:56', 'YYYY-MM-DD:HH24:MIS'), INTERVAL=> 'sysdate + 8/24', WHAT=> 'wwv_flow_cache.purge_sessions(p_purge_sess_older_ then_hrs => 24);', NO_PARSE=> TRUE); END;
    ORA-39083: Object type JOB failed to create with error:
    ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
    Failing sql is:
    BEGIN DBMS_JOB.ISUBMIT( JOB=> 4002, NEXT_DATE=> TO_DATE('4000-01-01 00:00:00', 'YYYY-MM-DD:HH24:MIS'), INTERVAL=> 'sysdate + 10/1440', WHAT=> 'wwv_flow_mail.push_queue(wwv_flow_platform.get_pr eference(''SMTP_HOST_ADDRESS''),wwv_flow_platform. get_preference(''SMTP_HOST_PORT''));', NO_PARSE=> TRUE); END;
    ORA-39083: Object type JOB failed to create with error:
    ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
    Failing sql is:
    BEGIN DBMS_JOB.ISUBMIT( JOB=> 23, NEXT_DATE=> TO_DATE('2012-09-02 15:00:00', 'YYYY-MM-DD:HH24:MIS'), INTERVAL=> 'TRUNC(SYSDATE+1)+15/24', WHAT=> 'Not_Rev;
    Pay_Time;
    RET_Pay_Time;
    STP_TEAMS;
    ', NO_PARSE=> TRUE); END;
    Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
    Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
    Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
    Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY
    Processing object type DATABASE_EXPORT/AUDIT
    Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 8017 error(s) at 13:43:50

    Thanks and regards,
    MBY

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    the errors are to be expected & not necessarily fatal.

    so is data from the source DB now in the target DB?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2012
    Posts
    6
    Hi,

    Please let me know whether i can first import the Tablespaces to the new system and then i can use Full=Y, as i have tried to do this and got the errors as updated in the previous post.One more thing i want to know whether i can import the default permanent tablespaces like system, sysaux,undo and users.

    I have been trying to complete this activity IMPDP/EXPDP with no errors what so ever but till now i have not been able to do so.

    Please help me out with sequence of steps that i need to follow and scripts if any to avoid errors.

    Sorce DB racle 11.1.0.6 on Win 2003 Server

    Target DB: oracle 11.1.0.6 on Win 7 machine.

    Thanks and Regards,
    MBY

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do the tablespaces from the source DB exist as the exact same fully qualified OS pathnames of the target DB?

    FWIW - SYS objects are not exported; since a fully functional DB must exist before import can occur.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Sep 2012
    Posts
    6
    Hi,

    Thanks for the reply, yes i have created a fresh DB with same name and tried to connect it with sqlplus and found it to be working fine.Then i have pre-created the Tablespaces as in the source DB with script generated from the source db.Now when i am trying to run the import command with on the new terget DB with newly created Tablespaces i am getting lots of errors.

    Error as below:

    ORA-39111: Dependent object type TRIGGER:"SYSMAN"."MEMBERSHIPS_INSERT_TRIGG
    kipped, base object type VIEW:"SYSMAN"."MGMT_TARGET_MEMBERSHIPS" already ex
    ORA-39111: Dependent object type TRIGGER:"SYSMAN"."EM_ADAPTIVE_THRESHOLDS_I
    kipped, base object type VIEW:"SYSMAN"."EM_ADAPTIVE_THRESHOLDS" already exi
    ORA-39111: Dependent object type TRIGGER:"SYSMAN"."SPACE_METRICS_PURGE_TRIG
    skipped, base object type VIEW:"SYSMAN"."MGMT_SPACE_PURGE" already exists
    Processing object type DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER
    ORA-31684: Object type TRIGGER:"SYSMAN"."MGMT_STARTUP" already exists
    Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
    ORA-31684: Object type MATERIALIZED_VIEW:"SYSMAN"."MGMT_ECM_MD_ALL_TBL_CO LU
    already exists
    Processing object type DATABASE_EXPORT/SCHEMA/JOB
    ORA-39083: Object type JOB failed to create with error:
    ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
    Failing sql is:
    BEGIN DBMS_JOB.ISUBMIT( JOB=> 27, NEXT_DATE=> TO_DATE('2012-09-15 01:00:00
    YYY-MM-DD:HH24:MIS'), INTERVAL=> 'wk_job.next_time(''DY0701'')', WHAT=> '
    .wk_job.invoke(22,25);', NO_PARSE=> TRUE); END;
    ORA-39083: Object type JOB failed to create with error:
    ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
    Failing sql is:
    BEGIN DBMS_JOB.ISUBMIT( JOB=> 4001, NEXT_DATE=> TO_DATE('2012-09-09 14:50:
    'YYYY-MM-DD:HH24:MIS'), INTERVAL=> 'sysdate + 8/24', WHAT=> 'wwv_flow_cac
    rge_sessions(p_purge_sess_older_then_hrs => 24);', NO_PARSE=> TRUE); END;

    ORA-39083: Object type JOB failed to create with error:
    ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
    Failing sql is:
    BEGIN DBMS_JOB.ISUBMIT( JOB=> 4002, NEXT_DATE=> TO_DATE('4000-01-01 00:00:
    'YYYY-MM-DD:HH24:MIS'), INTERVAL=> 'sysdate + 10/1440', WHAT=> 'wwv_flow_
    push_queue(wwv_flow_platform.get_preference(''SMTP _HOST_ADDRESS''),wwv_flow
    form.get_preference(''SMTP_HOST_PORT''));', NO_PARSE=> TRUE); END;

    ORA-39083: Object type JOB failed to create with error:
    ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
    Failing sql is:
    BEGIN DBMS_JOB.ISUBMIT( JOB=> 29, NEXT_DATE=> TO_DATE('4000-01-01 00:00:00
    YYY-MM-DD:HH24:MIS'), INTERVAL=> 'sysdate + 1 / (24 * 60)', WHAT=> 'EMD_M
    NANCE.EXECUTE_EM_DBMS_JOB_PROCS();', NO_PARSE=> TRUE); END;

    Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_I
    CE
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOB
    Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
    Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
    Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY
    Processing object type DATABASE_EXPORT/AUDIT
    Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 8098 error(s) at 10:44:46

    After completion of the Import i have tried to check for the tables that i have created on source DB before running the expdp command.I did not find any tables with the name as i created previously.

    Thanks and Regards,
    MBY

  9. #9
    Join Date
    Sep 2012
    Posts
    6
    Hi,

    After completing the Import i have tried to check for the tables that i created in Source DB before running the ExpDp command and found that it is missing in newly created DB after running ImpDp command.

    Thanks and Regards,
    MBY

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You claim a problem exists, but have not posted any proof that any problem exists.
    Regardless one of two realities exist.
    1) you are correct & therefore need to submit a Bug Report to Oracle
    2) you are mistaken & have not accurately reported reality.

    in either case, we can not do anything to change your situation; only you can do that.
    post any errors from impdp that involve the "missing" tables.
    Last edited by anacedent; 09-10-12 at 11:43. Reason: fix typo
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •