Database News You Can Use
A monthly newsletter for Oracle usersApril 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 firstname.lastname@example.org. 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.
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.
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:
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.
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 :