0509-0512

Previous Table of Contents Next

Page 509

CHAPTER 22

Backup and Recovery

IN THIS CHAPTER

• Importance of Backups 510
• Terminology 511
• Requirements for Backups 519
• Types of Backups 522
• Alternative Backup Methods 532
• Types of Database Failure 534
• Recovery Methods 537
• Sample Database Backup Scripts 540

Page 510

It is 3 a.m. Do you know where your data is?

The answer depends on the type of business that your Oracle RDBMS supports. Some systems such as data warehouses and other types of decision-support systems (DSS) sustain little overall activity during the day, but perform database-intensive, table-reloading operations during off-peak hours. OLTP systems typically reserve long-running query reports or resource- intensive batch jobs for the night hours.

Of course, there is not a right or wrong answer. Every site is a unique environment and has its own business requirements. However, it is important to know what is happening on an Oracle database during typical operations so that you know how to react when the telephone rings in the middle of the night and a voice on the other end informs you that a disaster has occurred. The disaster can be anything from a database instance that has abnormally terminated to a physical disk-drive failure. If you have not already planned a formal backup and recovery procedure, it would be too late to start.

Backup and recovery are among the most important considerations on any system, but they generally receive less than 1 percent of the total planning, developing, and testing effort. Suppose you were a project leader. Would you allow a developer who is responsible for mission-critical corporate applications to read a few articles, kludge together a functional program, and deploy it in a production environment with only cursory testing? This is precisely the approach that most DBAs take with backup and recovery procedures.

In their defense, though, backups usually seem to work. Scripts are run, copies are made to tape or disk, and someone dutifully checks to make sure that nothing abnormal has happened . No one knows whether this works until the system is down and the backup is needed to restore production operations. In many cases, there are political conflicts over whose responsibility it is to make backups. At some sites, the system administrator is responsible. Other sites make each individual administrator responsible for his own database or system. A few sites rely on an operations group that assumes responsibility for backups.

Importance of Backups

In the past, when the economy was centered primarily on agriculture or manufacturing, businesses were especially concerned with production and materials. As the information age becomes a reality, backups become important because they safeguard the only thing that a business cannot replacedata. Data takes many different formscustomer names , part numbers , purchase orders, and so on. When businesses mostly existed as regional companies, the loss of data was less significant than that of companies yielded by today's growing industries. For a multinational corporation today, however, the loss of even a single hour 's data can result in thousands of dollars of damage.

In the event of a catastrophe, it is imperative that the data be recovered. Two factors are critical for success: accuracy and timeliness.

Page 511

If a backup does not properly record all the information necessary to recover the database, it is worthless. A backup that fails to provide a critical piece of information, such as a required database file, cannot be used to restore operations. It is essential, therefore, that all the necessary database components be part of scheduled backups. Likewise, recovery of a mission-critical database that takes several days is, in most cases, unacceptable. A backup must expeditiously and completely restore the database after a failure occurs.

Terminology

At the functional level, Oracle is simply a collection of physical data files that reside on one or more hard drives , such as the hard drive on a PC, a midrange system, or a mainframe. Thus, backing up the system becomes a matter of knowing which data files to copy to tape or disk through standard copy utilities.

Because of the large amount of processing done by the background processes that comprise an Oracle database instance, making copies of these files gets complicated. To satisfy data consistency requirements, Oracle constantly updates its database files. Most operating systems copy files in blocks, so the snapshot taken of a file when it is copied might not be the same snapshot that it finishes with. (See Figure 22.1.)

Figure 22.1.
Operating system
backup.

In Figure 22.1, the file FOOBAR is 20,000 bytes (a little less than 20KB). Assuming that the operating system copies in 2,048-byte (2KB) blocks, it can successfully copy the file in 10 packets. Compare this with Figure 22.2.

Page 512

Figure 22.2.
Operating system
backup of Oracle
data file.

In Figure 22.2, the USERS tablespace is composed of two physical data files, each of which is 2MB (2,048KB) in size . It takes the same operating system utility 512 separate packets to copy each file. During this time, the Oracle RDBMS continues to update the data files. Because of this, the snapshot of the database file taken by the utility is corrupt, incorrect, and unusable. Obviously, more is involved in making backups than just performing a copy with an operating system utility.

Redo Logs

Oracle maintains information concerning changes, or transactions, in online redo logs. These online redo logsoften called simply redo logsare reminiscent of audit trails of information. They specify what changes were made to information within the database and in what order. In the event of an abnormal condition, such as the database instance being terminated instead of being properly shut down, the RDBMS uses information stored within the redo logs to return the database to its previous, consistent state. It uses the redo logs cyclically, as shown in Figure 22.3.

Figure 22.3 shows four 10MB redo logs. This means that the database instance can write up to 10MB of information to each redo log, at which point it begins writing to the next online redo log in the sequence. When it reaches the final redo log, the database instance removes all the information from the first redo log and begins writing information back to it. In this way, Oracle cycles through the redo logs and keeps the most current transactions online and accessible. A recovery from an instance failure that uses only redo logs is called an online recovery.

Previous Table of Contents Next