8 Replies Latest reply: Mar 22, 2013 9:51 AM by Nicolas.Gasparotto RSS

    expdp issue

    user3266490
      Hi,

      Application team run expdp on client machine and stored in CLIENT machine.They using below format.

      schema.par
      -------------
      DUMPFILE=schema.dmp
      LOGFILE=schema.log
      USERID='cdata/cdata@(DESCRIPTION=(ADDRESS=(HOST=PLM-55)(PROTOCOL=tcp)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=M10)))'
      SCHEMAS=cdata

      schmea.sh
      -------------
      DIRECTORY=TEST parfile=schema.par

      We got a call from application team.Their expdp scirpt is not working last two days.They got below error.Before it worked fine.the dump file stored in their local server(Not DATABASE server).

      ORA-39002: invalid operation
      ORA-39070: Unable to open the log file.
      ORA-39087: directory name TEST is invalid

      Is it possilbe to store dumpfile in local server using EXPDP?

      Then we tried in East connect method.But not working

      expdp userid=@//plm-55:1521/M10 directory=TEST

      Export: Release 11.2.0.1.0 - Production on Fri Mar 22 08:11:48 2013

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, Automatic Storage Management, OLAP, Data Mining
      and Real Application Testing options
      ORA-39002: invalid operation
      ORA-39070: Unable to open the log file.
      ORA-29283: invalid file operation
      ORA-06512: at "SYS.UTL_FILE", line 536
      ORA-29283: invalid file operation

      In db server,directory is there at db level.

      Help me out.

      Thanks & Regards,
      VN

      Edited by: user3266490 on Mar 22, 2013 1:58 PM
        • 1. Re: expdp issue
          606331
          please post below details..

          SELECT directory_name, grantee, privilege
          FROM user_tab_privs t, all_directories d
          WHERE t.table_name(+)=d.directory_name
          ORDER BY 1,2,3;

          and check OS permissions..some times OS permissions will cause above errors....

          source oracle.com
          • 2. Re: expdp issue
            user3266490
            Hi,

            Thanks for your reply.

            Please check the below output.

            TEST SYSTEM READ
            TEST SYSTEM WRITE
            TEST CDATA READ
            TEST CDATA WRITE
            TEST_DIR PUBLIC READ
            TEST_DIR PUBLIC WRITE

            Is it possible to store a dumpfile in local server?

            Thanks & Regards,
            VN
            • 3. Re: expdp issue
              606331
              >

              >
              Is it possible to store a dumpfile in local server?
              yes you can....by using network_link=<database_link_name_on_local> and necessary privileges(OS for dump creation&Database)......

              http://docs.oracle.com/cd/E11882_01/server.112/e16536/dp_overview.htm#CJABHJHD
              Please check the below output.

              TEST SYSTEM READ
              TEST SYSTEM WRITE
              TEST CDATA READ
              TEST CDATA WRITE
              TEST_DIR PUBLIC READ
              TEST_DIR PUBLIC WRITE
              for dump creation Oracle database requires permissions from the operating system to read and write files in the directories. ..please check os permissions aswell...

              Edited by: dbc001 on Mar 22, 2013 3:19 PM

              Edited by: dbc001 on Mar 22, 2013 3:29 PM
              • 4. Re: expdp issue
                Nicolas.Gasparotto
                dbc001 wrote:
                >
                Is it possible to store a dumpfile in local server?
                yes you can....by using network_link=<database_link_name_on_local> and necessary privileges(OS for dump creation&Database)......
                No, it's not. NETWORK_LINK is not used to create a dumpfile locally from a remote database, it's used to a direct import into a local database without dump.

                Nicolas.
                • 5. Re: expdp issue
                  Nicolas.Gasparotto
                  user3266490 wrote:
                  ...Their expdp scirpt is not working last two days.They got below error.Before it worked fine.the dump file stored in their local server(Not DATABASE server).
                  ORA-39002: invalid operation
                  ORA-39070: Unable to open the log file.
                  ORA-39087: directory name TEST is invalid

                  Is it possilbe to store dumpfile in local server using EXPDP?
                  Unless a share point - the DIRECTORY parameter - is created between database server and client, it's not possible.
                  What'is the definition of TEST directory (select * from dba_directories where directory_name='TEST') ? And ask yourself if the directory still exists on db server, something should have changed out there if it works before these last two days.

                  Nicolas.
                  • 6. Re: expdp issue
                    ragu.dba.in
                    is it possilbe to store dumpfile in local server using EXPDP?
                    it is possible.i think your directory path is a network path pointed to client machine.
                    but please refer the following links and comment given by
                    impdp, expdp error ORA-39070 has me dead in the water

                    Sky13
                    Posts: 287
                    Registered: 11/23/09
                    Yes I agree... you are most likely running into a Windows security issue. When an oracle instance is created on Windows the services are >setup to Log On as the SYSTEM account. For "Calls" coming from the database to be able to access access directories they must have >privileges granted to the SYSTEM account. Basicaly the account that the OracleService[Instance Namer] service is running under has to >have access to the UNC path.
                    further..

                    impdp, expdp error ORA-39070 has me dead in the water

                    Expdp serve-side thing??

                    regards,
                    ragunath
                    • 7. Re: expdp issue
                      Dean Gagne
                      Nicolas,
                      No, it's not. NETWORK_LINK is not used to create a dumpfile locally from a remote database, it's used to a direct import into a >local database without dump.
                      this is not true, you can use network_link to do an export. It will create the dumpfile on the local database.

                      I have never seen a directory object pointing to a network disk work so I don't think that is possible.

                      Dean
                      • 8. Re: expdp issue
                        Nicolas.Gasparotto
                        Dean Gagne wrote:
                        Nicolas,
                        No, it's not. NETWORK_LINK is not used to create a dumpfile locally from a remote database, it's used to a direct import into a >local database without dump.
                        this is not true, you can use network_link to do an export. It will create the dumpfile on the local database.
                        Ok, maybe it is a misunderstanding. You are right, we can use export and NETWORK_LINK to export from remote database to a local directory defined by a DIRECTORY on a local database. To do so, we have to run export from a local database, as a bridge. Most of the client machine does not have it. That was what I was thinking.
                        Maybe the definition given in the doc is clearer than mine though :
                        http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL856
                        +"Enables an export from a (source) database identified by a valid database link. The data from the source database instance is written to a dump file set on the connected database instance."+
                        I have never seen a directory object pointing to a network disk work so I don't think that is possible.
                        I was thinking about a NFS point for instance. But again, it depends what defines the client for the OP.

                        I hope I'm clear enough now, thanks for correcting me.

                        Nicolas.

                        Edited by: N Gasparotto on Mar 22, 2013 3:43 PM