Code frequently needed to an oracle dba while shell scripting.

Thanks and due respect to Casimir Saternos for his wonderful article. If you would like to determine the ORACLE_HOME for a given database, you have several options. You can log on as the database user and echo the $ORACLE_HOME variable. You can also search through the /etc/oratab file and select the name associated with a given instance. Database entries in this file are of the form


The following one-liner prints out the ORACLE_HOME of an entry with an ORACLE_SID of TESTDB:

cat /etc/oratab | awk -F: '{if ($1=="TESTDB") print $2 }'

However, what if you have a requirement that an operation needs to be performed on each ORACLE_HOME listed in the /etc/orainst file? You can iterate through such a list by utilizing the following code snippet.

dblist=‘cat /etc/oratab | grep -v "#" | awk -F: '{print $2 }'‘

for ohome in $dblist ; do
  echo $ohome

Continue reading

Oracle scan listener

Single Client Access Name (SCAN) is a feature used in Oracle Real Application Clusters environments that provides a single name for clients to access any Oracle Database running in a cluster. You can think of SCAN as a cluster alias for databases in the cluster. The benefit is that the client’s connect information does not need to change if you add or remove nodes or databases in the cluster.

EZconnet sqlplus system/manager@sales1-scan:1521/oltp

JDBC connect jdbc:oracle:thin:@sales1-scan:1521/oltp

Getting the Bind variables

If the sql was executed some time before : 

 select name,datatype_string,position,value_string from dba_hist_sqlbind where sql_id like lower(‘<sql_id’); 

If the sql was executed recently :

select distinct name,datatype_string,position,value_string from gV$SQL_BIND_CAPTURE where sql_id=’9t9mmxmjgkab2′;