I tought I was … perfect migration (from Ora9i) in test system with the good old export/import , all tests ok,  perfect migration in production system.. . ready, set, GO .

And then I started to notice red mountains on the Active-sessions chart … locks … lots of them … customer calling and slowly slipping into panic while I am trying to understand what the f**k is going on.

As printed on HGTTG  (and on a print behind my desk) DON’T PANIC .

CLUES:

  • sessions locking themselves on tables they don’t interact directly
  • sessions locked waiting on event “enq: TM – contention”
  • the only link between the locked instruction and the locking object is a network of few triggers and several foreign keys constraints (yes, sometimes programmers use them)

And here I have to thank :

And to link the following pages that led me to a solution:

http://hoopercharles.wordpress.com/2011/06/19/addressing-tm-enqueue-contention-what-is-wrong-with-this-quote/

http://hoopercharles.wordpress.com/2010/01/07/deadlock-on-oracle-11g-but-not-on-10g/

http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html (Actually I found this 1st : http://oraqa.com/2006/02/12/how-to-locate-unindexed-foreign-keys/ )

The moral of the story is :

WHEN YOU WILL MIGRATE YOUR DB TO ORACLE 11g  IS HIGHLY PROBABLE THAT UNINDEXED FOREIGN KEYS WILL GENERATE LOCKS….  SO BEFORE GOING LIVE DON’T FORGET TO PUT INDEXES ON ALL YOUR FK!

Here is my easy review of  Tom’s script to create the missing indexes for the connected user (this will generate the commands as output then use them as you like changing the names of  indexes, tablespaces etc. ) :

select 'CREATE  INDEX  I_FK'||rn||'_'||table_name||' ON '||
table_name||' ('||ccc||') TABLESPACE MY_INDEX_TBS;' CMD
from
(
select decode( b.table_name, NULL, '****', 'ok' ) Status,
    a.table_name
    , a.columns ccc
    , b.columns
    , row_number() over (partition by a.table_name order by a.columns) rn
from
( select substr(a.table_name,1,30) table_name,
   substr(a.constraint_name,1,30) constraint_name,
      max(decode(position, 1,     substr(column_name,1,30),NULL)) ||
      max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
      max(decode(column_position, 1,     substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
    from user_ind_columns
   group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
  )
  where status != 'ok'

Of course it worked for me but there is no warranty that your server will not explode when you use this query or the commands generated 😉

Advertisements

This time it was a short Orlov‘s piece : “Living on a stolen time”  … maybe Orlov’s style is sometimes too prissy for my taste … but he’s very competent and his words can inspire and enlighten readers (including myself).

http://www.comedonchisciotte.org/site/modules.php?name=News&file=article&sid=9391&mode=&order=0&thold=0

This is the source : Orlov’s Blog

Here  is my latest translation for CDC  :  The Money Masters: Behind the Global Debt Crisis

here  is the original.

It’s a “viral” meme spreding around (here is a cartoon, here  is an illustration … and I’m sure I found it somewhere else  more than one time in the latest week).

I hope it will lead to an evolution for human society … “You see: money doesn’t exist in the 24th century”  (J.L. Picard)  (extended ver. with an intresting comment  … in case you did not see “Star Trek, First Contact” ) .

Let’s imagine you are a wise DBA , and you have a brand new shiny SQL Server 2008 R2 DB on a Win server 2008 R2 , 64 bit OS platform … and for some INSANE reason you have to move A LOT ( we talk about some millions of rows ) of data from an Oracle server to this DB … what would you do ?

Before today my answer would have been “Install the appropriate (64bit) Oracle client with  OLE Db provider for Oracle and use it as data source in the MSSQL Import (AKA DTS)” …

But, of course, the fight between Oracle and Microsoft will not make things so easy … so this solution will not work : Microsoft OleDB provider for Oracle will return error when initializing and Oracle OLE DB Provider for Oracle will not show up in the list of the data sources; so you will not be able to use DTS …  unless… here comes the magic: you install ALSO the  32bit Oracle client !

I found this solution in an article from Jorg Klein where he suggested this   for a similar problem.

I also adjusted the registry keys  as indicated in the article … and right now my DTS import is copying rows at decent speed between the two bitter enemies RDBMS ‘s.

But I just PASSED exam 1Z0-050 … and I am very happy about it.

and now … now what ?

For the 2nd half of this translation I have to thank a good friend of mine…

 

And here is my translation … someone still thinks Climate Change was a hoax and the review commission was not independent at all … easy justification : nothing is really independent ( you know everything is Anatta ) … I can live with that … as long as it’s not used to pollute  and conscioulsy cause suffering to others …  contemporaries or future generations.

http://www.cce-review.org/

It seems that the old mountaineers were not too wrong when stating that the glaciers are withdrawing, after all …

Soon italian translations for chapter 1.3 of the latest review…. in the neanwhile a spooky picture from IPCC AR4:

(check here http://www.cru.uea.ac.uk/~timo/datapages/ipccar4.htm for caption)

1 Soldier or 20 schools ?

Original (Thank you Mr. Kristof)

Translation:

http://www.comedonchisciotte.org/site/modules.php?name=News&file=article&sid=7328

Yet another american president …  keeps doing the world the same things that predecessors did … some says he can’t just stop at once and withdraw all the troops…  can’t he ? … Actually I fear he can not.

I guess aliens are watching us, disgusted, considering what sort of nasty animals we are: fighting and killing each other and distroying our enviroment to satisfy our endless personal desires …  probably they are waiting for some evolution before allowing a first contact …

I don’t know what to trust more:  their patience or  mankind’s ability to evolve …

“La via del dubbio” by Stephen Batchelor  … I guess original title is “The Way of Korean Zen” ( many thanks to Luciano for taking care of my Zen readings and borrowing me the book) … and, of course, 1Z0-050 exam guide :-/

Advertisements