OraNA :: Oracle News Aggregator

Not very long before we kick off the 2013 Great Lakes Oracle Conference in Cleveland ( 2013 Great Lakes Oracle Conference ).  This will be our biggest and best ever conference.

Key notes by Tom Kyte and 2 from CJ Date ( the godfather of relational database design ). (more...)

Actually the best suggestion I had from a competent developer was that I the DBA should physically remove the keyboard from this SQL challenged individual.

This should go into Oracle WTF but that blog is not very active these days.

So here we go ... what do you think about this?

(more...)

I had a batch program chew up 16 gb of PGA and run for extended time doing updates to ( my only ) table that has a text index on it ( customer last name ) ... used for "soundex" lookups.

It choked eventually and terminated with ORA 4030 ... have not (more...)

My OLTP system just cutover from 11.1.0.7.x to 11.2.0.3.5 and a bunch of my custom monitoring stuff now looks a whole lot different.

I had some custom slicing and dicing of the contents of sgastat ( I query it once an hour (more...)

Sorry for anyone that may have seen this already via Oracle L ... some volleying back and forth on this item ( and thanks to Jonathan Lewis ).

This is the same approximate workload ( one week in between ) on an 11.1 system and now running on 11.2.

(more...)

We are working on a set of upgrades to our environment and replacing a set of very stable but now old IBM x3650 servers ( currently running 5.7 ) with a set of new Dell R 710 servers.

New Dell servers on Oracle Linux 6.2 ... using red hat compatible kernel aka:
2.6.32-220.el6.x86_64
The new dell boxes have an internal raid controller ( Perc H700 ? ) and are connected to EMC direct attached storage using emulex HBA's.  All operating system and linux software installed on internal disks ( mirrored ) ... all database stuff going (more...)

My usergroup ( NEOOUG = Northeast Ohio Oracle Users Group ) has been running a big two day conference at Cleveland State in May for a number of years.  Last year our keynotes were from Cary Millsap and Rich Niemic.

This year we are hitting it out of the ball park with Chris Date ( CJ Date ) the godfather of relational database design theory and principles along with Tom Kyte.

Conference renamed this year as GLOC Great Lakes Oracle Conference on May 14th/15th with additional 1/2 day workshop ( extra cost ) on monday May 13th by Tom (more...)

Geez some days it is hard for a grumpy old dba to be so grumpy ... no worries I will find something to make me complain eventually.

This geek is pretty pumped up to have my presentation accepted for hotsos 2013 !

I am presenting with the exciting title of "OS Truth, Little White Lies, and the Oracle Wait Interface" ... kind of a play on "Truth, Lies, and Videotape" ( which I think I saw once a long long time back ).

This is my presentation abstract here ... John Hurley

Cary Millsap I believe is the ultimate decision maker on who makes (more...)

In my prior post I showed a bad inline view that was causing a huge performance hit.  Running in an OLTP environment no less but well thats kind of outside of the solution.

It was bugging me all weekend and with the help of Mladen I came up with a solution.

The original SQL ( again part of an inline view in a much bigger piece of SQL ) looked like this:

Part of a very long query put together by a developer and of course implemented in production. Lots of inline views ( not a bad idea many times ) ... big tables it was going against ... many of them. Still we have a pretty fast system ...

Things were still ok until you get to this part of the SQL:

 (SELECT POC.PO_ID, POC.LINE_ITEM_PO, POC.COMMENT_PO, POC.DATE_ADD_COMMENT FROM PO_COMMENT POC WHERE (POC.PO_ID,POC.LINE_ITEM_PO,POC.ROW_TIMESTAMP) IN (SELECT A.PO_ID,A.LINE_ITEM_PO,MAX(A.ROW_TIMESTAMP) FROM PO_COMMENT A WHERE A.TYPE_PO_COMMENT='LE' GROUP BY A.PO_ID, A.LINE_ITEM_PO) ) T193

So were are operating against (more...)

Been running by default in 11.1 the sql tuning advisor but not doing anything really with results ... we are closing in on migration to 11.2 but not quite there yet.

My current 11.1 system just starting barfing last week after 1 piece of horrendous sql ( looks like from hyperion ... some smart person doing ad hoc analysis on production OLTP system ... thanks ) apparently caused it to lose its mind.

 It chews cpu and chews cpu until task terminated ...
Target Name=****************
Target Type=Database Instance
Host=********************
Metric=Generic Operational Error Status Metric Value=1
Timestamp=Nov 25, 2012 7:12:11 AM EST
(more...)

For the time being we are still sitting on 11.1.0.7.x on all our systems but that will be changing this year. Strong possibility that everything will move to 11.2.0.3.x at some point in 2012.

Our first system that may go live is currently sitting at 11.2.0.3.1 ( was just patched up to there with Jan 2012 PSU update ).

That system ... like all of ours is a non RAC system but is using ASM. Because of the change in 11.2 that means two separate oracle homes one (more...)

So I get a 4 pm sunday afternoon slot and guess who is presenting in the next room over? Rich Niemic from Tusc. I show up about 30 minutes before time and there is a line already going halfway down the hallway of DBA fans for Rich getting lined up. I think this is looking bad for getting anyone into my room.

However the room starts filling up and eventually probably had 175 attendee's or so. Maybe even 200? Not quite sure. All in all the presentation went pretty well ( Shared Pool Monitoring aka Dodging ORA 4031's ) and (more...)

Seriously Oracle support what is going on here? I have had a service request SR 3-3474803321 open for a very long time. On June 6 2011 I escalated it yet again and provided a reproducible test case. Oracle support at that time noted they were waiting on getting a test machine to verify it and then get a bug opened.

It is now July 17 2011 and still Oracle support has not been able to get access to a test machine. I called back in on July 6th 2011 and was told that basically two things were going on ... 1) (more...)

Some things make even an old grumpy dba smile well or at least grimace. I will be speaking this year using a topic first done in January 2011 at our local Northeast Ohio Oracle Users Group session.

The session ID is 04828 and topic name ( might need a little tweaking ) is "Three Approaches to Shared-Pool Monitoring (Dodging ORA-4031s )".

The event this year happens to overlap my 20th wedding anniversary and my wife is going to fly out to San Francisco ( her first visit out there ). The appreciation event featuring Tom Petty & Sting ( notice (more...)

Here is a strange one. Why does one query get an ORA-00904 but the same query when used as part of an IN apparently get ignored ( or not noticed as an error )?

This query returns an error:

select table_name from dba_users;
Error at line 1
ORA-00904: "TABLE_NAME": invalid identifier


This query executes ( not very well but ... ):
select * from dba_tables where table_name in
( select table_name from dba_users );

This reproduces in 11.1.0.7.6 and 10.2.0.4.

Is this a well known bug already or ( for some reason ) (more...)

While ASM is often thought of as being deployed principally in RAC based configurations it can be a very useful component to help manage and/or migrate storage in a single instance ( non clustered ) setup also. Logically the steps involved to add some additional LUNs and then expand ( one or more ) tablespaces would look like this:

1) Get the LUNs established in your storage environment and then make them available to the server.

*** In a linux/emc environment this might involve steps to scan the storage environment from linux ( several techniques available such as /usr/sbin/lpfc/lun_scan all (more...)

The Oracle Advanced Security aka OAS ( one of many Oracle anacronyms using OAS unfortunately ) is an extra cost feature/option that we implemented a while back in an 11.1 environment. One of the many possible Oracle references here is: OAS ...

We implemented tablespace based Transparent Data Encryption for 2 fairly large ASM based tablespaces ( the old data and index separation which may not really be needed any longer ) and that has worked very well. Used expdp to put all the existing data out into a file, deleted and recreated the tablespaces using AES256 based encryption, and (more...)

Filed under Oracle metalink as ASM and Database Instance hang when exceeding around 1800 sessions doc id 858279.1

This can occur in both an 11.1 RAC environment as well as a 11.1 single instance non clustered environment.

If you are running an 11.1 single instance non clustered system and using ASM then you probably ( or may ) have just one Oracle home. You installed the oracle software and when you used dbca to create a database and selected ASM you were prompted to run as root a local config. This puts in part of the (more...)

To be grumpy about something it while I like much of the older stuff that Sting has done a lot of what he has done in the last 15 years leaves me cold. Tom Petty is another completely different story. A genius an artist a perfectionist ( could have been a monster DBA probably ) ... and going to be at Open World this year!

Open World 2011 appreciation event

Wow ... even bigger than Aerosmith or Counting Crows ( sorry while I like Fergie the Black Eyed Peas overall don't quite get it for me ).