OTN Discussion Forums : Beta 3 Bug: using database link freezes ...

mcgoran

Posts: 25
Registered: 01/03/99

Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 9:39 AM

Hi, I'm planning to use XE edition as front end for
some of our data stored in other Oracle databases.
So I depend of database links working properly.

After installing XE Beta 3, I encountered the bug relating to database links usage
that makes all my applications useless.
Please help.
Thanks in advance.

The problem description:
(Test case is at the bottom)

On every page belonging to apex itself or to apex application,
that uses database link and that has the buttons,
when you click the button, in most of cases,
you got the download dialog box saying:
----
You have chosen to open
f
which is a: application/octet-stream
----

If you choose to save to disk you got the file named "f" with:
IBMPC/WIN_NT-8.1.0
single line text in it.

This happens with Firefox ver 1.
With IE and firefox 1.5 you have the same "IBMPC/WIN_NT-8.1.0"
text but on the blank browser screen (or blank frame).

Regardless of browser vendor and version, when this happen,
and regardless what you choose to do (save, cancel, or just back button)
there will be a "lock" for about 40 seconds,
i.e you cannot do anything in apex during this time.
And if you had opened /i url in IE as webdav and pressed refresh you had a freeze during this time. (xdb freezes) !!
And if you had opened sql*plus window and tried to do "select * from dual@databaselink" it would freeze during this time. !!

After 40 seconds everything starts to work normally.
Xdb webdav folder content appears, sql*plus displays the query result.
Until you provoke the next lock the same way.

All this worked fine in Beta 1 version.

Environment:
- XE Beta 3
- Windows XP SP1
- Firefox 1.0.7
- Firefox 1.5 (tested from other PC)
- IE 6.0 SP1
- Remote Oracle databases where links point to: 8.1.7.4 and 9.0.1.3

Test case to reproduce the bug:

1) Create database link in Object browser to any remote Oracle database
2) Click Test button on db link details page
3) Click Finish button when asked
4) Click Ok button when page shows:
"Test Successful the link is working."
"Success Testing Database Link."

At any of these three clicks you might have the behaviour described above.
Sooner rather than later.

I tied with SHARED database links as well ... the same behaviour
I haven't tried to link to Oracle db ver 10.2 yet.

This all looks as bug introduced somewhere in htmldb packages
as you cannot induce the lock by just querying through the db link in sql*plus.

UPDATE!

It never happened before, but just now I have clicked on "Logout" link on apex home page !
and got the same download dialog box. Saved file name is "f-1" this time but the content is the same.
Browser stalled for a while when I tried to log in again.

mtownsen

Posts: 785
Registered: 01/10/01

Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 10:30 AM

in response to: mcgoran

Reply

I just tried this with a 'loopback' database link (i.e link back to the same XE instance). No issues at all. I will try later against a different Oracle database, but I think this is probably more likely to be a problem at the other end, or maybe with the network layer ?

Can someone else test and see if they can confirm ?

mcgoran

Posts: 25
Registered: 01/03/99

Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 11:16 AM

in response to: mtownsen

Reply

Good idea to test with local db link.
I have tested now with local db link and have the same problem.

I don't think it is network problem as I can use db link with sql*plus without any problems.

I have created new empty db user and try with it - the same problems.

It can be something unrelated with db links as I have the same problem clicking Logout link. I have spent whole day yesterday trying to find the best way to reproduce the behaviour then suddenly, when I was just about to post original message I had errors elsewhere (and still have).

Nothing changed on my notebook, apart from MS security patches deploying from company' network.

BTW, Firefox 1.5 get tested from colleagues PC.

I will test this on other PC or in vmware.

Maybe the clue is in content of "f" file. If someone from development team recognizes its content please help.

mtownsen

Posts: 785
Registered: 01/10/01

Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 11:48 AM

in response to: mcgoran

Reply

From what I pasted below I couldn't work out how you got the f file - can you explain to me the screens you are in/steps you took ?

On every page belonging to apex itself or to apex application,
that uses database link and that has the buttons,
when you click the button, in most of cases,
you got the download dialog box saying:
----
You have chosen to open
f
which is a: application/octet-stream

mtownsen

Posts: 785
Registered: 01/10/01

Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 12:22 PM

in response to: mtownsen

Reply

Per the hanging problem - I have now tried with two remote databases (one of which is huge). select count(*) from user_objects took 21 seconds (subsequent queries took 14 seconds) to the large database. select count(*) from all_objects took less than .39 seconds to the smaller database. Both systems are 1000's of miles remote.

I really think this is a local environment issue. But let me know how to reproduce the f file problem and I will look some more

mcgoran

Posts: 25
Registered: 01/03/99

Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 12:37 PM

in response to: mtownsen

Reply

From what I pasted below I couldn't work out how you
got the f file - can you explain to me the screens
you are in/steps you took ?

You simple use those steps I explained around testing the db link and after a few clicks you get the dialog box with title "Opening link" and with the text I have posted and with choice what to do with the file. To open it or to save to disc.

If this happens on final OK button then file name is "www_flow.accept" instead of "f" but the content is the same.

I the meantime I have installed XE on Windows XP SP2 PC. Everything was OK. I didn't manage to replicate the problem even after 50ish clicks. Then I browsed it from my notebook and get the same problem in the same way. Everything is blocked during 40 seconds period even the apex on XP SP2 PC browsed locally. And even more surprising was when I eventually replicated the surprising in local browser on surprising surprising PC. But just once! Here I'm clicking 5 minutes already nothing happen. Mmmm, I will try from third PC to browse this one thus eliminating my notebook completely from the picture.

mtownsen

Posts: 785
Registered: 01/10/01

Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 12:56 PM

in response to: mcgoran

Reply

Can I ask if you are browsing with a language set other than English (United States) ?

mcgoran

Posts: 25
Registered: 01/03/99

Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 1:01 PM

in response to: mtownsen

Reply

Pardon it should display : with title "Opening f"
(spell checker was smart

PC Global setting are English (United Kingdom)
Browser is on en-gb

mcgoran

Posts: 25
Registered: 01/03/99

Re: Beta 3 Bug: using database link freezes apex and xdb, Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 1:18 PM

in response to: mcgoran

Reply

Oh Dear, how many "surprising" words I have posted

(Spell checker drove me mad)
the correct sentence should be:
And even more surprising was when I eventually replicated the same in local browser on XP SP2 PC. But just once!

UPDATE:
In the meantime I have replicated the problem from third machine windforumID=251

mcgoran

Posts: 25
Registered: 01/03/99

Re: Beta 3 Bug: using database link freezes apex and xdb, Re: Beta 3 Bug: u, Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 1:25 PM

in response to: mcgoran

Reply

This is madness. The message posting gets stacked. Let me repeat:

UPDATE:
In the meantime I have replicated the problem from third machine windows forumID=251

mcgoran

Posts: 25
Registered: 01/03/99

Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 1:30 PM

in response to: mtownsen

Reply

Fine! I have managed to mess the message posting twice.

I'm now on linux pc. I hope this will be readable.

lets try again....
UPDATE:

In the meantime I have browsed the XP SP2 pc from third PC on Windows 2000 and Firefox 1.0.7. I manage to have the same problem.
On XP PC itself in local browser we didn't manage to get the error again
(other family member got the chore to click all the time - get tired after 10 mins

Now will try from linux ... but first to send this message

mtownsen

Posts: 785
Registered: 01/10/01

Re: Beta 3 Bug: using database link freezes apex and xdb, Re: Beta 3 Bug: u, Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 1:33 PM

in response to: mcgoran

Reply

Can you check to see if you have a trc and/or core file that matches the times you saw the errors ?

Something like xe_s001_2324.trc
in C:\oraclexe\app\oracle\admin\XE\bdump

And maybe a xecore.log from around the same time
in C:\oraclexe\app\oracle\admin\XE\cdump

mcgoran

Posts: 25
Registered: 01/03/99

Re: Beta 3 Bug: using database link freezes apex and xdb, Re: Beta 3 Bug: u
Posted: Feb 16, 2006 2:54 PM

in response to: mtownsen

Reply

Wow! there are plenty of trc files. Hundreds on my notebook under bdump and udump but only 50 something on XP SP2 PC where I installed XE few hours ago. I need time to look into. Will post the results.

On XP SP2 PC we managed to produce the bug but by using IE 6.0.

Now, browsing both XE installations from linux and Firefox 1.0.7, I did not manage to produce any error when browsing them alone. Only when I stalled them from windows browsers, during the lock period, I managed to get the same error plus there were basic logon dialog box with XDB asking me username password, I have canceled it then got the Unauthorised message in Object browser frame, then after the lock period passed, all was back to normal, and never to produce the bug again.

So it looks much better browsing from linux Firefox!
Mmmm, does this have any connection with windows security patches.
As all windows machines get patched yesterday/today.
Shall I just leave this for few days until they re-issue patch on patch? :))

Anyway lets analyze the trace log files.

Have you got any "success" in producing anything similar?

mcgoran

Posts: 25
Registered: 01/03/99

Re: Beta 3 Bug: using database link freezes apex and xdb
Posted: Feb 16, 2006 4:42 PM

in response to: mtownsen

Reply

This is how .log and .trc files look

There are many of ORA-07445 errors for every single "lock"
trace file produced and after about 45 sec pmon reports dead shared server
which coresponds with duration of lock state.

There is xecore.log file as well showing pretty elaborate list of processes, stack dump etc.
If anyone is interesting to look I can post it here (or someone else).

**** bottom of alert_xe.log *******

Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_s000_3640.trc:
ORA-07445: exception encountered: core dump ACCESS_VIOLATION _npierr+487 PC:0x5F22C3 ADDR:0x4 UNABLE_TO_READ] [

Thu Feb 16 23:29:26 2006
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_s002_1940.trc:
ORA-07445: exception encountered: core dump ACCESS_VIOLATION _npierr+487 PC:0x5F22C3 ADDR:0x4 UNABLE_TO_READ] [

Thu Feb 16 23:29:28 2006
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_s002_1940.trc:
ORA-07445: exception encountered: core dump ACCESS_VIOLATION _npierr+487 PC:0x5F22C3 ADDR:0x4 UNABLE_TO_READ] [

Thu Feb 16 23:29:30 2006
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_s002_1940.trc:
ORA-07445: exception encountered: core dump ACCESS_VIOLATION _npierr+487 PC:0x5F22C3 ADDR:0x4 UNABLE_TO_READ] [

Thu Feb 16 23:30:14 2006
found dead shared server 'S000', pid = (14, 2)
found dead shared server 'S002', pid = (16, 1)

**** Top of xe_s000_3640.trc file *******

Dump file c:\oraclexe\app\oracle\admin\xe\bdump\xe_s000_3640.trc
Thu Feb 16 23:29:20 2006
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Windows XP Version V5.1 Service Pack 1
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:368M/1023M, Ph+PgF:1120M/1694M, VA:1602M/2047M
Instance name: xe

Redo thread mounted by this instance: 1

Oracle process number: 14

Windows thread id: 3640, image: ORACLE.EXE (S000)

*** ACTION NAME:(PAGE 2603) 2006-02-16 23:29:20.726
*** MODULE NAME:(APEX:APPLICATION 4500) 2006-02-16 23:29:20.726
*** CLIENT ID:(HR:1025569074123209) 2006-02-16 23:29:20.726
*** SESSION ID:(22.35) 2006-02-16 23:29:20.726
*** 2006-02-16 23:29:20.726
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump ACCESS_VIOLATION _npierr+487 PC:0x5F22C3 ADDR:0x4 UNABLE_TO_READ] [
Current SQL statement for this session:
COMMIT

PL/SQL Call Stack

object line object
handle number name
1D31B2D4 164 package body FLOWS_020100.WWV_FLOW_LOG
208372F4 9543 package body FLOWS_020100.WWV_FLOW
1D3A3BD0 236 procedure FLOWS_020100.F
2083DDE4 30 anonymous block

**** Top of xe_s002_1940.trc file *******

Dump file c:\oraclexe\app\oracle\admin\xe\bdump\xe_s002_1940.trc
Thu Feb 16 23:29:26 2006
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Windows XP Version V5.1 Service Pack 1
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:369M/1023M, Ph+PgF:1123M/1694M, VA:1605M/2047M
Instance name: xe

Redo thread mounted by this instance: 1

Oracle process number: 16

Windows thread id: 1940, image: ORACLE.EXE (S002)

*** ACTION NAME:() 2006-02-16 23:29:26.764
*** MODULE NAME:() 2006-02-16 23:29:26.764
*** SERVICE NAME:(SYS$USERS) 2006-02-16 23:29:26.764
*** CLIENT ID:() 2006-02-16 23:29:26.764
*** SESSION ID:(38.108) 2006-02-16 23:29:26.764
*** 2006-02-16 23:29:26.764
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump ACCESS_VIOLATION _npierr+487 PC:0x5F22C3 ADDR:0x4 UNABLE_TO_READ] [
Current SQL statement for this session:
COMMIT

PL/SQL Call Stack

object line object
handle number name
1D21B048 43 anonymous block

mcgoran

Posts: 25
Registered: 01/03/99

Re: Beta 3 Bug: using database link freezes apex and xdb, Re: Beta 3 Bug: u
Posted: Feb 20, 2006 7:24 AM

in response to: mtownsen

Reply

This all is still annoying me.

It is definitely not the problem only on my notebook as I replicate the problem on other Windows XP SP2 PC, although only when browsing with IE.

It is definitely not the problem with db links only, as I manage to replicate the problem while clicking on date picker in issue tracker application while browsing from Linux Firefox 1.0.7 to XE on my notebook. Suddenly, instead of date picker I had a popup window the same size but saying something like www_flow..... doesn't exist on this server. Then, exactly the same 45sec lock has happened and the same ORA-07445 with exactly the same error message got reported in the log and trc files. The only difference is apex app and page id reported. The SQL command in all the cases is the same: COMMIT. Maybe that's indicator of something.

The only combination without the problems, for now, is when browsing with Firefox 1.0.7 on Linux to the XE installed on Windows XP SP2 PC. (My notebook is on XP SP1.)

I can't believe this happens only to me. Must be someone else with ORA-07445.

In any case I'd like someone from Oracle team to give me at least rough explanation and/or the direction what to look. At the end there is not much info about ORA-07445 error. Basically it says to contact your customer support representative. I have all log/trc/core dump files. Just need someone to look into.

Thanks,
Goran