Renaming Files for RMAN Restore

We usually need to duplicate our databases to test servers which have different disk layouts than production server. So we need to distribute the datafiles according to the disk space and directory structure of the new server. It becomes a time-consuming task if you’re dealing with hundreds of datafiles. Today I saw a PL/SQL script written by Bobby Durrett for renaming datafiles for RMAN restore. It produces required “set newname” commands which can be used by RMAN. That was a great idea and I decided to write my own version to satisfy my needs:

Bash Script to Upload RMAN Backups via FTP

Our customer asked if they can automatically upload latest RMAN backups via FTP to another server. Because they keep latest 3 backups in same directory, I wrote a small bash script which calls an SQL script to queries RMAN backups and then uploads only the backup pieces belong to the latest backup job. I wanted to share this script because it demonstrates some interesting methods such as handling arrays returned from SQLPLus.

Here’s the SQL script (rmanfind.sql) to query RMAN backups. Our customer takes archive log backups during the day, so I needed to state full backup jobs while querying.

Backup and Recovery Best Practices

Here are the best practices for backup and recovery for Oracle Databases. Although most of them are basic principles and can be applied to all Oracle versions, some of them are only valid for Oracle 10g and newer databases:

1) No need to explain: You must always run your DB in Archivelog mode!

2) Multiplex the controlfile at least 3 different locations. Be sure that they are located in different partitions (if possible different physical disks).

3) Multiplex the online redo log groups and members, be sure that putting them in different partitions (if possible different physical disks):

4) Take regular backups and verify that they work! Use validate command of RMAN or restore them to a test environment periodically:

Performing Block Media Recovery with RMAN

We’ll corrupt a block on a datafile and try to restore it via RMAN. First let’s create a sample tablespace:

Then let’s create a table in this tablespace and insert a sample row:

Now we need to find the block number of this record (so we can corrupt it):

Before we corrupt the datablock, we’ll take a full backup.

After the backup completed, we’ll fill this block with zeros at the OS level.

Oracle 11g New Feature: RMAN Data Recovery Advisor

In Oracle 11g, RMAN provides a new advisor called Data Recovery Advisor (DRA). DRA has several new commands to help DBAs for performing recovery tasks.

To list of database failures, you can issue:

DRA will consolidate related failures into a single failure. You can list a failure individually by issuing “list failure X detail” command.