Friday, November 30, 2012

java.sql.SQLException: unsupported verifier type

You may receive following error while trying to login to database using EUS. This is a known problem with java. You should add following option to java process:

-Doracle.jdbc.thinLogonCapability=o3

or you should simple not use EUS user, instead use a normal database user account.

EUSException: There is no such user in directory

In order to do user operations on a oracle internet directory(OID) entry like granting role or  proxy permission, that entry should have "orclUser" object class:


without this object class following error occurs:

EUSException: There is no such user in directory

that is because OID searches user with a similar search query as:

ldapsearch -h oid_host -p 389 -D "user_dn" -w pasword -b "search_user_dn" -s base "objectclass=orcluser"

if entry does not have orclUser object class then search does not return any result.

Monday, November 26, 2012

How to Capture SQL Error



Here is a way to capture session errors except following errors (modified from here):
ORA-01034 ORACLE not available
ORA-01403 no data found
ORA-01422 exact fetch returns more than requested number of rows,
ORA-01423 error encountered while checking for extra rows in exact fetch
ORA-04030 out of process memory when trying to allocate string bytes.

create table myschema.caught_errors (
  dt        date,              
  username  varchar2( 30),
  msg       varchar2(4000),
  stmt      varchar2(4000)
);


create or replace trigger myschema.catch_errors
   after servererror on database
declare
   sql_text ora_name_list_t;
   msg_     varchar2(4000) := null;
   stmt_    varchar2(4000) := null;
   i int;
begin
  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
  end loop;

  for i in 1 .. ora_sql_txt(sql_text) loop
    if (nvl(length(stmt_),0) + length(sql_text(i))) < 4000 then
        stmt_ := stmt_ || sql_text(i);
    else
        insert into     caught_errors (dt     , username      ,msg ,stmt )
           values (sysdate, ora_login_user,msg_,stmt_);
         stmt_ :=sql_text(i);
    end if;
  end loop;
  i:= ora_sql_txt(sql_text);
  insert into
    caught_errors (dt     , username      ,msg ,stmt )
           values (sysdate, ora_login_user,msg_,stmt_);
end;
/

alter trigger myschema.catch_errors disable;
truncate table myschema.caught_errors;

Friday, November 9, 2012

Exadata Flash Cache Hit Ratio

Here is the cumulative numbers for system physical I/Os and cell flash cache read hits:

SQL>select name,value  from v$sysstat where name in('physical read total IO requests','cell flash cache read hits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                  3052130616
cell flash cache read hits                                        452644513


Flash cache hit ratio is approximately %15

Following sql calculates cumulative stats and ratios using dba_hist_sysstat:

SQL> select c.snap_time, sum(a.value) physical_read_IO_requests, sum(b.value) flash_cache_read_hits,  round((sum(b.value)/sum(a.value) * 100),2) flash_cache_hit_ratio from
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=3343375620) a,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=2923827453) b,
(select to_char(begin_interval_time,'YYYY-MM-DD hh24') snap_time,snap_id from DBA_HIST_SNAPSHOT where instance_number=1) c
where
a.snap_id=b.snap_id and a.instance_number=b.instance_number and a.snap_id=c.snap_id
group by c.snap_time
order by 1
/


In order to find not cumulative but delta stats and ratios:

select c.snap_time, sum(a.value-d.value) physical_read_IO_req_delta, sum(b.value-e.value) flash_cache_read_hits_delta,  round((sum(b.value-e.value)/sum(a.value-d.value) * 100),2) flash_cache_hit_ratio from
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=3343375620) a,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=2923827453) b,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=3343375620) d,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=2923827453) e,
(select to_char(begin_interval_time,'YYYY-MM-DD hh24') snap_time,snap_id from DBA_HIST_SNAPSHOT where instance_number=1 and begin_interval_time > sysdate-3) c
where
a.snap_id=b.snap_id and a.instance_number=b.instance_number
and a.snap_id=(d.snap_id+1) and a.instance_number=d.instance_number
and a.snap_id=(e.snap_id+1) and a.instance_number=e.instance_number
and a.snap_id=c.snap_id
group by c.snap_time
order by 1
/