11 Replies Latest reply: Jun 18, 2014 11:13 PM by Prejib RSS

    Crontab is not working for expdp

    Prejib


      Hi ,

       

                I am able to run the .sh file from linux prompt successfully . If I schedule this through crontab thecommand 'expdp' and 'exp' are not getting executed fromt the .sh file , If I print Oracle_Home and Path this is getting printed in the log file. The file contents are given below

       

      [oracle@lnxoradb1 shell]$ crontab -e

       

      18 23 * * * bash /data/scripts/shell/test.sh >> /data/scripts/log/test.log

       

      [oracle@lnxoradb1 shell]$ cat test.sh

      cd ~

      . .bash_profile

       

       

      PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1:/usr/sbin:/usr/bin:/usr/local/bin:/u01/app/oracle/oradata:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/home/oracle/bin:/home/oracle

      export PATH

      export ORACLE_BASE=/u01/app/oracle

      export ORACLE_SID=xqa1

      export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

       

      echo "Path =" $PATH

      echo  "starting ..........."

      expdp

      exp

      echo "Ending.."

       

      [oracle@lnxatgoradb1 log]$ cat test.log

      Path = /usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1:/usr/sbin:/usr/bin:/usr/local/bin:/u01/app/oracle/oradata:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/home/oracle/bin:/home/oracle

      starting ...........

      Ending..

       

      Thanks

      Prejib

        • 1. Re: Crontab is not working for expdp
          DK2010

          Hi,

           

          You must have some error in the log file like unknown command something like this,Try with following,check with echo u have set all the parameter

           

          export ORACLE_BASE=/u01/app/oracle

          echo $ORACLE_BASE

          export ORACLE_SID=xqa1

          echo $ORACLE_SID

          export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

          echo $ORACLE_HOME

          export PATH=$ORACLE_HOME/bin:$PATH

          echo $PATH

          • 2. Re: Crontab is not working for expdp
            sybrand_b

            Why do you recommend to hardcode Oracle_home in scripts? It is unprofessional!

             

            To OP:

            You should call

            export ORAENV_ASK="NO"

            export ORACLE_SID=<your sid>

             

            . oraenv

             

            For the rest

            - there is a PL/SQL API which you can use to export your database

            - Oracle comes with dbms_scheduler, so using cron is obsolete.

             

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

            Sybrand Bakker

            Senior Oracle DBA

            • 3. Re: Crontab is not working for expdp
              Prejib

              Hi ,

                   Still I am getting the result like this after setting all the variables correctly as given below ..

              /u01/app/oracle

              xqa1

              /u01/app/oracle/product/11.2.0/dbhome_1

              /u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1:/usr/sbin:/usr/bin:/usr/local/bin:/u01/app/oracle/oradata:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/home/oracle/bin:/home/oracle

              Path = /u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1:/usr/sbin:/usr/bin:/usr/local/bin:/u01/app/oracle/oradata:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/home/oracle/bin:/home/oracle

              starting ...........

              Ending..

              • 4. Re: Crontab is not working for expdp
                Prejib

                Hi ,

                 

                 

                sybrand_b wrote:

                 

                Why do you recommend to hardcode Oracle_home in scripts? It is unprofessional!

                 

                To OP:

                You should call

                export ORAENV_ASK="NO"

                export ORACLE_SID=<your sid>

                 

                . oraenv

                 

                For the rest

                - there is a PL/SQL API which you can use to export your database

                - Oracle comes with dbms_scheduler, so using cron is obsolete.

                 

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

                Sybrand Bakker

                Senior Oracle DBA

                    First time  Oracle_home was not hard coded , it was given as ORACLE_HOME =$ORACLE_HOME .. For trial purpose ,I have hard coded.

                I have tried with the above commands also , and not getting the expected result .

                Can you please help me to understand --> PL/SQL API

                • 5. Re: Crontab is not working for expdp
                  sybrand_b

                  'Not getting the expected result'. So what result did you get? I know for a fact this works, I don't post non-operational code here, and I am using this for years.

                  So please don't post this kind  of false assertions.

                   

                  For the datapump api refer to Morgans Library Oracle 12c DBMS_DATAPUMP Demos&lt;/title&gt;&lt;/head&gt;&lt;body&gt;&lt;div align=&quot;center&quot;&gt;&lt;div class=&…

                  At the bottom of this page are working examples.

                  To make this working in batch you need to remove the progress reporting, but that is not difficult to accomplish.

                   

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

                  Sybrand Bakker

                  Senior Oracle DBA

                  • 6. Re: Crontab is not working for expdp
                    Jagdeep Sangwan

                    I don't post non-operational code here, and I am using this for years.

                    So please don't post this kind  of false assertions.

                    These are some kind of words for an expert to say .....

                    • 7. Re: Crontab is not working for expdp
                      EdStevens

                      Prejib wrote:

                       

                      Hi ,

                       

                       

                      sybrand_b wrote:

                       

                      Why do you recommend to hardcode Oracle_home in scripts? It is unprofessional!

                       

                      To OP:

                      You should call

                      export ORAENV_ASK="NO"

                      export ORACLE_SID=<your sid>

                       

                      . oraenv

                       

                      For the rest

                      - there is a PL/SQL API which you can use to export your database

                      - Oracle comes with dbms_scheduler, so using cron is obsolete.

                       

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

                      Sybrand Bakker

                      Senior Oracle DBA

                          First time  Oracle_home was not hard coded , it was given as ORACLE_HOME =$ORACLE_HOME .. For trial purpose ,I have hard coded.

                      I have tried with the above commands also , and not getting the expected result .

                      Can you please help me to understand --> PL/SQL API

                      What do you think the statement "ORACLE_HOME =$ORACLE_HOME" actually accomplishes?  You are simply setting an environment variable (ORACLE_HOME) to its current value .....

                       

                      When a script is executed by cron, it does NOT inherit any envrionment at all.  The script is responsible for setting any and all environment variables that it needs.  And to that end, I would reiterate what Sybrand said about the use of oraenv to set the environment variables that Oracle cares about (ORACLE_BASE, ORACLE_HOME, ORACLE_SID, PATH, LD_LIBRARY_PATH).  He gave you the sample code.  I do the same thing in ALL of my shell scripts.  To reiterate:

                       

                      set ORACLE_SID=whatever

                      set ORAENV_ASK=NO

                      .  oraenv    #<< that's 'dot space oraenv'

                      unset ORAENV_ASK

                       

                      I would NOT reference '.bash_profile'  That is really intended to be used for your interactive command line session.  There will be too much temptation to customize it for that environment, and end up with unintended consequences in any shell scripts that reference it.  It is simply a lazy way of trying to avoid figuring out what your script really needs.  Kind of like granting DBA to an app user of a database.

                      • 8. Re: Crontab is not working for expdp
                        EdStevens

                        Prejib wrote:

                         


                        Hi ,

                         

                                  I am able to run the .sh file from linux prompt successfully . If I schedule this through crontab thecommand 'expdp' and 'exp' are not getting executed fromt the .sh file , If I print Oracle_Home and Path this is getting printed in the log file. The file contents are given below

                         

                        [oracle@lnxoradb1 shell]$ crontab -e

                         

                        18 23 * * * bash /data/scripts/shell/test.sh >> /data/scripts/log/test.log

                         

                        [oracle@lnxoradb1 shell]$ cat test.sh

                        cd ~

                        . .bash_profile

                         

                         

                        PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1:/usr/sbin:/usr/bin:/usr/local/bin:/u01/app/oracle/oradata:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/home/oracle/bin:/home/oracle

                        export PATH

                        export ORACLE_BASE=/u01/app/oracle

                        export ORACLE_SID=xqa1

                        export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

                         

                        echo "Path =" $PATH

                        echo  "starting ..........."

                        expdp

                        exp

                        echo "Ending.."

                         

                        [oracle@lnxatgoradb1 log]$ cat test.log

                        Path = /usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1:/usr/sbin:/usr/bin:/usr/local/bin:/u01/app/oracle/oradata:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/home/oracle/bin:/home/oracle

                        starting ...........

                        Ending..

                         

                        Thanks

                        Prejib

                        YOur supposed output does not square with the script you show.  Those references to expdp and exp should/would have returned something.  BTW, if those were the entire commands, your script should have 'hung' on the expdp command because it would have entered interactive mode, since you provided it with NO information on which to act.

                        • 9. Re: Crontab is not working for expdp
                          EdStevens

                          18 23 * * * bash /data/scripts/shell/test.sh >> /data/scripts/log/test.log


                          try

                           

                          18 23 * * * bash /data/scripts/shell/test.sh >> /data/scripts/log/test.log  2>&1

                           

                          this will redirect errout to your log file.

                           

                          Also, add this line to the top of your script:

                           

                          set -x

                           

                          That will add a lot of tracing information to your output.

                          • 10. Re: Crontab is not working for expdp
                            Catch-22

                            The complete console output of a crontask is normally recorded by cron and sent by e-mail unless it has been disabled. Please check the email of your Oracle user for any clues and error messages.

                            • 11. Re: Crontab is not working for expdp
                              Prejib

                              Thanks ..

                               

                              When I have added 'set -x ' at the top of the file , the crontab executed successfully without any issues including expdp..

                              and printed the expected outpu in the log file

                              Regards,

                              Prejib