Fun with Logon Triggers

Sometimes a process happens too fast to be traced effectively, but it's happening slowly enough to make your users angry.

Here's an example of a logon trigger setup to make a trace happen, we're using it to diagnose an issue with slow response from an ODBC connection, using user account ADMIN_JOSH:

DROP TRIGGER SYSTEM.JOSH_LOGINTRIGGER;
 
CREATE OR REPLACE TRIGGER SYSTEM.Josh_LoginTrigger AFTER LOGON ON ADMIN_JOSH.SCHEMA
BEGIN
   execute immediate 'ALTER SESSION SET tracefile_identifier = MYTRACEFILE';
   execute immediate 'alter session set sql_trace=true';
END;
/

Note if you get the following error, the owner of the trigger needs the alter session priv (in this case system).

ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 2

It's the env, stupid

While performing the following...

$ORACLE_HOME/forms/lib32/make -f ins_forms.mk install

Got the following error:

ld: fatal: library -ljvm: not found
ld: fatal: File processing errors. No output written to frmbld
make: *** [frmbld] Error 1

Apparently no one else has ever reported this problem before...

Turns out the user's env didn't include LD_LIBRARY_PATH. Fixed that, and problem was solved.

It pays to read the documentation

Had an issue where I couldn't get forms builder to work.

Here's the error code:

# frmbld
FRM-91111: Internal Error: window system startup failure.
FRM-10039: Unable to start up the Form Builder.

It should work right out of the box, looks like the build config had changed much, here's the env file used:

ORACLE_HOME=/u01/app/oracle/product/ias10gds
export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jdk/jre/lib/sparc:$ORACLE_HOME/jdk/jre/lib/sparc/native_threads
export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin:/usr/local/bin:/usr/ccs/bin:/bin:/sbin:/usr/local/bin:/usr/bin:/usr/sbin:/usr/ucb:/usr/ccs/bin:/usr/sbin:/usr/X/bin:$ORACLE_HOME/OPatch
export PATH
FORMS_PATH=/u01/app/sct/baninbr2/QUAL/forms/fmb
export FORMS_PATH

Eventually found this documentation post about how to solve the problem. Basically since the last time we used forms builder on this server, the other Oracle homes required a change to NLS_LANG with a UTF8 characterset.

Simply added the following to the above forms builder env file fixed the issue with forms builder:

export NLS_LANG=

Problem solved.

Before you set global_names=true

For all the years that I've been working with Oracle I have not had to deal with the oracle streams capture/apply processes. Other DBA's on my team have had to work with it, but I didn't get the hands on experience. That is until past weekend when I had installed a streams backed process for our identity management product for creation of downstream accounts.

Part of the installation process was to set global_names=true, which as I have now found out is the recommendation from oracle. From what little I know about it, it is for additional security as well as preventing bad things from happening during clones (or so I've read).

It turns out that one of the results from enabling global_names is that you end up getting "ORA-02085: database link FOO.DB.COM connects to BAR.DB.COM" when the database link name does not match the target.

I tried "fooling" it with everything I could think of that didn't involve rewriting any of the code that calls the database link, but nothing in fact worked. The name itself has to be changed to match the target database name (which is clearly stated in oracle documentation...I hoped it was a lie).

This was new to me.

I currently have set global_names=false until I can figure out how much code has to be reworked reflecting all the database link name changes.

So the one thing I should have checked into before setting global_names to true was which database links were going to fail after the switch. And had I done that before, I would have realized the answer would be... all of them. At least now I can push for a new standard for everyone to follow when they create database links.

Fun with ancient clients

User was reporting an error with ODBC after a recent upgrade to 11g:

Unable to connect
SQLState=28000
[Oracle][ODBC][Ora]ORA-01017: invalid username/password; login denied

The user reported, and the tech verified that on this machine they could login with sqlplus but not ODBC on this PC. Multiple accounts were tried.

Interesting test case illuminated the issue: Created user with ALL UPPERCASE PASSWORD. Using this, could connect through ODBC.

The ancient 9i ODBC connection will not send a case sensitive password.

Made the following change, problem solved, user is happy though we probably could use this as an opportunity to force upgrading ODBC clients:

alter system set sec_case_sensitive_logon=FALSE scope=both

Empty variables can be troublesome

Upgrading a 10.2.0.4 ERP test database to 11.2.0.2 today, and shortly after starting the upgrade via DBUA, got the following error:

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

I had chosen to disable archiving as one of the options for DBUA, but was really surprised to see this error. After a little bit of research I found Metalink Doc 96278.1.

Turns out this error was happening because we had log_archive_dest along with log_archive_dest_1 set -- the kicker was it was set to be blank. Here's the relevant info from the pfile:

*.log_archive_dest=''
*.log_archive_dest_1='LOCATION=/u01/app/oracle/admin//arch'

So the trick was to completely remove the entire *.log_archive_dest='' line from the pfile and DBUA was able to proceed with the upgrade without the ORA-16024 error.

Death by a thousand trace files

Found a large number of trace files generated in the /udump admin directory.

Each file looked like the following:

$ cat orcl_ora_12191.trc 
/u01/app/oracle/admin/ORCL/udump/orcl_ora_12191.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    SunOS
Node name:      hostname
Release:        5.10
Version:        Generic_144488-14
Machine:        sun4u
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 389
Unix process pid: 12191, image: oracle@hostname

opiino: Attach failed! error=-1 ifvp=0

Not much help on support.oracle.com or google in diagnosing this issue.

Oracle support was helpful in doing some tracing which proved that these were failures to connect rather than existing connections that were lost.

With that information in hand, we correlated the timestamps of the trace files to timestamps of listener connections. This narrowed our issue down to a specific IP address, and by that a specific application.

Our lead application administrator was able to determine the root cause of the failed connections, an outdated library used in development of the application:

From what I can tell CoreLab Oracle is now dotConnect for Oracle from Devart. They resolved this issue with version 5.70.140.

Whew.