Trace user sessions with login trigger

In order to trace sessions created by a specific user, you can use following login trigger:

CREATE OR REPLACE TRIGGER tgardba.TRG_SESSION_TRACE
AFTER LOGON
ON DATABASE
DECLARE
 sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
BEGIN
  IF (USER = 'USERNAME') THEN
    execute immediate sqlstr;
  END IF;
END TRG_SESSION_TRAC;
/

OBJECT_GROWTH_TREND

DBMS_SPACE.OBJECT_GROWTH_TREND can be used for object size growth trend analysis.For example, for weekly analysis:

SELECT *
FROM
table(
DBMS_SPACE.OBJECT_GROWTH_TREND (
object_owner =>'TABLE_OWNER',
object_name  =>'TABLE_NAME',
object_type  =>'TABLE PARTITION',
partition_name =>'01',
start_time =>NULL,
end_time   =>NULL,
interval   =>to_dsinterval('7 00:00:00')  ,
skip_interpolated => 'FALSE',
timeout_seconds =>NULL,
single_datapoint_flag =>'TRUE')
)
order by 1
/

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.