Home arrow Oracle arrow Page 2 - Recovering an Oracle Database

Recovering Database Objects - Oracle

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).

  1. Recovering an Oracle Database
  2. Recovering Database Objects
By: Apress Publishing
Rating: starstarstarstarstar / 0
June 09, 2011

print this article



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 viewV$DATAFILEto identify these datafiles:

select name from v$datafile;


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 theUSERStablespace, 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


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.

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: