Log Buffer #447: A Carnival of the Vanities for DBAs


This Log Buffer Edition covers the weekly blog posts of Oracle, SQL Server and MySQL.


  • An Index or Disaster, You Choose (It’s The End Of The World As We Know It).
  • SQL Monitoring in Oracle Database 12c.
  • RMAN Full Backup vs. Level 0 Incremental.
  • Auto optimizer stats after CTAS or direct loads in #Oracle 12c.
  • How to move OEM12c management agent to new location.

SQL Server:

RMAN Full Backup vs. Level 0 Incremental

Perhaps you’ve wondered about this. What is the difference between taking an RMAN full backup and a level 0 incremental backup?

If you read the documentation the following explanation will be found here: Incremental Backups

The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy. Thus, an incremental level 0 backup is a full backup that happens to be the (more...)

Moving a 3TB Database Datafiles With Only 2 Minute Downtime

In the last week, I had couple of my customer ask me the same question: how can we move our data files between disks with minimum downtime?

This is actually a really good question. When we want to move a database around (let’s say to a new storage or a new disk device) we will need to do it when the files are closed (offline or database down) in order to keep them consistent.  We (more...)

Restoring a Database to a New Diskgroup

I had the pleasure of rebuilding an Exadata rack for a customer a while back, and it provided a pretty good refresher in backup and recovery for me.  As DBAs, we back up databases all the time, but the restores are performed much less frequently.  In the case of this rack, there were several databases across multiple ASM diskgroups.  One of the goals of the rebuild was to consolidate all of the databases into a (more...)

Monitoring RMAN Operations

Just a reference to source and my version of the script.

This is for restore since there are OUTPUTS.

Script to monitor RMAN Backup and Restore Operations (Doc ID 1487262.1)

$ sqlplus / as sysdba @mon_rman_restore.sql

SQL*Plus: Release - Production on Sun Aug 23 01:14:31 2015

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release -  (more...)

ORA-17629 ORA-20079

Yesterday I received page for ” ORA-17629: Cannot connect to the remote database server”  reported in Primary Database alert log.

Fri Apr 10 06:47:41 2015
Errors in file /oracle/app/diag/rdbms/testdb/testdb/trace/testdb_ora_161991.trc:
ORA-17629: Cannot connect to the remote database server
Errors in file /oracle/app/diag/rdbms/testdb/testdb/trace/testdb_ora_161991.trc:
ORA-17629: Cannot connect to the remote database server
ORA-17629: Cannot connect to the remote database server
Fri Apr 10 06:48:22 2015

The error trace file shows

*** 2015-04-10 (more...)

Restore to Restore Point on Standard Edition (no Flashback technology)

Restore points and Flashback database are nice features introduced in 10g database that provide efficient point in time recovery to reverse unwanted data changes.

But what if you have Standard Edition database:

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;
alter database flashback on
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

In Standard Edition you don’t have Flashback Database feature, but you can still create restore points and (more...)

12c Migrate Database from non-ASM to ASM using online relocation of data files

There are many articles explaining how to migrate database from file system into ASM. You could use RMAN to create an image copy of the database into ASM and switch to the database copy, restore database from backup sets into ASM or create duplicate database.

All of these RMAN features are available on Oracle versions before 12c.

In this post I will use slightly different approach - using online relocation of data files into ASM.


Making Copies of Copies with Oracle RMAN

I recently had need to make a copy of an image copy in Oracle rman. Since it wasn't immediately obvious to me, I thought it was worth sharing once I had it sorted out. I was familiar with making a backup of a backup, but had never thought about making a copy of a copy.

First you need to create an image copy of your database or tablespace. For the sake of example, I'll make (more...)

The Importance of Backups (A Cautionary Block Recovery Tale)

Just wanted to share a quick story with everyone. As I was in the airport waiting to fly to Oracle OpenWorld this year, I noticed a flurry of emails indicating that part of our storage infrastructure for our standby production database had failed. Long story short, my co-workers did a brilliant job of stabilizing things and keeping recovery working. However, we ended up with more than a few block corruptions.

Using the RMAN command "validate (more...)


Last week I was creating a new testing database from a backup of our demo database, both under Oracle I grabbed one of my old scripts to handle the duplicate function, which looked similar to this:

connect auxiliary /;
run {

        duplicate database to testdb
                backup location '$BACKUPDIR'


One important difference between the demo database and this new test database is that the original demo database (more...)

ORA-19909: datafile 1 belongs to an orphan incarnation

I love to read Oracle related blogs, forum posts and mailing lists much more often than books. Why? Because there many Oracle DBA’s and developers share their experiences, problems, "best practices",... which are very valuable to me.

It's great that we have so big and active Oracle community.

Today I noticed mail from Oracle-L list where someone asked for help with recovery after overwriting production controlfiles. Check Oracle-L for more info.

It reminded me that (more...)

¿Archive log perdido? No reconstruya su Standby (al estilo 12c)

Se ha escrito hasta la saciedad (incluso yo) sobre cómo re-sincronizar una base de datos Standby en aquellos casos en los cuales la sincronización se suspendió y al tratar de reiniciarla nos topamos con que al menos un archivelog requerido ya no está disponible. En este artículo que he publicado en Toad World les explico lo simple que resulta resolver este problema cuando están usando Oracle Server 12c. ¡Que disfruten la lectura!

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

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

RMAN Catalog backuppiece located on Tape

I've recorded backups on tape to RMAN repository several times already, but every next time I needed to do that I was searching through notes to find proper procedure.

This time I will note procedure in form of the blog post.

Test is performed on Oracle version

These were my unsuccessful attempts:

RMAN> run
2> {
3> allocate channel c1 device type 'sbt_tape';
4> send 'NSR_ENV=(NSR_SERVER=backup_server,NSR_CLIENT=oracle_client,NSR_DATA_VOLUME=OrclPool)';
5> catalog backuppiece 'ARCH_ORCL_rep2dod5_s128878_p1';

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

When error say nothing about real issue

We want restore backup from source. So we took backup from source and copied it to /dba/share/MYDB on target. My collouge sent me email saying  restore failed with following error

rman target /

Recovery Manager: Release - Production on Wed Sep 18 20:47:38 2013

Copyright (more...)

RMAN Duplicate from Active Database – ASM to non-ASM

In this post I want to explain how to create duplicate database from active 11gR2 database using RMAN. There are many blog posts covering that subject but most of them are covering non-ASM to non-ASM, ASM to ASM or non-ASM to ASM duplications.
I want to cover ASM to non-ASM (more...)