This forum is currently locked. You can't register or post questions at this time. (read more)

Bash script with sqlplus

All posts relating to Oracle database administration.

Moderator: Tim...

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Bash script with sqlplus

Postby peterx » Wed Dec 19, 2012 1:33 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Bash script with sqlplus

Postby Tim... » Wed Dec 19, 2012 3:22 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Bash script with sqlplus

Postby peterx » Thu Dec 20, 2012 1:56 am

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
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Bash script with sqlplus

Postby peterx » Thu Dec 20, 2012 7:08 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Bash script with sqlplus

Postby Tim... » Thu Dec 20, 2012 7:36 am

OK.
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Bash script with sqlplus

Postby peterx » Thu Dec 20, 2012 8:18 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Bash script with sqlplus

Postby Tim... » Thu Dec 20, 2012 2:15 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Bash script with sqlplus

Postby peterx » Fri Dec 21, 2012 6:46 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Bash script with sqlplus

Postby Tim... » Fri Dec 21, 2012 7:34 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests

cron