Search

OakieTags

Who's online

There are currently 0 users and 51 guests online.

Recent comments

Affiliations

Oracle SQL's MEDIAN Function

Article #3 in my ongoing series covering SQL statistic functions in Oracle Database is now up. The topic is the median:
 
MEDIAN: For When You Don't Really Mean It
 
Median is useful in typifying a data set when the data might be skewed, or in the presence of extreme outliers. For example, the U.S. Census Bureau reports median household income for states and counties so as paint a picture unskewed by the presence of, say, Bill Gates or Warren Buffet living just down the street. To learn more, hit the link.

Standard Deviation and the Mean

I've just put up the second in an ongoing series (I hope!) of articles on Oracle SQL's build-in statistical functions. The topic is standard deviation. The previous one, my first, is on the mean. Here are links to the two:
 
2. STDDEV: Standing Sentinel on Your Data
 
1. AVG: What Does it Mean?

Oaktable Sunday 2012

 
Abstracts
 
8-bit Oak Roots
Bernie Dugggs, Imagine Software

There’s a reason so many Oakies are about the same age. We were the keen users of the first popular computers. The British version of that experience was based on cheap and available 8-bit home computers that unleashed people’s latent passion, talent and ingenuity in over-coming platform limitations.
Bernie Dugggs will take a nostalgic look at his personal history as a developer of commercial ZX Spectrum computer games for Imagine, Ocean and Odin and show how relevant the experiences and lessons learned have remained throughout a career in software

 
 

SLOB - The Silly Little Oracle Benchmark

This is the February 1, 2013 drop of the SLOB tar archive. After downloading it, please compute an md5sum on it. For example, using OSX md5 command you will see the following type of output. The md5 checksum will, of course, be the same whether computed by Linux md5sum or any other such command.
The changes in this tarball are to the cr_db.sql script which had a faulty alter tablespace command in it. I've also added a very crude yet helpful awr post-processing script called awr_info.sh under the misc directory. See the README in that directory for more info on the script.


$ ls -l 2013.02.01-slob-kit.tar_.gz
-rw-r--r--@ 1 clossk  staff  10040 Feb  1 16:16 2013.02.01-slob-kit.tar_.gz
$ md5 2013.02.01-slob-kit.tar_.gz
MD5 (2013.02.01-slob-kit.tar_.gz) = f157b1c51b553f90df53fcea95f89632

 

Mining listener logs

When is the last time you looked at the listener logs? Perhaps never. Not a very good idea. Listener logs contain a wealth of information on security events - it shows you the profile of hosts connecting to the database, the programs they are using and attempting to communicate but failed, among other things. But reading the listener logs is not exactly a pleasant experience. A while ago I wrote a series of articles on an online eZine called DBAZine.com on how to create an external table to read the listener logs using simple SQL; but unfortunately DBAZine.com has folded.
I have placed the articles here as well as on my website for your reference. As always, I would love to hear from you how you felt about these, stories of your own use and everything in between.

Edition-Based Redefinition

Upgrading critical applications can be very difficult. One of the main problems is that for reasons of availability, long downtimes cannot be periodically scheduled. Therefore, for such applications, it is desirable to implement online upgrades. This requires that the application in question, as well as any software used by the application (e.g. the database engine) all support online upgrades. Oracle has recognized this problem for years. Unfortunately, up to and including Database 11g Release 1, only a limited number of features have been implemented for that purpose. As of Oracle Database 11g Release 2, this situation has changed greatly. With edition-based redefinition, Oracle Database offers real support for implementing online upgrades. The aim of this paper is to provide an overview of this new feature.
Download full article: http://antognini.ch/papers/EditionBasedRedefinition20101204.pdf
 

How to log on to an instance when even SYSDBA can't do so?

When you have a situation where let say the archiver process is stuck, regular users and even users with DBA role may not be able to log on. This usually because new sessions logging on generate audit records or (sometimes) update SEQ$ table to get new values from a sequence for populating AUDSID. If the archiver is stuck, then any DML will get blocked as well as they can't generate any new redo until the archiver stuck issue is fixed.

In these cases the SYSDBA (and SYSOPER) privlege holders will still be able to log on and create their session. This is because SYSDBA connections do not generated any audit records to the database tables (that's why there's the audit_file_dest directory where SYSDBA audit files will be written) and SYSDBA connections also don't get their AUDSID from a sequence but use a hardcoded value (0xFFFFFFFF) instead.

But there are cases when even SYSDBA privilege holders can't log on! One situation what I've seen was due a bug, where a process never released a shared pool latch it had taken (in fact it was stuck spinning in free memory searching code). That instance happened to have only one shared pool subpool in use, protected by a single latch. 

That meant that no-one else could allocate/free memory from shared pool, causing most sessions to eventually hang.

Read on how to resolve such situations here:
http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so

Systematic Oracle Latch Contention troubleshooting

As an Oracle DBA, developer or performance analyst, you may have run into what is termed "latch contention" at various points. So what exactly is a "latch" and why do we have contention on this "latch". In this article, we will take an in-depth look at latches and how we determine and resolve such contention. Whether you are a newbie or an experienced old-timer, we hope this article will cast a little more light on this ill-understood subject.
Read the article here:
 http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting
 

How to read an Oracle ERRORSTACK output

Errorstack tracefiles are very useful for troubleshooting ORA-600's, crashes, hangs and even just bad performance.
Errorstack dumps are dumped automatically by Oracle when critical errors such as ORA-600 happen. This is when you see an error reported in alert.log and the generated tracefile has a "ksedmp: internal or fatal error" string in its beginning, followed by the error code (usually ORA-7445 or ORA-600 with some parameters). 
"ksedmp" means Kernel Service Error DuMP, so everything below that line is the errorstack dump.

Errorstack dumps can also be manually invoked by issuing ORADEBUG ERRORSTACK 3 (when being connected to the target process using ORADEBUG SETOSPID). This can be useful when a session seems to be hung (but without showing a reasonable wait event in V$SESSION_WAIT) or is consuming much more resources than normally and you want to know which exact bind variable values are currently used for executing the SQL.

Read more here:
http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output
 

Sane SAN

 
 

SANE SAN
The Random Acronym Seminar (or RAS for short...)
James Morle, Scale Abilities, Ltd.

Introduction
This paper talks about storage within SANs. It is easier than ever to implement a badly laid out SAN, with the levels of abstraction and data sharing made possible through this technology. We will look at ways this can be simplified though careful planning, and discover why its a good idea to lie to your boss. Before that, though, it's worthwhile taking a journey into the past to find out why all this stuff exists, and why there are so many acronyms in the storage industry.