Oracle Database Backup and Recovery Practices

Ensuring database availability is a critical skill you need even if your Oracle Database XE instance is used by a small group of developers in your department. Many types of database failures are beyond your control as a DBA, such as disk failures, network failures, and user errors. This emphasizes the need to prepare in advance for all of these potential failures after assessing the cost of database downtime versus the effort required to harden your database against failure. Many of these failures, as you might expect, require you to work closely with the server system administrators and network administrators to minimize the impact. You need to promptly receive notification when failures occur, or a warning when they are about to occur.

In this chapter, we start by presenting you with Oracle’s recommended best practices for ensuring the recoverability of your database when, not if, you have a database failure. If your database is a production database that must be available continuously, these requirements are mandatory. On the other hand, if your database is for development, an occasional backup may suffice. However, by using Oracle’s best practices, your downtime will be minimal in the event of a failure, giving you more time to focus on PHP application development instead of data recovery.

Next, we show you how to back up your database, using the Oracle Database XE scripts. Once you have backed up your database, you will need to know how to recover the database from a media failure such as a missing or corrupted datafile.

Oracle recommends several techniques you can use to ensure database availability and recoverability. Many of these techniques are automatically implemented when you install Oracle Database XE. However, there are a couple of places where you can tweak the default configuration to improve the recoverability further. We discuss these tweaks in the sections that follow.

Before we dig in to the recoverability and availability techniques, it is important to know the types of failures you may encounter in your database so that you may respond appropriately when they occur. Database failures fall into two broad categories: media failures and nonmedia failures.

Media failures occur when a server disk or a disk controller fails and makes one or more of your database’s datafiles unusable (see Chapter 28 for an overview of Oracle Database XE’s storage structures). After the hardware error is resolved (e.g., the server administrator replaces the disk drive), it is your responsibility to restore the corrupted or destroyed datafiles from a disk or tape backup. As the price of disk space falls, the added level of convenience and speed of disk makes tape backups less desirable except for archival purposes.

Nonmedia failures include all other types of failures. Here are the most common types of nonmedia failures and how you will deal with them:

1. Statement failure: Your SQL statement fails because of a syntax problem, or your permissions do not allow you to execute the statement. The recovery process for fixing this error is relatively easy: use the correct syntax or obtain permissions on the objects in the SQL statement. 
    
2. Instance failure: The entire database fails due to a power failure, server hardware failure, or a bug in the Oracle software. Recovery from this type of failure is automatic: once the server hardware failure is fixed or the power is restored, Oracle Database XE uses the online redo log files to ensure that all committed transactions are recorded in the database’s datafiles. In the case of a possible Oracle software bug, your next step after restarting the database is to investigate whether there is a patch file or a workaround for the software bug. 
    
3. Process failure: A user may be disconnected from the database due to a network connection failure or an exceeded resource limit (such as too much CPU time). The Oracle Database XE background processes automatically clean up by freeing the memory used by the user connection and roll back any uncommitted transactions started during the user’s session. 
    
4. User error: A user may drop a table or delete rows from a table unintentionally.