Blog Archives

Over a year after first releasing Oracle Enterprise Linux 6, Oracle finally announced Thursday that it will support running Oracle Databases on Oracle Enterprise Linux 6 (OEL6). Currently, the certification is only valid with the Unbreakable Enterprise Kernel (UEK).

What’s that? You thought Oracle already supported their flagship software product on the current version of their flagship operating system and kernel? Well that is what most of Blue Gecko’s Remote DBA customers thought too. Too often we have had to break the news to a customer who has already dutifully built out their hosts with Oracle’s latest Linux that they will need to reinstall with OEL 5.x in order to be running a supported combination.

Oracle’s certification site adds a few more details. Although the UEK v.2 has been available since March 13, this certification only applies to OEL6 with UEK v.1. So don’t go upgrading to the latest and greatest UEK yet. Also, this certification appears to be valid only for x86-64 architectures, not i386. My Oracle Support’s Certify tool also only shows the certification as valid only for Oracle databases running patchset 11.2.0.3.

Finally, certain things you would want to have when running Oracle, such as the oracle-validated RPM bundle, are still missing from the Unbreakable Linux Network’s OEL6 Yum repositories.

Our advice is to not go rushing to upgrade yet. Let’s give it a few months for the early adopters to flush out the usual SNAFUs, and then take another look.

With apologies to Dr. Seuss.

Did I ever tell you the makers of RAC
had seven features and named each flashback?

Well they did, and it wasn’t a smart thing to do.
You see, when the customers wanted a clue
as to how to keep data from getting deleted
the RAC folks said “flashback” and customers heeded.

They turned on all seven of those flashback features
Each one was a slightly dissimilar creature.
Some used the UNDO, some used flashback files
Some just renamed tables to bin$ styles.

One was a place you keep things for recovering
Another was just for forensic discovering
With so many features called by the same name
when thinks broke no one knew just which one they should blame.

On a Friday three minutes past seventeen hundred,
users ask, “What’s the deal? Our data’s been plundered.”
“It looks like all names in the customer table,
are now ‘John Q Public,’ a certain mislabel.”

It was Nimrod, an intern fresh from his instruction.
What he thought was just test was really production.
“No Problem,” says Morton, the wise DBA,
“Flashback is on. I’ll restore it today.”

First Morton asked Nimrod what time he committed
his update that had any where clause omitted.
“I ran it at just past noon yesterday lunch,
It ran for so long that I went for a munch.”

By now it had been almost thirty one hours
but Morton knew that he could call on his powers,
and on the mere fact that that the undo_retention
was set to two days, a quite lengthy extension.

But woe! When he tried to engage Flashback Query,
he got “snapshot too old” and it ruined his theory
that undo_retention makes Oracle hold
all undo data no matter how old.

It turns out that there was no undo_guarantee
or autoextend which would also be key
to use all Flashback features reliant on Undo
A realization he would slowly come to.

Now Flashback Transaction and Versions Between
were out of the picture, although unforseen.
But he still had four more kinds of Flashback to try
So he thought them each through, to see if they might fly.

Undropping the table would use flashback syntax,
But that wouldn’t help, since the table was intact.
With database flashback all could be reverted
But subsequent changes would then be subverted

Do you know who was governor of California
When Flashback Data Archive came out and I’ll warn ya,
it will not help poor Morton, though it’s called Total Recall.
He’d have had to enable it for tables to see all.

The last of the flashbacks was the old FRA,
the Flashback Recovery Area they say.
And it’s just a directory where things are kept
Like logs and old backups made while Morton slept.

So after all that there was nary a way
to use Flashback of any sort to save the day.
Old-fashioned LogMiner was what Morton used
To restore all the rows that poor Nimrod abused.

With so many flashbacks and so much confusion
I bet that Oracle regrets the profusion
of so many things that they call the same name.
But now its too late and there’s no one to blame.

Before I joined Blue Gecko, I did independent remote DBA work, and called myself ORA-600 Consulting. Stemming from my hair-raising experiences in the trenches at Amazon in the late ’90s / early 2000s, I decided to specialize in emergency DBA work for companies in the midst of crises (I know, great idea for someone who wanted to get away from the Amazon craziness, right?).

One day in 2009, a company in Florida called my cell phone at 2AM. They described their problem as follows:

We have a 32-bit Intel server running Red Hat Enterprise Linux 4 and Oracle Database Enterprise Edition 9.2.0.1. There are four databases ranging in size from 20G to 100G. The storage is EXT3 filesystems on partitions of an Apple Xserv RAID5 array.

We had a power outage yesterday, and the database server powered down and booted back up. Prior to yesterday, it has not rebooted for about one year. We have been running trouble-free for the previous year. Upon reboot, Oracle started automatically, but all of the databases appeared as they did about one year ago. It is like the database hasn’t been saving the changes we have been making for the past year. None of the inserts, updates or deletes made in the past year are present in the databases. We are absolutely flummoxed. Please help!

I logged into the server and it was just as they described. Even the alert log and messages files ended suddenly about one year prior, and picked up again on the day of the most recent reboot. There was no trace of the intervening 12 months of work. The customer was ready to resort to their backups, but wanted to understand the problem before they proceeded. In addition, restoring backups would mean losing the last 24 hours of transactions, since archivelogs had not gone to tape for that long, and they were missing just like everything else from before the most recent reboot.

They weren’t the only ones who were flummoxed. I just sat there thinking, “where do I start?” After some poking around, though, I solved the problem. Any guesses what went wrong here? I’ll post the solution in about a week. No fair posting the solution if I’ve told you this story before!

In our Remote DBA practice, we frequently perform comprehensive system reviews for our customers on their database services. Among the things we always check for are non-default settings for the database software. We want to validate that any non-default setting is set that way for a good reason, and that any setting that is default really should be that way.

In Oracle, this is easy.  The <code>gv$parameter</code> view has a column, <code>ISDEFAULT</code>, that allows a simple SQL query to show which settings are set to non-default values.

It’s not so easy in SQL Server.  There is a view, master.sys.configurations, but it doesn’t have a way to tell if the setting is default or modified or anything.

That’s why I was pleased to find that Michael Campbell came up with a good solution that hard codes known default values into a script that works for SQL Server 2008 and up.

The style of insert used in the above script doesn’t work for SQL Server versions lower than 2008, so I made slight changes to allow it to work on 2005 and lower.  Here it is, with full attribution to Michael Campbell for developing the underlying script and technique. I can’t guarantee all the default values are valid for earlier versions, but the script runs and shows results for SQL Server 2005.

-- Server Configuration (find any non-standard settings)
--        for SQL Server 2008.
DECLARE @config_defaults TABLE (
    name nvarchar(35),
    default_value sql_variant
)

INSERT INTO @config_defaults (name, default_value) VALUES ('access check cache bucket count',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('access check cache quota',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('Ad Hoc Distributed Queries',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('affinity I/O mask',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('affinity mask',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('affinity64 I/O mask',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('affinity64 mask',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('Agent XPs',1)
INSERT INTO @config_defaults (name, default_value) VALUES ('allow updates',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('awe enabled',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('backup compression default',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('blocked process threshold (s)',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('c2 audit mode',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('clr enabled',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('common criteria compliance enabled',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('cost threshold for parallelism',5)
INSERT INTO @config_defaults (name, default_value) VALUES ('cross db ownership chaining',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('cursor threshold',-1)
INSERT INTO @config_defaults (name, default_value) VALUES ('Database Mail XPs',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('default full-text language',1033)
INSERT INTO @config_defaults (name, default_value) VALUES ('default language',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('default trace enabled',1)
INSERT INTO @config_defaults (name, default_value) VALUES ('disallow results from triggers',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('EKM provider enabled',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('filestream access level',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('fill factor (%)',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('ft crawl bandwidth (max)',100)
INSERT INTO @config_defaults (name, default_value) VALUES ('ft crawl bandwidth (min)',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('ft notify bandwidth (max)',100)
INSERT INTO @config_defaults (name, default_value) VALUES ('ft notify bandwidth (min)',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('index create memory (KB)',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('in-doubt xact resolution',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('lightweight pooling',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('locks',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('max degree of parallelism',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('max full-text crawl range',4)
INSERT INTO @config_defaults (name, default_value) VALUES ('max server memory (MB)',2147483647)
INSERT INTO @config_defaults (name, default_value) VALUES ('max text repl size (B)',65536)
INSERT INTO @config_defaults (name, default_value) VALUES ('max worker threads',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('media retention',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('min memory per query (KB)',1024)
-- NOTE: SQL Server may change the min server
--   memory value 'in flight' in some environments
--    so it may commonly show up as being 'non default'
INSERT INTO @config_defaults (name, default_value) VALUES ('min server memory (MB)',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('nested triggers',1)
INSERT INTO @config_defaults (name, default_value) VALUES ('network packet size (B)',4096)
INSERT INTO @config_defaults (name, default_value) VALUES ('Ole Automation Procedures',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('open objects',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('optimize for ad hoc workloads',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('PH timeout (s)',60)
INSERT INTO @config_defaults (name, default_value) VALUES ('precompute rank',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('priority boost',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('query governor cost limit',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('query wait (s)',-1)
INSERT INTO @config_defaults (name, default_value) VALUES ('recovery interval (min)',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('remote access',1)
INSERT INTO @config_defaults (name, default_value) VALUES ('remote admin connections',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('remote login timeout (s)',20)
INSERT INTO @config_defaults (name, default_value) VALUES ('remote proc trans',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('remote query timeout (s)',600)
INSERT INTO @config_defaults (name, default_value) VALUES ('Replication XPs',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('scan for startup procs',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('server trigger recursion',1)
INSERT INTO @config_defaults (name, default_value) VALUES ('set working set size',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('show advanced options',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('SMO and DMO XPs',1)
INSERT INTO @config_defaults (name, default_value) VALUES ('SQL Mail XPs',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('transform noise words',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('two digit year cutoff',2049)
INSERT INTO @config_defaults (name, default_value) VALUES ('user connections',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('user options',0)
INSERT INTO @config_defaults (name, default_value) VALUES ('xp_cmdshell',0)

SELECT c.name, value, value_in_use, d.default_value
from sys.configurations c
INNER JOIN @config_defaults d ON c.name = d.name
where
    c.value != c.value_in_use
    OR c.value_in_use != d.default_value
go

On Tuesday, Amazon announced availability of an Oracle version of their Relational Database Service (RDS). RDS is one of Amazon’s cloud services. You can think of it as ”database as a service.” Amazon provides a running database, storage, horsepower and a variety management tasks. And all you have to do is store you data in it. RDS has been available with a MySQL engine for some time, but the Oracle version of this service has been long anticipated. Read the full article…

Amazon’s Simple Storage Service (S3) is a robust, inexpensive and highly-available internet data storage service.  At Blue Gecko, we occasionally help our customers design and implement S3-based backup strategies.

Compared to conventional off site tape vaulting services, the advantages of vaulting database and other backups to S3 are many.  S3 backups are always on line, so you never have to wait for a  truck to arrive with your tapes. S3 backups are replicated, so if one of Amazon’s availability zones experiences a failure, your data is still intact and available at one of their other zones. Best of all, Amazon also offers the Elastic Compute Cloud (AKA EC2, virtual server hosts by the hour), so your S3 backups double as a super-low-cost disaster recovery strategy. S3 is low-cost, starting at just 3.7¢ / GB / month for storage, and 10¢ / GB for uploads. Read the full article…

Amazon EC2′s high-profile outage in the US East region has taught us a number of lessons.  For many, the take-away has been a realization that cloud-based systems (like conventionally-hosted systems) can fail.  Of course, we knew that, Amazon knew that, and serious companies who performed serious availability engineering before deploying to the cloud knew that. In cloud environments, as in conventionally-hosted environments, you must implement high-availability if you want high availability.  You can’t just expect it to magically be highly-available because it is “in the cloud.” Thorough and thoughtful high-availability engineering made it possible for EC2-based Netflix to experience no service interruptions through this event. Read the full article…

Here’s a revised version of an old popular article I wrote over ten years ago.  I wrote this when I was at Amazon.com, long before I came to work at remote DBA provider Blue Gecko. Enjoy!

Oracle’s pre-RMAN hot backup mode is the subject of one of the most pervasive and persistent misconceptions about Oracle.

During an Oracle tablespace hot backup, you (or your script) puts a tablespace into backup mode, then copies the datafiles to disk or tape, then takes the tablespace out of backup mode. These steps are widely understood by most DBAs.

However, there is a popular misconception that datafiles are “quiesced,” “frozen,” “offlined” or “locked” during backup mode. So many people think it is true, that it appears in some books on Oracle and on numerous websites. Some have even reported that they learned this from DBA class instructors. Read the full article…

Before launching into this, I must give due deference to Mogens Nørgaard’s landmark article, You Probably Don’t Need RAC (YPDNR), available here, but originally published Q3 2003 in IOUG Select Journal.  Mogens showed that you can be a friend of Oracle without always agreeing with everything they do. Read the full article…

Openworld 2010, despite the supposedly lagging economy, had record attendance again this year.  No doubt this was the result of Oracle acquiring something like fourteen companies since last year, including Sun in 2009.  The crowds were thick, divided about evenly between geeks in badly-fitting vendor t-shirts and slick sales-side hustlers with dress pants and shiny shoes.  Read the full article…