As I have discussed in this column before, availability is the most important issue faced by eDBAs in managing the database environment for an e-business. An e-business, by definition, is an online business - and an online business should never close.

Customers expect Web applications to deliver full functionality regardless of the day of the week or the time of day. And never forget that the Web is worldwide - when it is midnight in New York it is still prime time in Singapore. Simply put, an e-business must be available and operational 24 hours a day, 365 days a year.

Recovery-To-Current

A useful database recovery strategy must plan for many different types of recovery. The first type of recovery that usually comes to mind is a recovery-to-current to handle some sort of disaster. This disaster could be anything from a simple media failure to a natural disaster destroying your data center. Applications may be completely unavailable until the recovery is complete.

These days, outages due to simple media failures can often be avoided by implementing modern disk technologies such as RAID. RAID, an acronym for Redundant Arrays of Inexpensive Disks, is a technology that combines multiple disk devices into a single array that is perceived by the system as a single disk drive.

There are many levels of RAID technology and, depending on the level in use, different degrees of fault-tolerance that are supported. For more details on RAID, please see the accompanying sidebar.

Another desirable aspect of RAID arrays is the ability to use hot swappable drives so the array does not have to be powered down to replace a failed drive. Instead, a drive can be replaced while the array is up and running - and that is a good thing for eDBAs because it enhances overall data availability.

A disaster that takes out your data center is the worst of all possible situations and will definitely result in an outage of some considerable length. The length of the outage will depend greatly on the processes in place to send database copies and database logs to an off-site location.

Overall downtime for a disaster also depends a good deal on how comprehensive and automated your recovery procedures are at the remote site. The eDBA should be prepared with automated procedures for handling a disaster.

But simple automation is insufficient. The eDBA must ensure the consistent backup and offsite routing of not just all of the required data, but also the IT infrastructure resources required to bring up the organization's databases at the remote site.

This is a significant task that requires planning, periodic testing and vigilance. The better the plan, the shorter the outage and the smaller the impact will be on the e-business. Consider purchasing and deploying DBA tools that automate backup and recovery processes to shorten the duration of a disaster recovery scenario.

Of course, other considerations are involved if your entire data center has been destroyed. The resumption of business will involve much more than being able to re-deploy your databases and get your applications back online. But those topics are outside the scope of this particular article.

Point-in-Time Recovery

Another type of database recovery is a Point-in-Time (PIT) recovery. PIT recovery usually is performed to deal with application level problems. Conventional techniques to perform a point-in-time recovery will remove the effects of all transactions performed since a specified point in time. The traditional approach will involve an outage. Steps for PIT recovery include:

1. Identifying the point in time to which the database should be recovered. Depending on the DBMS being used, this can be to an actual time, an offset on the database log, or to a specific image copy backup (or set of backups). Care must be taken to ensure that the PIT selected for recovery will provide data integrity, not just for the database object impacted, but for all related database objects as well.
2. The database objects must be taken off-line while the recovery process applies the image copy backups.
3. If the recovery is to a PIT later than the time the backup was taken, the DBMS must roll forward through the database logs applying the changes to the database objects.
4. When complete, the database objects can be brought back online.

The outage will last as long as it takes to complete steps 2 through 4. Depending on the circumstances, you might want to make the database objects unavailable for update immediately upon discovering data integrity problems so that subsequent activities do not make the situation worse. In that case, the outage will encompass Steps 1 through 4.

Further problems can ensue if there were some valid transactions after the PIT selected that still need to be applied. In that case, an additional step (say, Step 5) should be added to re-run appropriate transactions. That is, if the transactions can even be identified and re-running is a valid option.

Overall, the quicker this entire process can be accomplished the shorter the outage. Step 1 can take a lot of time and the more it can be automated the better. Tools exist which make it easier to interpret database logs and identify an effective PIT for recovery. For the e-business, this type of tool can pay for itself after a single usage if it significantly reduces an outage and enables the e-business application to come back online quickly.

Transaction Recovery

A third type of database recovery exists for e-businesses willing to invest in sophisticated third-party recovery solutions. Transaction Recovery addresses the shortcomings of traditional recoveries by reducing or eliminating downtime and avoiding the loss of good data.

Simply stated, Transaction Recovery is the process of removing the undesired effects of specific transactions from the database. This statement, while simple on the surface, hides a bevy of complicated details. Let's examine the details behind the concept of Transaction Recovery.

Traditional recovery is at the database object level: for example, at the data space, table space or index level. When performing a traditional recovery, a specific database object is chosen. Then, a backup copy of that object is applied, followed by reapplying log entries for changes that occurred after the image copy was taken.

This approach is used to recover the database object to a specific, desired point in time. If multiple objects must be recovered, this approach is repeated for each database object impacted.

Transaction recovery uses the database log instead of image copy backups. Remember that all changes made to a relational database are captured in the database log. So, if the change details can be read from the log, recovery can be achieved by reversing the impact of the logged changes. Log-based transaction recovery can take two forms: UNDO recovery or REDO recovery.

For UNDO recovery, the database log is read to find the data modifications that were applied during a given timeframe and:

• INSERTs are turned into DELETEs
• Deletes are turned into Inserts
• UPDATEs are turned around to UPDATE to the old value

In effect, an UNDO recovery reverses database modifications using SQL. The traditional DBMS products do not provide native support for this. To generate UNDO recovery SQL, you will need a third-party solution that understands the database log format and can create the SQL needed to undo the data modifications.

An eDBA should note that in the case of UNDO Transaction Recovery, the portion of the database that does not need to be recovered remains undisturbed. When undoing erroneous transactions, recovery can be done online without suffering an outage of the application or the database. UNDO Transaction Recovery is basically an online database recovery.

Of course, whether or not it is desirable to keep the database online during a Transaction Recovery will depend on the nature and severity of the database problem.

The second type of Transaction Recovery is REDO Transaction Recovery. This strategy is a combination of PIT recovery and UNDO Transaction Recovery with a twist. Instead of generating SQL for the bad transaction that we want to eliminate, we generate the SQL for the transactions we want to save. Then we do a standard PIT recovery eliminating all the transactions since the recovery point. Finally we reapply the good transactions captured in the first step.

Unlike the UNDO process, which creates SQL statements that are designed to back out all of the problem transactions, the REDO process re-creates SQL statements that are designed to reapply only the valid transactions from a consistent point of recovery to the current time.

Since the REDO process does not generate SQL for the problem transactions, performing a recovery and then executing the REDO SQL can restore the data to a current state that does not include the problem transactions.

A REDO Transaction Recovery requires an outage for the PIT recovery. When redoing transactions in an environment where availability is crucial, the database can be brought down during the PIT recovery and when done, the database can brought back online. The subsequent redoing of the valid transactions to complete the recovery can be done with the data online, thereby reducing application downtime.

In contrast with the granularity provided by traditional recovery, Transaction Recovery allows a user to recover a specific portion of the data based on user-defined criteria. So only a portion of the data is affected. And any associated indexes are automatically recovered as the transaction is recovered.

Additionally, with Transaction Recovery the transaction may impact data in multiple database objects. A traditional recovery is performed object by object through the database. A transaction is a set of related operations that, when grouped together, define a logical unit of work within an application.

Transactions are defined by the user's view of the process. This might be the set of panels that comprise a new hire operation. Or perhaps the set of jobs that post to the General Ledger. Examples of user-level transaction definitions might be:

• All Updates issued by userid DSGRNTLD since last Wednesday at 11:50 AM.
• All Deletes made by the application program PAYROLL since 8:00 PM yesterday.

Why is Transaction Recovery a much-needed tool in the arsenal of eDBAs? Well, applications are prone to all types of problems, bugs and errors. Using Transaction Recovery, the DBA can quickly react to application-level problems and maintain a higher degree of data availability. The database does not always need to be taken off-line while Transaction Recovery occurs (it depends on the type of Transaction Recovery being performed and the severity of the problem).

Choosing the Optimum Recovery Strategy

So, what is the best recovery strategy? Of course, the answer is - it depends. While Transaction Recovery may seem like the answer to all your database recovery problems, there are times when it is not possible or not advisable. To determine the type of recovery to choose, you need to consider several questions:

• Transaction Identification. Can all the problem transactions be identified? You must be able to actually identify the transactions that will be removed from the database. Can all the work that was originally done be located and redone?
• Data Integrity. Has anyone else updated the rows since the problem occurred? If they have, can you still proceed? Is all the data required still available? Recovering after a REORG, LOAD or mass DELETE may require the use of image copy backups. Will any other data be lost? If so, can the lost data be identified in some fashion?
• Availability. How fast can the application become available again? Can you afford to go off-line? What is the business impact of the outage?

These questions actually boil down to a matter of cost. What is the cost of rework and is it actually possible to determine what would need to be redone (what jobs to run, what documents to reenter)? This cost needs to be balanced against the cost of long scans of log data sets to isolate data to redo or undo, and the cost of applying that data using SQL.

The ultimate database recovery solution should analyze your overall environment and the transactions needing to be recovered, and recommend which type of recovery to perform. Furthermore, it should automatically generate the appropriate scripts and jobs to perform the recovery to avoid the errors that are sure to be introduced with manually developed scripts and jobs.

Database Design

In some cases you can minimize the impact of future database problems by properly designing the database for the e-business application that will use the database. For example, you might be able to segment or partition the database by type of customer, location, or some other business criterion whereby only a portion of the database can be taken off-line while the rest remains operational.

In this way, only certain clients will be affected, not the entire universe of users. Of course, this approach is not always workable, but sometimes "up front" planning and due diligence during database design can mitigate the impact of future problems.

Reducing the Risk

These are just a few of the recovery techniques available to eDBAs to reduce outages and the impact of downtime for e-businesses. For example, some disk storage devices provide the capability to very quickly "snap" files using hardware techniques - the result being very fast image copy backups. Some recovery solutions work well with these new, smart storage devices and can "snap" the files back very quickly as well.

Other solutions exist that back out transactions from the log to perform a database recovery. For eDBAs, a backout recovery may be desired in instances where a problem is identified quickly. You may be able to decrease the time required to recover by backing out the effects of a bad transaction instead of going back to an image copy and rolling forward through the log.

The bottom line is, as an eDBA you need to keep up-to-date with the technology available to reduce outages - both hardware and software offerings - and you need to understand how these technologies can work with your database environment.

Remember that recovery does not always have to involve an outage. Think creatively, plan accordingly and deploy diligently, and you can deliver the service required of e-database administration. With proper planning and wise implementation of technologies that minimize outages, you can maintain high availability for your Web-enabled databases and applications.

--

Craig Mullins is an independent consultant and president of Mullins Consulting, Inc. Craig has extensive experience in the field of database management having worked as an application developer, a DBA, and an instructor with multiple database management systems including DB2, Sybase, and SQL Server. Craig is also the author of the DB2 Developer’s Guide, the industry-leading book on DB2 for z/OS, and Database Administration: Practices and Procedures, the industry’s only book on heterogeneous DBA procedures. You can contact Craig via his web site at http://www.craigsmullins.com.