Disable RAC server (and client) side load balance

Here is how to disable RAC server (and client) side load balance, and the test:

* Client side load balance:

SALES_DB =
(DESCRIPTION_LIST=
(LOAD_BALANCE=off)
(FAILOVER=on)
(DESCRIPTION=
(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac01_vip)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=rac02_vip)(PORT=1521))

)

(CONNECT_DATA=(SERVICE_NAME=oltp_workload))))

* server side load balance:

srvctl modify service -d <dbname> -s <servicename> -B NONE

* Test

$>

#!/bin/bash

echo "">t.txt

for i in {1..70}

do

sqlplus -s system/ABC@sales_db >> t.txt <<+++

set head off

set feedback off

set pagesize 0

select instance_name from v\$instance;

begin

  for lc in (select * from all_objects where rownum < 500)

  loop

    Null;

  end loop;

end;

/

exit;

+++

sleep 0.02

done

cat t.txt|uniq

rac01

If load balance is enabled, then the output will be 2 lines or more，for example, 

rac01,

rac02.

Otherwise, it'll be always one line, e.g. : rac01.

* Reference

srvctl modify service -d db_unique_name -s service_name
     [-g server_pool] [-c {UNIFORM|SINGLETON}] [-P {BASIC|PRECONNECT|NONE}] 
     [-l {[PRIMARY] | [PHYSICAL_STANDBY] | [LOGICAL_STANDBY] | [SNAPSHOT_STANDBY]}
     [-q {TRUE|FALSE}] [-x {TRUE|FALSE}] [-j {SHORT|LONG}] 
     [-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e {NONE|SESSION|SELECT}] 
     [-m {NONE|BASIC}] [-z failover_retries] [-w failover_delay]
     [-y {AUTOMATIC | MANUAL}]

Table A-79 srvctl modify service Options

Option	Description
-d db_unique_name	Unique name for the database
-s service_name	Service name
-g server_pool	The name of a server pool used when the database is policy managed. Note: This option is available only with Oracle RAC and only for policy-managed databases.
-c {UNIFORM | SINGLETON}	The cardinality of the service, either UNIFORM (offered on all instances in the server pool) orSINGLETON (runs on only one instance at a time) Note: This option is available only with Oracle Clusterware.
-P {BASIC|PRECONNECT|NONE}	TAF failover policy Note: The PRECONNECT option applies only to administrator-managed databases.
-l {[PRIMARY] | [PHYSICAL _STANDBY] | [LOGICAL _STANDBY] | [SNAPSHOT _STANDBY]}	The database modes for which the service should be started automatically.
-q {TRUE | FALSE}	Indicates whether AQ HA notifications should be enabled (TRUE) for this service
-x {TRUE | FALSE}	Indicates whether or not Distributed Transaction Processing should be enabled for this service Note: This option is available only with Oracle Clusterware.
-j {SHORT | LONG}	Connection Load Balancing Goal
-B {NONE | SERVICE_TIME | THROUGHPUT}	Runtime Load Balancing Goal
-e {NONE | SESSION | SELECT}	Failover type
-m {NONE | BASIC}	Failover method
-z failover_retries	The number of failover retry attempts
-w failover_delay	The time delay between failover attempts
-y {AUTOMATIC | MANUAL}	Service management policy

• http://docs.oracle.com/cd/E11882_01/rac.112/e41960/hafeats.htm#RACAD076
• http://oracleinaction.com/rac-load-balancing
• http://www.slideshare.net/MarkusMichalewicz/oracle-rac-11g-release-2-client-connections

Thanks for the help from Ricky Zhu

Application code instrumentation

When developing software application, why code instrumentation/tracing/logging must be done?

Easy to maintain

Tells where the problem is when something goes wrong, where to look for. Helps troubleshooting and fix bugs.

It's often difficult to track down the source of an error without some form of debugging instrumentation. This is particularly true in multi-tier, stateless architectures, running on production system.

There will be lot’s of restrictions to reproduce the bugs and errors on production system, most time it’ll corrupt the production data.

To make application traceable.

Traceability, auditing and logging are crucial.

http://97things.oreilly.com/wiki/index.php/Focus_on_Application_Support_and_Maintenance

http://en.wikipedia.org/wiki/Traceability#Software_development

“In transaction processing software, traceability implies use of a unique piece of data (e.g., order date/time or a serialized sequence number) which can be traced through the entire software flow of all relevant application programs. Messages and files at any point in the system can then be audited for correctness and completeness, using the traceability key to find the particular transaction.”

To get runtime status and stats.

To know what’s going on in real time on production system.

To measure performance. Profile.

Tells the application transaction response time and transaction volume (throughput).

“End users want to know, is the system going slower over time, if so, by how much.  Management wants to know, what are my transaction response times, how many transactions do we do, when is the busiest time, and so on.  People responsible to administering the system need to be able to identify where bottlenecks are, who needs to be brought in to look at something, who is responsible.”

To make the code runs faster.

The overhead of instrumentation is approximately -27%

Yes, NEGATIVE 27%.

Evidence that is true:

a) oracle is heavily instrumented (v$ tables, sql_trace, AWR, ASH, statspack, etc etc etc)

b) without that instrumentation - you would not know where to begin to start to tune

c) therefore, we run faster with it than without it - much much faster...

Good engineer practice.

The logging should be able to be turned ON and OFF easily - just like sql_trace.

That’s why we migrated to logger from debugf. It's easy to "turn-off" logger globally with virtually no performance impact, it's easy to get in the habit of leaving debug calls in production code.

The instrumentation should always be there, it should always be infinitely detailed.

but most of the time, it shouldn't be executed. Common sense must prevail.

Reference

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:54174245781686

http://tkyte.blogspot.ca/2005/06/instrumentation.html

http://tkyte.blogspot.ca/2006/08/do-i-ever-agree-with-this.html

Logger is used by Oracle developers to instrument their PL/SQL code

https://github.com/tmuth/Logger---A-PL-SQL-Logging-Utility

psql.sh Tool to get session and SQL stats by process_id

Many a times, we want to get database session and SQL information based on the process ID.
Here is the one I've used for many years.


Next time I will show you how to troubleshooting Oracle database performance by this tool.

==
{code start}


#!/bin/bash
if [ $# -ne 1 ]
then
  echo "Usage: psql.sh <ProcessID>"
  exit
fi


sqlplus -S / as sysdba <<+++
set     wrap on
set     feedback off
set     pagesize 0
set termout off

set echo off
set verify off
set arraysize 200
set linesize 200
set trimspool on
set trimout on
set long 5000
set serveroutput on size 200000

/*
-- Get execution plan 
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, format=>'Advanced')) t
where s.sql_id = 'dcfkuagnmqx3y';

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'BASIC')) t
where s.sql_id = 'dcfkuagnmqx3y'
and s.last_load_time is not null
and loaded_versions > 0;

provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL
cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views

*/

Begin

for s in
(select b.sid,b.serial#,b.username, b.machine,b.osuser,
b.program sprogram,c.program pprogram,c.terminal pterminal,b.terminal sterminal,PGA_ALLOC_MEM,
b.process,b.status,b.server,b.type,b.taddr,b.logon_time,b.LAST_CALL_ET,
b.lockwait, c.latchwait,c.latchspin,c.spid,
b.PREV_SQL_ID, b.sql_id,
EVENT, WAIT_TIME, SECONDS_IN_WAIT
from v\$session b, v\$process c
where  c.spid  = $1
and  c.addr  = b.paddr)
loop

--dbms_output.put_line('======================================================');
dbms_output.put_line('SID/Serial  : '|| s.sid||','||s.serial#);
dbms_output.put_line('SQL_ID      : '|| s.sql_id);
dbms_output.put_line('Foreground  : '|| 'PID: '||s.process||' - '||s.sprogram);
dbms_output.put_line('Shadow      : '|| 'PID: '||s.spid||' - '||s.pprogram);
dbms_output.put_line('Terminal    : '|| s.sterminal || '/ ' || s.pterminal);
dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User    : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time  : '|| to_char(s.logon_time, 'Day HH24:MI:SS'));
dbms_output.put_line('Last Call   : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Day HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(s.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin  : '|| nvl(s.latchspin, 'NONE'));
dbms_output.put_line('PGA         : '|| s.PGA_ALLOC_MEM);
dbms_output.put_line('Wait        : '|| s.EVENT||': '||s.WAIT_TIME||', Seconds: '||s.SECONDS_IN_WAIT);

dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from v\$sqltext
            where SQL_ID = s.sql_ID order by piece) loop
  dbms_output.put_line(chr(9)||c1.sql_text);
end loop;

for c1 in ( 
SELECT t.*
FROM v\$sql v, table(DBMS_XPLAN.DISPLAY_CURSOR(v.sql_id, v.child_number, 'TYPICAL')) t
where v.sql_id = s.sql_id
and v.loaded_versions > 0
) 
loop
  dbms_output.put_line(chr(9)||c1.PLAN_TABLE_OUTPUT);
end loop;
/*
SELECT *
FROM TABLE(CAST ( DBMS_XPLAN.display_cursor('s.sql_id') AS SYS.DBMS_XPLAN_TYPE_TABLE)) ;
*/
dbms_output.put_line('Previous SQL statement:');
/* for c1 in ( select * from v\$sqltext
            where SQL_ID = s.PREV_SQL_ID order by piece) loop
  dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
*/

for c1 in ( 
/* SELECT * FROM TABLE(CAST ( DBMS_XPLAN.display_cursor(s.prev_sql_id) AS SYS.DBMS_XPLAN_TYPE_TABLE))
*/
SELECT tb.PLAN_TABLE_OUTPUT
FROM v\$sql v, table(CAST ( DBMS_XPLAN.DISPLAY_CURSOR(v.sql_id, v.child_number,'BASIC') AS SYS.DBMS_XPLAN_TYPE_TABLE)) tb
where v.sql_id = s.prev_sql_id
and v.loaded_versions > 0
) 
loop
  dbms_output.put_line(chr(9)||c1.PLAN_TABLE_OUTPUT);
end loop;

dbms_output.put_line('Session Waits:');
for c1 in ( select * from v\$session_wait where sid = s.sid) loop
  dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
  dbms_output.put_line(chr(9)||'Wait time: '||c1.WAIT_TIME||', wait seconds: '||c1.SECONDS_IN_WAIT);
end loop;

dbms_output.put_line('Session History Waits:');
for c1 in ( select * from v\$session_wait_history where sid = s.sid order by seq# asc) loop
  dbms_output.put_line(chr(9)||c1.seq#||': '||c1.event||',Wait time: '||c1.WAIT_TIME);
end loop;

dbms_output.put_line('Session Statistics:');
/* Top session
for c1 in ( select a.statistic#,a.value, b.name, b.class, b.stat_id
 from V\$SESSTAT a, V\$STATNAME b
 where a.STATISTIC# = b.STATISTIC# and sid = s.sid and a.value>0) 
loop
  dbms_output.put_line(chr(9)||c1.statistic#||': '||c1.name||': '||c1.value);
end loop;
*/

End Loop;

END;
/

exit;
+++

{code end}

Example output:

$> psql.sh 26046

SID/Serial  : 33,32215

SQL_ID    : 98g72446puumc

Foreground  : PID: 26043 - sqlplus@mydb02.abc.com (TNS V1-V3)

Shadow    : PID: 26046 - oracle@mydb02.abc.com (TNS V1-V3)

Terminal    : / UNKNOWN

OS User     : oracle on mydb02.abc.com

Ora User    : SYS

Status Flags: ACTIVE DEDICATED USER

Tran Active : 00000000D9569228

Login Time  : Monday 14:27:33

Last Call   : Monday 14:27:33 -    0.3 min

Lock/ Latch : NONE/ NONE

Latch Spin  : NONE

PGA    : 34308772

Wait    : db file scattered read: 0, Seconds: 0

Current SQL statement:

select qt.msgid, e.local_transaction_id, e.source_commit_scn, e.

external_source_pos, e.aq_transaction_id, e.source_transaction_i

d, NVL(e.xidusn, 0), NVL(e.xidslt, 0), NVL(e.xidsqn, 0), NVL(e.f

lags, 0) from "STRMADMIN"."APPLY_QUEUE_TABLE" qt, sys.apply$_err

or e where e.apply#=:1 and qt.enq_tid(+)=e.aq_transaction_id and

qt.q_name(+)=:2 and e.local_transaction_id=:3 order by qt.step

_no

SQL_ID 98g72446puumc, child number 0

-------------------------------------

select qt.msgid, e.local_transaction_id, e.source_commit_scn,

e.external_source_pos, e.aq_transaction_id, e.source_transaction_id,

NVL(e.xidusn, 0), NVL(e.xidslt, 0), NVL(e.xidsqn, 0), NVL(e.flags, 0)

from "STRMADMIN"."APPLY_QUEUE_TABLE" qt, sys.apply$_error e where

e.apply#=:1 and qt.enq_tid(+)=e.aq_transaction_id and qt.q_name(+)=:2

and e.local_transaction_id=:3  order by qt.step_no

Plan hash value: 2690713002

----------------------------------------------------------------------------------------------------------

| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      | | | |  3227 (100)| |

|   1 |  SORT ORDER BY      | |     1 |   132 |  3227   (1)| 00:00:39 |

|*  2 |   HASH JOIN OUTER      | |     1 |   132 |  3226   (1)| 00:00:39 |

|*  3 |    TABLE ACCESS BY INDEX ROWID| APPLY$_ERROR |     1 |    75 |     2   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN      | STREAMS$_APPLY_ERROR_UNQ |     1 | |     1   (0)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL      | APPLY_QUEUE_TABLE |   566 | 32262 |  3224   (1)| 00:00:39 |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  2 - access("QT"."ENQ_TID"="E"."AQ_TRANSACTION_ID")

  3 - filter("E"."APPLY#"=:1)

  4 - access("E"."LOCAL_TRANSACTION_ID"=:3)

  5 - filter("QT"."Q_NAME"=:2)

Note

-----

  - dynamic sampling used for this statement (level=2)

Previous SQL statement:

EXPLAINED SQL STATEMENT:

------------------------

select tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.delay,

tab.expiration , tab.exception_qschema,   tab.exception_queue,

tab.chain_no, tab.local_order_no, tab.enq_time,

tab.time_manager_info, tab.state, tab.enq_tid, tab.step_no,

tab.sender_name, tab.sender_address, tab.sender_protocol,

tab.dequeue_msgid, tab.user_prop, tab.user_data  from

"STRMADMIN"."APPLY_QUEUE_TABLE" tab  where q_name = :1 and msgid = :2

Plan hash value: 4210003331

---------------------------------------------------------

| Id  | Operation    | Name |

---------------------------------------------------------

|   0 | SELECT STATEMENT    | |

|   1 |  TABLE ACCESS BY INDEX ROWID| APPLY_QUEUE_TABLE |

|   2 |   INDEX UNIQUE SCAN    | SYS_C004100 |

---------------------------------------------------------

Session Waits:

WAITED SHORT TIME: db file scattered read

Wait time: -1, wait seconds: 0

Session History Waits:

1: db file scattered read,Wait time: 0

2: db file scattered read,Wait time: 0

3: db file scattered read,Wait time: 0

4: db file scattered read,Wait time: 0

5: db file scattered read,Wait time: 0

6: db file scattered read,Wait time: 0

7: db file scattered read,Wait time: 0

8: db file scattered read,Wait time: 0

9: db file scattered read,Wait time: 0

10: db file scattered read,Wait time: 0

Session Statistics:

.

Next time I will show you how to troubleshooting Oracle database performance by this tool.

DDL to create table scott.emp and DML to load data

Purpose
=======
I often use emp (and dept) table to build test cases, to prove concept and explore new features.
It's not easy to find the original DDL and DML to create the table and load the data.
So I just get it by myself. I think these DDL/DML are useful to you too.

Notes: I hope you don't have tables named "dept" and "emp" in your application schema.

SQL>


drop table dept purge;

CREATE TABLE dept (
 deptno NUMBER(2),
 dname  VARCHAR2(15),
 loc    VARCHAR2(15),
 constraint dept_pk primary key (deptno)
);

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

COMMIT;

drop table emp purge;

CREATE TABLE EMP
( EMPNO NUMBER(4) NOT NULL,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7, 2),
  COMM NUMBER(7, 2),
  DEPTNO NUMBER(2),
  constraint emp_pk primary key (Empno),
  CONSTRAINT emp_fk1 FOREIGN KEY (deptno) REFERENCES dept
);

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

commit;


Demo de-duplicate on emp.ename
=======

INSERT INTO EMP VALUES (7935, 'MILLER', 'ANALYST', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
INSERT INTO EMP VALUES (7936, 'MILLER', 'SALESMAN', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
commit;

A deduplicate will be like this,

DELETE FROM scott.emp
WHERE ROWID IN
 (
  SELECT rid
    FROM (SELECT /*x parallel(a) */
                 ROWID rid, ename,
                 ROW_NUMBER () OVER (PARTITION BY ename ORDER BY empno) rn
            FROM scott.emp a)
  WHERE rn <> 1
);


Gather table statistics 
=======
begin
    dbms_stats.gather_table_stats(
        ownname      => user,
        tabname      =>'EMP',
        estimate_percent => 0.2,
        method_opt   => 'for all columns size 1'
    );
end;
/

- 木匠

ORA-00911: invalid character

This one is simple, but useful.  :)

When compile  a VIEW, the error "ORA-00911: invalid character" poped up.

@biprd_abc.com> @/dev/SQL/v_nov09.sql

    AND f.COL_ABC not in

                          *

ERROR at line 444:

ORA-00911: invalid character

=====

 not in

 not in

========

Copy above 2 "not in" into a text editor, you'll see the invisible Special Character.

TextWrangler can show the invisible Special Characters.  I couldn't find this function in SubLime Text 2.

The solution is simple too: remove the string contains Special Character, and then type them again.

Thanks,

Charlie

runsql.sh shell tool to run a SQL file

Purpose: Run SQL or PL/SQL in a file.

Background:

For the SQL that takes a long time to finish, we usually let them run as a backend job in database server. E.g. rebuild index, Online Table Redefinition.
This shell tool will make it simple to call a SQL_file.
.
When doing low level things I prefer to run as sys via sqlplus on the local server (not via client sqlplus and never thru tool).  General good practice.  Eliminate workstation or network flaps etc from causing issues.
.

Notes: use full name when referencing the database objects. e.g. scott.emp;

cat  $SQLPATH/runsql.sh
.
{code start}

#Goal: run SQL in a file, so we can run it with nohup in background,


if [ $# != 1 ]
then
 echo Usage: $0 "SQL file_name"
 echo Usage 2: nohup $0 "SQL_file_name > 0.log &"
 exit
else
 echo $1
 sql_file=$1
fi

#my_path=$(pwd)
#. $HOME/.bash_profile
#echo $my_path

sqlplus -S / as sysdba  <<+++
set wrap off
--set feedback off
set pagesize 0
set verify off
set termout off

set echo off
set verify off
set arraysize 2000
set linesize 5000
set trimspool on
set long 5000
set serveroutput on
set timing on

column filename new_val filename

select '$sql_file'||'_'|| instance_name || to_char(sysdate, '_yyyymmdd_hh24miss')||'.log' filename
from v\$instance;

spool &filename

select sysdate from dual;

@$sql_file

exit;

+++

MAIL_TO=your_name@abc.com
CC=your_group_name@abc.com

tail "$sql_file"*.log | mail -s "SQL $sql_file done at $(hostname) $ORACLE_SID" -c $CC $MAIL_TO

{code end}
.

Ora-4068 Handler

What is an ORA-4068 Error?

An ORA-4068 error occurs when:

• you have a pl/sql package
• someone uses the package
• the package maintains a state
• you recreate the package (deploy a new version), killing their state

How is an ORA-4068 Error Handled?

The first time a pl/sql package is invoked it will throw an ORA-4068 error.  One simply needs to capture this error and retry the call to the pl/sql package.  This will 'clean' a flag on the DB connection and the ORA-4068 error should no longer be thrown.  When using a DB connection pool it is possible to migrate connections between retries and receive a connection that has not had the ORA-4068 'flag' cleared, thus requiring a max of NUM_CONNECTIONS_IN_DB_POOL + 1 retries.

Why should I care about handling ORA-4068 Errors?

By handling ORA-4068 errors you allow the database team to deploy new pl/sql package versions as much as they like without requiring a restart of your application (yay)!

Code Example:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleDriver;
import java.sql.SQLException;
public class ExecutePackageProcedureTwice {
public  static void main(String[] args) throws Exception {
Connection conn = null;
CallableStatement cstmt = null;
long sleepInSecs = 20;
try {
    conn = getConnection();
    cstmt = conn.prepareCall("{call lib_history_pkg.log_book(?,?)}");
    cstmt.setString(1,"1");
    cstmt.setString(2,"1");
    executePkg(conn, cstmt);
    System.out.println("Sleeping for " + sleepInSecs + " seconds...");
    Thread.sleep(sleepInSecs*1000);
    System.out.println("Out of sleep...");
    executePkg(conn, cstmt);
} catch (SQLException e) {
            if(reExecutionRequired(e)){
                System.out.println("ORA-04068 detected - re-executing the package...");
                executePkg(conn, cstmt);
            } else
                throw e;    
} finally {
    try {
        if(cstmt != null)
            cstmt.close();
        if(conn != null)
            conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}
}
private static boolean reExecutionRequired(SQLException e) {
        return "72000".equals(e.getSQLState()) && e.getErrorCode() == 4068;
    }
private static Connection getConnection() throws Exception {
DriverManager.registerDriver(new OracleDriver());
return DriverManager.getConnection("jdbc:oracle:thin:@test_db01.abc.com:1521:test1", "app_user", "abc");
}
private static void executePkg(Connection conn,  CallableStatement cstmt) throws Exception {
System.out.println("Executing the package...");
cstmt.executeUpdate();
conn.commit();
}
}

Reference:

http://yong321.freeshell.org/oranotes/4068.txt

.