RMAN exclude

A couple of weeks ago i blogged about moving a large old 10g database to a new disk array, this process is continuing across all of our servers and this week i had to move another 10g one (although for the purposes of this post the version is largely irrelevent).

Our new simplified setup is just 2 filesystems - one for data/redo/undo/control and one for arch/redo/control - this is the default setup if you make (more...)


I kicked off an expdp with the following parameters. I set the filesize parameter but forgot to set the dumpfile parameter to match:


The job produced 1 file with the filesize I had specified:

D:oracle11.2.0adminAKTPRODdpdump>dir andrew.dmp
Volume in drive D is Datas
Volume Serial Number is ECE8-9011
Directory of D:oracle11.2.0adminAKTPRODdpdump
05/15/2015  12:20 PM       999,997,440 ANDREW.DMP
               1 File(s)    (more...)

Quiz Night

How many rows will you return from a single table query if you include the predicate

        rownum > 2

in the where clause.

Warning: this IS a catch question

To make it easier and avoid ambiguity, you may assume the table is the standard SCOTT.EMP table.


Part 2:

This posting was prompted by noticing a note that Dominic Brooks posted a few months ago.

Can you supply a workaround for the little oddity (more...)

applyElasticConfig.sh fails with Unable to locate any IB switches

With the release of Exadata X5 Oracle introduced elastic configurations and changed the process on how the initial configuration is performed. Back before you had to run applyconfig.sh which would go across the nodes and change all the settings according to your config. This script has now evolved and it’s called applyElasticConfig.sh which is part of OEDA (onecommand). During one of the recent deployments I ran into the below problem:

[root@node8 linux-x64]# ./applyElasticConfig. (more...)

Some People’s Kids

"Expectations is the place you must always go to before you get to where you're going. Of course, 
some people never go beyond Expectations, but my job is to hurry them along whether they like it or not."
- Norton Juster, The Phantom Tollbooth

From time to time I find a situation where a developer believes that:

	He or she believes referential integrity is not necessary


	He or she is better at enforcing  (more...)

Understanding Flash: Summary – NAND Flash Is A Royal Pain In The …


So this is it – the last article in my mini-series on understanding flash. This is the bit where I draw it all together in a neat conclusion that makes you think, “Yes! That was worth reading”. No pressure eh?

So let me start with the conclusion first: as a storage medium, NAND flash is a royal pain in the ass.


Why? Well, let’s look back at what we’ve learned in the previous 9 (more...)

Pointing MySQL Read-Replica to new master after failover

Here I am describing a simple scenario where we have a read replica (other than slave) which is currently pointing to master mysql instance.
We have another slave which is also pointing to same master. What we are going to see is, how to point read replica to new master after failover.


To make you familier with environment, we have 3 instances of mysql

mysql_a -> Current master
mysql_b -> Current slave
mysql_c -> read replica  (more...)

Simple C program for testing disk performance

I dug up a simple C program that I wrote years ago to test disk performance.  I hesitated to publish it because it is rough and limited in scope and other more capable tools exist. But, I have made good use of it so why not share it with others?  It takes a file name and the size of the file in megabytes.  It sequentially writes the file in 64 kilobyte chunks.  It opens the (more...)

Instance stats


While reading a posting by Martin Bach on a new buffering option for 12c I was prompted to take a look at another of his posts on the instance activity stats, which reminded me that the class column on v$statname is a bit flag, which we can dissect using the bitand() function to pick out the statistics that belong to multiple classes. I’ve got 2 or 3 little scripts that do this one, (more...)

How to configure Link Aggregation Control Protocol on Exadata

During a recent X5 installation I had to configure Link Aggregation Control Protocol (LACP) on the client network of the compute nodes. Although the ports were running at 10Gbits and default configuration of Active/Passive works perfectly fine the customer wanted even distribution of traffic and workload across their core switches.

Link Aggregation Control Protocol (LACP), also known as 802.3ad is a methods of combining multiple physical network connections into one logical connection to increase (more...)

Additional information on Oracle 12c big table caching

Teaching is on the things I like doing, and currently I am investigating the Oracle 12c features around caching data in the various memory areas. Since the In-Memory (cost) option has been discussed by other far more knowledgeable people I would like to share some findings about the big table caching here.

Some Background

In Oracle 12c you have two additional options to cache information: full database caching and big table caching. The first is (more...)

Troubleshooting Target Status Availability Issues

If you’ve been using EM12c (or any of its precursors for that matter), you’d know that it can sometimes be problematic to troubleshoot an availability issue for targets.  You can see they might be up (hopefully!), down, pending, unreachable or showing a metric collection error, but understanding what’s causing that particular status (and indeed why it can sometimes be wrong) can be difficult at times.

Thankfully there’s a couple of Information Publisher reports (more...)

Parallel Query

According to the Oracle Database VLDB and Partitioning Guide (10g version and 11g version):

A SELECT statement can be executed in parallel only if the following conditions are satisfied:

  • The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the schema objects referred to in the query have a PARALLEL declaration associated with them.
  • At least one table specified in the query requires one of the following:


Here’s a question to which I don’t know the answer, and for which I don’t think anyone is likely to need the answer; but it might be an entertaining little puzzle for thr curious.

Assume you’re doing a full tablescan against a simple heap table of no more than 255 columns (and not using RAC, Exadata, In-memory, etc. etc. etc.), and the query is something like:

select  {columns 200 to 250}
from    t1
where    (more...)


I tried to log on to an Oracle 11.2 test database, which the developers could no longer use, and saw the following error:

MDMDEV1 /export/home/oracle > sqlplus / as sysdba
SQL*Plus: Release Production on Fri Mar 28 09:41:24 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ORA-01075: you are currently logged on
Enter user-name:

I looked in the alert log and saw (more...)

The Right and Wrong Ways to Add a NOT NULL Constraint

I tested these examples in an Oracle 11.2 database. The first one shows how to add a NOT NULL constraint retrospectively. You start by creating a table:

SQL> create table andrew (col1 varchar2(1))
  2  /
Table created.

Then at some point in the future, you add a NOT NULL constraint like this:

SQL> alter table andrew modify (col1 not null)
  2  /
Table altered.

Doing it (more...)

Fourth International NoCOUG SQL Challenge

The Fourth International NoCOUG SQL Challenge has been published in the May issue of the NoCOUG Journal available at http://www.nocoug.org/Journal/NoCOUG_Journal_201505.pdf. Shakespeare’s story of Romeo and Juliet plays out in an American setting. In the NoCOUG version, Romeo sings American songs to Juliet and gives her gourmet dipped swizzled strawberries for her birthday. The bard of Avon must be rolling in his grave.

Entries should be attached as a comment to this announcement. (more...)

Oracle Pre-Built Developer VMs and VMBox

Virtual machines (VM) are not new –it has been around for quite some time, and as a consultant I find myself use them all the time. As a matter of fact, just on my laptop and external drive there are at least 15 or 20 different virtual environment which I use for testing, experimenting, and for creating new blog posts.

The thing with virtual machines that you need to be a little more than just (more...)

Exadata Fast Data File Creation

This post is the result of some testing I performed on Exadata data file creation. You may know that Exadata offloading incorporates SQL optimisations as well as some infrastructure work. For quite some time Exadata allowed the DBA to create data files a lot quicker than on traditional systems. This has been documented before, for example by @mpnsh here

The final comment on his blog entry was a remark that data file creation is quite (more...)

Parallel Execution

This is another little reference list I should have created some time ago. It covers a series of posts on interpreting parallel execution plans and understanding where the work happens.