testnap failure

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 9.2.0.6.0 (going to 9.2.0.8 soon)

I'd appreciate any ideas

Cheers
Tony
--

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