Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2005
    Posts
    29

    Unanswered: sqlplus not accessed via cron

    Hello,

    I am trying to pass a date variable via cron but receive the following error.
    What I am hoping to achieve is to pass the date stamp to a logfile. The actual command works from the command line but when I schedule via cron receive an error message - see below

    Code:
    crontab -l
    # Daily Archive Logs Checks - (this script is run twice per day 1) first at 08:00am and then at 17:00pm
    47 9 * * * sh /home/oracle/scripts/dataguard_logs_applied.sh > "/home/oracle/scripts/dataguard_logs_applied_$(date +\%d\%m\%y).log"
    this is the contents of the job which runs

    Code:
    #!/bin/bash -xv
    export ORACLE_SID=sibprd01
    ORAENV_ASK=NO;export ORAENV_ASK
    sqlplus / as sysdba << EOF
    select INSTANCE_NAME from v\$instance;
    select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v\$archived_log     where sequence# = (select max(sequence#) from v\$archived_log where applied='YES')
    union     select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v\$archived_log where sequence# = (select max(sequence#) from v\$archived_log);
    exit
    EOF
    
    # End of sibprd01
    
    export ORACLE_SID=misprd01
    ORAENV_ASK=NO;export ORAENV_ASK
    sqlplus / as sysdba << EOF
    select INSTANCE_NAME from v\$instance;
    select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v\$archived_log     where sequence# = (select max(sequence#) from v\$archived_log where applied='YES')
    union     select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v\$archived_log where sequence# = (select max(sequence#) from v\$archived_log);
    exit
    --More--(39%)
    this is the mail errors generated from the cron job

    Code:
    Date: Wed, 17 Apr 2013 09:47:01 +0100 (BST)
    Status: RO
    
    + export ORACLE_SID=sibprd01
    + ORACLE_SID=sibprd01
    + ORAENV_ASK=NO
    + export ORAENV_ASK
    + sqlplus / as sysdba
    /home/oracle/scripts/dataguard_logs_applied.sh: line 5: sqlplus: command not found
    + export ORACLE_SID=misprd01
    + ORACLE_SID=misprd01
    + ORAENV_ASK=NO
    + export ORAENV_ASK
    + sqlplus / as sysdba
    /home/oracle/scripts/dataguard_logs_applied.sh: line 18: sqlplus: command not found
    + export ORACLE_SID=dsprd01
    + ORACLE_SID=dsprd01
    + ORAENV_ASK=NO
    + export ORAENV_ASK
    + sqlplus / as sysdba
    /home/oracle/scripts/dataguard_logs_applied.sh: line 30: sqlplus: command not found
    + export ORACLE_SID=cogprd01
    + ORACLE_SID=cogprd01
    + ORAENV_ASK=NO
    + export ORAENV_ASK
    + sqlplus / as sysdba
    /home/oracle/scripts/dataguard_logs_applied.sh: line 42: sqlplus: command not found
    + export ORACLE_SID=prsprd01
    + ORACLE_SID=prsprd01
    + ORAENV_ASK=NO
    + export ORAENV_ASK
    And if I execute the job from the command line it works perfectly well without changing any of the code

    Code:
    [oracle@pipdlr3hdb02 scripts]$ sh /home/oracle/scripts/dataguard_logs_applied.sh > "/home/oracle/scripts/dataguard_logs_applied_$(date +\%d\%m\%y).log"
    [oracle@pipdlr3hdb02 scripts]$ ls -ltr *.log
    
    -rw-r--r-- 1 oracle oracle   3674 Apr 16 15:00 dataguard_logs_applied_160413.log
    -rw-rw-r-- 1 oracle oracle   3369 Apr 17 10:10 dataguard_logs_applied_170413.log
    [oracle@pipdlr3hdb02 scripts]$
    So my main question is how can I get the job to perform the sql commands

    Many thanks in advance

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You are missing a statement in your job script:
    Code:
    #!/bin/bash -xv
    export ORACLE_SID=sibprd01
    ORAENV_ASK=NO;export ORAENV_ASK
    . /usr/local/bin/oraenv $ORACLE_SID  #<<== You are missing this ==
    sqlplus / as sysdba << EOF
    select INSTANCE_NAME from v\$instance;
    select 'Last applied  : ' Logs, to_char(ne . . . E t c  . . .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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