Several months ago I got a brand new Lenovo Think Centre workstation at workplace with Windows 7 Enterprise Edition (x64). Of course, the first thing I always do is to install a bunch of Oracle software that serves as a playground. So, I installed 10gR2 and 11gR2 and created test databases for both versions. In addition to RDBMS I installed several other tools that I use more or less on a daily basis, or that I need while reproducing end user errors.
Everything worked as expected. At least at the beginning. After awhile, I noticed unsuspected error while connecting locally as sysdba on 11g instance.
I could successfully “connect / as sysdba” for two or three times in a row after fresh OS reboot, then when I tried to connect for third or forth time in a row as sysdba, I got ORA-01031 error out of the blue sky:

Being a member of ORA_DBA OS group, having SQLNET.AUTHENTICATION_SERVICES = (NTS) in SQLNET.ORA, not having any issues with 10g database, just with a 11g, having the same issue while being a standalone machine as well as being a domain member workstation…
I soon run out explanation other than: “…it must be something about BEQ protocol in 11g and/or some OS specific issue, I’ll troubleshoot later…”. Well, a lot later :-).
Today, I noticed Miladin Modrakovic recently wrote a blog post about errorstack tracing. Hmm…could this help me resolve my sporadic ORA-1031 errors while trying to connect locally as sysdba.

First, I issued:

SQL> alter system set events '1031 trace name errorstack level 3; name library_cache level 10';

and then I was trying to connect without a password:

Now, I should have a trace file generated for the ORA-01031 error, so I looked in alert.log for trace file:

...
Wed Nov 02 14:42:43 2011
Errors in file E:\ORACLE\diag\rdbms\test11\test11\trace\test11_ora_1936.trc:
ORA-01031: insufficient privileges

It took me some time glimpsing through file test11_ora_1936.trc as it was about 38MB in size.
When I saw tnsManager entry in error stack, I knew something weird is going on…

What the heck is tnsManager* doing in the trace file, if I’m not using this service at all in my 11g installment.
It turned out that I somehow forgot that I setup TNS_ADMIN variable system wide pointing to the tnsManager home directory. In that directory sqlnet.ora file was not setup properly (NTS was turned off), don’t have a clue why 10g worked despite that and why 11g worked sometimes. The bottom line is that thanks to error stack trace I was able to pinpoint the problem.

Finally, I disabled tracing with:

sql> alter system set events '1031 trace name errorstack off; name library_cache off';

As Miladin pointed out in his blog, errostack tracing is useful also for other common errors, such as ORA-00942, where Oracle reports error and then it’s up to you to pinpoint which permission is missing and this is more often than not a pain in the *ss.

Side note*: if you’re not familiar with tnsManager then you don’t know what you’re missing, it’s a great little gem….