Month: May 2011

Finding TNSNames Path

Thanks to this post on StackOverflow, I learned a new trick with diagnosing TNS Names issues.  First, you need to download this tool called Process Monitor from SysInternals.

Installing the program is pretty easy.  Simply unzip the program and place it in a good location such as the Program Files directory:

Process Monitor Installed

I always like to create a shortcut in the Start Menu.  If you right click on the Programs menu in your Start Menu, you can choose the open option to open it.  Then, drag with your right mouse button, and you can drop a shortcut in your start menu:

Shortcut for Process Monitor

Once you have Process Monitor installed, you can use the filter to narrow it down to show where it is looking for tnsnames.  Here is what I found that works:

Process Monitor Filter

Add the process name for the process that you want to check.  Use psdmt for Data Mover.  pside for App Designer.  PrcsAppSrv for the App Server launched by psadmin.

Then, if you put tnsnames in for the Path it will show where it is looking for the tnsnames file.

This tool works great for all kinds of situations.  I have also used it to diagnose other connection issues.  I found it was loading the wrong Oracle client on one installation.  I found it was looking in the registry in the wrong place for Tuxedo settings because I had the wrong version.

So, this is a great tool that will help with troubleshooting.

Resources

SysDBA Connection Woes — Simple Fix

I needed SYSDBA privileges in order to extend a data file, and I could not make that happen for some reason.  I felt rather silly when I figured it out.  I’ve got to write it down in case I ever need to come back to it.

First, I opened SQLPlus without logging in:

[sourcecode]
sqlplus /nolog
[/sourcecode]

Then, in SQLPlus, I tried to connect using OS authentication with SYSDBA access.  Here’s the command:

[sourcecode]
connect / as sysdba
[/sourcecode]

The only response I could get was:

[sourcecode]
ERROR:
ORA-01031: insufficient privileges
[/sourcecode]

I jumped through hoops trying to change the password for sys.  I regenerated the file password file.  Nothing helped.  Finally, I ran across this post that led me to check the OS group. All I had to do was put my Windows User into the Windows group for database administration.

I opened the start menu and right clicked on “My Computer”.  Then, I chose Manage from the menu.  In the Manage utility I went to “Local Users and Groups”, then I clicked on the Groups folder.  In the groups folder I double clicked on the “ORA_DBA” group to open the properties.  On the “Members” tab, I added my Windows username to the list.

From there, the connect worked fine.  To complete the story, here’s the resize SQL that I used:

[sourcecode]
ALTER DATABASE
TEMPFILE ‘<path to file>\pstemp01.dbf’
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
[/sourcecode]

For me, the temp space was causing me trouble.  If you want to use a regular tablespace, the syntax is similar:

[sourcecode]
ALTER DATABASE
DATAFILE ‘<path to file>\<data file>’
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
[/sourcecode]

Quick Note: Invalid Version of PSORA

These days the 32-bit / 64-bit Windows situation is quite frustrating!  We need everything to be either 64-bit or 32-bit.  This half and half stuff makes things challenging.

This post is just a quick note to remind you (and me) that even though you are running 64-bit Windows and 64-bit Oracle database, the PeopleTools on Windows is still 32-bit.  Data mover and the App Server both require a 32-bit database client to talk to the database.

If you don’t install a 32-bit Oracle client, you may get an error that reads something like this:

[sourcecode]
PS General SQL Routines: Missing or invalid version of SQL library PSORA (200,0)<
[/sourcecode]

Resources: