Troubleshooting ORA-00600

Normally, the ORA-600 error will be displayed with a set of arguments. For example,

ORA-00600: internal error code, arguments: [12333], [0], [101], [2], [], [], [], []

1) You can then log in to metalink and use the ORA-600/ORA-7445 Troubleshooter
2) You have to provide the first argument of the error in this case it is 12333. Optionally you can choose your database version and submit.
3) Then the metalink reports the cause of the error, impact (any data corruption) and also it provides suggestions
4) But the better approach would be to raise a service request with oracle, because it is an internal error and only oracle technicians can interpret the trace files

Well, I have seen only two ORA-00600 errors this year, which needed no Oracle support. 

On the first instance, a DBA ran a create database script on an already running instance.  Then the instance started throwing ORA-00600 for few queries.  They bounced the instance and the issue was solved. Not sure if bouncing an instance would be a right approach.  What happens if the database refuse to come up? Luckily we did not face that issue.

On the second occasion, a java program calling a hierarchical query went in a nested loop causing ORA-00600.  The java program was later identified and fixed, thus resolving the ora-00600 error.

Of-course, we logged an service request with oracle on both occasions.

Every ORA-00600 is a different issue, though the error looks same.  The better approach would be to contact metalink support always.

Addendum on 27 March 2008 :

Since there are lot of people are hitting this page, I though it might be useful to give the URL of the ORA-600/ORA-7445 Troubleshooter (You need metalink access) :

https://metalink.oracle.com/metalink/plsql/f?p=130:14:782470108129457391::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,153788.1,1,1,1,helvetica

You can also refer to the following notes on metalink

Note:146580.1 Additional ORA-600 related information.
Note:211909.1 Additional ORA-7445 related

UTL_HTTP

Well, we all know about simple web application architecture.  It will have a servlet/jsp calling an backend procedure or function.  Is it possible to reverse it? i.e. can you call a servlet from a database procedure/function.  Yes!  It is possible with UTL_HTTP package in oracle.  Here is a sample :

DECLARE
 REQ_1 UTL_HTTP.REQ_1;
 RESP_1 UTL_HTTP.RESP_1;
 VALUE VARCHAR2(1024);
BEGIN
  REQ_1 := UTL_HTTP.BEGIN_REQUEST('HTTP://www.yoursite.com:8988/servletpath/servlet');
  UTL_HTTP.SET_HEADER(REQ_1, 'USER-AGENT', 'MOZILLA/4.0');
  RESP_1 := UTL_HTTP.GET_RESPONSE(REQ_1);
  LOOP
  UTL_HTTP.READ_LINE(RESP_1, VALUE, TRUE);
  DBMS_OUTPUT.PUT_LINE(VALUE);
  END LOOP;
  UTL_HTTP.END_RESPONSE(RESP_1);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
  UTL_HTTP.END_RESPONSE(RESP_1);
END; 

One of my developer colleague wanted to test this functionality, so we tested this on 10g.  And it really worked nicely.

The file is too large

Last post I mentioned about how I installed VMplayer.  I ran into a small issue while opening the OpenSUSE Linux image.  The size of the image is around 2.5 GB.   So, the VMPlayer gave me an error like “The file is too large”.  

Luckily for me, I came across this forum, on which the following suggestion was made

Try adding
diskLib.sparseMaxFileSizeCheck= “false”
to the vmx-file – maybe that helps.

I added diskLib.sparseMaxFileSizeCheck= “false” to my vmx file and bingo, the image opened without any further issues!! 

Now I have a fully functional Linux.  It comes with lot of tools like an browser – which can actually connect to Internet without any configuration!, games, chat applications, Office editors, pdf reader!!  Its great!   

Now I need to install oracle on it and play around with it!

VMware

VMware is a revolution.   Gone are the days where people use to run dual operating system, they choose which operating system at boot time.   Now with this virtual machine technology, you can run an operating system within another.  Cool, isn’t?

All you need is a VMPlayer.  Then download a pre-configured OS images available on the internet.  For example here.

Last night, I downloaded VMPlayer and OpenSUSE 10.2 Linux image.   Its nice to see I have a Linux OS running inside my Windows XP.  Very nice indeed!

VM is getting very big, thats why oracle has launched its own VM called Oracle VM.

DISPLAY

Today, I was trying to run runInstaller on an SUSE LINUX installed on VMWare.  The well familiar oracle GUI failed to come up.   Actually I was trying things as follow :

1. Log on to the LINUX as root
2. su – oracle
3. ./runInstaller.sh

Then I thought, it could be because of DISPLAY environment variable.  Rightly so.  Then I set DISPLAY as :0.0.  Even then runInstaller failed to bring up GUI.  Even xclock didn’t come up.

 Then, I logged off as root and logged in as oracle.  Bingo.. it worked nicely! 

 So, some more extra env variables are need than just DISPLAY.  I need to figure out those!

Deadlock

“A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work.”

That is how oracle concept manual describes Deadlock.  Today I encountered an Deadlock.  No, no, no…  not on the database I support, but on the road!!.  Today it rained a lot in Chennai and thus resulted in traffic jams everywhere.   I was comming home from office in a narrow road – room for only two small cars to go in parallel.  Two small trucks comming against each other got stuck.  They can’t move either backward or forward.  Luckily, one of the guy showed me a small narrrow road (lane??) which took me to the highway. 

“Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock”

There was no such thing on the road.  I hope someone would have rolled back one of those trucks and solved the deadlock by this time now

11g…

At last I started downloading Oracle 11g for windows today.  The download is around 1.7 GB.  My remaining free GB quota for this month is 1.2 GB .  Luckly only yesterday I noticed that I have unlimited download from 02.00 AM – 08.00 AM.  I can’t really get up that early .  So, I first downloaded “Download acclerator” – ofcourse before 08:00 AM   - It has a cool feature of “Pausing” and “Resuming” downloads.  I downloaded 13MB of 11g and then paused it.  Its a total of 7hrs download.  I am planning to download 2 hrs per day.  Hopefully, I will get my hands on 11g before this weekend!! I am looking forward for it… 

More posts later!

Summon the DBA

Well, in my opinion the DBAs are the poorest of poorest guys – No, no, no… I am not talking financially here .  If there is an performance issue in the application, the first thing they do is to summon the DBA incharge and ask him to look “whats going on” in the database.  They stop thinking about the other tiers – these days its quite common to find a 5 tier 6 tier application. Their “instinct” tell them to look “something” in the database. 

Especially people with a “bit” of oracle knowledge start sitting on DBAs head and firing questions after questions 

“Have you run the statspack report?“

“Can you put the trace on and see whats happening?“

hmmm,  DBA’s life is tough afterall. 

The DBA as usual run all the script he has on hand, only to find out the database is working fine. 

About 70-80% of such incident I faced were becasue of application issue or a non-database issue.  But it doesn’t stop the people from yelling “Summon the DBA…“

Anyways, a good DBA must be prepared for this kind of situation.  He should get all his tools – monitoring script, performance stats scripts – ready to face this situation.  Some uses nice fancy GUI tools, but others like me uses sqlplus.  I usually have few useful sql scripts to check the v$session, v$session_longops excetra excetra.  It saves lot of time and energy.

Hello world!

Hi there!! I am Mani.  I have around 8 Years of IT experience.  I started as a Web programmer, then a Java programmer, a PL/SQL programmer and finnally - at least for now – an Oracle DBA.   Throughout, I have been working on Oracle one way or other.  Last year I started reading blogs of some oracle gurus like Tom Kyte, Jonathan Lewis, HJR.  I thought I have read enough to start a blog of my own – just kidding  

Hopefully I try to put in informations I learn about Oracle, non-oracle stuff which I feel useful to others.  I swear to keep the blog updated regularly.

Thanks for reading!