Search

Top 60 Oracle Blogs

Recent comments

March 2011

Informative Error Messages

You have probably all experienced situations where you get an error message from Oracle and it turns out to be a total Red Herring. Occasionally though the error messages can be really helpful. I got an email from a co-worker today (thanks Stephan) that read like this:

I was working on an external table today and fat fingered something. This is the error report-

Error report:
SQL Error: ORA-30657: operation not supported on external organized table
30657.0000 – “operation not supported on external organized table”
*Cause: User attempted on operation on an external table which is
not supported.
*Action: Don’t do that!

Gotta love it. They don’t actually tell us what we did wrong, but they do tell us how to resolve the issue!

Apparently some of the Oracle developers have a sense of humor.

By the way, I’m sure you already know this but there is a nifty little Oracle provided tool called oerr that spits out this information:

 
SYS@SANDBOX1> !oerr ora 30657
30657,0000, "operation not supported on external organized table"
// *Cause:  User attempted on operation on an external table which is
//          not supported.
// *Action: Don't do that!

MOATS: The Mother of All Tuning Scripts!

People talk about the Oracle SQL Developer 3 being out, which is cool, but I have something even cooler for you today ;-)

I finally figured out how to convert my screen-recordings to uploadable videos, so that the text wouldn’t get unreadable and blurry.

So, here’s the first video, about a tool called MOATS, which we have built together with fellow OakTable Network member and a PL/SQL wizard Adrian Billington (of oracle-developer.net).

Here’s the video, it’s under 3 minutes long. Play the video in full screen for best results (and if it’s too slow loading, change it to lower resolution from HD mode):

Check it out and if you like MOATS, you can download it from Adrian’s website site (current version 1.05) and make sure you read the README.txt file in the zip!

Also thanks to Randolf Geist for finding and fixing some bugs in our alpha code… Note that MOATS is still kind of beta right now…

P.S. I will post my ORA-4031 and shared pool hacking video real soon now, too! :-)

P.P.S. Have you already figured out how it works?! ;-)

Update: Now you can suggest new features and improvement requests here:

MOATS: The Mother of All Tuning Scripts!

Update: Based on the original MOATS built by Adrian Billington and myself, other folks have built cool variations of MOATS (either based on our code or written completely new ones):
MOATS (original) by Adrian Billington & Tanel:
https://github.com/oracle-developer/moats MOATS 2.0 (RAC-aware) by Sidney Chen:
https://github.com/dbsid/moats_rac SQL Dashboard by Jagjeet Singh:
https://jagjeet.wordpress.com/2013/12/20/sql-dashboard-v2/ Both are RAC-aware, use terminal coloring techniques like my (fish.sql :) and some Exadata metrics.

MOATS: The Mother of All Tuning Scripts!

Update: Based on the original MOATS built by Adrian Billington and myself, other folks have built cool variations of MOATS (either based on our code or written completely new ones):
MOATS (original) by Adrian Billington & Tanel:
https://github.com/oracle-developer/moats MOATS 2.0 (RAC-aware) by Sidney Chen:
https://github.com/dbsid/moats_rac SQL Dashboard by Jagjeet Singh:
https://jagjeet.wordpress.com/2013/12/20/sql-dashboard-v2/ Both are RAC-aware, use terminal coloring techniques like my (fish.sql :) and some Exadata metrics.

Repetition

One of the problems of building models of Oracle activity is that it’s easy to build the wrong model. One of the commonest issues appears with repetitive actions – how do you write code that repeats a simple action many times in a row. It’s often enough to write a simple pl/sql loop but there are cases where a pl/sql loop behaves very differently from a long list of individual SQL statements – which is why I’ve occasionally used a very simple-minded approach to avoid that particular trap.

If you checked the directory of my database work area, you’d find an intereseting pattern of names made by five very short scripts:

q:> dir start*.sql
 Volume in drive Q has no label.
 Volume Serial Number is A8BA-79AA

 Directory of q:\

28/03/2011  08:56               350 start_1.sql
13/01/2005  13:38               110 start_10.sql
13/01/2005  13:39               120 start_100.sql
13/01/2005  13:39               130 start_1000.sql
13/01/2005  13:40               140 start_10000.sql
               5 File(s)            850 bytes
               0 Dir(s)  82,803,621,888 bytes free

The script start_10000.sql contains just 10 lines:

@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000

and you won’t be surprised to find that the script start_1000.sql also contains just 10 lines:

@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100

and so on down the chain, until the script start_1.sql contains the piece of code that I actully want to run many times.

Obviously there are cases where you need a more subtle framework – but it’s very convenient to be able to write a small script holding the piece of code that you want to keep repeating and then run it 10,000 times with just a single call to: @start_10000.sql.

MySQL Plug-in for Oracle Grid Control: Reports Fail with ORA-01017 or ORA-28000

One MySQL Plug-in customer reported an issue with reports — all charts were displaying an Oracle error and didn’t display any data as if it couldn’t connect to the EM repository. I concluded that this has nothing to do with the plug-in itself since the extensibility framework provides no way of controlling usernames and passwords that are used to access the repository. After opening an SR with Oracle, it turned out that MGMT_VIEW user password has been changed and this causes “ORA-01017: invalid username/password; logon denied” and possibly subsequent “ORA-28000: the account is locked” when policy is set to auto lock an account after certain failed login attempts (default in 11g database).

If you face this issue, follow My Oracle Support Note 374382.1 “Grid Control Repository: How to Change the Password of the MGMT_VIEW User”.

Thanks for reporting it Teffany!

More on oracle.com

This is just a quick update (basically agreeing with Robin Moffat in the comments here and Dom Brooks here ). One of the blogs I follow is that of the optimizer team. . They’ve recently just released quick update because their excellent white papers have moved – and er there’s no redirection in place. Oracle, [...]

Summer Seminars

I am doing a couple of one day seminars with Oracle University, currently planned for Austria and Switzerland. They go by the title “Grid Intrastructure and Database High Availability Deep Dive”, and can be accessed via these links.

To save you from having to get the abstract, I copied it from the Oracle University website:

Providing a highly available database architecture fit for today’s fast changing requirements can be a complex task. Many technologies are available to provide resilience, each with its own advantages and possible disadvantages. This seminar begins with an overview of available HA technologies (hard and soft partitioning of servers, cold failover clusters, RAC and RAC One Node) and complementary tools and techniques to provide recovery from site failure (Data Guard or storage replication).

In the second part of the seminar, we look at Grid Infrastructure in great detail. Oracle Grid Infrastructure is the latest incarnation of the Clusterware HA framework which successfully powers every single 10g and 11g RAC installation. Despite its widespread implementation, many of its features are still not well understood by its users. We focus on Grid Infrastructure, what it is, what it does and how it can be put to best use, including the creation of an active/passive cold failover cluster for web and database resources. Special focus will be placed on the various storage options (Cluster File System, ASM, etc), the cluster interconnect and other implementation choices and on troubleshooting Grid Infrastructure. In the final part of the seminar, we explore Real Application Clusters and its various uses, from HA to scalability to consolidation. We discuss patching and workload management, coding for RAC and other techniques that will allow users to maximise the full potential of the package.

See you there if you are interested!

Kevin Closson Joins EMC Data Computing Division To Focus On Greenplum Performance Engineering!

Last week the email account associated with my blog amassed no less than 83 emails from readers asking what I’m up to in response to the cliff-hanger I left in my post entitled Will Oracle Exadata Database Machine Eventually Support Offload Processing for Everything?

I appreciate all the email and I regret I was unable to answer any of them as I was taking some time away with my family.

I’ve resigned from my position of the last 4 years as performance architect in Oracle’s Exadata development organization and have joined the EMC Data Computing Division to focus on Greenplum in a performance engineering role.  While this is a big and exciting news piece for me personally, I need to make this a small and quick blog entry at this time.

Filed under: oracle

Tanel Poder's Online Seminars

What are good ways to learn about the inner workings of Oracle to troubleshoot performance and availability issues?

Fortunately there is only one good answer - just one good source - Tanel Poder's virtual seminars at http://tech.e2sn.com/oracle-training-seminars

Why? Let's examine the typical objectives and the various means to accomplish them. I'm sure you have heard this complaint before - "the database is slow". If I had a dime every time I heard it, well ... you know the rest! Most DBAs by now know the next best thing they should do - check the wait interface - V$SESSION. That's a very good first step.