Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted March 31, 2014

An RMAN Catalog: Right or Wrong?

By Mary Mikell Spence

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!

See all articles by Mary Mikell Spence



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM