RDBMS -- Analyzing HTTPS / SSL errors -- ORA-29273 HTTP Request failed ORA-28860: Fatal SSL error / Gathering the dump using tcpdump & Analyzing with Wireshark

Recently dealed with a SSL issue in an Oracle Database 11.2.0.4 Enterprise Edition environment.
Issue was appearing when testing a SSL web service.
This SSL web serice was called using UTL_HTTP through an Oracle Wallet.

Example of test command :
select UTL_HTTP.request('https://<url>',null,'<wallet_path>','wallet_password') from dual;

The call was ending with "ORA-29273 HTTP Request failed" and "ORA-28860: Fatal SSL error" errors.

I checked the wallet and it was okay..
Certificates were correct and wallet was accessible..(it could be opened and queried)
The database version was 11.2.0.4 enterprise edition.. It was running on an Exadata Cloud at Customer machine.
The database was created using Cloud GUI of the Exadata Cloud at Customer environment.

Anyways; the "Fatal SSL error" seemed so weird to me, so I decided to analyze it further.

I first checked the IP address of the server that was hosting this web service.
Then I checked the route on OS to find the interface that was used when we called this web service.
After finding the interface, I started a tcpdump on it and reproduced the error. (using sqlplus / as sysdba on the database server)

My tcpdump command was as follows;

tcpdump -s 0 -i bondeth0 -w erman.tcpdump

Note that:
I used the option s because -s 0 will set the capture byte to its maximum.
I used the option -w to create an output file for analyzing with Wireshark.
I used -i to specify the Ethernet interface to capture.

After gathering the tcpdump, I opened the file named erman.tcpdump with Wireshark.

I reordered the contents of the file by the destination ip address and directly saw the SSL connection related traffic & packets..

The issue was there.. 

The server was trying to speak TLS V1.2, but the client (Oracle Database) was not able to handle it.

The real error was "Protocol Version" error.. This means, the Oracle Database which was trying to call the webservice could not handle the TLS 1.2 traffic.

Actually, I had a blog post about another SSL case and in that blog post, I was already mentioning this TLS 1.2 Support of Oracle Database 11.2.0.4 thing.

Here -> https://ermanarslan.blogspot.com/2018/12/rdbms-tls-12-support-and-issues-ora.html

Basically;

Oracle Database needs MESv415 for supporting TLS 1.2 and this MESv415 comes with OCT 2018 DB PSU.. (or Exadata Bundle Patch OCT 2018)

Note that -> as this was an ECC environment, we applied Exadata Bundle OCT 2018.. (DB PSU 2018 had lots of conflicts and incompatabilities with the patches that were applied in Oracle Home which was created by Cloud GUI)

The patch that I applied was Oracle Database Patch For EXADATA(OCT2018- 11.2.0.4.181016)  for Bug 28462975.

Note that: MES is short for RSA BSAFE Micro Edition Suite which is a software development toolkit for building cryptographic, certificate, and Transport Layer Security (TLS) security technologies into C and C++ applications, devices and systems. With release of Oct 2018 PSU, all supported DB versions use RSA BSAFE toolkit MESv415 or greater.

Well.. After patching the database with Exadata Bundle Patch OCT 2018, the issue was fixed.

But actually, I wrote this blog post to show you the analysis part..

As you may already recognize, it is important to do the analysis correctly...

At the end of the day, we analyze the network packets using wireshark.. We also used route command, tcpdump command etc..

Another point that you may derive from this post is that being a good DBA  requires more that just the database knowledge :)

RDBMS -- Startup Upgrade & Startup Migrate + a tip for running utlrp.sql

Today, I want to give a quick info about those fancy startup modes, called startup upgrade and startup migrate..

Actually, there is no big differences between these startup modes and the regular/default startup mode. The only difference in these modes are some events and some initialization parameters that are set  during the startup.

These modes are generally required while doing big and sensitive operations like database upgrades.
By setting those parameters and events, Oracle starts itself in a restricted mode to build a suitable environment for executing upgrade scripts or scripts like catproc and catalog.

Even for running utlrp.sql, it is recommended to be in startup upgrade mode. That is, while compiling the database objects with utlrp, there shouldn't be any concurrent compilation attempts to the related database objects.. When there is a concurrent attempt there, we may end up a deadlock and our invalid count could dramatically increase suddenly. (in order to prevent it, we disable our custom compilation jobs and compilation cron jobs before utlrp run, as well)

To understand this relation better, you can take a look at the utlrp.sql issue that I 've recent dealed with -> http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/ORA-04068-existing-state-of-packages-has-been-discarded-td7030.html

This is an interesting topic, isn't it?

So,  when a database is started in MIGRATE mode, the following ALTER SYSTEM  commands will be set automatically: ( note that, these parameters and events may change according to the database release and version)

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

The release 10.1 added "startup upgrade" in place of the startup migrate.

Beginning with Oracle 10g, the following additional ALTER SYSTEM commands will also
be executed in addition to the setting above:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='' SCOPE=MEMORY;
ALTER SESSION SET EVENTS='10933 trace name context off'