ORA-00600 [4454] error on Oracle 8.1.7.4 1

[HarpreetSachdeva]

Hi there,
I'm getting ORA-00600 [4454] error on Oracle 8.1.7.4, Operating System - AIX. This error is coming while running a packaged procedure.
Kindly suggest us the reason for the error mentioned above and if its a bug then is there any patch available for the same?
Thanks n Regards,

 

[stefanhei]

Metalink says this error occurs due to a internal savepoint# wrap, usually for long running jobs.
Workaround: split job to smaller chunks.

Stefan

 

[DBAwhosaysNIE]

unfortunately ORA-00600 is kind of a generic "error of last resort".

600s usually generate a user process trace file (user_dump_dest in parfile) which may provide some useful information but usually all you can do is open a tar w/OSS and send them the file (udump).

 

[HarpreetSachdeva]

Need advice on ORA-0600 [4454] [4414] [1236] on 8.1.7 (AIX platform) while executing a packaged procedure.
And this packaged procedure is supposed to run 24 X 7.

 

[stefanhei]

Is that procedure an infinite loop? If so you might have hit Bug 1402161 or Bug 2222219. It still occurs in Versions 9i+, so I guess there is no patch available.
The reason for this error is, that during execution of that procedure the internal savepoint# hits an overrun.
If you describe what that procedure is doing we maybe are able to suggest a method that doesn't need your procedure to run 24x7.

Stefan

 

[HarpreetSachdeva]

Thanks a lot for your reply.

Yes this procedure is in infinite loop. As it has to constantly poll the table and pipe.

The working of this procedure is as follows:

Procedure reads messages from one pipe. And if message(s) exist(s) in the pipe, then it fetches the respective row from the table and sends a message to the response pipe.

This problem occurs once this procedure has looped for aound 7 to 8 hours.

Summary is to read and send messages from/TO pipe and perform select on the table in 24X7 fashion.

 

[stefanhei]

You could schedule a job (dbms_job - package) to call your procedure reading the pipe. That way no infinite loop is needed.

Stefan

 

[HarpreetSachdeva]

This procedure starts polling the data table once its status is set online by the calling procedure in a configuration table than it polls the data table in a continues loop untill its status is set to offline in configuration table by the another procedure.

In short this procedure loops and polls the data table for 20 - 24 hours depending upon its status in the configuration table.

Because the requirement was to poll the table on the basis of the status received. So that status can remain true for 20 to 24 hours.

 

[stefanhei]

As said, you should get rid of that loop or reduce duration.
Best solution in my opinion would be a job (maybe more than one).
Or you could stop your procedure after a given condition (time or x loops complteted or...) and immediately restart it (maybe via trigger checking config-table).
If all this isn't possible you should contact oracle-support as DBAwhosaysNIE suggests.

Stefan

 

[HarpreetSachdeva]

We have got response from Metalink and they say that this is the problem with Oracle and as such there is no patch available for this.
So we will have to re-write the PL-SQL block or will have to shift the infinite loop somewhere else as you suggested.

Thanks a lot for your help and prompt response.

Harpreet

 

[Bullrich]

I have this same ora 00600 [4454] error on Oracle 8.1.7.4 running on AIX. I am also running a packaged procedure, and the script that creates the SP runs, then hangs on the execution of the SP.

I have taken stephanhei's advise and decided to break that packaged proc up into four parts, but my question is this: The first part of the script creates the package. How do I carry that through the remaining parts of the script after I break it up? Can I set a variable that reads in the package info from the previous sub-scripts?

I'm sorry, I AM a developer, but not a DBA, and my client's DBA is not familiar with writing PL-SQL, etc. You get the point. So, it seems to be me.

Can anyone help me with this?

 

[stefanhei]

Hi.
Could you elaborate your problem in more detail please?
Is your code also running an infinite loop? If so what's the reason for the loop?
In most cases infinite loops can be avoided by using tirggers or by submitting a job (via dbms_job - package).

The first part of the script creates the package

Do you create the package dynamically? Can the task be carried out by a static package?
To split up the procedure you only need to stop execution and restart, not entirely recreate it.

Stefan

 

[Bullrich]

Stefan,

Thanks. No, this is not running an infinite loop, although the fourth part of the script does create a SP that loops through, making commits every 10000 passes. I have included snippets of the code below. I'd be grateful to hear your thoughts on how to run this.

you can also contact me directly at bullrich@ullrichsys.com

Thanks so much for your assistance..I sincerely appreciate it.

Brian

--------------

----- Script Part One ---------------
CREATE OR REPLACE Package ASAP.PACKAGE_MAP_PFX IS
-- Procedure Prototypes
-- Do a SP --
END PACKAGE_MAP_PFX;
------ End Script Part One --------

-------- Script Part Two ---------
CREATE OR REPLACE Package Body ASAP.PACKAGE_MAP_PFX
/* TBSVER 5.0.0 mappos.sql */
-- do a bunch of stuff to map circuits --
END PACKAGE_MAP_PFX;
----- End Script Part Two --------

----- Script Part Three -------------
declare
-- A bunch of dm_process VARIABLES, then do some stuff --
end;
----- End Script Part Three --------

----- Script Part Four ---------
/*** Stefan, here is where we're getting the problem - See my note below - BKU ***/
create or replace procedure SP_CR22014 as
-- Create a SP ---
END SP_CR22014;
show errors;
--EDIT the below statement
/*** Stefan, Everything above here works. The Script hangs right here, during the execution of the Stored Proc ***/
execute SP_CR22014;
--EDIT the below statement
drop procedure SP_CR22014;
DROP Package ASAP.PACKAGE_MAP_PFX;

spool off;
----- End Script Part Four ---------

 

[stefanhei]

Brian,
The error tells you, that the duration of execution of the procedure SP_CR22014 is too long for Oracle to handle it (which should be more than 24 hours).
Is it possible to split up SP_CR22014? Maybe add a parameter (or two) which limits the number of rows to be touched by it. Then call the procedure again with the next set of parameters. This could be done using a SQL*Plus commandfile, so noone has to sit and wait for completion.
Part 4 would look like this:

-- use a proper datatype to seperate your 
-- data and use it in the WHERE clause
-- in this procedure
CREATE PROCEDURE sp_cr22014 (p_value number)
AS
-- Create a SP ---
END sp_cr22014;
execute SP_CR22014(1); -- handle the first 100,000 records
execute SP_CR22014(2); -- handle the second 100,000 records
execute SP_CR22014(3); -- handle the third 100,000 records
...

When you use parameters correctly there is no need to drop the procedure (and package) as it can be reused.
Also you can look for other ways to speed up your procedure (indexes,...).

Btw, Tek-Tip administration disencourages posting e-mail adresses as this attracts spammers.

Stefan