It is odd how topics to write on will present themselves. I was working in the yard recently and struck up a conversation with my neighbor—who also happens to work in IT. He, self-admittedly, is no DBA. But, he has a small Oracle database that he is tasked with managing and had been setting up RMAN backups. He related to me what someone had told him, which was that a catalog was required in order to recover the database. What?! Nothing could be further from the truth.
I have been using RMAN for over ten years now. In the early days, when everyone was struggling with exactly how this darned thing worked, I think that RMAN catalogs were implemented more often than not. It just seemed the safe and prudent thing to do. Today, however, as we’ve all gotten a bit more comfortable, I think the opposite is the case. Speaking just for myself–and I manage databases for a number of diverse clients—I have exactly one production database that is using an RMAN catalog.
What is the RMAN Catalog?
The RMAN catalog is a schema created in a separate database from your target databases (i.e. the databases that you backup) using the CREATE CATALOG command. You should first create the username (RMAN is a good choice) and tablespace to hold the schema objects:
create tablespace rcat datafile '/u01/oradata/RMANCAT/rcat01.dbf' size 128m autoextend on next 128m maxsize 2g ; CREATE USER rman IDENTIFIED BY backup TEMPORARY TABLESPACE temp DEFAULT TABLESPACE rcat QUOTA UNLIMITED ON rcat ;
Then, create the catalog using RMAN:
$ rman catalog rman/backup RMAN> create catalog ;
The schema is essentially comprised of the same information regarding the database backups which is stored in the database controlfiles of the target databases. When a backup, recovery, clone, or even a show command is run with the nocatalog option, you will see this message:
using target database control file instead of recovery catalog
Whether using a catalog or not, the RMAN utility provides for reporting on backups. However, that same information can be found in the V$ views. The V$ views are what Oracle calls “fixed views” and are stored in the controlfile. If all you have available to you is a controlfile, you can mount the database and query those views. Oracle even has a view that tells you which views are available. The following list pertains to backup information:
SQL> r 1 select name 2 from v$fixed_table 3 where name like 'V$%' 4 and name like '%BACKUP%' 5* order by 1 NAME ------------------------------ V$BACKUP V$BACKUP_ARCHIVELOG_DETAILS V$BACKUP_ARCHIVELOG_SUMMARY V$BACKUP_ASYNC_IO V$BACKUP_CONTROLFILE_DETAILS V$BACKUP_CONTROLFILE_SUMMARY V$BACKUP_COPY_DETAILS V$BACKUP_COPY_SUMMARY V$BACKUP_CORRUPTION V$BACKUP_DATAFILE V$BACKUP_DATAFILE_DETAILS V$BACKUP_DATAFILE_SUMMARY V$BACKUP_DEVICE V$BACKUP_PIECE V$BACKUP_PIECE_DETAILS V$BACKUP_REDOLOG V$BACKUP_SET V$BACKUP_SET_DETAILS V$BACKUP_SET_SUMMARY V$BACKUP_SPFILE V$BACKUP_SPFILE_DETAILS V$BACKUP_SPFILE_SUMMARY V$BACKUP_SYNC_IO V$RMAN_BACKUP_JOB_DETAILS V$RMAN_BACKUP_SUBJOB_DETAILS V$RMAN_BACKUP_TYPE V$UNUSABLE_BACKUPFILE_DETAILS 27 rows selected.
To ensure that enough information regarding your backups is maintained in the controlfile, you should set the initialization parameter, control_file_record_keep_time, to some value greater than what your RMAN retention policy is. Even if you don’t set a retention policy, I would recommend setting this parameter to 31. This ensures that a month’s worth of backup information will be available to you in your controlfile.
Within RMAN, you also need to “set controlfile autobackup on.” This ensures that the current controlfile is backed up the end of your backup. This is the controlfile that you will want to restore if a database recovery becomes necessary.
Make Those V$ Views Work For You
If recovering or cloning from a tape backup, it can be daunting to understand exactly where the backup pieces are. More than once, I’ve kicked off a clone just to have it hang there, waiting for a tape. The V$BACKUP_PIECE view can help out here:
SQL> desc v$backup_piece Name Null? Type ----------------------------------------- -------- ------------------- RECID NUMBER STAMP NUMBER SET_STAMP NUMBER SET_COUNT NUMBER PIECE# NUMBER COPY# NUMBER DEVICE_TYPE VARCHAR2(17) HANDLE VARCHAR2(513) COMMENTS VARCHAR2(64) MEDIA VARCHAR2(65) MEDIA_POOL NUMBER CONCUR VARCHAR2(3) TAG VARCHAR2(32) STATUS VARCHAR2(1) START_TIME DATE COMPLETION_TIME DATE ELAPSED_SECONDS NUMBER DELETED VARCHAR2(3) BYTES NUMBER IS_RECOVERY_DEST_FILE VARCHAR2(3) RMAN_STATUS_RECID NUMBER RMAN_STATUS_STAMP NUMBER COMPRESSED VARCHAR2(3)
The HANDLE gives you the name of the file and the MEDIA column gives you the name of the tape. Over the years, the following query has come in very handy when trying to narrow down what tapes are needed for a recovery:
SQL> r 1 select handle, media, start_time, completion_time 2 from v$backup_piece 3 where start_time >= trunc(sysdate-1) 4* order by start_time HANDLE MEDIA START_TIM COMPLETIO ---------------------- -------------------- --------- --------- gjp46c94_1_1 N00531 27-MAR-14 27-MAR-14 gkp46c9c_1_1 N00531 27-MAR-14 27-MAR-14 glp46co7_1_1 N00531 27-MAR-14 27-MAR-14 gpp46d8v_1_1 BD0934 27-MAR-14 27-MAR-14 gqp46d9o_1_1 BD0934 27-MAR-14 27-MAR-14 grp46db6_1_1 BD0934 27-MAR-14 27-MAR-14 gsp46dgl_1_1 BD0934 27-MAR-14 27-MAR-14 . . .
To find what tapes were used in backups over the past two days, you could issue the following query:
SQL> r 1 select distinct media 2 from v$backup_piece 3* where start_time >= sysdate-2 MEDIA -------------------- N00575 BD0934 BD0257
If you are looking to find what tape a particular backup piece ended up on, try something like the following:
SQL> r 1 select media 2 from v$backup_piece 3* where handle = 'j9p494j4_1_1' MEDIA -------------------- ER0899
Once you know what RMAN is waiting on, you can get that tape mounted and you’re on your way.
Don’t Forget the Log File
Another invaluable resource is the simple log file. A simple, low-tech solution adhering to the K.I.S.S. principle (Keep It Simple, Silly). Your RMAN logs are going to tell you the DBID for your database, exactly when something was backed up (particularly if you set NLS_DATE_FORMAT to something like’Mon DD YYYY HH24:MI:SS’), and what the names of all of the backup pieces are. I’m actually shocked when I find that people do not maintain at LEAST a week’s worth of log files for their backups. I maintain at least a month’s worth–but that’s just me.
Remember the ‘controlfile autobackup’ mentioned earlier? Toward the end of the log file for your backup you will see something like:
Starting Control File and SPFILE Autobackup at Mar 24 2014 01:08:03 piece handle=/orabackup/PROD/c-2751435724-20140324-01 comment=NONE Finished Control File and SPFILE Autobackup at Mar 24 2014 01:08:09
To perform a recovery, first ‘startup nomount’ your database. Then, from within RMAN issue:
RMAN> restore controlfile from '/orabackup/PROD/c-2751435724-20140324-01' ; RMAN> alter database mount ; RMAN> restore database ;
At this point you can open a SQLPlus session and query V$ views, if the spirit moves you. The point is that, with that controlfile, you now have all of the information that you need to get your database restored and recovered.
No catalog? No problem.
Between the fixed views that are available in the controlfile, and the wealth of information available in your RMAN log files from your backups, you’ve got all of the information that you need to recover a database from your RMAN backups. To me, the RMAN catalog is just one more thing to maintain. The only time I’ve needed it was when I wanted to set a backup to never go obsolete (using the KEEP FOREVER clause). On the other hand, one might rightly argue that the RMAN catalog is small and having it gives you one more level of protection. In any event, do not despair if you have no catalog. You’re covered!
