Oracle Midlands : Event #4 – Summary

What a cracking Oracle Midlands event!

The evening started with a session on “Designing Efficient SQL” by Jonathan Lewis. The first few slides prompted this tweet.


When someone asks me a question about SQL tuning my heart sinks. It’s part of my job and I can do it, but I find it really hard to communicate what I’m doing. Jonathan’s explanation during this session was probably the best one I’ve ever heard. Rather than trying (more...)

The value of audit_trail=DB,EXTENDED

I was recently reading a blog entry by Dominic Brooks regarding auditing and I was intrigued by the line referring to the audit_trail parameter being set to DB, EXTENDED

Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available.

Nothing wrong with that and straight from the manual but I was surprised that they were CLOBS. However on looking at them they (more...)

Presenting at UKOUG Tech14 Conference (Ian Fish, U K Heir)

I’ve been lucky enough to present at various conferences, seminars and user group events over the years in some 20 odd countries. But somewhere I’ve never quite managed to present at before is the place of my birth, the UK. Well this year, I’ve decided end my drought and submitted a number of papers for the UKOUG Tech14 Conference and […]

Create Physical Standby Database using RMAN Restore

Normally, when I create physical standby database, the configuration has the same directory structures and name values as production with the exception of db_unique_name.

But this time was not the case as shown below.

ANGEL:(SYS@xmenstby):PHYSICAL STANDBY> show parameter name

NAME                      TYPE        VALUE
------------------------- ----------- ----------------------------------------
cell_offloadgroup_name    string
db_file_name_convert      string      /oradata/xmenprod, /oradata/xmenstby
db_name                   string      xmenprod
db_unique_name            string      angel_xmenstby
global_names              boolean     FALSE
instance_name             string      xmenstby
lock_name_space           string
log_file_name_convert     string      /oradata/xmenprod, /oradata/xmenstby
processor_group_name      string
service_names             string      xmenstby

I (more...)

MySQL on-premise to Amazon RDS migration tips

Things to watch and do when migrating MySQL databases from ‘on-premise’ to Amazon AWS RDS

  • Not all versions of databases can be migrated to RDS. Especially if you want to do a 0 downtime migration. Make sure you know which versions are possible, at this writing Amazon announced that it  will support any old version of MySQL 5.1 and above.
  • In a zero downtime migration to Amazon RDS you work with mysqldump or (more...)

What books by what authors should I read?

I’ve been asked the same question by multiple people, around what books by what authors are good to have, so let me answer it once here. :) The answer is easy – it depends! Are you interested in database administration? Application development against an Oracle database? Technical apps? Functional apps? High Availability? Enterprise Manager? No […]

Orion I/O calibration tool bug

I use fio for all my I/O testing. Why not Orion from Oracle since almost all of my I/O testing and benchmarking has been geared toward Oracle? Several reasons

fio is

  • super flexible – able to configure it for almost all types of test
  • active community – updates almost every week, many by Jens Axobe (who wrote much of the Linux I/O layer)
  • reliable – if there are problems, it’s open source and one can (more...)

Associative arrays and Nested tables

A common criticism of PLSQL is that the "original" array datatype, now called associative arrays are perfect for passing stuff back and forth to 3GL environments (for example .Net),  but canno be used within SQL natively, for example:

SQL> create or replace
  2  package BLAH is
  3    type num_list is table of number index by pls_integer;
  4    type str_list is table of varchar2(30) index by pls_integer;
  6  procedure ODP_PROC(n out num_list, s out str_list);

Row cache lock

A rather odd performance issue happened at work a few days ago. All of a sudden, one of the databases started to work very slowly, and a quick look in ASH data showed that it was spending over 70% of its time waiting on “row cache lock” (not to be confused with “latch: row cache lock”).

It was a test database ( on an overloaded server with non-standard configuratioin, so my initial reaction (more...)

ASM Startup Fails With ORA-04031 After Adding CPUs

A few weeks ago we upgraded one of our production server, adding another CPU tray. This brought the number of CPU cores from 80 to 160, and took us from 2Tb of RAM to 4Tb (just in time for Oracle to announce the in-memory database in!).

However when I went to start things up, ASM wasn't starting up, giving me these errors:

ORA-04031: unable to allocate 32 bytes of shared (more...)

Deferrable RI – 2

A question came up on Oracle-L recently about possible locking anomalies with deferrable referential integrity constraints.

An update by primary key is taking a long time; the update sets several columns, one of which is the child end of a referential integrity constraint. A check on v$active_session_history shows lots of waits for “enq: TX – row lock contention” in mode 4 (share), and many of these waits also identify the current object as the index that (more...)

Exadata storage indexes and DML

Last week I’ve gotten a question on how storage indexes (SI) behave when the table for which the SI is holding data is changed. Based on logical reasoning, it can be two things: the SI is invalidated because the data it’s holding is changed, or the SI is updated to reflect the change. Think about this for yourself, and pick a choice. I would love to hear if you did choose the correct one.

First (more...)

Force option in ASM

This below url is very useful to know about force option in asm

Create diskgroup in ASM

Steps for creating diskgroup in ASM (Single Node)


Step 1: Add new HDD to server and done partition. Here I added two HDD

[root@asmoracle ~]# fdisk /dev/sde
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklab el
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of  (more...)

Swimming Progress

While I was at BGOUG I went for swim each morning before the conference. That got me to thinking, perhaps I should start swimming again…

It’s been 4 weeks since I got back from the conference and I’ve been swimming very morning. It was a bit of a struggle at first. I think it took me 2-3 days to work up to a mile (1600M – about 9M short of a real mile). Since then I’ve (more...)

Finished first pass through Alapati 12c OCP upgrade book

I just finished reading Sam Alapati’s 12c OCP upgrade book for the first time and I really like it because of the content that it covered which I hadn’t discovered through my study of the Oracle manuals.  Also, it did a good job explaining some things that Oracle’s manuals left unclear.

After reading each chapter I took the end of chapter test and got between 60% and 75% of the questions right.  Next (more...)

OTN Latin America Tour, 2014

The dates, and the speakers, for the Latin America Tour have been anounnced.

De cómo un clon nos puede ayudar con un patch (updated to 12c)

Cada vez que vamos a aplicar un patch o un patch set update (PSU), el procedimiento tradicional implica detener todos los procesos que se estén ejecutando en el Oracle home directory involucrado, lo cual obliga a que dejemos de prestar servicio para iniciar el patching, proceso que si bien es relativamente fácil y sencillo, está sujeto a eventuales fallas que, de ocurrir, pueden llevar a una situación de crisis en la cual el (more...)

User Group Meetings Next Week (free training everyone!)

I know, posts about up-coming user group meetings are not exactly exciting, but it’s good to be reminded. You can’t beat a bit of free training, can you?

On Monday 14th I am doing a lightning talk at the 4th Oracle Midlands event. The main reason to come along is to see Jonathan Lewis talk about designing efficient SQL and then he will also do a 10 minute session on Breaking Exadata (to achieve that (more...)

Tuning EM12c, the OMS, Part II

We went over a few of the Java “tuning” options last time, so let’s go onto the OMS tier for this post.

Location, Location, Location

High latency issues between the OMS(Service) and the OMR, (Repository) when separated by geographical location is common.  It’s important when designing the Enterprise Manager environment that you keep your OMS hosts close to the repository hosts geo-locationally.  Your agents can be global, with minor network considerations, but the (more...)