Re: testnap failure

If you're using a logon trigger to turn on tracing for the session, you're changing the session's environment, and therefore you're causing the optimizer to generate new plans.

In your case, this could mean that a "bad" index is not being used in the new plans, allowing the sql to execute successfully.

What you can do to further analyze it, is

• analyze table <table_name> validate structure cascade; or
• analyze index <index_name> validate structure;

to see if there's logical corruptions.

HTH Cheers

Stefan

On Wed, Jun 25, 2008 at 2:31 AM, Tony Adolph <tony.adolph.dba_at_gmail.com> wrote:

> 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
>
>
>

-- 
=========================

Stefan P Knecht
Senior Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht_at_trivadis.com
http://www.trivadis.com

OCP 9i/10g SCSA SCNA
=========================

--
http://www.freelists.org/webpage/oracle-l