De cómo un clon nos puede ayudar con un patch (updated to 12c)

Cada vez que vamos a aplicar un patch o un patch set update (PSU), el procedimiento tradicional implica detener todos los procesos que se estén ejecutando en el Oracle home directory involucrado, lo cual obliga a que dejemos de prestar servicio para iniciar el patching, proceso que si bien es relativamente fácil y sencillo, está sujeto a eventuales fallas que, de ocurrir, pueden llevar a una situación de crisis en la cual el (more...)

Don’t delete your flashback logs manually

What happen when someone will delete Oracle flashback logs ? You probably don't notice it until you will try to flashback database or bounce instance. 
There is no hope for flashback database without flashback files but there is still way to start your database again without recovery or data loss.

Here is a scenario:
[oracle@dev-6 alert]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:34:18 2014

Copyright (more...)

Beware April 2014 PSU and Golden Gate Integrated Capture Users

When the latest quarterly Patch Set Update (PSU) came out from Oracle, we planned to get it in place as soon as reasonable due to our need to stay current with security patches, and my need to apply what I had assumed were well-tested bug fixes for our 11.2.0.3 installations. However we were in for an unpleasant surprise.

We first applied the April 2014 PSU on our staging & development database hosts (more...)

archive_lag_target Works in SE

TL;DR: The archive_lag_target parameter will force log archiving in Standard Edition.

Just a quick note here that I wanted to share since I didn't see anything directly confirming this when I was searching around.

I have an Oracle 11gR2 Standard Edition (SE) database that I'm also maintaining a manual standby for, since Oracle Data Guard is not available in SE. I created a metric extension in EM12c to alert me if the standby is more (more...)

Don’t Fear the EM12c Metric Extensions

A few weeks ago, our customer support team asked us to automate part of their checklist that looks at the number of active sessions in our production database. In EM12c, this seemed like a no-brainer with the Average Active Sessions metric. So I added this to my production incident ruleset and went back to another project. Over the next few days we'd get pinged by EM12c but the support folks would say it shouldn't. After (more...)

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...)

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...)

Plan change using load_plans_from_cursor_cache

This post is more of a note for myself and might be helpful to few other. Assuming db is 11gR2 and baselines/spm is used. When a new query is introduced in db, it might be that it runs with the good plan, but sometimes it picks up wrong plan. It could be that Index Range [&hellip

My Latin American Tour in March

At March I will be presenting my very successful seminar “Mastering Backup and Recovery” in some countries of Latin America for the very first time. Thank you Panama, Chile and Brazil OUGs for inviting me to your amazing countries! Please, use the following links for registration and also to find more information about the seminar: […]

Join me at the next OTN Virtual Developer Day

    Hi All, On February 4, 2014 at 9:30 am PT I will be talking on the next OTN Virtual Developer Day about Oracle VM and Oracle Database. Come and discover the answers for the following questions: Does an Oracle Database perform well on a virtualized environment? What virtualization technology is more stable and […]

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...)

“enq: TX – contention” on SELECT with a large buffer cache and 2PC

Note: The following is based on testing with 11.2.0.3 (I believe same issue exists within other Oracle versions).

I recently worked on an interesting problem relating to the “enq: TX – contention” wait event. There are a number of reasons for the wait but the most common (more...)

11.2.0.3 v$sqlstats.last_active_time stops changing and breaks AWR

My site uses a 3rd party SQL monitoring tool which collects data based on the Oracle view v$sqlstats.  The tool collects data for all sql statements which have been executed since the previous collection using the last_active_time column. A few months ago we noticed (after an upgrade to 11g) we (more...)

Index Clustering Factor finally more realistic

I just stumbled upon this bug reference on My Oracle Support:

Bug 13262857  Enh: provide some control over DBMS_STATS index clustering factor computation

This enhancement was long due. Previously, when computing the clustering factor during gathering statistics, the value was incremented, whenever the row was not found in the same block as the previous row. Now, it is finally possible to determine how many blocks should be considered when computing clustering factor. The patch delivers an improved DBMS_STATS package body that can be used to set preferences with value TABLE_CACHED_BLOCKS.

The flaw in the over-simplistic and pessimistic original computation was (more...)