15.8. What Can Happen to an RDBMS?
A lot of things can happen to interfere with the normal operation of a database. What you need to do to get the database running again will depend on what broke it in the first place. The following is a list of some of the things that can happen to an RDBMS:
15.9. Backing Up an RDBMS
Protecting an RDBMS is very complex. There are several storage elements, including datafiles, rollback logs, transaction logs, and the master database. How do you get all of the data to a secondary storage medium if it's changing all the time?
15.9.1. Physical and Logical
Raw Devices Versus Cooked Files
This is an often-debated topic in DBA circles. Many DBAs want to use raw devices because they believe they are faster than using filesystem files. Some DBAs prefer to use filesystem (cooked) files due to their ease of administration.
The historical reasons for using raw partitions were increased performance and data integrity. A database spends a lot of time trying to ensure all the data is in its proper state. It logs every changed page and
Modern databases resolve this problem by opening a cooked file in
mode, which automatically flushes any changes from the OS buffer to the disk. This means that this historical argument is no longer true with most databases. Therefore, the only remaining reason for using raw partitions as datafiles is performance. Many claim a 5 to 15 percent performance increase over filesystem files. Your mileage may vary; these tests were performed on a closed track with a professional driver;
Chapter 2 includes a section called "Are You Backing Up What You Think You're Backing Up?" It talks about how your backup programs should be written so that everything in your system is automatically
You need some way to ensure that every database instance on every server is being backed up. Commercial backup products can automatically ask the operating system which
Since the files described here are not always used and yet should be, I'd like to
This important topic often is misunderstood. The confusion comes from Sybase and SQL Server documentation that often refers to a transaction log dump as an incremental backup. They are not the same thing!
What is the difference between the two? An incremental backup is a special backup that contains only the changed pages (blocks) since the last full (or higher level incremental) backup. A transaction log dump is a backup of all the transactions that have occurred since the last transaction log dump. They may sound similar, but they're not. The latter is much more difficult to manage and much slower to read.
Perhaps the best way to
Now assume the same scenario as the
Besides the difference in complexity, reading an incremental backup also is much quicker than reading a transaction log backup. Ask
DB2 has incremental backups , which back up all changes since the last full backup, and delta backups , which back up only changes since the last backup of any kind. They also have transaction log backups. Oracle and Informix support multiple levels in their backups (e.g., level 0, level 1, etc.).
You don't have to use a high-priced commercial utility to back up your databases. Doing so
This is one of the most popular ways to do homegrown database backups. It's fast, clean, and easy. The basic idea uses a script that backs up the database to disk. That backup is treated as a regular file and backed up by the nightly filesystem backup. You can even save the amount of disk space needed by compressing the files after they're backed up. If you're really pressed for space, you can use named pipes to compress the backup
it's being written. In that case, you would need a backup disk that is only one-third to one-half the
You can use homegrown backup scripts to back up to a dedicated tape drive. This is a little more complicated and requires somewhat more work on your part. Depending on the size of your database, this may be more or less expensive than backing up to disk, but it definitely will be slower. It is also more complex, because you must keep track of each volume and label it in such a way that you know which database was backed up to it. (If you back up to disk, this can be done by naming the backup file the same
I assume that you are doing the preceding backups using some
This is one of the biggest growth markets within the backup product industry. Most commercial filesystem backup products now have interfaces to automatically back up your database to volumes that are managed by their product. It's really a beautiful thing, but it does come at a price!
Each major database vendorincluding all those covered in this bookhas a backup utility API that commercial backup products can program to that allows them to back up that database. (The commercial backup product is often called a
.) On a high level, these backup utilities all work in
These backup utilities sometimes will not function without a storage manager. For example, Oracle's
must have a storage manager to talk to in order to back up to tape, although
can back up to a disk without a storage manager. In contrast, Sybase's and SQL Server's
, and Exchange's
can back up to tape or disk without any storage manager present. Since Oracle and Informix didn't want to force their customers to buy a storage manager or the interface to their backup utility, they came up with a compromise. Both vendors bundled a free, stripped-down version of a storage manager that gives you enough functionality to be able to use
to do backups. Figure 15-5 uses Oracle and this storage manager to illustrate the different pieces of the backup puzzle. Oracle uses
to interface with the database on one side and the storage manager's database module on the other side. The storage manager communicates with the backup media on one side and the database module on the other side. The storage manager then uses the database module to interface between the storage manager and
. The backup data flows from the Oracle database, through
, tHRough the database module, through the storage manager, and to the backup media.
Utilities for each of the big databases have their own backup and recovery history.
db2 backup has a long history and has always kept backup and recovery in mind. You can restore databases created on different types of platforms, and the restore utility can also be used to restore backup images that were produced on a previous version of DB2 (up to two versions earlier) as long as the word size (32- or 64-bit) is the same. You can even back up a database created on Linux for zSeries (mainframe) and restore it to an AIX-based DB2 UDB server. DB2 has also recently added the recovery command, which helps simplify restores.
Exchange is a special-purpose database and does not have a built-in backup command. However, there is an API that
interfaces with, so you can use
to back up and recover a live Exchange database. You can also back up and recover at the storage
Informix backups have historically been done with
), which is a standalone backup command designed to back up to tape.
is simple, has incremental capabilities, also can back up to disk, and backs up the database live. Some of these features, which always were assumed by Informix users to be present in other database systems, are just now appearing in other products. Informix also has
, which is designed
How you back up a MySQL database depends on which storage engine you use. Each major storage engine has its own backup and recovery method.
Oracle databases can be backed up with the Recovery Manager, or
has come a long way since its introduction and is a much easier way to back up and restore your database than what Oracle now calls
There are two main ways to back up PostgreSQL databases. The first is equivalent to the user-managed hot backups in Oracle, where you run a script that puts the database in backup mode, then copies its files. The second is a command ( pg_dump or pg_dumpall ) that dumps the entire database into a large text or binary file.
Sybase has come a long way in the backup arena. You can now recover an individual tablespace or device. Unfortunately, since many people use only the default segment or filegroup, this means most DBAs have to restore the entire database or nothing at all. Creating additional segments or filegroups could help with this issue.
The SQL Server backup utility provides basic backup and restore capabilities. It enables you to do incremental, differential, full, and transaction log backups without third-party tools.