RMAN Configuration’s Commonest Mistake By DBAs

Oracle RMAN refers to the Oracle Recovery Manager, which is Oracle’s tool for performing database backup and recovery operations. Any database administrator [DBA] who performs backups or recovery operations for the Oracle database has used RMAN, unless the backups are taken using the Oracle export data pump utility, expdp. Expdb is used to take logical backups which are Operating System agnostic, since the backups can be used to restore the database to any Operating System platform, irrespective of the Operating System hosting the database from which the backups were taken. On the contrary, RMAN is used to take physical backups. Most third-party backup solutions also use RMAN, embedded into their logic for backing up and restoring Oracle databases.

Needless to say, any tool that is used to take backups and then restore those backups when required must have a mechanism of storing data or information about each of the backup operations that it performs. As a very robust backup and recovery tool, RMAN too stores information about each of the backup operations it performs. This way, RMAN can be used to restore any backup taken using it: RMAN does not just restore the most recent backup.

The subject of this post is the LOCATION chosen for storing backup and recovery information for backups taken using RMAN, as this is one single mistake the majority of DBA commit, from a best-practice (state-of-the-art) perspective.

There are two viable locations where information about backup and recovery operations can be stored, viz:

• The Control File of the database being backup [i.e. target database]
• The Recovery Catalog database

One core function of a Database Management System (DBMS), is to GUARANTEE TRANSACTIONAL CONSISTENCY in all the data that it captures, stores and maintains. This transactional consistency must be GUARANTEED and enforced. Potential causes of loss of transactional consistency include UNPROCEDURAL database shutdowns before all the data in a transaction is saved and synchronized in the permanent [secondary] storage [data files]. Such causes of unprocedural shotdowns include unexpected electrical power outages on the database server[s] or any software or hardware malfunction that could lead to a DBMS to stop working unexpectedly. In the event that such unprocedural shutdowns do occur, the information required to restore transactional consistency in the database is retrieved from the control file, the next time the database is started up following an unprocedural shutdown, or a shutdown with the ABORT option [SQL> shutdown abort].

DATABASE SYNCHRONIZATION DATA

The Oracle database is designed to ensure it maintains its integrity by guaranteeing transactional consistency. This is achieved by avoiding to save incomplete or inconsistent records. This is enforced by various mechanisms in the database’s engine. As the DBMS performs its normal operations, it keeps on generating CHECKPOINT numbers and SYSTEM CHANGE NUMBERS [SCNs], and writing these numbers in the control file and data files. These numbers are what the database uses to restore transactional consistency into itself following an unprocedural shutdown, by using them to synchronize all data files, since table data is stored in data files.

By now, it should be clear why the control file is a critical component of the database.

One cardinal principle of any DBMS is to keep all reference elements of its engine lean and small in size so that reading from and writing into them becomes fast enough not to cause performance degradation. Hence, any DBMS component that stores data OUGHT to be protected from ballooning and getting bloated up. This applies to the AUD$ data dictionary table, and in the same way, OUGHT to apply to the control file.

NB: AUD$ is the only Oracle data dictionary table DBAs are allowed to delete data from. Depending on the database version in use, the AUD$ data dictionary table has a specific size limit beyond which it is not recommended to grow.

Let us pause the control file details there and have a quick look at the Recovery Catalog database.

The Recovery Catalog [commonly referred to as RCAT, is the second possible location for storing RMAN backup information, in addition to the control file. Oracle provides the RCAT as a dedicated mechanism of storing RMAN backup information. The RCAT is a more flexible repository that, in addition to other benefits, enables DBAs to overcome the limitations of the RMAN backup history retained by the control file. The RCAT is actually a schema in an Oracle database.

The RCAT database OUGHT to be hosted on a different server from the server[s] being backed up for obvious reason: in the event that the server hosting the [target] database  that has been backed up suffers an outage and so requires to be restored on an alternative server, the RCAT [located on a different server] would be available to facilitate the recovery.

Given that the RCAT is in an Oracle database, it allows many diverse queries, and backup related reports to be extracted from it using SQL statements in a manner akin to querying a standard online transaction processing [OLTP] database. Other reporting tools like Analytics Publisher [formerly known as BI Publisher] can also be used to develop nice looking graphical backup and recovery reports from the RCAT for enterprise-wide use,

THE BAD HABIT IN THE STATE-OF-THE-PRACTICE BY DBAs

The majority of the DBAs I have come across in diverse organizations use the NO CATALOG option of RMAN when taking their backups. This implies that they rely only on the control file to store the backup and recovery related information that is generated by RMAN. This practice leads to the loss of older RMAN backup and recovery information [even if the associated physical backups are retained], since the control file cannot incrementally store RMAN data without limit. This is because, doing so would bloat up the control file to the point of affecting the efficiency of the DBMS operations. This is why it is not a best practice to let the control file get oversized, as the consequence would be some performance tradeoffs.

In the event of a loss in the target database, its associated control files would also potentially be lost, adding more trouble for using the control file as the RCAT database. [Even though there are mechanisms for creating a new control file for database restore purposes, the downtime as a result of the loss of control files may interfere with the recovery time objective – RTO].

THE CORRECT HABIT [MANUFACTURER’S RECOMMENDED STATE-OF-THE-ART]

The best enterprise-grade practice is to make use of a Recovery Catalog database to which RMAN writes backup related information every time it is used to conduct a backup operation: be it a full backup, an incremental backup, a backup of the archived redo logs or control files. This way, although the backup run would store the backup related data in the control file of the database being backed up, a copy of this data would also be stored in the RCAT database, where it can be retained indefinitely, depending on the policy defined by the organization’s business continuity plan (BCP).

This RCAT database SHOULD also be located on a server that is different from the ones hosting the databases being backed up. Basically, the RCAT database is normally hosted on a small virtual machine that is hosted in a physical server which is DIFFERENT from any of the physical servers hosting any of the target database[s].

As a best practice, given its importance, the RCAT database should itself be backed up after every backup run of a target database. This is because, with every backup, RMAN generates new records, which are written into the RCAT. These RCAT backups are normally in the form of EXPORT backups taken using the expdp tool. RCAT databases are normally small in size, and their expdp backups take just a few minutes to complete.

The RCAT export dump file should also be stored in a location that is different from the location of the RCAT host server for proper RCAT redundancy, as part of an enterprise’s business operations continuity strategy.

This way:

• The control file is allowed to remain lean for speedier manipulation by the DBMS without losing older backup and recovery data, just in case it becomes necessary to restore an older backup of the database say for investigative purposes. The fact that it is possible to register older RMAN backup sets by “cataloging” them does not imply proper information about available backup sets should not be retained.
• The backup and recovery information is safeguarded by separating its location from the target databases that would require it should they suffer outages or damages that would necessitate any level of restoration or recovery
• Standard reports can be designed and generated, similarly to the reports generated from transaction processing applications or from analytics platforms

USEFUL INSIGHTS

The following are important state-of-the-art practices to consider:

• Only ONE RCAT database is necessary for an entire organization, irrespective of the size of the organization
• Each target database should have its own RCAT schema in the RCAT database
• High Availability technologies available to the TARGET databases are also available to the RCAT database. For example, the RCAT database can be configured to use ASM, RAC and Data Guard. [ASM gives the DBA the leeway to configure different levels of high availability for the storage tier. These ASM high redundancy configurations are Oracle’s proprietary implementation of RAID technologies. For more information about RAID, please see our journal on “Implementation of Redundant Array of Independet Disks (RAID)“]
• The RCAT schema can also be used to store RMAN scripts in a centralized location. For example, scripts for a FULL backup, LEVEL 0 and INCREMENTAL backup scripts can be centrally store in the RCAT schema for a target database. The RMAN execution job would then “call” the scripts from the RCAT database during the respective backup run. This way, database backup and recovery information as well as backup procedures are managed centrally, which is a more effective way of approaching backup operations at the enterprise level.

It is worth noting that by default, the CONTROL_FILE_RECORD_KEEP_TIME database parameter is set to 7 days. The maximum time period allowed for this parameter is 365 days. The last backup record or information for the last RMAN backup is always retained in the control file even after 7 days since the backup ran. If more RMAN historical backup records need to be retained in the database, the value of the CONTROL_FILE_RECORD_KEEP_TIME parameter can be increased. Increasing the value of CONTROL_FILE_RECORD_KEEP_TIME , however, leads to an increase in the size of the control file. This is where an RCAT database comes in handy to retain more historical RMAN backup information without leading to a bloated control file.

Our consultancy and database support services include the implementation of RCAT databases to enable organizations achieve the highest possible level of business continuity preparedness as well as adapt RMAN best practices. Get in touch with us for support with RCAT database implementations or to update your business continuity preparedness.