Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2009
    Posts
    14

    Unanswered: Data pump utility in oracle

    Hello All,

    I am very new to oracle. I am using datapump utility provided by oracle to export my entire database. The command that I am using is
    expdp system/system@mxsdb dumpfile=mxsdb.dmp full=y logfile=mxsdblog.log
    The command ends successfully and I am getting the my dump file in some location under ORACLE_HOME. But I want to specifiy my own location for export file like D:\oracledumps\mxsdb,.dmp...
    But dumpfile parameter doesnt allow me to supply the path variable..
    Could you please tell me how can I do this
    and while importing if i copy this file on some different location then how can i provide the complete path of dump file to be imported..
    Best Regards
    Milind

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try using the DIRECTORY parameter.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I think you can also use

    Code:
    ... dumpfile=<directory name>:mxsdb.dmp ...

  4. #4
    Join Date
    Mar 2009
    Posts
    14
    Hello All,

    I try out several way as below,
    option 1. expdp system/system@mxsdb full=y directory='D:\mxs' dumpfile=mxsdb2TS.dmp logfile=mxsdblogTS.log
    Error: - ORA-39002: invalid operation
    ORA-39070: Unable to open the log file.
    ORA-39087: directory name D:\MXS is invalid
    Note: - However D:\mxs path is available on my system. From the documentation it seems Directory paramter talks about some objects in database and not the physical location.

    option 2: - expdp system/system@mxsdb full=y dumpfile=d:\mxs:mxsdb2TS.dmp logfile=mxsdblogTS.log
    Error: - ORA-39001: invalid argument value
    ORA-39000: bad dump file specification
    ORA-39088: file name cannot contain a path specification

    option 3: - expdp system/system@mxsdb full=y dumpfile=d:mxs:mxsdb2TS.dmp logfile=mxsdblogTS.log
    Error: - ORA-39001: invalid argument value
    ORA-39000: bad dump file specification
    ORA-39087: directory name D is invalid

    Can you please post the command so that I try it out.

    Best Regards
    Milind

  5. #5
    Join Date
    Mar 2009
    Posts
    14

    Thumbs up

    Hi All,

    I found my mistakes while executing the expdp command
    mistake1: The expdp command is a server side command and I am running it on client
    mistake2: - I need to create the directory object in the database before running this command which i didnt create before

    I ran this command and it is working fine.

    Thanks a lot for all your suggestion

    best Regards
    Milind

  6. #6
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    suppose you create a folder in c:/ and name dump

    > sqlplus / as sysdba

    >CREATE or REPLACE DIRECTORY dump_dir AS 'c:\dump';

    if you export/import other than system user
    >GRANT read,write on DIRECTORY dump_dir to prod7;

    impdp system/system directory=dump_dir ......


    For details http://shaharear.blogspot.com/2008/0...data-pump.html
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  7. #7
    Join Date
    Mar 2009
    Posts
    14
    The impdp command with sqlfile parameter works fine when I have both source and target database on windows machine.
    But in my second case, the customer sends me the database that is build on Unix machine and now I want to import it to my windows machine.
    I ran impdp command using sqlfile parameter. and I receive the sqlfile that show me the source database picture including tablespaces, users, controlfiles, redologs etc...But the problem is
    1. The source database is having around 15 tablespaces with the path as /app/database/......(Unix paths)
    2. There are around 12 schemas (users) available.
    3.Control file path and all the important paths are in unix fashion.
    4. The alloted tablespace size is too high however the actual usage is too low.
    Now if i decide to chage each and every paths and tablespace sizes as per windows, then it will be too hectic...
    Note: - There are total 9 dump files that customer sends me.
    Is there any simple solution available to my problem.
    Please let me know if anything is unclear here

    best regards
    Milind

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there any simple solution available to my problem.
    Depends upon the meaning of "simple".
    One solution is to pre-create all objects in the DB prior to invoking impdp.
    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.

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    Importing the controlfile, redo logs? I'm going to go out on a limb here & say that it can't be imported ... it just doesn't make sense. impdp/exp dp is all about database objects & data, not about the structures which an Oracle db actually uses to function. The Controlfile and redo logs wouldn't be included. Are you seeing information about them in the SQLFILE? Weird.

    All you should have to do is pre-create the tablespaces...the users will get created upon impdp.

    Or, if you don't want to pre-create the tablespaces, use the remap_schema to push everything to a tablespace that already exists, like 'USERS'. This shouldn't be too hard, since you know the existing tablespace names. The only thing I haven't tried is including more than 1 REMAP_SCHEMA in a single parameter file.

    --=Chuck

  10. #10
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    you can use remap schema of impdp
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

Posting Permissions

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