A few tips on resolving ORA-29024 in the Oracle database. This error is thrown when Oracle cannot verify the SSL certificate.
Troubleshooting ORA-29024: Certificate Validation Failure
I have struggled with the ORA-29024 error a few times. Here are a few tips for sorting it out.
Start fresh with a new wallet for SSL certificates only, and troubleshoot on a test database. Do not use the wallet that TDE uses or that has the database passwords. Add one certificate at a time. You can set the path to the new wallet as a parameter to various procedures like MAKE_REQUEST in APEX_WEB_SERVICE. In UTL_HTTP, you can set it with the procedure SET_WALLET.
I recommend setting the wallet path explicitly in your code. If the file path is wrong, it will fail with another error, and you can be sure the database picks up the wallet you are debugging.
When you get an error with the package UTL_HTTP, execute dbms_session.reset_package after changes to the wallet. It is my impression that Oracle caches some of the validations and this can cause confusion (see below).
Make it work with a simple test, like using UTL_HTTP.REQUEST against a simple SSL-secured URL:
select UTL_HTTP.REQUEST('https://www.oracle.com/') FROM DUAL;
curl is a valuable tool, use parameter -i to check the headers and look for redirects (301 Moved Permanently) to an address that may have another certificate.
I have a hypothesis on how the database process the incoming certificates: If it is unknown, it will try to verify the parent certificate (upwards in the chain). When it gets to a certificate it has seen before, it is happy and returns OK before the whole chain is verified again. Once during testing at a customer, I had two certificates with the same intermediate parent certificate but different root certificates. One of those two root certificates was missing from the wallet. These things can drive you crazy. Because the next time (in another session), the order changed, and it tried to validate the intermediate certificate whose root certificate was missing. It failed with ORA-29024 even though the same URL worked earlier.
I have seen both recommendations on adding only the root certificate or the whole chain except the last one. On 19c, it looks like only the top-level root certificate is necessary, and I would start with that and make it work with one case before moving on.
Some sites, especially internal ones, use domain certificates (*.example.com). Set the parameter https_host to a string representing the hostname used in the certificate.
If something works that shouldn’t, you may have a picnic error . Perhaps the best tip I can give is this: Don’t try to fix a wallet with lots of stuff in it. Start over with an empty wallet and do one step at a time.