Oracle Backup and Recovery: Best Practice #1: RMAN Autobackup

If you are an Oracle DBA, and you don't know RMAN, don't bet on being taken seriously in a competitive job market. More importantly, you are making your job more difficult by sheer laziness of learning. Many years have passed since Oracle 8 introduced RMAN, and it is now a fifth-generation (yes count it, five) Oracle tool. For a few years, I too trusted my hand-written Perl scripts so much I dismissed RMAN as an optional tool, but as years have passed, I have found it is more of a friend than a tool, and I eventually retired the old Perl hot backup script to the museum. RMAN has many capabilities, but let's cover RMAN Best Practice numero uno.

For all RMAN beginners, the very first thing you should know how to enable, and why, is the AUTOBACKUP option. First, what does it do? It takes care of automatic backup of both your controlfile and your dynamic parameter file, not only during routine backups, but more importantly, as physical changes are made to the database. It also makes your RMAN backup scripts simpler by removing the need to explicitly specify the requisite inclusion of the control file in the backupset.

The only reason I can conclude that Oracle does not enable the option, by default, is: in order to do so, Oracle must assume everyone uses RMAN on every database setup, in the first place. Or, perhaps it is to preserve the illusion that we DBAs still have some level of usefulness and control in the ever advancing Oracle world, but I digress. AUTOBACKUP is best practice number one. As an Oracle DBA, you should never lose your system, undo or data tablespaces, your redo logs, your control file, or your parameter file. Technically, the latter two can be re-created, the control file explicitly, and the parameter file from the alert log -- maybe, but it can be a nightmare in some cases, if you have an especially complex configuration and must resort to mining your alert log for configuration changes, or worse, some proactive system administrator has been rotating your alert log for you, you may not even have a record of your parameters or changes dating back many months.

Would you like to waste the hours in a disaster recovery trying to hurridly recreate that complex Data Guard, large SGA config param file, while your customer is on the phone asking why his database is not back up and running? It's like telling him you are trying to recover his files, but you forgot the password to access the tape server, it should NEVER HAPPEN! Wasting time is something you cannot afford in a production disaster, and wasting time on the easy things is inexcusable. I've seen this happen to more than one DBA. Oracle instances that run for months or years are especially prone to losing configuration history, especially since 9i introduced the dynamic parameter file (spfile), and most likely, with his job in jeopardy, the DBA will punt the problem to the system administrator as a "cold backup" file and hope the folks in charge aren't educated enough to know better. DBAs often do not even know how to generate an init file after changing their spfile, and now it is common for DBAs not to even understand how to use the old standby init.ora at all. They just expect the init parameters to be magically preserved as part of the database after they issue the "alter system" command. Never let this rookie mistake happen, but better yet, take the lazy way out. Use RMAN and turn on RMAN AUTOBACKUP.

We haven't even mentioned the annoyances that can happen when you lose your controlfile. Traditionally, it was not too much hassle for an experienced DBA to create a new controlfile. It is sometimes done, anyway, to change static parameters, but rarely ever by choice. With RMAN, losing the controlfile is even more of an annoyance, because it is the controlfile that ultimlately holds the RMAN repository. Creating a new controlfile will wipe all of your RMAN history, and even if you are using an RMAN catalog, you can only resync _to_ the catalog, not _from_ the catalog. With Oracle 10g and up, we can re-catalog backupsets manually, but this is, again, needless hassle, wasted time and should never happen. Don't be the DBA who has to post on Experts Exchange to ask how to re-catalog backupsets into his control file because he lost the original.

A neat bonus to using the feature, is by nature of RMAN being, in Oracle's words, kernelized, so it can be active and able even when the Oracle server instance is not, such as with cold backups. It can be called by the Oracle server, triggered by structural DDL, to backup on demand. Autobackup will not only backup your control file upon every structural change to your database, but will also include a backup of the dynamic spfile. The autobackup is not only in effect during RMAN sessions; it is active at all times. Verify this with sqlplus by adding a simple tablespace, then issue an RMAN: "list backup" command and you will see an immediate, asynchronous autobackup. Example below.

# Step 1: First turn autobackup on
rman target /

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

# Step 2: Note the latest backupset.

RMAN> LIST BACKUP;

# Step 3: Create a tablespace.

sqlplus / as sysdba
SQL> create tablespace sample datafile 'C:/sample.dbf' size 10m;

Tablespace created.

# Step 4: Now re-issue the LIST BACKUP command in RMAN
RMAN> LIST BACKUP;

You'll see the latest autobackup was taken immediately upon creation of the sample tablespace, you had to do nothing.

I don't know about you, but the more safety nets I can use, and the more redundant copies I have of critical data, the better I sleep at night.