Re: 1259157 & 1275224

From: Okan CIMEN <okan_at_cimen.org>
Date: Mon, 17 Sep 2001 02:00:48 -0700
Message-ID: <F001.003900CD.20010917013518@fatcity.com>

BUg reports are below..Thank god I dont use NT

Okan

Bookmark Fixed font Go to End Monitor Bug

Bug No. 1275224
Filed 20-APR-2000 Updated 01-JUN-2001
Product Oracle Server - Enterprise Edition V7 Product Version 8.1.6.0.0 Platform Intel Windows NT Platform Version 4.0 RDBMS Version 8.1.6.0.0 Affects Platforms Generic Priority Severe Loss of Service Status Q/A To Development Base Bug N/A Fixed in Product Version 9.0.1

Problem statement:

ORA-4020 EXECUTING AN INVALID STORED FUNCTION
----------------------------------------------------------------------------
----

04/20/00 01:26 am *** I'm filing this bug as the base bug for DE bug#1259157. Turns out this bug *only* reproduce against 8.1.6. I ran it against our current 8.1.6 and it reproduces. . Rep? Client Client Ver. RDBMS Ver. ---- --------- ------------- ------------ N WinNT 6.0.7.2.1 #228 8.0.5 N WinNT 6.0.7.2.1 #228 8.1.5 Y WinNT 6.0.7.2.1 #228 8.1.6 N Solaris 6.0.7.2.1 #0 8.0.5 N Solaris 6.0.7.2.1 #0 8.1.5 Y Solaris 6.0.7.2.1 #0 8.1.6

The exact test case and the step by step instructions to reproduce this bug are given in the DE bug#1259157.
.
Following are my findings on the problem.... When DE gets a block to execute PL/SQL function 'peicnt' is called ( by depxcrun ) which in-turn calls 'pfrrun'. .
pfrrun does a remote procedure call thru 'pfr_do_rcal' which eventually calls
'kpurpc2' which returns with the error ORA - 04020 ( in exc and exct parameters )
.
We then ends up returning from peicnt with ORA - O4020 and an unhandled exception.
.
When we run the same test against other databases like 8.0.5 and 8.1.5 then we
get ORA - 04062 which seems more appropriate. .
Filing this bug at priority 2 at the DE bug is an escalated priority 2 bug.
*** 04/20/00 01:40 am ***

04/20/00 04:45 am *** (CHG: Asg->NEW OWNER)
04/20/00 04:45 am *** For initial investigation...
04/20/00 04:48 am *** (CHG: Asg->NEW OWNER)
04/20/00 10:20 am ***
04/27/00 10:45 pm *** (CHG: Sta->30)
04/27/00 10:45 pm *** Could you please move the testcase to wrvms.us. I tried to get it from wrvms, but it seems the directory is missing for bug 1259157.
04/28/00 02:46 am *** (CHG: Sta->11)
04/28/00 02:46 am ***
05/03/00 10:08 pm ***
05/05/00 05:29 am *** (CHG: Asg->NEW OWNER)
05/05/00 05:29 am ***
05/08/00 10:12 am *** (CHG: Sta->16 Asg->NEW OWNER)
05/08/00 05:16 pm *** (CHG: Asg->NEW OWNER)
05/08/00 06:33 pm *** (CHG: Asg->NEW OWNER)
05/08/00 06:33 pm *** (CHG: Confirmed Flag->Y)
05/08/00 06:33 pm *** 8.1.5.0 alone => ORA-04062 (Correct), 8.1.5.0 + fix for BUG#694149 => ORA-04020 (Incorrect). Possible regression where it seems that we are in circular lock.
06/22/00 02:57 pm *** (CHG: Asg->NEW OWNER)
06/22/00 04:50 pm *** (CHG: Asg->NEW OWNER)
06/23/00 04:24 pm *** (CHG: Sta->11 Asg->NEW OWNER)
08/14/00 11:54 am *** Client is asking for an update on this bug. Thank you.
10/26/00 07:41 pm *** (CHG: DevPri->2)
01/12/01 03:12 am *** New info: Rep? Client Client Ver. RDBMS Ver. ---- --------- ------------- ------------ Y WinNT 6.0.8.12.1 8.1.6/8.1.7 Y Solaris 6.0.8.10.3 8.1.6/8.1.7
01/22/01 09:09 am ***
01/22/01 09:09 am *** ESCALATED
02/03/01 05:55 pm *** (CHG: Asg->NEW OWNER)
02/05/01 09:54 am ***
02/14/01 08:07 am ***
02/21/01 09:29 am ***
03/02/01 08:20 am ***
03/02/01 09:21 pm ***
03/08/01 02:27 am ***
03/09/01 03:02 pm *** I have begun working this bug.
03/09/01 08:07 pm *** select * from obj where object_name= 'FUNCTIONCOUNT'; shows status= INVALID right after: alter table TESTIT modify (col1 number(1)); The object in the library cache shows: LIBRARY OBJECT HANDLE: handle=12beb158 name=SCOTT.FUNCTIONCOUNT hash=93d45471 timestamp=03-09-2001 19:20:05 namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000] kkkk-dddd-llll=0000-001f-003f lock=N pin=0 latch=4 lwt=12beb170[12beb170,12beb170] ltm=12beb178[12beb178,12beb178] ref=12beb160[124f4414,124f4414] LIBRARY OBJECT: object=12beaee0 type=FNCT flags=EXS/LOC[0005] pflags=NST [01] status=INVL load=0 This is logical RDBMS behavior. Furthermore, recompiling the function manually: alter function functioncount compile; will validate and ORA-4020 does not occur. It appears that the procedure builder/ pls should trigger a compilation when it finds an invalid object. SQLPLUS does serial compilation and execution while procedure builder/ PLS seems to do concurrent compile/ exec which induces the deadlock.
03/09/01 11:13 pm ***
03/09/01 11:58 pm ***
03/12/01 01:54 am ***
03/13/01 02:15 pm *** (CHG: Asg->NEW OWNER)
03/13/01 02:15 pm *** Reassigning to RDBMS/DICTIONARY.
03/13/01 02:18 pm ***
03/20/01 11:25 pm *** (CHG: Asg->NEW OWNER)
03/20/01 11:26 pm ***
03/21/01 01:55 am ***
03/21/01 04:13 am *** Verified that the problem reproduces in 8.1.6.3.0 as well as main's 010314 label. Will debug this in main from now on.
03/21/01 06:30 am ***
03/23/01 02:39 am ***
03/26/01 05:42 am ***
03/27/01 12:27 am *** (CHG: Sta->80)
03/27/01 12:27 am *** (CHG: Fixed->9.0.1)
03/27/01 12:27 am *** Merge approved and merged to main(9.0.1) on 27Mar01. Rediscovery Information: The problem was not reproduced from other internal client tools such as pls, only from Procedure Builder. If you encounter symptoms reported for bug 1259157, where the server has raised an ora-4020 when dealing with an invalidated stored pl/sql object, then this is the likely problem. ]] OORA-4020 WAS RAISED WHEN EXECUTING AN INVALID STORED FUNCTION Please note that the fix will now attempt to recompile the invalid pl/sql object, and may return error-free if the compilation is successful, as is the case for the testcase provided.
03/27/01 03:58 am *** ESCALATION -> CLOSED
03/29/01 01:08 am *** Publishing Bug.
04/02/01 03:38 am ***
04/09/01 01:58 am *** Backport to 8.1.7(for PSR 8.1.7.2) completed on 09apr2001.
04/09/01 09:50 pm ***
04/12/01 06:11 am ***
06/01/01 07:38 am ***

Bookmark Fixed font Go to End Monitor Bug

Bug No. 1259157
Filed 04-APR-2000 Updated 23-AUG-2001
Product Oracle Procedure Builder Product Version 6.0.7.2.1 Platform Intel Windows NT Platform Version No Data RDBMS Version 8.1.6.0.0 Affects Platforms Generic Priority Severe Loss of Service Status Q/A To Development Base Bug 1275224 Fixed in Product Version 6.0.8.16.0

Problem statement:

ORA-4020 EXECUTING AN INVALID STORED FUNCTION
----------------------------------------------------------------------------
----

04/04/00 03:32 pm *** Problem description:
Receive ORA-4020 error when executing an 'INVALID' stored function from a procedure. . ORA-4020: deadlock detected while trying to lock object %s%s%s%s%s . Keywords:
FUNCTION INVALID . Testcase location:
WRVMS -> DISK$BUG2: [BUG.1241328] . Testcase step-by-step instructions:
1. Connect to 8.1.6 database, run script functionandtable816.sql to create table TESTIT and funtion FUNCTIONCOUNT.
2. Open Procedure Builder and connect to the 8.1.6 database.
3. Create and Compile the following procedure: . PROCEDURE test IS fcvar number; BEGIN fcvar := functioncount; END; .
4. Run the following statement on the 8.1.6 database to invalidate the stored function: . alter table TESTIT modify (col1 number(1)); . Run the following statement to verify that the function is invalid: . select object_name from user_objects where status='INVALID'; .
5. Execute procedure test from the PL/SQL Interpreter. . Results: . PL/SQL> test; ORA-04020: deadlock detected while trying to lock object SCOTT.FUNCTIONCOUNT ORA-06512: at "TEST", line 4 ORA-06512: at "PU_010", line 1 PDE-PXC002 Program unit execution aborted due to unhandled exception (4020). PL/SQL> . Generic/Port-specific findings:
  Did you test with the latest version? . Rep? Client Client Ver. RDBMS Ver. ---- --------- ------------- ------------ N WinNT 6.0.7.2.1 #228 8.0.5 N WinNT 6.0.7.2.1 #228 8.1.5 Y WinNT 6.0.7.2.1 #228 8.1.6 N Solaris 6.0.7.2.1 #0 8.0.5 N Solaris 6.0.7.2.1 #0 8.1.5 Y Solaris 6.0.7.2.1 #0 8.1.6

Available workarounds:

Compile the function before running the procedure. .
*** 04/04/00 03:33 pm *** (CHG: Sta->11)

04/17/00 07:53 am *** ESCALATED
04/19/00 12:26 pm ***
04/19/00 12:26 pm *** (CHG: Asg->NEW OWNER)
04/19/00 12:26 pm *** (CHG: Asg->NEW OWNER)
04/20/00 01:31 am *** (CHG: Sta->40)
04/20/00 01:31 am ***
09/19/00 02:09 am ***
03/27/01 04:10 pm *** (CHG: Sta->11)
03/27/01 04:10 pm ***
04/09/01 04:28 am *** I have noticed that the same is the issue with forms. But in forms 4.5 (1.6.1) runtime this was not the issue. The stored object was validated when they are invoked, where as in later versions of forms the forms runtime does not validate the stored objects. Can anyone clarify how it is database related issue.
04/09/01 04:28 am *** runtime this was not the issue. The stored object was validated when they are invoked, where as in later versions of forms the forms runtime does not validate the stored objects. Can anyone clarify how it is database related issue.
04/09/01 04:28 am *** validate the stored objects. Can anyone clarify how it is database related issue.
04/09/01 04:28 am *** I have noticed that the same is the issue with forms. But in forms 4.5 (1.6.1) runtime this was not the issue. The stored object was validated when they are invoked, where as in later versions of forms the forms runtime does not validate the stored objects. Can anyone clarify how it is database related issue.
04/09/01 04:28 am *** I have noticed that the same is the issue with forms. But in forms 4.5 (1.6.1) runtime this was not the issue. The stored object was validated when they are invoked, where as in later versions of forms the forms runtime does not validate the stored objects. Can anyone clarify how it is database related issue.
04/09/01 04:28 am *** I have noticed that the same is the issue with forms. But in forms 4.5 (1.6.1) runtime this was not the issue. The stored object was validated when they are invoked, where as in later versions of forms the forms runtime does not validate the stored objects. Can anyone clarify how it is database related issue.
04/09/01 04:28 am *** runtime this was not the issue. The stored object was validated when they are invoked, where as in later versions of forms the forms runtime does not validate the stored objects. Can anyone clarify how it is database related issue.
04/09/01 04:28 am *** runtime this was not the issue. The stored object was validated when they are invoked, where as in later versions of forms the forms runtime does not validate the stored objects. Can anyone clarify how it is database related issue.
04/09/01 04:28 am *** I have noticed that the same is the issue with forms. But in forms 4.5 (1.6.1) runtime this was not the issue. The stored object was validated when they are invoked, where as in later versions of forms the forms runtime does not validate the stored objects. Can anyone clarify how it is database related issue.
04/09/01 04:28 am *** runtime this was not the issue. The stored object was validated when they are invoked, where as in later versions of forms the forms runtime does not validate the stored objects. Can anyone clarify how it is database related issue.
04/10/01 10:01 am ***
04/11/01 07:39 am *** I tried executing forms 6i against 8.0.5 database , it does give error but a different one - ORA-4062.I am not convinced that this is a databsase issue.
04/11/01 10:55 am ***
06/19/01 02:01 am ***
08/23/01 02:40 am *** (CHG: Sta->80)
08/23/01 02:40 am *** (CHG: Fixed->6.0.8.16.0)
08/23/01 02:40 am ***

Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Monday, September 17, 2001 7:25 AM

>
> Could some one send me notes on the bugs 1259157 & 1275224 from metalink.
> I don't have access.
>
> rgds
> amar
>
>
>

Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Monday, September 17, 2001 7:25 AM

>
> Could some one send me notes on the bugs 1259157 & 1275224 from metalink.
> I don't have access.
>
> rgds
> amar
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Okan CIMEN
  INET: okan_at_cimen.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Sep 17 2001 - 04:00:48 CDT