testnap failure

From: Tony Adolph <tony.adolph.dba_at_gmail.com>
Date: Wed, 25 Jun 2008 12:31:25 +1200
Message-ID: <4a38d9060806241731q2902146em52e2493c72239cdd@mail.gmail.com>

Hi folks,

I don't know if I have a database or an Oracle BRM (Infranet) problem.

Its been a seemingly random problem over the last year and goes something like this:

A test environment is "rebuilt"
-o- test user's objects all dropped
-o- test user's object re-created from an export from a "reference" schema
-o- test schema has no invalid objects
-o- on the application server the cm and dm are started.

Now we run testnap to check the cm (connection manager) -> dm (database manager) -> database connectivity.

it throws an error:

ERROR: testnap: pcm_connect():: err 56:PIN_ERR_BAD_LOGIN_RESULT, field 0/1:PIN_FLD_ERR_BUF,
        loc 6:PIN_ERRLOC_FLIST, errclass 1:PIN_ERRCLASS_SYSTEM_DETERMINATE, rec_id 0, resvd 0

If I create a logon trigger to trace this user's session to find out what's going on, start and stop the cm/dm testnap works. But when I drop the trigger, bounce the cm / dm and try again, it fails.

The only fix we've found to date is to rebuild an index on SERVICE_T, any index. But note as mentioned above, none of these indexes were invalid,... nothing is invalid.

Everything then seems to run fine for weeks / months, then fails again in the same way... same fix.

I'm struggling with this one,..I can't see why rebuilding any index on this table would
fix the problem.

Is it something to do with invalidating plans? But then, why should a plan cause testnap to fail and why would tracing the session "fix" the problem?

SQL> show parameter cursor

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
cursor_sharing string exact cursor_space_for_time boolean FALSE open_cursors integer 1080 session_cached_cursors integer 0

We're running Infranet 7.0
Oracle9i Enterprise Edition Release (going to soon)

I'd appreciate any ideas


http://www.freelists.org/webpage/oracle-l Received on Tue Jun 24 2008 - 19:31:25 CDT

Original text of this message