Shell Script to Run a SQLPLUS against all databases running on a server...

#!/usr/bin/bash
#---------------------------------------------------------------------------
# Written by  : David Mann @ http://ba6.us
# Script Name : alldatafiles.sh
# Description : Provides list of all datafiles for each running database on 
#               on a machine
#---------------------------------------------------------------------------

ORATAB=/etc/oratab
echo "INSTANCE_NAME, FILE_NAME"

# Step through running instances
ps -ef | grep ora_smon_ | grep -v grep | cut -b61-70 | while read LINE
do
    # Assign the ORACLE_SID
    ORACLE_SID=$LINE
    export ORACLE_SID

    #Find ORACLE_HOME info for current instance
    ORATABLINE=`grep $LINE $ORATAB`
    ORACLE_HOME=`echo $ORATABLINE | cut -f2 -d:`
    export ORACLE_HOME
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
    export LD_LIBRARY_PATH

    # Put $ORACLE_HOME/bin into PATH and export.
    PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc ; export PATH

    # Get SGA
    sqlplus -s "/ as sysdba" <<EOF
    SET HEADING OFF
    SET FEEDBACK OFF
    SET LINESIZE 3800
    SET TRIMSPOOL ON
    SET TERMOUT OFF
    SET SPACE 0
    SET PAGESIZE 0
    select (select instance_name from v\$instance) as DB_NAME,
           file_name
      from dba_data_files
     order by 2;
EOF

done

Comments

You have shed a ray of sunshine into the forum. Tahkns!

Hi David,

Request you to provide script to connect to multiple databases on multiple servers with "/as sysdba".

Hi, Context of this script is to run on a single server, you are free to use it as a basis for future scripts. Involving multiple servers brings up many issues that I can't address for you. As far as the operating system rights needed to run "ps -ef", do you have SSH equivalency set up? For the DB side of things I know by default many organizations disallow logging in as "/ as sysdba" from remote servers. If you have SSH equivalency set up between servers you may be able to use SSH command to run "ps -ef" remotely to determine what servers are running. Then you can use that list to form a command line that would work for logging into them. Good luck!

Add new comment