Sunday, March 3, 2019

RDBMS/RAC -- gathering AWR, an easy and efficient way

Today, I wanto to share a handy script with you.. It is prepared for gathering AWR reports, especially from Oracle Real Application Clusters (RAC).

This script help us to gather AWR reports without using the necessary tools such as awrrpt.sql, awrgrpt.sql, toad's AWR manager and so on.. It is also RAC aware :)

By using this script, we can just specify the AWR snapshot intervals and generate all the AWR reports between those intervals.

The count of the AWR reports generated, depends on our snapshot interval.

That is, if we have a 1-hour long AWR snapshot interval, and if we use this script to generate all the AWRs for a day; then we will end up with 24 AWR reports each generated for a 1-hour long interval.

The script is as follows;

set trimspool on trimout on
set lines 1500
set echo off
set heading off
set pages 0
set feedback off
set verify off
set trimspool on trimout on
spool generate_awr_reports&1..sql
select 'set heading off'   || chr(10) ||
      'set feedback off'  || chr(10) ||
      'set linesize 5000' || chr(10) ||
      'set trimspool on trimout on' || chr(10) ||
      'spool awr_'|| to_char(instance_number) || '_' || to_char(snap_id) || '_' || to_char(snap_id+1) || '.html' || chr(10) ||
      'select output from table(dbms_workload_repository.awr_report_html(' || to_char(dbid) || ',' || to_char(instance_number) || ',' ||
to_char(snap_id) || ',' || to_char(snap_id+1) || '));' || chr(10) ||
      'spool off'
where instance_number=&1
and snap_id < ( select max(snap_id) from dba_hist_snapshot where instance_number=&1)
order by snap_id;
spool off

So, we just copy this script in to a file and save it as AWR_WRAPPER.sql

Ofcourse we may modify it according to our snapshot interval.

I mean, we may modify the line starting with "and snap_id" according to our needs.

For instance; snap_id between <some_snap_id> and <some_other_snap_id>

Once, we modify the script and save it; we run it one by one for each of our Oracle RAC instances;

For example;

sqlplus /as sysdba
@AWR_WRAPPER.sql 1    -- for instance 1
@AWR_WRAPPER.sql 2    -- for instance 2

By running this script for each of our RAC instances; there will 2 scripts generated (in case we run it in a node RAC env)

These 2 scripts named with the prefix "generate_awr_reports" are actually the ones , which are supposed to be used for gatherin our AWRs.

So , once they are generated, we run them;

@generate_awr_reports1.sql    -- for instance 1
@generate_awr_reports2.sql    -- for instance 2

That 's it.. After the completing of these executions, we will end up with all the AWR reports generated for our snapshot interval in our current working directory.

Pretty cool right? :)

Thursday, February 7, 2019

Promoted to the Trustred rank in Experts Exchange

Helping others understand and optimize their usage of technology...
First in Erman Arslan's Oracle Forum ( , now in Expert-Exchange as well.
Thanks to Expert-Exchange for granting me the Trusted Expert status..

Thursday, January 31, 2019

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 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 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 thing.

Here ->


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-  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 :)

Monday, January 21, 2019

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 ->

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)


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 SESSION SET EVENTS='10933 trace name context off'

Wednesday, December 26, 2018

Oracle Seminar -- Oracle Technologies and Oracle Products + Oracle Job roles

Nowadays, we are giving seminars in universities.

In these seminars, we talk about 3 main subjects.

1) We first introduce Oracle to the audience.
2) Then we continue with the subject -> "How to make a career in Oracle".
3) Lastly, we intdroduce the main Oracle Technologies and Oracle Products to the audience.

It is quite good to talk about these subjects with the university students.. Also, we get good questions while delivering our presentations.

I just wanted to share one of the presentations that we use in these seminars.

This presentation is about Oracle technologies and products.

In this presentation, we take a quick look at the main Oracle technologies and Oracle products..

In addition to that, we try to give a filtered information about the Oracle job roles in demand..

Here is a quick look at the slides ->

-- the contents of the slides are in Turkish -- ofcourse, I will translate them to english -- when I have a time :)