April 2007

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users
April 2007
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Querying the RMAN Catalog

You’re invited to submit your Oracle-related questions to us at askdbspecialists04@dbspecialists.com. Please include your name and telephone number in your email.

This month’s question comes to us from Greg in Pittsburgh, PA: We have a fairly complex backup scenario using Legato, a number of tape farms and virtual tape libraries, and approximately 600 databases across 350 Oracle hosts. As such, it is nearly impossible to monitor each host’s RMAN message log and determine if a backup has failed or succeeded. Is there a way to do so by querying the RMAN catalog and producing a report each morning?

Terry Sutton of the Database Specialists team responds: One of the main benefits of using a recovery catalog with RMAN is the ability to have information about the backups of all your databases stored in one central repository. In the schema of the catalog owner is a set of views with names beginning “RC_”. The actual views in the catalog vary depending on the version of Oracle used for the catalog. Oracle 8i has 24 RC_ views, while Oracle 10g has 53.

Using the RC_ views in the catalog, it would be straightforward to generate a daily backup report covering all of your instances. Each of the RC_ views has a db_key column, which is a unique identifier for the target databases. This column can be used to form a join between RC_ views.

Your starting point would be the RC_DATABASE view, which has the following columns:

- db_key
- dbinc_key
- dbid
- name
- resetlogs_change#
- resetlogs_time

The dbinc_key column is used to form a join with the RC_DATABASE_INCARNATION view, which lists information about all database incarnations registered in the recovery catalog. (A new incarnation is created whenever you open a database with the RESETLOGS option.) So if we want to see a list of the databases registered in the catalog (for simplicity, we’ll assume that we’re only interested in current incarnations), the query would be:

SELECT D.name, D.dbid 
FROM   rc_database D, rc_database_incarnation I
WHERE  I.current_incarnation = 'YES'
AND    I.db_key = D.db_key
AND    I.dbinc_key = D.dbinc_key;

The dbid in RC_DATABASE is the same as the dbid in a database’s V$DATABASE view. So this column can be used to join RC_ views with data in target databases. For example, you could query both a target database and the recovery catalog (using a database link) to list the completion time of the latest backup of each datafile in the target database. The query might look like this:

SELECT   D.file#, B.name, MAX (D.completion_time)
FROM     v$database A, v$datafile B,
         rc_database@RMAN C, rc_backup_datafile@RMAN D
         rc_database_incarnation@RMAN E
WHERE    C.dbid = A.dbid
AND      D.db_key = C.db_key
AND      D.file# = B.file#
AND      E.db_key = C.db_key
AND      E.dbinc_key = C.dbinc_key
AND      E.current_incarnation = 'YES'
GROUP BY D.file#, B.name;

By using the RC_ views from the recovery catalog, you can access extremely detailed information about backups of your databases. We encourage you to investigate the RC_ views in your version of Oracle, and see how they can simplify your reporting and notification procedures. More information on the views can be found in the Oracle Database Backup and Recovery Reference manual in the Oracle documentation library.

COLLABORATE07 Conference

If you are going to the COLLABORATE07 conference April 15-19, don’t miss Database Specialists. In keeping with our goal of sharing knowledge with the Oracle community, we are pleased to announce that Database Specialists’ CTO, Roger Schrag, will present two technical sessions at this annual conference of the Independent Oracle Users Group in Las Vegas, Nevada. Schrag has been an invited speaker at these events for many years.

This year, he will present the following two topics:

For more information on the COLLABORATE07 event which takes place at the Las Vegas Mandalay Bay Resort and Convention Center.

NoCOUG Spring Conference

The Spring Conference of the Northern California Oracle Users Group is another great learning opportunity for Oracle DBAs and Developers. Steve Lemme of Computer Associates will give the keynote address titled “Fusion: The New Frontier – Oracle Administration in the Future.” So, mark your calendar right now for Thursday, May 17, 2007 at Lockheed Martin in Sunnyvale.

This full-day educational event will also feature eleven technical presentations, vendor exhibits, book raffles, networking, and more—all geared towards Oracle DBAs, developers, architects, and team leads. Sessions include:

  • RAC for Beginners: The Basics by Dan Norris of IT Convergence
  • Managing the Data Exchange Relationship by Michael Scofield of ESRI, Inc.
  • Writing Maintainable Code by Steven Feuerstein of Quest Software

The full conference agenda can be found at http://www.nocoug.org/next.html. In keeping with our support of the Oracle community, Database Specialists will be exhibiting at the event. If you plan to attend, please stop by our booth and say hello.

A Fountain of Information for DBAs and Developers

Courtesy of the speakers of the last meeting of the Northern California Oracle Users Group, you now have access to slides and presentation notes on a variety of topics addressed at the group’s last conference.

Here’s a sampling of what you’ll find on NoCOUG’s website at http://www.nocoug.org/presentations.html :

  • Oracle’s ASM Reduces Cost of Deploying VLDB by Hanan Hit and Lina Shabelsky, SkyRider
  • Thinking Out of the Box: Redefining Database Storage Management with ASM by Ara Shakian, Oracle Corporation
  • RMAN in the Trenches: To Go Forward, We Must Backup by Philip Rice, UC Santa Cruz
  • Fixing Broken SQL by Dan Tow, Singing SQL

Leave a Reply

Your email address will not be published. Required fields are marked *