sqlplus -s Shell Scripting Techniques

My #1 Oracle DBA tool is sqlplus – I use it to automate DBA tasks directly on the Oracle Server from shell scripts. I often put the output of sqlplus -s in a shell variable and use it for further processing. I also wrap the sqlplus script output in a shell function and pipe it’s output to egrep filtering. These are all very powerful and useful techniques and I’ll show you how to use them in this post. And to make it easier – I’ll go over these use-cases in the context of the different ways we can invoke the sqlplus utility:

1. sqlplus username/password@TNS_ALIAS
2. sqlplus username@TNS_ALIAS
3. sqlplus /nolog
4. sqlplus / as sysdba
5. sqlplus -s

I will focus on Linux / Unix because this is where my expertise is and where Oracle put most of it’s focus and support (Oracle EL and Solaris). I will also assume that you already have sqlplus installed and the basic connectivity to your database is configured using tnsnames.ora file. Now lets dig deep!

sqlplus user/pass@TNS_ALIAS

This is the most basic way to start sqlplus and connect to a database defined by the TNS_ALIAS in the tnsnames.ora file. This is also the most insecure way of making a connection because anyone with access to your server where you are executing this command will be able to see the values of your username and password by simply running a ps -ef | grep sqlplus from their terminal. And for this reason I don’t recommend using this format to connect to an Oracle database from a machine that is open to other users/processes.

sqlplus username@TNS_ALIAS

Here we lost the password and made our connection much more secure because no-one can sniff our password by running ps -ef | grep sqlplus from their terminal. This is the method I use to connect to a remote Oracle database from my personal Mac OS X workstation. I simply save my very long password in a TextExpander snippet and supply it when sqlplus prompts me for it:

Note that my prompt is setup to display username and TNS_ALIAS that were used to make the connection with – this makes it very convenient when you have multiple terminal sessions opened to different databases and often switch between them. I use a special script to accomplish this – it’s called login.sql and here are it’s contents:

set timing on
set tab off
set time on
SET SERVEROUTPUT ON size unlimited
WHENEVER SQLERROR CONTINUE
set sqlprompt           "_USER'@'_CONNECT_IDENTIFIER=> "
define _editor=vi
set echo on

If you save the above code in a script called login.sql and then place this script in the directory where you start sqlplus from – you’ll get the same result. I highly recommend doing this.

sqlplus /nolog

The /nolog tells sqlplus to skip the login and go directly to the sqlplus prompt where you can make a connection. I use this when calling sqlplus from a shell script directly on the Oracle database server because it allows me make a connection using connect / as sysdba and then pass sqlplus some quick commands to process.

For example here’s a quick way to dump an Oracle systemstate in order to Find which Session is Holding a Particular Library Cache Lock (the example below works on 11g and above):

sqlplus /nolog <<EOF
connect / as sysdba
oradebug dump systemstate 266
oradebug tracefile_name
EOF

In the above script we wrap sqlplus /nolog command in a shell redirection which passes all commands between EOF words to sqlplus via stdin. This technique allows you to save these quick snippets and then recall/execute them with a lightning speed. I use TextExpander for this but you can simply save these snippets in a file/script.

sqlplus / as sysdba

If your only intention is to login as sysdba once and then execute some commands directly on the Oracle Server then you can skip the /nolog option and instead use / as sysdba. The / tells sqlplus to use OS level authentication and the as sysdba tells sqlplus that you want to gain access to the Oracle server with the sysdba privileges. If we were to rewrite the previous example using this annotation here’s what it would look like:

sqlplus / as sysdba <<EOF
oradebug dump systemstate 266
oradebug tracefile_name
EOF

At this point you might be wondering why use /nolog at all? The answer lies in the sqlplus scripting where a script prompts for a username/password or when the connect credentials are already embedded inside the body of the script. In such a case you don’t want to make the initial connection to the database and instead you let the script handle it for you. After all – there would be no reason to make that initial connection only to have the script reconnect again.

sqlplus -s

The -s flag tells sqlplus to be banner silent and not prompt for any input such as username/password or variable substitution. Note however that sqlplus -s will still output the results of the sql you execute and this makes it an extremely useful option in shell scripting because we can save the output of the sqlplus -s in a shell variable! For example here’s a way to verify Oracle RAC interconnect using oradebug ipc:

output=`sqlplus -s /nolog <<EOF
connect / as sysdba
oradebug setmypid
oradebug ipc
oradebug tracefile_name
exit
EOF`

trcfile=`echo $output | awk '{print $NF}'`
grep SSKGXPT $trcfile

Here we save the output of everything that runs between the two tick marks and the EOF words in a shell variable output and then we parse it’s contents using awk’s {print $NF} which gives us the last column in the record, and that happens to be the trace file name. We then grep this trace file for a line containing SSKGXPT to get the IP address of the interconnect network in an Oracle RAC Cluster.

Here’s a more advanced example of wrapping the sqlplus -s in a shell function and then piping it’s output to a grep filter:

#!/bin/ksh

get_asm() {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
col path format a35
col CREATE_DATE format a20
set lines 132
set trims on
set tab off
set pages 2000
select inst_id, DISK_NUMBER, header_status, 
       state,path,TOTAL_MB,FREE_MB,
       to_char(CREATE_DATE,'yyyy-mon-dd hh24:mi') CREATE_DATE 
from gv\$asm_disk;
EOF
}

ASMDISK=$1
if [ ${ASMDISK}"x" == "x" ]; then
    get_asm;
else
    get_asm | egrep "INST_ID|^--|${ASMDISK}"
fi

NOTE: this is a shortened version of my lasmdsk.sh script which I described last week in Oracle ASM Diagnostics Script.

Here we wrap the sqlplus -s script in a function get_asm() – at this point it’s simply defined and ready to be used, we then check if ASMDISK variable was supplied to us: if it wasn’t – we execute get_asm and let it print it’s output; on the other hand, if ASMDISK was given to us – we execute get_asm and pipe it’s output to egrep that prints all of the heading lines INST_ID|^-- plus the lines that contain the value of ASMDISK.

sqlplus wrapper script

Finally, I typically use a wrapper script when I need to execute a long running sql and have it’s output saved and emailed to me. For example, lets say we have a scriptmysql_script.sql which has a big job to insert append a lot of data from one big table to another using parallel dml – here are it’s contents:

set echo on
set time on
set timing on

ALTER SESSION SET recyclebin = OFF;
alter session set parallel_force_local=FALSE;
alter session enable parallel dml;
alter session enable parallel ddl;
select sid from v$mystat where rownum = 1;

alter table table_owner.big_target_table disable constraint btt_pk;
drop index table_owner.btt_pk;

insert /*+ append parallel(t,64) */ into table_owner.big_target_table t
select /*+ parallel(s,64)*/ * from source_owner.big_source_table s;

I then create a wrapper shell script that will let me execute above sql script safely and at the same time provide instrumentation and logging. I call this wrapper scriptexec_sql.sh – here are it’s contents:

mve$ cat ./exec_sql.sh
sqlscript=$1
sqlparams=$2
DBA=yourname@domain.com

sqlplus /nolog <<EOF
connect / as sysdba
set echo on
set feed on
set time on
set timing on
set lines 300
set trims on
set serveroutput on size unlimited
spool ${sqlscript}.log
select sid from v\$mystat where rownum = 1;
begin
    dbms_application_info.set_client_info(client_info=>'${sqlscript}');
    dbms_session.set_identifier('${sqlscript}');
    commit;
end;
/
@${sqlscript}.sql $sqlparams
spool off
exit
EOF

mailx -s "${sqlscript} done `date`" $DBA < ${sqlscript}.log

We can then call it as follows:

nohup ./exec_sql.sh mysql_script > mysql_script.hn &
tail -f mysql_script.hn

We are executing a sql script mysql_script.sql and piping it’s output to mysql_script.hn which we then start viewing “live” using tail -f. And while the above script is executing we can open another sqlplus session to the same database and execute the following sql to monitor what the script is doing or waiting for:

set lines 132
set pages 1000
set trims on
col CLIENT_IDENTIFIER format a20
col ACTION format a17
col P1TEXT format a14
col EVENT format a30

select inst_id,event,p1raw,max(seconds_in_Wait) max_wait,
       trunc(avg(seconds_in_Wait)) avg_wait,
       count(*), state
from gv$session
where CLIENT_IDENTIFIER = 'mysql_script'
  and wait_time=0
group by inst_id,event,p1raw,state
order by inst_id,event;

As soon as the script is finished the exec_sql.sh will send us an email with a subject “mysql_script done date” and pipe the log file generated by the script in the email body for our review.

And there you have it – we just went over my favorite ways to utilize sqlplus in shell scripting. Armed with these techniques you can start developing some very elaborate automation scripts in your Oracle environment. The best way to start with this is to just try something small – find a problem you need solved and slowly build a script to attack it.

Happy scripting! And if you found this writeup useful please subscribe to my newsletter and get new articles as soon as I post them:

SUBSCRIBE

Vitaliy Mogilevskiy December 30, 2015

Posted In: Operations, Scripts