RMAN 12c NF – SQL interface in RMAN

Admittedly, I did not realize the benefit at first until there came a need.


### This retrieve the numeric value from PROD1/PROD2, i.e. 1,2 respectively
n=`echo "${ORACLE_SID:(-1)}"`

run {
backup tag 'ARCHIVED_LOG' archivelog all delete input skip inaccessible;
alter database backup controlfile to trace as '/rman_bkup$n/$ORACLE_SID/cf_@.sql' REUSE RESETLOGS;
create pfile='/rman_bkup$n/$ORACLE_SID/init@.ora' from spfile;
create pfile from spfile;


Oracle Education Disastifaction

Just like anything, there is good and bad.

Unfortunately for me, my experience with Oracle education has been predominately bad and former manager experienced the same where he was able to retake a course almost a year later since the original was unrealistic for real world scenarios.

With Oracle education, the most simplistic setup is used, e.g. no ASM, no Grid Infrastructure as it may add a little more complexity to set up?

A (more...)

Experiences with moving to multitenant in

Lately I’ve been upgrading our 11g Standard Edition databases in test environments to 12c Enterprise Edition and also plugging them in to a multitenant container database.
It’s a new technology for Oracle, but I was still quite surprised about the number of issues I faced when trying to plug in an existing non-CDB database. After resolving all these issues it has been quite painless process since then.

In short, upgrading 11g database to 12c pluggable (more...)

cloud control upgrade attempt – falling at the first 3 hurdles

So i finally got round to attempting the upgrade from to 13c, buoyed with the confidence of having done a fresh install on azure i had carefully planned by 'real' upgrade on our system monitoring 150 servers.......

I thought i had done all the research i needed to but perhaps i missed something....

here are the first 3 things that got in the way (and have forced me to suspend the (more...)

Friday Philosophy – Tech Writing Is Like Religous Art

I’m putting together an article for Oracle Scene at the moment – I’ve delayed it for a couple of issues as we wanted the space for other tech articles, but my time has come. And I’m finding it very hard going. Why?

I’m not an expert on religious art (or religion… or art) but one thing I know is that with religious artifacts, especially things like sculpture, furniture, and plaques, they often differ from non-religious (more...)

DML and Bloom

One of the comments on my recent posting about “Why use pl/sql bulk strategies over simple SQL” pointed out that it’s not just distributed queries that can change plans dramatically when you change from a simple select to “insert into … select …”; there’s a similar problem with queries that use Bloom filters – the filter disappears when you change from the query to the DML.

This seemed a little bizarre, so I did (more...)

ASM disks – lsdg compared with the v$asm_diskgroup view

What is the difference between the summaries of disk space on these two systems? Look at the free_usable_file_MB column and free space

System 1

ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU   Total_MB   Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  4194304   72769536   2031032           433152          798940              0             N  DATA_PEX0/
MOUNTED  NORMAL  N         512   4096  4194304  128286720  20004028          2672640         8665694              0             N  DATA_PEX1/
         Group                 Diskgroup     Total  Req'd     Free    (more...)

Can a query on the standby update the primary ?

You would think that (with the exception of the V$ tables which are predominantly memory structures reflecting the state of various parts of the database instance) a query on a read-only standby database would have absolutely no interaction with the primary.  After all, the standby database needs to be able to run independently of the primary should that primary database be down, or destroyed.

But there’s an exception to the rule.  Consider the (more...)

Invisible Bug

At this Wednesday’s Oracle Midlands event someone asked me if Oracle would use the statistics on invisible indexes for the index sanity check. I answered that there had been a bug in the very early days of invisible indexes when the distinct_key statistic on the index could be used even though the index itself would not be considered as a candidate in the plan (and the invisible index is still used to avoid foreign key (more...)

Impact of BREXIT on Oracle price list in the UK

I saw this from an Oracle account manager who I deal with.

Oracle’s regional price lists are based on a constant Dollar based price list, and these are usually recalculated at the beginning of each financial quarter. Given the recent sharp decline in the £ against the $, I’m expecting an increase of roughly 15% for Oracle’s next financial quarter, beginning September.

For illustration, a single Oracle DB EE license has a current GBP list (more...)

Create a SQL Profile to let the Optimizer ignore hints in #Oracle

Something I presented recently during an Oracle Database 12c Performance Management and Tuning class. Hints are a double-edged sword; they may do more harm than good. What if  hinted SQL comes from an application that you as the DBA in charge can’t modify? You can tell the Optimizer to ignore that nasty hint.

One method is to use alter session set “_optimizer_ignore_hints”=true; This will make the optimizer ignore all hints during that session  – also (more...)

Simple Oracle Document Access (SODA)

ordsAt Oracle OpenWorld 2014 I spoke about the existence of the Simple Oracle Document Access (SODA) functionality after a visit to the Demo Grounds. At that point I didn’t know it was called SODA though. I was hoping to get on the beta for the next release of ORDS, so I could try it out, but that never materialised and I promptly forgot about it… 🙂

During my recent whistle-stop tour of ORDS, I was reminded of its (more...)

Modified IO CPU+IO Elapsed Graph (sigscpuio)

Still tweaking my Python based Oracle database performance tuning graphs.

I kind of like this new version of my “sigscpuio” graph:


The earlier version plotted IO, CPU, and Elapsed time summed over a group of force matching signatures. It showed the components of the time spent by the SQL statements represented by those signatures. But the IO and CPU lines overlapped and you really could not tell how the elapsed time related to IO and (more...)

Oracle Midlands : Event #16 – Summary

oracle-midlandsLast night was Oracle Midlands Event #16 and we were lucky enough to get Jonathan Lewis for the third time. I think he now holds the record for the most presentations at Oracle Midlands. Both his sessions were on indexing.

The first session was really a general session about B-Tree and Bitmap indexes. What they are. Where they are not. Ways to reduce the number of indexes or avoid indexes entirely etc. It started with (more...)

What’s Involved in a New STIG Release?

This blog post was prompted by a comment on my website by Chris Peterson, where he asked why the 11g STIG checklist doesn’t work against Oracle Database 12c.  This required a more comprehensive answer than I could give in a simple comment, so that’s what this blog post is all about.  Let’s start off with a bit of an introduction for those of you that are asking, “What the heck is a STIG anyway?” 🙂


SQLplus command line password no longer shown on screen

I was showing some basic sqlplus connection tips to a new DBA starter today and my normal warning about putting the password on the line when calling sqlplus didn’t produce what I expected.

For ever and a day if you have entered a line within a server session such as when using putty or similar to this :-

sqlplus john/N0tverysafe 

and then ‘host’ to the unix shell and run a ps -ef|grep sqlplus you will (more...)

The Problem with autotrace

There are few ways to see the execution plan of a SQL statement. One of these ways is the autotrace option in sqlplus. It is a very easy-to-use feature and people use it quite often. But there is a risk here. The autotrace option doesn’t always show you the correct execution plan. Lately I prepared […]

He’s Making A List

"But I suppose there's a lot to see everywhere, if only you keep your eyes open."
-- Norton Juster, The Phantom Tollbooth

Recently a tweet by Chris Saxon (@chrisrsaxon) with respect to SQL, the language and implementation, started a list of contributions which should cause people to think a bit differently about how the language works and how queries should be structured. The list, found here, is by no stretch of the imagination (more...)


I rarely blog about anything non-technical but after the events last Friday (1st July) I wanted to say something about the pride that I shared with several hundred parents around the country as they saw the effect their offspring created through a living memorial of the terrible waste of life that happened  a hundred years ago on 1st July 1916 when some 70,000 soldiers (a very large fraction of them British) were killed or injured (more...)

All Flash Arrays: Controllers Are The New Bottleneck


Today’s storage array market contains a wild variation of products: block storage, file storage or object storage; direct attached, SANs or NAS systems; fibre-channel, iSCSI or Infiniband… Even the SAN section of the market is full of diversity: from legacy hard disk drive-based arrays through the transitory step of tiered disk+flash hybrid systems and on to modern All-Flash Arrays (AFAs).

If you were partial to the odd terrible pun, you might even say that it was a bewildering array of (more...)