OMS, select from dual and library cache latches (and how to diagnose)

This entry was posted in dual latch library cache OMS Oracle on by

Yesterday I went through a problem related to library cache latch contention which I was able to diagnose with the help of Arup Nanda blog. 
It was OMS-Enterprise Manager Grid Control who was blocking dozens of other user sessions… The problem is classified in Metalink under Bugs but described as “not a bug”: Bug 10061837: CHILD LIB CACHE LATCH HELD BY SESSION TRAVERSING V$SQL CAUSES HANG

This led to investigate if we could decrease the library cache latch, and investigation on how to avoid soft parses and how to tune the SESSION_CACHED_CURSOR parameter. Help this time come from a compilation work by Juan Carlos Reyes Pacheco on oracle-l mailing list

Another question was brought by another DBA, what is the influence of “select … from dual” into library cache latches. This time a good answer from Tom Kyte showed the huge gain of having direct assignment of variables in PLSQL instead of using or abusing of the dual table. I copy Tom Kyte’s conclusion: “Why do so many people “select …. from dual” in plsql when a simple assignment would work??”

Oracle RDA modules, profiles and security filter

This entry was posted in Audit Oracle RDA on by

Running the Remote Diagnostics Assistant from Oracle is always a pain with so many questions.

Today I took a bit more time reading the Readme and I found you can avoid most of the questions by using predefined profiles.

 Today I was running on a 10g RAC, so what I did for the setup was (instead of ./rda.pl -S)

./rda.pl -S -p Rac_Assessment

With this I had only questions about the SID, way to connect, if there is OCFS, ASM… but no questions about the 300 other Oracle products…

More information on Metlink note: Remote Diagnostic Agent (RDA) 4 – Profile Manual Pages [ID 391983.1]

Something interesting also, it that you can remove more sensitive data from the RDA report. The readme says:

RDA allows you to remove sensitive data from RDA reports. The security profile can be used to turn on filtering and can be combined with other profiles. For example:
    -S -p DB10g-Security 
This will do the RDA setup for the DB10g profile and turn on filtering through the Security profile. If you want to enable the filtering for an existing setup:
    -X Filter enable

Coming from Linux to an AIX world…

This entry was posted in AIX Oracle on by

Disk usage per directory, current directory:
du -h -> find ./* -type d -prune -exec du -sm {} ;
du -h -> ls | xargs du -sg (if there are only directories)

Autocomplete on AIX
If .profile has: set -o emacs -> ESC ESC (twice escape key)
If .profile has: set -o vi -> ESC (ESC then )

Oracle auditing – answer to auditors

This entry was posted in Audit Oracle Oracle 10g on by
Queries for audit information in Oracle 10gR2. The auditors are there…

They want to know that audit exists and what is audited:

select * from v$spparameter where name like ‘%audit%’; — equivalent to SQL> show parameter audit

select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from dba_STMT_AUDIT_OPTS a;
select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from DBA_OBJ_AUDIT_OPTS a;
select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from DBA_PRIV_AUDIT_OPTS a;
And they want to know who has access to what:
Roles per user:
set pages 1000
break on grantee;
select grantee, granted_role 
  from dba_role_privs, dba_users 
  where grantee=username
  order by 1,2; 
User per role:
set pages 0
break on granted_role
select granted_role,grantee username 
  from dba_role_privs, dba_users
  where grantee=username
  order by 1,2; 
Table privileges per role:
set lines 150
set pages 0
break on privilege on role_name
col role_name for a30
col privilege for a20
col table_name for a60
select grantee role_name,privilege, a.owner||’.’||a.table_name table_name
  from dba_tab_privs a, dba_tables b, dba_roles c
  where a.owner not in (‘ORDSYS’,’EXFSYS’,’MDSYS’,’SYSMAN’,’OUTLN’)
    and a.table_name=b.table_name
    and a.grantee=c.role
  order by 1,2,3;
Privileges given directly to users:
set pages 0
set lines 150
break on username
col username for a30
col privilege for a20
col table_name for a60
select grantee username,privilege, a.owner||’.’||a.table_name table_name
  from dba_tab_privs a, dba_tables b
  where a.owner not in (‘ORDSYS’,’EXFSYS’,’MDSYS’,’SYSMAN’,’OUTLN’,’SYS’,’SYSTEM’,’DBSNMP’,’WMSYS’)
    and a.table_name=b.table_name
    and a.grantee not in (select role from dba_roles)

  order by 1,2,3;