7 Replies Latest reply on Sep 15, 2019 5:50 PM by EdStevens

    Data pump on Linux/Oracle fails writing to an external Windows server.

    3467952

      My group has a 2 node RAC, Oracle 12.2.0 database on an Oracle Linux 7 server.  We are trying to perform a Data Pump schema export to an external Windows 2012R2 folder.  The following is the result of running the script:

       

      Export: Release 12.2.0.1.0 - Production on Wed Sep 4 11:39:43 2019

       

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

       

      Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

      ORA-39002: invalid operation

      ORA-39070: Unable to open the log file.

      ORA-29283: invalid file operation

      ORA-29283: invalid file operation

       

      Here is the shell script.  I have replaced identifying names, but the format remains.

       

      expdp DBA_UserID@DB_SID/DBA_password DIRECTORY=DAILYEXP DUMPFILE=export_$(date +\%Y\%m\%d).dmp SCHEMAS=<all schemas we want to export> COMPRESSION=ALL logfile=DAILYEXP:export$(date +\%Y\%m\%d).log

       

      The DAILYEXP has been declared as a DIRECTORY for the main schema, owned by SYS, with READ, WRITE, EXECUTE for the DBA_UserID in the script.   We recently migrated the database from a Windows 2012R2 server to a Linux 7 server.  This script used to work in Windows, but my limited Linux experience (HP/UX from 1987) prevents me from figuring it out myself.   The SysAdmin assured me the Windows server and folder have the proper permissions for Linux/Unix to READ and WRITE to the folder.

       

      Any help would be greatly appreciated.

        • 1. Re: Data pump on Linux/Oracle fails writing to an external Windows server.
          EdStevens

          3467952 wrote:

           

          My group has a 2 node RAC, Oracle 12.2.0 database on an Oracle Linux 7 server. We are trying to perform a Data Pump schema export to an external Windows 2012R2 folder. The following is the result of running the script:

           

          Export: Release 12.2.0.1.0 - Production on Wed Sep 4 11:39:43 2019

           

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

           

          Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

          ORA-39002: invalid operation

          ORA-39070: Unable to open the log file.

          ORA-29283: invalid file operation

          ORA-29283: invalid file operation

           

          Here is the shell script. I have replaced identifying names, but the format remains.

           

          expdp DBA_UserID@DB_SID/DBA_password DIRECTORY=DAILYEXP DUMPFILE=export_$(date +\%Y\%m\%d).dmp SCHEMAS=<all schemas we want to export> COMPRESSION=ALL logfile=DAILYEXP:export$(date +\%Y\%m\%d).log

           

          The DAILYEXP has been declared as a DIRECTORY for the main schema, owned by SYS, with READ, WRITE, EXECUTE for the DBA_UserID in the script. We recently migrated the database from a Windows 2012R2 server to a Linux 7 server. This script used to work in Windows, but my limited Linux experience (HP/UX from 1987) prevents me from figuring it out myself. The SysAdmin assured me the Windows server and folder have the proper permissions for Linux/Unix to READ and WRITE to the folder.

           

          Any help would be greatly appreciated.

          The directory may have the "permissions", but it most certainly doesn't have the same name.  You'd need to re-create directory DAILYEXP to reference a directory with a *nix name format.  Show us the output of

           

          col directory_name for a20

          col directory_path for a20

          SELECT directory_name,

                        directory_path

          from dba_directories

          where upper(directory_name) = 'DAILYEXP'

          ;

          • 2. Re: Data pump on Linux/Oracle fails writing to an external Windows server.
            3467952

            When I select via SQL*Plus without the UPPER conversion or TOAD without the conversion, this is the result.  Sorry for the crude blocking of the path.  It is a valid path.   Do I need to include this as a declaration in my script?

            • 3. Re: Data pump on Linux/Oracle fails writing to an external Windows server.
              EdStevens

              3467952 wrote:

               

              When I select via SQL*Plus without the UPPER conversion or TOAD without the conversion, this is the result. Sorry for the crude blocking of the path. It is a valid path. Do I need to include this as a declaration in my script?

              Well, you hid the very thing we needed to see. 

              You assert that it is a valid path, but at this point I'd have to say that Oracle disagrees, and you know where my money is.

              Include it in your script?  What script? We haven't seen any script. And don't expect us to comment on code we cannot see, no matter what you tell us about it.

              • 4. Re: Data pump on Linux/Oracle fails writing to an external Windows server.
                BPeaslandDBA
                It is a valid path

                 

                But is it a valid path for your Linux server? And does the 'oracle' user have file permissions to write to this directory?

                 

                Keep in mind that Data Pump writes to the local database server. It cannot write to a UNC path or a Windows server.

                 

                Cheers,

                Brian

                • 5. Re: Data pump on Linux/Oracle fails writing to an external Windows server.
                  3467952

                  Being an old novice at this stuff... I had no idea.  That makes sense.  I removed the directory references, and it's running.   Now I just need to schedule a job to move that dump file from Linux to Windows where I have more space.   Thank you all for your input.  It enlightened an old man.

                  • 6. Re: Data pump on Linux/Oracle fails writing to an external Windows server.
                    Khalid Ur Rehman OCP 12c DBA

                    Dear,

                    Please you follow to below solution about dump backup issue.
                    Apply below soltuion in test environment. it's very simple and understand to resolve your issue.


                    ==============================================================================================================
                    $ sqlpus / as sysdba
                    or

                    $ sqlplus sys/password@databasename as sysdba

                     

                    Under Windows OS:

                    -------------------------

                    SQL> CREATE OR REPLACE DIRECTORY prod_dir AS 'c:\backup';

                     

                    Under Linux OS:

                    ----------------------
                    SQL> CREATE OR REPLACE DIRECTORY prod_dir AS '/u01/backup';

                     

                    SQL> CREATE OR REPLACE DIRECTORY prod_dir AS '/backup';

                    SQL> GRANT READ, WRITE ON DIRECTORY prod_dir to username;

                     

                    expdp username/password@databsename schemas='PROD' directory=bivp_dir dumpfile=prod.dmp logfile=expbivp.log

                    expdp username/password schemas='PROD' directory=prod_dir dumpfile=bivp.dmp logfile=expprod.log

                    expdp username/password tables='tablename' directory=prod_dir dumpfile=table1.dmp logfile=table1.log

                    impdp username/password schemas='PROD' directory=prod_dir dumpfile=prod.dmp logfile=prod.log


                    if you want to dmup (database) backup. user full option to take full database backup.

                    Thanks,

                    Thanks,

                    • 7. Re: Data pump on Linux/Oracle fails writing to an external Windows server.
                      EdStevens

                      K

                      halid Ur Rehman OCP 12c DBA wrote:

                       

                      Dear,

                      Please you follow to below solution about dump backup issue.

                      Why are you responding to me?  I wasn't the one with the question.

                      Why are you responding at all?  The OP indicated he had his answer two weeks ago!

                       

                      Apply below soltuion in test environment. it's very simple and understand to resolve your issue.

                      Actually, it might not, even if I were the one with the problem.

                      ==============================================================================================================
                      $ sqlpus / as sysdba
                      or

                      $ sqlplus sys/password@databasename as sysdba

                      One does not, and probably should not, be connected as sysdba to perform the necessary steps. I say 'should not' because being connected to an oracle database as sysdba is like being connected to *nix as root.  There are no constraints to keep you from driving off a cliff.  It's like 'running with scissors . . . while juggling knives and chain saws'.

                       

                      Under Windows OS:

                      -------------------------

                      SQL> CREATE OR REPLACE DIRECTORY prod_dir AS 'c:\backup';

                       

                      Under Linux OS:

                      ----------------------
                      SQL> CREATE OR REPLACE DIRECTORY prod_dir AS '/u01/backup';

                       

                      SQL> CREATE OR REPLACE DIRECTORY prod_dir AS '/backup';

                      SQL> GRANT READ, WRITE ON DIRECTORY prod_dir to username;

                       

                      Why did you create directory PROD_DIR pointing to '/u01/backup', then immediately re-create it  pointing to '/backup'/ ?

                       

                      expdp username/password@databsename schemas='PROD' directory=bivp_dir dumpfile=prod.dmp logfile=expbivp.log

                       

                       

                      Where did directory 'bivp_dir' come from?  You created directory 'prod_dir'  (twice, the second overriding the first) just for this exercise, now you are using a different directory . . .

                       

                      expdp username/password schemas='PROD' directory=prod_dir dumpfile=bivp.dmp logfile=expprod.log

                      expdp username/password tables='tablename' directory=prod_dir dumpfile=table1.dmp logfile=table1.log

                      impdp username/password schemas='PROD' directory=prod_dir dumpfile=prod.dmp logfile=prod.log

                      And here you are inconsistent with your naming "conventions"

                      if you want to dmup (database) backup. user full option to take full database backup.   On the first export, the name of your log file is informative, indicating that it was an export operation and (I assume) that it was exporting the schema 'prod'.  But on your next export, you do nothing to indicate if the log was that of an export or an import.  Same for your import.

                      Thanks,

                      Thanks,

                      exports (neither exp nor expdp) are considered a backup of the database.  Only rman gets that distinction.  You cannot restore a database from an export dump file.   You can only use the dump to recover DATA into an existing, running, healthy database.  That is not the same as recovering a database.