Preface
Are you using UTL_HTTP and recently did a database upgrade?
This article talks about a situation which was induced due to manual error. The database was upgraded to 9208 but was erroneously started with the 9207 version binaries. Everything worked fine until some batch programs started failing with weird ORA-7445 errors.
The ugly ora-7445 error stack...
*** SESSION ID:(131.2450) 2007-10-25 12:16:34.600
*** 2007-10-25 12:16:34.600
Exception signal: 11 0x101ede9ac (pihtipg+030c) 90070000
Registers:
iar: 0000000101ede9ac, msr: a00000000000d0b2
lr: 0000000101ede85c, cr: 0000000024222224
r00: 0000000000000000, r01: 0fffffffffff3bf0, r02: 00000001101fa870,
r03: 00000000ffffffff, r04: 00000001109c67a0, r05: 0000000000000000,
r06: 00000000ffffffff, r07: 0000000000000000, r08: 00000001109c6800,
r09: 00000001109c6850, r10: 0000000000000001, r11: 0000000000000008,
r12: 0000000024222288, r13: 0000000110238c90, r14: 0000000000000040,
r15: 00000001109c6360, r16: 0000000000007fff, r17: 0fffffffffff3cd0,
r18: 0000000000000000, r19: 0000000102d63450, r20: 00000001109c6870,
r21: 00000001109c6820, r22: 00000001109c67d0, r23: 00000001109c6710,
r24: 0fffffffffff7550, r25: 000000011036a2f8, r26: 00000001109c66e0,
r27: 00000001109c6590, r28: 0000000000000000, r29: 00000001109c6570,
r30: 00000001109c6550, r31: 0000000000000001,
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
pihtipg+0328 ? 00000000
spefcifa+08d8 bl _ptrgl
spefmccallstd+0184 bl spefcifa FFFFFFFFFFF5850 ?
FFFFFFFFFFF5488 ?
FFFFFFFFFFF5558 ?
FFFFFFFFFFF46C8 ?
FFFFFFFFFFF46D4 ?
FFFFFFFFFFF46B0 ?
pextproc+008c bl spefmccallstd FFFFFFFFFFF5850 ?
FFFFFFFFFFF5488 ?
FFFFFFFFFFF5558 ?
FFFFFFFFFFF54C8 ? 000000000 ?
peftrusted+00f0 bl pextproc FFFFFFFFFFF4870 ? 000000000 ?
1000E4600 ? 000001620 ?
psdexsp+003c bl 01FD3E44
rpiswu2+0358 bl _ptrgl
psdextp+01e8 bl rpiswu2 700000047A3CD30 ?
2222242400000001 ?
10262DE38 ? 110006668 ?
110416CD0 ? 000000000 ?
000000000 ? 000000000 ?
pefccal+0220 bl _ptrgl
pefcal+002c bl pefccal 800000000 ?
pevm_FCAL+00e4 bl pefcal FFFFFFFFFFF5890 ?
pfrrun+9888 bl pevm_FCAL 11036A2F8 ? 11036A360 ?
peicnt+01b8 bl pfrrun 11036A2F8 ?
kkxuexe+02d4 bl peicnt FFFFFFFFFFF7550 ? 11036A2F8 ?
kkxmpexe+022c bl kkxuexe FFFFFFFFFFF78B0 ?
222224241000673C ?
10009E1AC ?
kgmexwi+0460 bl _ptrgl
kgmexec+08ec bl kgmexwi 110006668 ? 11036CE50 ?
FFFFFFFFFFF80F0 ? 000000000 ?
000000000 ? 11036DEB8 ?
33000000000000 ? 000000000 ?
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-07445: exception encountered: core dump [] [] [] [] [] []
Debugging...
SQL> select count(1) from dba_objects where status='INVALID';COUNT(1)
----------
29
The following test case proved that each time a UTL_HTTP API was invoked, the session was aborting with a ORA-03113.
SQL> select utl_http.request('http://www.company.com') from dual;
select utl_http.request('http://dev11.us.company.com') from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Similar hits in Metalink..
Some things which came up on metalink were Note 399736.1 and bugs 5645944 and 5979559.The Solution..
The solution was simply to use the correct 9208 oracle home for restarting the database and re-create the utl_http package:
SQL> @$ORACLE_HOME/rdbms/admin/utlhttp.sql
Package created.
Grant succeeded.
Synonym created.
SQL> @$ORACLE_HOME/rdbms/admin/prvthttp.plb
Library created.
Package body created.
SQL> select utl_http.request('http://www.company..com') from dual;
UTL_HTTP.REQUEST('HTTP://WWW.COMPANY.COM')
--------------------------------------------------------------------------------
<html>
<head>
<title>Leading manufacturer of chairs & home equipment - Company C
orporation</title>
<Meta name="description" content="Leading manufacturer of chairs.Browse our home
equipment catalog.">
<Meta name="keywords" content="home equipment, wheelchair, wheelchairs,
mobility aids, mobility scooters, motorized wheelchairs, oxygen concentrato
UTL_HTTP.REQUEST('HTTP://WWW.COMPANY.COM')
