Supplemental Logging and Securefiles Causing DBWn to Block

A few weeks back, we began making changes to prepare for using Oracle Golden Gate. One of the first steps required is to enable "minimal supplemental logging" at the database level. We did this during an evening maintenance window. However by the time the morning workload picked up, we started seeing a lot of sessions blocking, and the root blocker was one of the DB Writer (DBWn) processes.

Looking at the blocked sessions, a query (more...)

RMAN Redundancy is not a Viable Retention Policy


Originally posted by me on the Pythian blog. This is an older post that I somehow forgot to post on my own blog, but another recent redundancy foul-up reminded me of it.

The story you are about to read is based on actual events. Names and paths have been changed to protect the innocent. I call this scenario “The Perfect Storm” because it took just the right combination of events and configurations. Sadly, this doesn’t (more...)

Migrating (and Upgrading!) Your EM12c Repository Database

This week I migrated our EM12c repository database to a new server as part of it's promotion to production status. Just to make it a little more exciting, the migration also involved an in-flight upgrade from 11.2.0.3 to 11.2.0.4. Much of this post is directly inspired by Martin Bach's post on the same subject. I ran into a few other snags that weren't mentioned so I thought it would (more...)

ORA-00600 [kkzlpllg:5] When Dropping MView Log

This week a co-worker and I have been doing some rapid-fire testing to improve fast-refresh performance on an old materialized view, which does some summary aggregations on a 1.9 billion row (and growing) master table. One of the things we tested was using the new-in-11gR2 COMMIT SCN feature. There is a great blog post describing the benefits of this feature by Alberto Dell'Era. To quickly summarize and over-simplify, it provides a much faster way (more...)

Battling Bigfile Backup Bottlenecks

Last Friday I kicked off a database backup to an NFS destination, using the standard "backup as compressed backupset database" syntax. Loyal readers of this blog may recall that I'm the proud custodian of a 25 Tb database, so this backup normally takes a few days, with an expected completion on Monday morning. However it was still running on Wednesday, and reviewing the logs I saw that there was just 1 channel (of the original (more...)

Why Is My MView Log Not Purging?

A few weeks ago we saw one of our tablespaces growing at a rate much higher than the others. Taking a look we saw that the biggest users of space were two materialized view logs, one being 110 Gb and the other 60 Gb. These logs were in place to facilitate the fast refresh of two materialized views, one for each log/table. These materialized views did some aggregations (sum) throughout the day on some important (more...)

What to Expect When You’re Changing the DB_UNIQUE_NAME

I recently had to change the db_unique_name of a database to make it jive with our typical database/DataGuard naming policy of appending the datacenter location. For the sake of this post let's say it was changed from ORCL to ORCL_NYC, since this database is in our fictional New York City datacenter.

I did a quick set of tests and thought I'd share the findings to save anyone any unpleasant surprises. Here are the things to (more...)

Oracle Direct NFS and Infiniband: A Less-Than-Perfect Match

Readers of an earlier post on this blog will know about my latest forays into the world of Direct NFS. Part of that means stumbling over configuration hiccups or slamming into brick walls when you find new bugs.

To quickly re-set the table, my organization purchased the Oracle ZFS Storage Appliance (ZFSSA) 7420. Oracle sold us on the Infiniband connectivity as a way to make a possible future transition to Exadata easier. However the pre-sales (more...)

The Danger of Moving Incrementally Updated Datafile Copies

When I sat down at my desk yesterday morning I was greeted with some disturbing email alerts notifying me that one of the NFS mounts on my standby database host was full. This was the NFS mount that held an image copy of my database that is updated daily from an incremental backup. The concept and an example can be found in the documentation. With a 25Tb database, waiting to restore from backups is not (more...)

ZFSSA EM12c Plugin Pro-Tip

When adding ZFSSA targets to #EM12c, be sure to uncheck "kiosk user" for the oracle_agent user in ZFSSA. Doc ID 1609846.1

https://twitter.com/dtseiler/status/428210917155692544

Upgrading EM12c to 12.1.0.3: Active Directory Authentication Quirks

We recently upgraded our EM12c 12.1.0.2 installation to 12.1.0.3. This service was configured to authenticate logins against our Active Directory server via LDAP. However, after upgrading we found that authentication stopped working, even after recreating the Active Directory Authenticator (as required). I could see the list of users from AD, but could not authenticate them in EM!

In this post I'm going to share what we did to diagnose (more...)

shutdown abort: Not always "fine"

When I started out as an Oracle DBA on Oracle 7.3, nearly everyone would tell you to avoid doing a "shutdown abort" if at all possible since it leaves the database open to possible corruptions. In the years and versions since, that thinking has softened greatly, to the point (more...)

Oracle Direct NFS (and Linux Routing) … for Dummies!

When I started my current role a few months ago, I was very interested to learn that direction had been set to migrate away from ASM and onto NFS storage that had some read flash cache in front of it. I'm not the world's biggest fan of ASM and the (more...)

UNTIL CANCEL UNTIL CANCEL UNTIL CANCEL

Yesterday I was creating a new Oracle 11.2.0.3 database from a copy of datafiles and archivelogs taken from our standby. I was sure to include archivelogs from just prior to well after the span of the datafile backup time. I had created a new controlfile, gotten everything (more...)

Oracle Restart Underscore Warning!

Bobby Curtis just posted a great post about adding a database to Oracle Restart, aka srvctl. The post is a must-read, but I do have one thing to add. In Bobby's example, he leaves out the instance_name clause (noted by the -i parameter). This won't cause a problem for (more...)

Views, Datatypes and Nulls

Had an interesting little case come up today when helping a client re-write a view. This legacy application stored a date value as January 1, 1970 when they didn't have a date (basically using it as a de facto NULL). I did my best to lecture the client on why (more...)

Learning to Love the ADR – Part 4: Incident Packaging Service

This post is the fourth (and final) in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.


IPS


Any DBA that has had to file a (more...)

Learning to Love the ADR – Part 3: Purging (Out With The Old)


This post is the third in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

One of the benefits of the new ADR system is the (more...)

Learning to Love the ADR – Part 2: Diagnostic Files and ADRCI

This post is the second in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

Wherever the DIAGNOSTIC_DEST is (remember that the default is the $ORACLE_BASE (more...)

Learning to Love the ADR – Part 1: Location, Location, Location!

This post is the first in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

The Automatic Diagnostic Repository, or "ADR", is a location on disk (more...)