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

Oracle 12c – incremental backup for DataGuard over network

If you have DataGuard or standby database in your organization you probably will love that new RMAN feature. Since 12c it is possible to catchup standby database using incremental backup using one command. Additional space and time need to run incremental backup, copy over to standby and restore can be (more...)

Slides and Offline Recording of my NYOUG Webinar

I have announced my webinar on Oracle ZFS Storage Appliance for Exadata Backup & Recovery in NYOUG DBA webinar series. You can download webinar slides and voice recod from NYOUG web site

Restoring a Database with Rman Controlfile Parameters

There are occasions when you recieve a complete backup of a database from a  place different then yours and have to do a restore. normally a restore is simple . put the files in a directory run rman catalog on them and you are done. but what if you have rman (more...)

ORA-16072: a minimum of one standby database destination is required

This is a quick post regarding the error on the subject. This is the second time it happens to me, so I thought I will write a bit about it.

The problem:

I am refreshing one of my UAT environments (happens to be a Full Rack Exadata) using Oracle RMAN duplicate command. Then the following happens (on both occasions).

1.- Duplicate command fails (lack of space for restoring archivelogs, or any other error). This is can be fixed quite easy.

2.- following error while trying to open the database after restore and recover has finished:

SQL> alter database  (more...)

Archive area +RECO has -7440384 free KB remaining (Usable_file_MB is negative)

I must say, this has been a busy weekend.

We have been promoting a release to production and a guaranteed restore point was created on Friday as rollback strategy. On Sunday I was called as we started to receive alerts.

The problem:

Our monitoring system started to send emails and SNMP Traps with the following alerts:

OEM alert for Automatic Storage Management +ASM4_ssssss4: Disk group RECO has used 100% of safely usable free space. (Current Disk Group Used % of Safely Usable value: 100)
OEM alert for Automatic Storage Management +ASM2_ssssss2: Disk group RECO has used 100% of safely usable  (more...)

Moving a datafile Online

A datafile cannot be completely moved online in a user transparent way, but what it can be done is to minimize the time required to perform the operation, and avoid the traditional procedure executed when the database is mounted, which permits more availability for the non affected production tablespaces.

The operation can be performed by creating a datafile copy, either with the traditional commands or by means of recovery manager. In this case the following script was prepared to move a datafile.

run {
copy datafile ''
to '';
set newname for datafile (more...)

Oracle RMAN command prompt hang???

This afternoon on a Oracle installation, i was trying to run the Oracle RMAN utility. Rman cli was just hanging and not going into RMAN.

[oracle@linux2 ~]$ . oraenv
ORACLE_SID = [oracle] ? 11202
The Oracle base has been set to /opt/oracle/product/rhel4/database/
[oracle@linux2 ~]$ rman

After entering above rman command, nothing happened. It just hung on the command prompt. I did not receive any error at all. Hmm… whats happening? (more...)

Last Sunday

Our production databases are backed up with one of two schedules.  Most days our backups are sent offsite and retained for 3 months before being returned.  But on the last Sunday of each month, our backups are archived for a longer period of time.  Therefore the RMAN backups and Veritas catalogs need to be retained for up to 7 years!

Us Linux DBAs love cron for scheduling jobs. These fancy software schedulers with their dependencies and clever logic have never really replaced a really dense, difficult to read crontab file. And that's just the way we like (more...)

Cold Backup RMAN Fun and Games

Here's an RMAN oddity.  If anyone could explain it, I'd be grateful.

We were moving a database to a new server.  Same OS, same version of Oracle (  The database is in Archivelog mode, but to keep things easy, we decided to do an offline, consistent backup.

So, we shutdown the database, then mounted it and performed an RMAN backup to a flash_recovery_area on a shared NFS filesystem:

backup device type disk database plus archivelog include current controlfile;
The backup worked fine. So we logged on to the new server, made sure that we could (more...)

Cool but unknown RMAN feature

Unknown to me anyway until just this week.

Some time ago I read a post about RMAN on Oracle-L that detailed what seemed like a very good idea.

The poster's RMAN scripts were written so that the only connection while making backups was a local one using the control file only for the RMAN repository.
rman target sys/manager nocatalog

After the backups were made, a connection was made to the RMAN catalog and a SYNC command was issued.

The reason for this was that if the catalog was unavailable for some reason, the backups would still succeed, which would not (more...)

RMAN Duplicate Gotchas – Part 2: Standby DB Restores and Redo Log Group

In my previous post I discussed identifying which media you would need to use when performing a restore in order to duplicate a database via RMAN.

In this post, I'll discuss a couple of other issues that you may come across; although, admittedly, they are fairly uncommon.

Standby DB Restores

Like a lot of other sites we maintain a physical standby database, managed using Dataguard, ready to use as a failover instance, in case of problems with the main RAC database. This database is the source for all backups, to avoid overloading the production servers. This leads to a small (more...)

RMAN Duplicate Gotchas – Part 1: Identifying the Media

I've recently been doing quite a few RMAN Duplicate Database jobs, in order to create dev and test systems as copies of our production server.

The production database is a 2-node RAC cluster, but each of our test systems are single-instance databases. This causes one or two odd issues which aren't always obvious until you've hit them at least once.

In this post I'll discuss the first tricky issue which I came across: Identifying the Media.

At my current job (as in most IT departments), each day the most recent backup tapes are removed from the tape robot and sent (more...)

Recovering Corrupted Data Blocks

Last Week, I experienced a kernel crash using Oracle on my Oracle Enterprise Linux 4 sandbox server. There was not a single reaction from the server, I had to reboot the box by pressing the reset button.
I happened to have a backup of this sandbox database, which gave me the opportunity to perform a media recovery.

The situation was that the undo Tablespace had a corrupt block.

Here is the alert log content:

Mon Aug 20 08:54:44 2007
SMON: enabling cache recovery
Mon Aug 20 08:54:45 2007
Errors in file /opt/oracle/app/oracle/admin/orcl/udump/orcl_ora_6733.trc:
ORA-01578: ORACLE data (more...)