Bash script with sqlplus

[peterx]

hi Tim,

I do get a simple question, but i can't fix it..., hope you can help me!
But, this is related to bash script with sqlplus ! thx.

I got this script:

Code: Select all

#!/bin/bash
#

RM="rm -f"
RMDIR="rm -rf"
LS="ls -l"
MV="mv"
TOUCH="touch"
TESTTOUCH="echo touch"
TESTMV="echo mv"
TESTRM=$LS
TESTRMDIR=$LS

SUCCESS=0
FAILURE=1
TEST=0
HOSTNAME=`hostname`
ORAENV="oraenv"
TODAY=`date +%Y%m%d`
ORIGPATH=/usr/local/bin:$PATH
ORIGLD=$LD_LIBRARY_PATH
export PATH=$ORIGPATH


# Function used to get database parameter values.
f_getparameter(){
  if [ -z "$1" ]; then
    return
  fi
  PARAMETER=$1

  sqlplus -s -l /nolog <<EOF | awk -F= "/^a=/ {print \$2}"
        conn / as sysdba
        set head off pagesize 0 feedback off linesize 200
        whenever sqlerror exit 1
        select 'a='||value from v\$parameter where name = '$PARAMETER';
EOF
}


# Check for the oratab file.
if [ -f /var/opt/oracle/oratab ]; then
  ORATAB=/var/opt/oracle/oratab
elif [ -f /etc/oratab ]; then
  ORATAB=/etc/oratab
else
  echo "ERROR: Could not find oratab file."
  exit $FAILURE
fi

# Build list of distinct Oracle Home directories.
OH=`egrep -i ":Y|:N" $ORATAB | grep -v "^#" | grep -v "\*" | cut -d":" -f2 | sort | uniq`

# Exit if there are not Oracle Home directories.
if [ -z "$OH" ]; then
  echo "No Oracle Home directories to clean."
  exit $SUCCESS
fi

# Get the list of running databases.
SIDS=`ps -e -o args | grep pmon | grep -v grep | awk -F_ '{print $3}' | sort`


# Gather information for each running database.
for ORACLE_SID in `echo $SIDS`
do

  # Set the Oracle environment.
  ORAENV_ASK=NO
  export ORACLE_SID
  . $ORAENV

  if [ $? -ne 0 ]; then
    echo "Could not set Oracle environment for $ORACLE_SID."
  else
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORIGLD

    ORAENV_ASK=YES

    echo "ORACLE_SID: $ORACLE_SID"

    # Get the audit_file_dest.
    ADUMPDEST=`f_getparameter audit_file_dest`
    echo "  Audit Dump Dest: $ADUMPDEST"
    if [ ! -z "$ADUMPDEST" ] && [ -d "$ADUMPDEST" 2>/dev/null ]; then
      ADUMPDIRS="$ADUMPDIRS $ADUMPDEST"
    fi

  fi
done



The questions is, the script get all the SID into "SIDS"
# Get the list of running databases.
SIDS=`ps -e -o args | grep pmon | grep -v grep | awk -F_ '{print $3}' | sort`

But..., when it try to get each db's Audit Dump Dest, it always only get first SID. The second SID will get nothing, like:

Code: Select all

[oracle@rac1 Oracle_Script]$ ./test.sh
The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_SID: +ASM1
  Audit Dump Dest: /u01/app/11.2.0.3/grid/rdbms/audit
The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_SID: RACHOME
  Audit Dump Dest:
[oracle@rac1 Oracle_Script]$



[Tim...]

Hi.

If you comment out the code in the loop and just echo the value of ORACLE_SID, what happens?

Code: Select all

for ORACLE_SID in `echo $SIDS`
do
  echo "ORACLE_SID: $ORACLE_SID"
done

Cheers

Tim...

[peterx]

hi Tim,

thanks for your help!

Code: Select all

# Gather information for each running database.
for ORACLE_SID in `echo $SIDS`
do
  # Set the Oracle environment.
#  ORAENV_ASK=NO
#  export ORACLE_SID
#  . $ORAENV

#  if [ $? -ne 0 ]; then
#    echo "Could not set Oracle environment for $ORACLE_SID."
#  else
#    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORIGLD

#    ORAENV_ASK=YES

    echo "ORACLE_SID: $ORACLE_SID"

    # Get the audit_file_dest.
#    ADUMPDEST=`f_getparameter audit_file_dest`
#    echo "  Audit Dump Dest: $ADUMPDEST"
#    if [ ! -z "$ADUMPDEST" ] && [ -d "$ADUMPDEST" 2>/dev/null ]; then
#      ADUMPDIRS="$ADUMPDIRS $ADUMPDEST"
#    fi

#  fi
done
~
"test.sh" 89 lines, 1892 characters


Changed the code like your advice..

the result is:

Code: Select all

rac1:[/home/oracle/bin]$ ./test.sh
ORACLE_SID: +ASM1
ORACLE_SID: TMSQA
rac1:[/home/oracle/bin]$


I'm quite sure the function `f_getparameter audit_file_dest` only can call once inside the for loop.

and I try before, change the `f_getparameter audit_file_dest` to directly sqlplus like:

Code: Select all

adump=`${ORACLE_HOME}/bin/sqlplus -s /nolog <<EOF
   connect / as sysdba ;
   set head off;
   set feedback off;
   set verify off;
   select value from v\\$parameter where name='audit_file_dest';
   exit;
   EOF `

the result will be the same, first loop can output my path. But second loop will give me error like:

Code: Select all

ORACLE_SID: TMSQA
  Audit Dump Dest: Output =     select value from v$parameter where name = 'audit_file_dest'
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


[peterx]

hi Tim,

Seems i found the problem:

Code: Select all

  # Set the Oracle environment.
#  ORAENV_ASK=NO
#  export ORACLE_SID
#  . $ORAENV

I replace this to run the local profile, seem fixed the problem.
using oraenv can't set a environment for this script.

thanks.

[Tim...]

OK.

[peterx]

hi Tim,

I got the final root cause for the problem, it is about the default RAC /etc/oratab.

default oratab:

Code: Select all

# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM1:/u01/app/11.2.0.3/grid:N          # line added by Agent
TMSQA:/u01/app/oracle/product/11.2.0.3/db_1:N         # line added by Agent


But.., in the RAC

Code: Select all

racp01:[/home/oracle]$ ps -ef |grep pmon
  oracle  7012492        1   0   Oct 03      - 12:43 asm_pmon_+ASM1
  oracle 22610120        1   0 14:40:20      -  0:00 ora_pmon_TMSQA_1


So......, the oratab should /must be :

Code: Select all

+ASM1:/u01/app/11.2.0.3/grid:N          # line added by Agent
TMSQA_1:/u01/app/oracle/product/11.2.0.3/db_1:N         # line added by Agent


if you want to use oraenv and without prompt you something !!!!

This problem block my road for 3 days.........
Sigh !!!!!!!!


thanks for your help!

[Tim...]

Hi.

You should probably leave in the Database entry as well as the instance entry. Some tools use the /etc/oratab when identifying available services on the machine.

Cheers

Tim...

[peterx]

hi Tim,

Yes, i learn it this time.

I'm very surprise that the default installation of the RAC will create this wrong /etc/oratab.
If this is wrong, why oracle leave this inside the /etc/oratab ?

thanks.

[Tim...]

Hi.

It's not really the wrong entry.

The dbstart and dbshut scripts are deprecated in 11.2, so one of the major uses of the oratab file has gone. Instead, RAC uses Oracle Restart to manage the services.

Cheers

Tim...