Oracle ias/db problem.

[Steven E. Protter]

An application using forms 6.0.x works great client server.

Works great on HP-UX 11.00 32 bit
Oracle 8.1.7.0.0 32 bit.
ias 1.2.2 patch 12 (Includes forms and reports) 32 bit only. Also known as 9ias

Migrate it here:
HP-UX 11.11(11i) 64 bit.
Oracle 8.1.7.4.0
ias 1.2.2 patch 12 32 bit only certified for the platform.

When you start the application connecting to the new server after running a record querey based on list of values or any one of a hundred other database functions the app locks up.

The error code, if you are careful to watch is
FRM-92100, your connection to the database has been terminated.

The database trace sometimes picks up an ORA-0600 error, which means the same thing.

We hung a process trace using the tusc utility on the server and got lots of interesting results in this thread:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x0a49b82b2d63d71190080090279cd0f9,00.html

Please take the time to read this thread carefully if you are going to make OS related suggestions(which I think is the issue). It includes the process traces and kmtune output and patch level information. Anything missing, ask for it I'll post it.

Question: has anybody encountered this problem on HP-UX 64 bit 11.11(11i)? If so, have they resolved it. If requested, I will post up init.ora tnsnames.ora and other configuration files.

The application runs on the web through the I.E. 5.5 browser running jinitiator 1.3.1.6

I'm just looking for the Oracle angle here. Os stuff should go to the other thread. Someone posted it up as an April success story because they thought it was a cool thread. If it wasn't my problem, I'd agree.

SEP

[Ramkumar Devanathan]

Hi Stephen,

I had been reading the thread and looking at the varied responses, had to point it out - it is not a success story but it is on the whole a good example of how many varied insights you get from the forum users... which I've pointed out to dan, although you haven't tasted much success till now. my sympathies are with you on that count.

- ramd.

[Steven E. Protter]

I think its a success because of the amount of resources brought to bear to solve a problem that effects multiple HP customers. It will be solved. I'm confident, I have almost as much ammunition to deal with this issue as the Pentagon. itrc has added tools to the hunt for a solution.

SEP

[Brian Crabtree]

Since you are looking for the Oracle angle =)

What changes were made between the two systems with Oracle besides the version (character set changes, etc)?

How was the database changed to the new version (offline backup, online backup with PITR, export/import)?

What is the ORA-00600 error posted?

Thanks,

Brian

[Steven E. Protter]

The only "changes" were the required database version upgrade. This was necessitated by problems converting the database data from 32 bit word size to 64 bit word size.

Not even the instance names were changed, though the tnsnames.ora and network files were changed to reflect the IP address of the new server.

SEP

[Steven E. Protter]

The ORA-600 was picked up by a database trace. the cool data on this issue is in the thread link in the original post.

SEP

[Brian Crabtree]

I dont see the actual ORA-00600 error message posted in the other side, that was what I was looking for. Also, have you considering tracing the database to look for what Oracle is doing at the time the process crashes. We have been having some problems with ORA-00600 [12333] that might be related to UTF8 character sets with different applications.

Regarding your word-size change, you did run the utlirp.sql script in ?/rdbms/admin to rebuild all of the scripts. This is not done as part of the migration as far as I know. (Just checking).

Also, it looks like your maxtsiz is a bit small from the kmtune report. Not sure how this would affect the 32bit IAS.

Thanks,

Brian

[Steven E. Protter]

Brian!

That's exactly what we're getting. !!!!

The funny part is we don't get it on the 11.00 32 bit servers.

Post a workaround or solution and earn a rabbit.

Please. The more details the better.

SEP

[Steven E. Protter]

Yeah, the word size conversion was successful. It only took two or three weeks to work the issues out.

We are certain it worked an we have no invalid objects on the database.

SEP

[Brian Crabtree]

Well, that's just it. We don't really know. The [12333] error is a network error, so Oracle goes as far as to say "This is a network error, you should fix that". We only see if on 8.1.7 64bit databases with UTF8. One option would be to change the character set to a non-utf8 (WE character set if possible) or move to 9i. The problem has always been intermittent.

As an FYI, one database that we were getting this error on we moved to WE8ISO8859P1 and the problem has not reoccured in the past two weeks (was about 1-2 times a day).

Hope this helps,

Brian

[Steven E. Protter]

Update: We have vastly increased shared memory resources, doubled nproc and other parameters affected by this.

Moved the new kernel into production. No effect.

Really, really, really frustrating. Please keep trying folks.

Does anyone want to see a database trace?

Attached.

[R. Allan Hicks]

You might try turning the tracing on for the 12333 error. Use netmgr to set to admin or even support trace levels.

One of the more annoying features that I have found with the 9i networking stuff is the service name is now case sensative.

Also note in Tom Kyte's 8i book (Expert one on one Oracle pg 3590, he warns that doing an exp from a db with one character set and an imp into a db with a different one can cause unexpected character conversions. Since you moved to a non UTF-8 makes me wonder.

-hmmmmmmmmm

[Brian Crabtree]

Have you thought about moving from 8.1.7.4.0 64bit to 32bit, or upgrading to 9.2.0.3.0 64bit? Possibly changing the character set if you are not locked into Unicode might resolve the problem as well.

Brian

[Steven E. Protter]

Brian,

can you elaborate. I'm not lead on this issue, and I don't understand what you are recommending.

SEP

[Brian Crabtree]

The major difference between 64bit and 32bit is the amount of allowable memory. Unless you need over 2g in SGA, or the application requires the 64bit wordsize to be used, you can probably move back to 32bit without a major issue (new binary installation, rebuild the controlfile with the new Oracle home, and run the utlirp.sql script to recompile the objects). This would probably be the easiest option to see if that resolves the problem.

The second option is to change the character set being used. UTF8 is a unicode character set, which encompasses most of the available characters that can be put on the database. You would need to define which characters are going to be put into the database (ie: English only requires US7ASCII, Western European (all character sets starting with WE) hold most of the special characters from German, Spanish, French, etc. The following is a metalink document of all of the character sets with what they carry, as well as bit sizes, etc.

http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=226492.1

To do this, you would want to export your database with current character set, rebuild the database with the new character set, and import with NLS_LANG set to the new character set. This should move your data into the new character set.

The last option would be to move the database off of 8i entirely, and move to 9i. I would recommend 9.2.0.3.0. This version has appeared to be stable on the systems that we have placed it on. There is a learning curve associated with it if you are used to using 'svrmgrl' or the 'internal' connection, however it is not incredibly difficult to get used to.

You can also run specific traces from Oracle to define the problem, however in my experiance in trying to diagnose the problem, there wasn't a single cause that generated the error, and it almost always looked like it was a fault of the application.

Thanks,

Brian

[Brian Crabtree]

Actually, here is a better link (dont assign points, this is just an addition to above). This one shows the languages associated with each character set.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=62421.1&p_database_id=NOT

Brian

[Steven E. Protter]

Update: We have successfully run an application est in X/Windows motif and the results are quite interesting.

In motif using oracle runtime, which is essentially client/server mode on two servers, the application gave the exact same troublesome behavior.

This eliminates jinitiator as a cause because you don't use jinitiator in that mode.

It seems to eliminate forms as an issue because the problem comes up in this mode. We are next going to take the form on the bad server and connect to a good database running on one of our 11.00 servers. If the problem does not occur its definitely a datbase problem on the bad server. If the problem recurs, then we know for sure its a forms problem on the bad servers.

SEP

[Steven E. Protter]

Testing finally indicated the problem was with forms, not the database.

We cross tested a good forms instance against the potential trouble database and a the potential bad forms install against a known good database.

The results pointed to forms, one of Oracle's shared libraries didn't get created properly. When Oracle sent us theirs the problem stopped happening.

Now Oracle's scratching their head trying to figure out why their library got corrupted. When Oracle finally got around to actually READING the tusc output and the database trace it pointed straight to the Oracle provided libraries.

I don't think anything else is going to come out of this thread other than an Oracle bug fix or me posting the solution. I'll get back to you.

SEP