utl_http call fails with external link

[satish]

Dear Erman,

Apps version 12.2.5
DB version 12.1.0.2

Problem Summary
---------------------------------------------------
ORA-29273: HTTP request failed


Problem Description
---------------------------------------------------
SQL> select utl_http.request('https://counter.sss.ss.gov.in',null,'file:/home/orasupp/wallet','WalletPasswd123') from dual;
select utl_http.request('https://counter.ssss.ss.gov.in',null,'file:/home/orasupp/wallet','WalletPasswd123') from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-28860: Fatal SSL error
ORA-06512: at "SYS.UTL_HTTP", line 1491
ORA-06512: at line 1

1)We have provided ACL access
2)We have installed the supplied certificates into the Oracle Wallet

Still we are facing the error

Thanks,
Satish

[ErmanArslansOracleBlog]

We have a bug record and this may be related with your case, because it is recorded especially for 12.1.0.2 version.. So if you did everything properly, please patch Patch 24848928: UTL_HTTP FAILS WITH ORA-29263 IN 12.1.0.2 and retry.. (apply it on test intance first, do your full checks and then plan applying in prod.)
Note that, alternatively, you may apply the latest psu for 12.1.0.2..


References:
UTL_HTTP FAILS WITH ORA-29263 IN 12.1.0.2(Patch 24848928) Linux x86-64 for Oracle 12.1.0.2.0
Bug 24848928 - Utl_http Fails With ORA-29263 in 12.1.0.2 (Doc ID 24848928.8)

[satish]

Thanks for the update erman.

If you remember,earlier we have done this for different link successfully.Below is for your reference.

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/wallet-certificate-addition-td8864.html

Please suggest

[ErmanArslansOracleBlog]

Yes Satish..

There you said : "We tried adding the certificates again and we have added root and intermediate certificates successfully.Now we are not seeing any certificate validation failures..

Go through that thread -> http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/wallet-certificate-addition-td8864.html and do the same diagnostics that I gave you there..

Keep in mind that, there is a bug as I told you in my last update.. So consider applying that patch, if you think you did everything right.

[satish]

Dear Erman,

There are multiple patches for patch 24848928.Can you please suggest which one to apply.

Our version is 12.1.0.2.0

24848928 12.1.0.2.160719
24848928 12.1.0.2.161018
24848928 12.1.0.2.160719ProactiveBP
24848928 12.1.0.2.0

So far we didnt apply any psu in our environment.Please help

Thanks,
Satish

[ErmanArslansOracleBlog]

You need top apply the one that fits to your patch level..

For instance, if you are on 12.1.0.2.160719ProactiveBP, then you need to apply the related one.. the one that is prepared for 12.1.0.2.160719ProactiveBP... I hope you understand what I mean.

[satish]

Hi erman,

Applied patch but issue remain

Thanks,
Satish

[satish]

Hi Erman,

Trace from database

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /u01/SUPPDB_HOME/12.1.0
System name:    Linux
Node name:      erpsupportdb.ttd.com
Release:        3.10.0-514.el7.x86_64
Version:        #1 SMP Wed Oct 19 11:24:13 EDT 2016
Machine:        x86_64
Instance name: SUPPDB
Redo thread mounted by this instance: 1
Oracle process number: 62
Unix process pid: 5249, image: [e-mail] (TNS V1-V3)


*** 2021-03-04 17:27:29.168
*** SESSION ID:(694.28315) 2021-03-04 17:27:29.168
*** CLIENT ID:() 2021-03-04 17:27:29.168
*** SERVICE NAME:(SYS$USERS) 2021-03-04 17:27:29.168
*** MODULE NAME:([e-mail] (TNS V1-V3)) 2021-03-04 17:27:29.168
*** CLIENT DRIVER:(SQL*PLUS) 2021-03-04 17:27:29.168
*** ACTION NAME:() 2021-03-04 17:27:29.168

nhp: 2021-03-04 17:27:29.157487 connect enter counter.tirupatibalaji.ap.gov.in:443
nhp: 2021-03-04 17:27:29.172994 connect return 0x7ff9517c9d48 0 0 elapsed +0 00:00:00.015507
nztysgs_genseed: entry
nztysgs_genseed: exit
nzosr_DefaultConfig: entry
nzosr_DefaultConfig: Renegotiation parameter undefined. Will use default value
nzosr_DefaultConfig: Renegotiation parameter ssl.renegotiate =
nzosr_DefaultConfig: exit
nzos_Initialize: nz initialize status 0
nztwOpenWallet: entry
nzosReadConfFile: entry
nzosReadCertParams: entry
nzosReadCertParams: sslconf.ora location is..
nzosReadCertParams: /u01/SUPPDB_HOME/12.1.0/ldap/admin/sslconf.ora
nzosReadConfFile: sslconf.ora file could not be opened. Continuing.
nzosReadConfFile: exit
nzupawp_apply_wrl_policy: entry
nzupgew_get_environ_wrl: entry
nzupgew_get_environ_wrl: Environment Variable not found or empty value.
nzupawp_apply_wrl_policy: Using wallet locator from caller argument ..
nzdcpgfd_get_file_data: entry
nzdcpaf_assemble_filename: entry
snzdafn_assemble_filename: entry
nzupawp_apply_wrl_policy: entry
nzupgew_get_environ_wrl: entry
nzupgew_get_environ_wrl: Environment Variable not found or empty value.
nzupawp_apply_wrl_policy: Using wallet locator from caller argument ..
nzhewRetrieveencwltBlob: entry
nzdtrsr_store_certreq: entry
nzupawp_apply_wrl_policy: entry
nzupgew_get_environ_wrl: entry
nzupgew_get_environ_wrl: Environment Variable not found or empty value.
nzupawp_apply_wrl_policy: Using wallet locator from caller argument ..
nzdcpgfd_get_file_data: entry
nzdcpaf_assemble_filename: entry
snzdafn_assemble_filename: entry
nzupawp_apply_wrl_policy: entry
nzupgew_get_environ_wrl: entry
nzupgew_get_environ_wrl: Environment Variable not found or empty value.
nzupawp_apply_wrl_policy: Using wallet locator from caller argument ..
nzdcpgfd_get_file_data: entry
nzdcpaf_assemble_filename: entry
snzdafn_assemble_filename: entry
nziropen: entry
nzdfo_open: entry
snzdfo_open_file: entry
snzdfo_open_file: Opening file /home/orasupp/wallet/ewallet.p12 with READ ONLY permissions
nziropen: entry
nzdfo_open: entry
snzdfo_open_file: entry
snzdfo_open_file: Opening file /home/orasupp/wallet/cwallet.sso with READ ONLY permissions
nzirretrieve: entry
nzdfr_reset: entry
nzdfr_reset: exit
nzdfr_reset: entry
nzdfr_reset: exit
nzirclose: entry
nzdfc_close: entry
nzdfc_close: exit
nzirclose: entry
nzdfc_close: entry
nzdfc_close: exit
nzhewencPkcs12wlttoWallet: entry
nzbc_cert_import: entry
nzbc_set_name: entry
nzbc_set_name: entry
nzdk_pubkey_from_obj: entry
nzdcfcx_free_cert_ctx: entry
nzxMKEOU_MapKeyExtToOrclUsg: entry
nzxMKEOU_MapKeyExtToOrclUsg: exit
nzbc_cert_import: entry
nzbc_set_name: entry
nzbc_set_name: entry
nzdk_pubkey_from_obj: entry
nzdcfcx_free_cert_ctx: entry
nzxMKEOU_MapKeyExtToOrclUsg: entry
nzxMKEOU_MapKeyExtToOrclUsg: exit
nzbc_cert_import: entry
nzbc_set_name: entry
nzbc_set_name: entry
nzdk_pubkey_from_obj: entry
nzdcfcx_free_cert_ctx: entry
nzxMKEOU_MapKeyExtToOrclUsg: entry
nzxMKEOU_MapKeyExtToOrclUsg: exit
nzbc_cert_import: entry
nzbc_set_name: entry
nzbc_set_name: entry
nzdk_pubkey_from_obj: entry
nzdcfcx_free_cert_ctx: entry
nzxMKEOU_MapKeyExtToOrclUsg: entry
nzxMKEOU_MapKeyExtToOrclUsg: exit
nzhewRetrieveencwltBlob: exit
nzhewencPkcs12wlttoWallet: entry
nzbc_cert_import: entry
nzbc_set_name: entry
nzbc_set_name: entry
nzdk_pubkey_from_obj: entry
nzdcfcx_free_cert_ctx: entry
nzxMKEOU_MapKeyExtToOrclUsg: entry
nzxMKEOU_MapKeyExtToOrclUsg: exit
nzbc_cert_import: entry
nzbc_set_name: entry
nzbc_set_name: entry
nzdk_pubkey_from_obj: entry
nzdcfcx_free_cert_ctx: entry
nzxMKEOU_MapKeyExtToOrclUsg: entry
nzxMKEOU_MapKeyExtToOrclUsg: exit
nzbc_cert_import: entry
nzbc_set_name: entry
nzbc_set_name: entry
nzdk_pubkey_from_obj: entry
nzdcfcx_free_cert_ctx: entry
nzxMKEOU_MapKeyExtToOrclUsg: entry
nzxMKEOU_MapKeyExtToOrclUsg: exit
nzbc_cert_import: entry
nzbc_set_name: entry
nzbc_set_name: entry
nzdk_pubkey_from_obj: entry
nzdcfcx_free_cert_ctx: entry
nzxMKEOU_MapKeyExtToOrclUsg: entry
nzxMKEOU_MapKeyExtToOrclUsg: exit
nztwOpenWallet: exit
nzosSetCredential: entry
nzosSetCipherSuite: entry
nzosSetCipherSuite: Setting ciphers to AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-SHA:AES128-SHA:DES-CBC3-SHA:RC4-SHA:RC4-MD5
nzosSetCipherSuite: exit
nzos_SetPersona: entry
nzosAddCertChain: entry
nzosAddCertChain: exit
nzos_SetPersona: exit
nzosSetCredential: exit
nzos_Create_Ctx: nz ctx create status: 0
nzos_Handshake: entry
SSL_Info: Handshake before/connect initialization (TLSv1 protocol)
nzospWrite: [Raw write] length = 112
nhp: 2021-03-04 17:27:29.191697 send enter 0x7ff9517c9d48 112
nhp:  > send 112 bytes
kg`@▒)▒*4▒*▒V▒▒T▒O▒▒4&(▒▒^5▒' ▒▒=<▒,▒+▒$▒#▒
▒       5/
[end]
nhp: 2021-03-04 17:27:29.191855 send return 0 0 elapsed +0 00:00:00.000158
nhp: 2021-03-04 17:27:29.191874 flush enter 0x7ff9517c9d48
nhp: 2021-03-04 17:27:29.191887 flush return 0 0 elapsed +0 00:00:00.000013
nzosp_bio_write: processed=112, ret=0
nzospLog_bio:  biowrite:  buf=0x11a882e3, requested=112, actual=112
     0: 16030300 6b010000 67030360 40cb299e       |....k...g..`@.).|
    16: 2a34862a c00156a2 fa54a64f c8fd341f       |*4.*..V..T.O..4.|
    32: 2628beed 5e171a07 35b12700 0020009d       |&(..^...5.'.. ..|
    48: 009c003d 003cc02c c02bc024 c023c00a       |...=.<.,.+.$.#..|
    64: c0090035 002f000a 00050004 00ff0100       |...5./..........|
    80: 001e000d 001a0018 02010301 04010501       |................|
    96: 06010202 04030503 02030303 06030101       |................|
SSL_Info: SSLv2/v3 write client hello A (TLSv12 protocol)
nhp: 2021-03-04 17:27:29.192053 avail enter 0x7ff9517c9d48 60
nhp: 2021-03-04 17:27:29.192341 avail return 1 0 0 elapsed +0 00:00:00.000288
nhp: 2021-03-04 17:27:29.192362 recv enter 0x7ff9517c9d48 4096
nhp: 2021-03-04 17:27:29.192390 recv return 7 0 0 elapsed +0 00:00:00.000028
nhp: <  recv 7 bytes
([end]
nzospRead: [Raw read] length = 7
nzospLog_bio:  bioread:  buf=0x11a84983, requested=7, actual=7
     0: 15030300 020228-- -------- --------       |......(         |
SSL_Alert: read - fatal - handshake failure
SSL_Alert: read - fatal - handshake failure
SSL_Info: error in SSLv3 read server hello A
nzos_Handshake: Handshake returned failure code -1
nzos_Handshake:  Handshake error(1,336032784)- error:14077410:SSL routines:SSL23_GET_SERVER_HELLO:sslv3 alert handshake failure
nzos_Handshake: exit
nzos_DestroyCtx: entry
nzos_DestroyCtx: exit
nhp: 2021-03-04 17:27:29.192574 disconnect enter 0x7ff9517c9d48
nhp: 2021-03-04 17:27:29.192652 disconnect return 0 28860 elapsed +0 00:00:00.000078

Please suggest

Thanks,
Satish.G

[ErmanArslansOracleBlog]

"Alert: read - fatal - handshake failure"
"error:14077410:SSL routines:SSL23_GET_SERVER_HELLO:sslv3 alert handshake failure"

This looks like a chiper issue. Probably, sqlplus doesn't use a strong chiper supported by the web service host.. So, the connection seems failing because a common cipher cannot be agreed on.

we have the following chiper-related line in the trace;

nzosSetCipherSuite: Setting ciphers to AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-SHA:AES128-SHA:DES-CBC3-SHA:RC4-SHA:RC4-MD5

Did you check with the server admin (admin of the web service host)?  Does the server support at least one of the chipers listed above?

[satish]

Dear Erman,

We are further investigating it with note UTL_HTTP fails with ORA-28860 In A 12c Database ( Doc ID 2225262.1 )

Thank You,
Satish

[ErmanArslansOracleBlog]

Okay good.. If you can't do anything on the webservice host, then you may try to do something on the db side.. If you find the missing chipers, then you may find a way(for instance) to install them as documented in the document you follow.

The following Note is also helpful in this way ->

How To Retrieve The SSL Cipher Suites Supported By A Website Using OPENSSL (Doc ID 2285241.1)