SOA administration (from DB perceptive)

Hi Guys,

I am blogging after a long time, nevertheless here is a knol about SOA administration (from DB perceptive) what I gained from an assignment in middle-east 10g project.

For long running queries with long running operations : 


col pct_comp for a10
col INST_ID for 99
col opname for a25
col SId for 99999
col message for a35
col STARTED for a25
col OPNAME for a23
SELECT INST_ID,SID,SERIAL#,
OPNAME, substr(message,1,instr(message,':',-1)) "message",
TO_CHAR(START_TIME,'DD-MON-YYYY HH12:MI:SS AM') STARTED,
sofar, totalwork, round(elapsed_seconds/60,2)  "Elaps(min)", round(TIME_REMAINING/60,2) "TimeRem(min)", round(SOFAR/TOTALWORK*100,2)||'%'
"PCT_COMP" FROM GV$SESSION_LONGOPS WHERE
sofar<totalwork
ORDER BY ELAPSED_SECONDS DESC;

For Blocking locks :


col oracle_username for a10
col terminal for a14
col object_name for a30
col os_user_name for a10
col machine for a15
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l1.INST_ID=l2.INST_ID
and l2.id2 = l2.id2 ;

For finding objects that are locked but not blocked :


col oracle_username for a10
col terminal for a14
col object_name for a30
col os_user_name for a10
col machine for a15
select distinct a.inst_id,a.oracle_username,a.os_user_name,b.machine,b.terminal,c.object_name,a.session_id,b.serial#, a.process
from gv$locked_object a, gv$session b , dba_objects c
where a.session_id=b.sid AND a.object_id=c.object_id
order by 4;

Connections by username :

select stragg(decode(username,null,decode(greatest(cnt,80),cnt,'Total Connections are ','     Other'),USERNAME) ||' '||cnt ) from (select username,count(1) cnt from gv$session  group by cube(username) order by cnt desc ) ;

Total Size of DB used :

select round(sum(bytes)/1024/1024/1024,0) from dba_segments;

Total space available in DB for usage :


select to_char(round(free_mb/decode(type, 'NORMAL',2, 'HIGH',3, 'EXTERN', 1)/1024,0))
from v$asm_diskgroup_stat where name = '<DISKGROUP_NAME>' and state= 'CONNECTED';

Connecting from sqlplus when tnsnames.ora is not editable:
sqlplus scott/tiger@'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <DB Host ip or hostname>) (PORT = <DB Port mostly 1521>))) (CONNECT_DATA = (SERVICE_NAME = <DB_Service_name>)))'

BPEL Title WildCard Search (Source : Gathered.. from web..)

The query gets generated as part of $ORACLE_HOME/j2ee/oc4j_soa/applications/orabpel/console/ngInstanceList.jsp.
To change exact title search to wild card search, here is the change required:

- In $ORACLE_HOME/j2ee/oc4j_soa/applications/orabpel/console/ngInstanceList.jsp, search for keyword called instanceTitle, you should see something like:

String instanceTitleQ = request.getParameter( "instanceTitle" );
if ( instanceTitleQ != null && instanceTitleQ.length( ) != 0 )
{
buf.setLength( 0 );
tmpWhere.setClause( buf.append( n++ > 0 ? " AND " : "" )
.append( SQLDefs.AL_ci_title )
.append( " = ? " )
.toString() );
tmpWhere.setString( 1, instanceTitleQ );
where.append( tmpWhere );
}

You can change it to:

String instanceTitleQ = request.getParameter( "instanceTitle" );
if ( instanceTitleQ != null && instanceTitleQ.length( ) != 0 )
{
buf.setLength( 0 );
tmpWhere.setClause( buf.append( n++ > 0 ? " AND " : "" )
.append( SQLDefs.AL_ci_title )
.append( " LIKE ? " )
.toString() );
tmpWhere.setString( 1, instanceTitleQ );
where.append( tmpWhere );
}


Once it is changed, you can search for Title in BPELConsole using % as wild card character.

Hi Fellas,

This blog is nothing but my experience about SOA,AIA, BPEL, ESB and more correctly my journey from CORBA to SOA.

I shall say that the journey has just begun and I am not even a beginner in this entirely new world.

However during this small period the learning curve was very steep.

I'll try to be correct in this blog, however please feel free to mail me or post any advice that you may feel.

Thanks for reading...