Setting multiple ports for RAC listener

srvctl command for setting multiple ports for local listener. Listener should be restarted later:

srvctl modify listener -l LISTENER -p "TCP:1521/TCP:1522"

RMAN-06216: WARNING: db_unique_name mismatch, RMAN-06218

You may not be able to delete rman backups if they were taken with a different db_unique_name. Although it is a backup for current database rman do not allow to delete it if you  are connected using a different db_unique_name.

RMAN> DELETE FORCE NOPROMPT backuppiece 'PPPPPPPPP';

using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
NNNNN ####### 1   1   AVAILABLE   SBT_TAPE    PPPPPPP

RMAN-06216: WARNING: db_unique_name mismatch - 1 objects could not be updated
RMAN-06218: List of objects requiring same operation on database with db_unique_name  DDDDD
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    PPPPPPP

How this happen?

 * Maybe you switched to physical standby database some time ago, taken backup there and switched back. If this is the case, you can delete these backups connecting rman using standby database.

 * You created physical standby database for database migration, switched to new server and then deleted the old one. If this is the case you don't have any database with required db_unique_name to connect rman  and delete these backups (if you have not converted db_unique_name of new server to old one, after migration). In this case you should identify db_unique_names registered with rman which are no longer exist:

RMAN> LIST DB_UNIQUE_NAME ALL;
 List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
######  DBNAME #########        PRIMARY          DBUNIQUENAME     
######  DBNAME #########        STANDBY          DBUNIQUENAME_STBY           
######  DBNAME #########        STANDBY          DBUNIQUENAME_STBY_TMP      

Then you should unregister db_unique_names which are no longer exist:

RMAN> UNREGISTER DB_UNIQUE_NAME DBUNIQUENAME_STBY_TMP;

database db_unique_name is "DBUNIQUENAME_STBY_TMP", db_name is "DBNAME" and DBID is #########

Now you can crosscheck and delete expired/obsolete backups.

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
/