Recovering an Oracle Database

In this conclusion to a three-part article series on backing up and recovering an Oracle database, you’ll learn how to perform manual and automatic backups, and how to recover database objects. This article excerpted from chapter 30 of the book Beginning PHP and Oracle: From Novice to Professional, written by by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Backing Up the Database

Now that you have multiplexed your online redo log files, multiplexed your control files, and enabled ARCHIVELOG mode in your database, you are ready for your first full backup of the database. Any media recovery operation requires at least one full backup of the database, even if you are not in ARCHIVELOG mode. (Remember that an instance failure requires only the online redo log files for recovery.) You can back up manually, or schedule an automatic backup at regular intervals. We cover both of these scenarios in the following sections.

Manual Backups

Whether you are using Linux or Windows as your operating system, performing a manual backup is very straightforward. Under Linux, start with the Applications menu under Gnome, or the K menu if you’re using KDE, select Oracle Database 10g Express Edition ➤ Backup Database. For Windows, from the Start menu, select Programs ➤ Oracle Database 10g Express Edition ➤ Backup Database. In both cases, a console window launches so that you can interact with the backup script. This interaction occurs only if you are not in ARCHIVELOG mode. The backup script warns you that Oracle will shut down the database before a full backup can occur.

For a full backup under Linux, the output in the console window looks similar to if not exactly like this:

——————————————–
Doing online backup of the database.
Backup of the database succeeded.
Log file is at /usr/lib/oracle/xe/oxe_backup_current.log. Press ENTER key to exit
——————————————–

The script output identifies the log file location. Oracle keeps the two most recent log files. The previous log file is at this location:

/usr/lib/oracle/xe/oxe_backup_previous.log

The log file contains the results of one or more RMAN sessions. After the backup completes, RMAN deletes all obsolete backups. By default, Oracle only keeps the last two full backups. If you are using Windows as your host operating system, the backup logs reside in these locations:

C:ORACLEXEAPPORACLEPRODUCT10.2.0SERVERDATABASEOXE_BACKUP_CURRENT.LOG C:ORACLEXEAPPORACLEPRODUCT10.2.0SERVERDATABASEOXE_BACKUP_PREVIOUS.LOG

Automatic Backups

Scheduling automatic backups is very straightforward. Oracle Database XE provides a script for each platform that you can launch using your favorite scheduling program, such as the cron program under Linux or the Scheduled Tasks wizard under Windows.

For Linux, the script is located here:

/usr/lib/oracle/xe/app/oracle/product
/10.2.0/server/config/scripts/backup.sh

For Windows, the script is located here:

C:oraclexeapporacleproduct10.2.0serverBINBACKUP.BAT

The log files for each platform are located in the same location as if you ran the scripts manually.

{mospagebreak title=Recovering Database Objects}

Eventually disaster will strike and you will lose one of your key database files, either a datafile, a control file, or an online redo log file, due to a hardware failure or an administrator error. In the following scenario, one of the datafiles is accidentally deleted and you must recover the database back to the point of time where the database failed due to the missing datafile.

In a default Oracle Database XE installation, you have four datafiles. You can use the dynamic performance view V$DATAFILE to identify these datafiles:

select name from v$datafile;

——————————————–
NAME
—————————————-
/usr/lib/oracle/xe/oradata/XE/system.dbf
/usr/lib/oracle/xe/oradata/XE/undo.dbf
/usr/lib/oracle/xe/oradata/XE/sysaux.dbf
/usr/lib/oracle/xe/oradata/XE/users.dbf
——————————————–

The system administrator performs some routine disk space reclamation and accidentally deletes one of the datafiles on the Linux server:

rm /usr/lib/oracle/xe/oradata/XE/users.dbf

You immediately get phone calls from your users because all user tables are stored in the USERS tablespace, which in turn is stored in the operating system file /usr/lib/oracle/xe/oradata/XE/ users.dbf . A user reports seeing the error message shown in Figure 40-4 when she tries to browse the contents of one of her tables.


Figure 40-4. User error messages after the loss of a datafile

Your first thought is that there must be some error other than a missing datafile, so you first try to shut down and restart the database to see what happens. The startup messages look normal at first, but then after the database is mounted you see an error message similar to the following:

——————————————–ORACLE instance started.

Total System Global Area 146800640 bytes Fixed Size 1257668 bytes Variable Size 88084284 bytes Database Buffers 54525952 bytes Redo Buffers 2932736 bytes Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/usr/lib/oracle/xe/oradata/XE/users.dbf’
——————————————–

You decide that a database recovery is your only option. From either the Windows or Linux GUI interface, select Restore Database from the same menu where you selected Backup Database, as noted earlier in the chapter, to back up the database. A command window opens to ensure that you know a shutdown must occur to restore the database to its previous state:

——————————————–
This operation will shut down and restore the database. Are you sure [Y/N]?
——————————————–

After you type Y, the restore operation proceeds with no further intervention other than to confirm that the operation is complete:

——————————————–
This operation will shut down and restore the database. Are you sure [Y/N]?y
Restore in progress…
Restore of the database succeeded.
Log file is at /usr/lib/oracle/xe/oxe_restore.log.
Press ENTER key to exit
——————————————–

The restore operation automatically starts the database after completion. If you are curious as to which RMAN commands were used to recover from the media failure (deleted datafile), you can look in the log file identified by the script at /usr/lib/oracle/xe/oxe_restore.log .

When the user reloads the Web page containing her Oracle Database XE session, she suddenly sees the table she was attempting to browse, as shown in Figure 40-5.

The user didn’t even have to log out and log back in; when the database was back up (after being shut down and restarted several times, including an attempt by the DBA to shut down and restart), refreshing the page logged the user back in behind the scenes and kept her on the page where she left off. She was none the wiser about the multiple database shutdowns and restarts; all of her committed transactions are still in the database as well.


Figure 40-5. Refreshed Web page after media recovery

Summary

This chapter gave you the basics for backing up and recovering your database. Although backup and recovery operations are not the most glamorous of tasks compared to application development, a database that is down because of a disk failure quickly becomes highly visible to upper management when the PHP developers (including yourself) are not able to store and retrieve application data. Implementing Oracle’s best practices to ensure database availability include multiplexing redo log files and control files, enabling ARCHIVELOG mode to ensure recoverability from media failure, and leveraging the Flash Recovery Area to quickly recover from media failure or user error.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan