Sqlplus BindVariables
An example script
Always test these 'sqlplus bindvariables' commands on a test server before implementing them on a production server.
Below is an example script (check_stby.sql) which I've used to compare the most
recently applied archive log file at a standby database to the most recently archived log
at the primary database.
(I know, DataGuard can do this for me with much less effort..)
I am using sql*plus bind variables because I needed to connect to 2 different databases
and compare their results.
The standby database is in mounted mode, so this precludes the idea of trying to get this information
across a database link.
The sql script is then called by a shell script. (check_stby.sh) which gets called by
a host-user-defined-metric in Enterprise Manager's Grid Control system.
If the returned value exceeds the metric's threshold, an alert is triggered and I receive an
email warning me to check the status of the standby database.
check_stby.sql
set echo off set feedback off set termout off VARIABLE prim_seq NUMBER variable stby_seq number variable diff number connect sys/xxxx@primarydb.world.com as sysdba begin select max(sequence#)into :prim_seq from v$archived_log; end; / connect sys/xxxx@standbydb.world.com as sysdba begin select max(sequence#) into :stby_seq from v$log_history; end; / begin :diff := :prim_seq - :stby_seq ; end; / exit :diff;
check_stby.sh
#!/bin/ksh export ORACLE_HOME=/home/oracle/product/10.2.0 $ORACLE_HOME/bin/sqlplus -s /nolog @check_stby.sql echo "em_result="$?"\n"
