ORA-28030: Server encountered problems accessing LDAP directory service

This error may occur while logging into oracle database using oracle internet directory (OID) authentication. First thing to do is to dump actual error:

SQL>alter system set events '28033 trace name context forever, level 9';
regenerate the error
SQL>alter system set events '28033 trace name context off';

if trace output is

kzld found pwd in wallet

KZLD_ERR: Failed to bind to LDAP server. Err=-1
KZLD_ERR: -1
KZLD is doing LDAP unbind
KZLD_ERR: failed from kzldob_open_bind.

then probably there is problem with the OID server connection parameters. Check parameters in ldap.ora:

#mkstore -wrl -viewEntry ORACLE.SECURITY.DN
Enter wallet password:
ORACLE.SECURITY.DN =
#mkstore -wrl  -viewEntry ORACLE.SECURITY.PASSWORD
Enter wallet password:

ORACLE.SECURITY.PASSWORD =


#ldapbind -h -p 389 -D  -w ""

if output produces error then correct the problem. if output is "bind successful" then check forward and reverse DNS lookup of :

#ping 

PING  () XX bytes of data.

...

#host 

it reverse dns is not successful or reverse DNS is different from the one in ldap.ora then you should correct this. A workaround is to put IP and DNS entry of directory server in /etc/hosts file. 

if trace output is

kzld_discover received ldaptype: OID

KZLD_ERR: failed to get cred from wallet

KZLD_ERR: Failed to bind to LDAP server. Err=28032

KZLD_ERR: 28032

KZLD is doing LDAP unbind

KZLD_ERR: found err from kzldini.

then check sqlnet.ora and be sure that wallet path is defined in it:

WALLET_LOCATION=

  (SOURCE=

      (METHOD=file)

      (METHOD_DATA=

         (DIRECTORY=)))

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.

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;

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
/

How to Modify Table or Table Partition Tablespace Attibute

If you are moving a partitioned table from one tablespace to an other with following similar command:

alter table owner.table_name move partition partition_name tablespace tablespace_name ...;

Partitions will be moved to new tablespace but default tablespace attribute of the partitioned table will be remained the old one. You can see default tablespace attribute from the dba_part_tables system view:

select def_tablespace_name from dba_part_tables where table_owner='OWNER' and table_name='TABLE_NAME';

In order to set this attribute to new tablespace use following alter command:

alter table owner.table_name modify default attributes tablespace tablespace_name;

Similar case also occurs when you move subpartitions of a partition with following similar command:

alter table owner.table_name move subpartition subpartition_name tablespace tablespace_name ...;

Subpartitions will be moved to new tablespace but default tablespace of the partition will be remained the old one. You can see it from the dba_tab_partitions system view:

select tablespace_name from dba_tab_partitions where table_owner='OWNER' and table_name='TABLE_NAME' and partition_name='PARTITION_NAME';

In order to alter partition tablespace value use following alter command:

alter table owner.table_name modify default attributes for partition partition_name tablespace tablespace_name;

How to see environmental variables of a running process

You can use unix ps command to see environmental variables of a running process. For example, for oracle asm pmon process:

$ps -ef|grep pmon

oracle XXXXXX  YYYYYY   0  hh:mm:ss       pts/2  0:xx grep pmon
oracle ZZZZZZZ   VVVVVV   0   MMM DD      -    0:xx asm_pmon_+ASM2

$ps eauwww ZZZZZZZ

USER          PID %CPU %MEM   SZ  RSS    TTY STAT    STIME  TIME COMMAND
oracle   .... asm_pmon_+ASM2 _=/..../oraagent.bin MANPATH=.... SUDO_GID=... LANG=.... LOGIN=... SUDO_UID=.... ORACLE_HOME=.... LIBPATH=... LD_LIBRARY_PATH=...

Finding Literal Sqls

a better way to find literal sqls in oracle 10g

Finding literal sqls, example for ORACLE 10g & 11g

SELECT force_matching_signature, COUNT(1)
FROM v$sql
WHERE force_matching_signature > 0
  AND force_matching_signature <> exact_matching_signature
GROUP BY force_matching_signature
HAVING COUNT(1) > 10
ORDER BY 2