Friday, March 11, 2016

Tip: A Counterintuitive RMAN Extraction of the Controlfile

On one of my last gigs, my DBA colleague and I needed to restore an R12.2 EBS backup to two VMs (using ESXi 5.5) built on RHEL 6.6. Typically one node serves as the database tier and the other as the Apps tier. The source servers were not available in our network; we accessed backups stored on a hard drive plugged into a docking station attached to an Ubuntu file server. There was an RMAN backup stored under a relevant folder. We then scp'ed the RMAN folder onto our target database VM. The source database version was Enterprise Edition 11G R2.

It is not my intention here to provide here a tutorial on the process of restoring datafiles to target mountpoints on your target database server. The key step is locating/extracting the controlfile from its backup piece. (The basic idea is once you locate the controlfile, you need to edit stored file mountpoints in the controlfile via RMAN or SQL*Plus for the directories on your target database server before you catalog/restore from the backup pieces.)

We had a copy of the RMAN log file, and there was an initial backup piece which seemed to have some variation of CONTROL embedded in its name, but no matter how we tried to extract the controlfile, e.g., restore controlfile from '[pathed backup piece file name]', the operation failed. Did we have a corrupt copy of the backup piece? Yet another DBA who thought he had used the same backup for an alternate restore insisted the controlfile was in that first piece.

We tried looking at My Oracle Support Knowledge Base and Googled for something relevant. But the real issue was that we had to look outside the box, i.e., the idea that the controlfile was in that first backup piece.

I don't have the exact post I stumbled across Googling that evening after coming home from work, but the gist was, if you don't find the controlfile in the first backup piece, try the extraction against each remaining backup piece and you'll eventually stumble across the backup piece with the controlfile. I intuitively knew this must be true.

The next morning I had my colleague read the post in question, and he manually retyped the command substituting the backup piece file name in sequence, finally getting an extraction near the end of the set of around 23 files.

I'm not sure I can generalize from one case, but in this case we had the first n-1 backup pieces the same size and the controlfile was in an odd-sized/smaller backup piece.