Making a Hot Backup and Doing an Incomplete Recovery

This post shows how to do a hot backup followed by an incomplete recovery. I ran it on an Oracle test database. First I checked that the database was in ARCHIVELOG mode:

SQL> select log_mode from v$database;



Then I decided where to copy the hot backup.

The directory listing below shows a sub-directory called backup. This contains a cold backup I made earlier in case the test (more...)

Tipping point

Today I've been looking at why some SQL is slower in one database than another. There can of course be myriad of reasons for this. Everything from you didn't gather stats to you did gather stats and everything conceivable in between.

I personally find it harder and harder to work out what the optimizer is doing now, more and more intelligence gets put into it with every release which makes it all the more difficult (more...)

Oracle 12c Learning Series: Automatic Table Recovery Using RMAN

Table recovery was possible in earlier release as well. Until previous release, if we wanted to recover a table, we had following options

  • Database point in time recovery (DBPITR)
  • Tablespace point in time recovery (TSPITR)
  • Flashback technology

In Oracle 12c, RMAN has been enhanced to perform recovery of table. We have a new command in RMAN which automates complete process of recovering the table. New process does not affect the existing objects in the database (more...)

Moving to a Different Continent

It’s been a year (and a week) since I moved from the small Israel to the huge Canada. I knew it wouldn’t be easy both personally and professionally, but until you actually do this you don’t know how strange it is. I’d like to share with you some of the professional difficulties I’ve experienced in […]

Uber’s move to MySQL

I was reading a very interesting article on Uber’s move from Postgres to MySQL. I really like it when IT professionals and/or companies take the time to explain their technology decisions.  It’s a brave thing to do, because it’s easy for people to jump on the bashing bandwagon (“Ha ha … Company X chose Y and now they’re bust” etc etc).  It’s the same reason you rarely see detailed customer reference information (more...)

Yes, Host Aggregate I/O Queue Depth is Important. But Why Overdo When Using All-Flash Array Technology? Complexity is Sometimes a Choice.

That’s The Way We’ve Always Done It

I recently updated the EMC best practices guide for Oracle Database on XtremIO. One of the topics in that document is how many host LUNs (mapped to XtremIO storage array volumes) should administrators use for each ASM disk group. While performing the testing for the best practices guide it dawned on me that this topic is suitable for a blog post. I think too many DBAs are still (more...)

EM 13c: Do not change OPatch

In previous versions, it was “best practice” to always get the most current opatch (patch 6880880) from MOS. Unfortunately, with Enterprise Manager Cloud Control 13c, this is problematic at the moment. The reason is that OMS 13.1 is shipped with OPatch 13.6:

[oracle@em13c ~]$ opatch version
OPatch Version:
OPatch succeeded.

Currently, OPatch 13.6 is not available in MOS. Only OUI Nextgen (more...)

Index Competition in #Oracle 12c

Suppose you want to find out which type of index is best for performance with your workload. Why not set up a competition and let the optimizer decide? The playground:

ADAM@pdb1 > select max(amount_sold) from sales where channel_id=9;


ADAM@pdb1 > @lastplan

SQL_ID  3hrvrf1r6kn8s, child number 0
select max(amount_sold) from sales where channel_id=9

Plan hash value: 3593230073

| Id  | Operation                            | Name  | Rows  | Bytes | Cost  (more...)

Oracle Database Cloud Cookbook published


If you want to know more about the Oracle Private/Hybrid Database Cloud using
the latest Enterprise Manager Cloud Control 13c, then this is the book.

Just published on August 5th 2016, and achieved Amazon Best Sellers Rank: #11 in all Oracle Books.  Read more about the book in my LinkediIn Pulse post:

This book will also be available in Oracle Open World.


Porus Homi Havewala
(Oracle Certified Master)
(Oracle ACE Director Alumni)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 2

This is the second part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time instead of burning CPU using a trivial PL/SQL loop (see part 1) the test harness executes a SQL statement that performs logical I/O only, so no physical I/O involved.

In order to achieve that a variation of Jonathan Lewis' good old "kill_cpu" script got executed. In principle each thread performed (more...)

Note to self for blocking locks

Session 1 starts UPDATE and nothing else.

Session 1:
02:53:39 ARROW:(MDINH@leo):PRIMARY> update t set object_id=100;

1 row updated.

02:53:45 ARROW:(MDINH@leo):PRIMARY>
Session 2:
02:53:50 ARROW:(SYSTEM@leo):PRIMARY> update mdinh.t set object_id=2;
Session 3:
02:53:58 ARROW:(DEMO@leo):PRIMARY> update mdinh.t set object_id=200;

Monitor blocking locks

$ sysdba @b.sql

SQL*Plus: Release Production on Sat Aug 6 02:55:03 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database  (more...)

Trying VirtualBox

I have been using  VMware Player to build test virtual machines on my laptop with an external drive for some time now. I used to use the free VMware Server. My test VMs weren’t fast because of the slow disk drive but they were good enough to run small Linux VMs to evaluate software. I also had one VM to do some C hacking of the game Nethack for fun. I got a lot of good (more...)

Basicfile LOBs

I got a call to a look at a performance problem involving LOBs a little while ago. The problem was with an overnight batch that had about 40 sessions inserting small LOBs (12KB to 22KB) concurrently, for a total of anything between 100,000 and 1,000,000 LOBs per night. You can appreciate that this would eventually become a very large LOB segment – so before the batch started all LOBs older than one month were deleted.


How to make Apple Airport wifi routers do remote logging.

This is a blog not related to Oracle products in any way.

Remote logging.
This post is specific to apple Airport Extreme and Express wifi routers. However, in general: if you have multiple (unix/linux) servers, it makes sense to centralise the (sys)logging of these servers, in order to get a better overview on what is happening on these servers. I would want to go as far as saying that if you don’t you are simply (more...)

Oracle 12c: Indexing JSON in the Database Part II (Find A Little Wood)

In Part I, we looked at how you can now store JSON documents within the Oracle 12c Database. For efficient accesses to JSON documents stored in the Oracle database, we can either create a function-based index based on the JSON_VALUE function or on JSON .dot notation. These indexes are useful for indexing specific JSON attributes, […]

No Fun with EM 12c

Confession: I have not been using OEM for decades since there was never a real need for it and it’s starting to take its toll on me.

Throughout the day, I keep getting paged from OEM – Message=Number of failed login attempts exceeds threshold value.

The information provided is utterly useless, e.g. what is the threshold value and what’s the error code.

What would be useful is to provide the SQL used for the (more...)

Java web application based on OAuth2

Java web application based on OAuth2


Last week I've investigated how does OAuth2 protocol works and developed a Proof of Concept (PoC) in Java. In this post I would like to show you how effortlessly develop simple client-server application using OAuth 2.0 standard for authorization of protected resources placed on a server.

Before we start developing our first secured web application with OAuth2 let's understand how it works.

What is it and how (more...)

The Book.

I’ve just added a picture to the right side of this site. It is for a book about SQL and PL/SQL. If you look at the image of the front cover, at the bottom is a list of authors and, near the end, is my name. It’s all finished and at the printers, but it is not out yet – It should be published in the next few weeks.

The British part of me wants (more...)

Monitoring transaction recovery

Sometimes we end up in a situation where our long running transaction is not completing and we are also not sure how much further time it’s going to take. This happened with one of our DBA where they found MLOG to be bloated because of one orphan snapshot entry. Orphan entries are the one where actual site is not registered on master (no entry in DBA_REGISTERED_SNAPSHOTS), but they see entry for MLOGS (entry in DBA_SNAPSHOT_LOGS). This could happen if (more...)

DBMS_STATS – Gather statistics on tables with many columns – 12c update

This is just a short 12c update on my post about gathering statistics on tables with many columns from some time ago.I'm currently investigating the "Incremental Statistics" feature in for a client, which probably will be worth one or more other posts, but since we're still in the process of evaluating and installing various patches it's too early to write about that.As part of the investigation I've noticed a (more...)