Skip Headers
Oracle® Database Express Edition 2 Day DBA
10g Release 2 (10.2)

Part Number B25107-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

11 Backing Up and Recovering

This section discusses backing up and restoring the entire database and recovering data from individual schema objects. It includes the following topics:

Backing Up and Restoring the Database

This section includes the following topics:

About Backing Up and Restoring the Database

Backing up and restoring Oracle Database XE is based on protecting the physical files that make up the database: the datafiles, the control file, the server parameter file (SPFILE), and, if in ARCHIVELOG mode, the redo log files.

In Oracle Database XE, the database backup and recovery facility is based upon the Recovery Manager (RMAN) utility that is integrated into the database. Although there is an RMAN command line client similar to SQL Command Line, you do not need to interact with it directly to back up or restore your database. Oracle Database XE includes backup and restore scripts that you access using menu choices on your desktop. These scripts perform a full backup and restore of the entire database, and store backup files in the flash recovery area.

Automatic Management of Backup Storage

Oracle Database XE implements a backup retention policy that dictates that two complete backups of the database must be retained, to provide a level of redundant protection for the database. In ARCHIVELOG mode, all archived logs required for media recovery from either backup are also retained. The database automatically manages backups and archived logs in the flash recovery area, deleting any that are obsolete (no longer needed to satisfy the retention policy) as space is needed for new files. The backup script provided with Oracle Database XE also deletes obsolete backups and archived logs at the end of each backup job.

Backup Script

The provided backup script performs online backups of a database that is in ARCHIVELOG mode and offline backups of a database that is in NOARCHIVELOG mode. Online backups are backups that can run while the database is running. Offline backups are backups that run when the database is in the mounted (but not open) state. For offline backups, the backup script automatically puts the database in the proper state. During offline backups, the database is unavailable to your applications.

You run the backup script by running the Backup Database command from the desktop.

Restore Script

The provided restore script restores the database differently depending on whether log archiving is on or off:

  • Log archiving on (ARCHIVELOG mode)—The restore script restores the backed up database files, and then uses the online and archived redo log files to recover the database to the state it was in before the software or media failure occurred. All committed transactions that took place after the last backup are recovered, and any uncommitted transactions that were under way when the failure took place are rolled back (using undo data from the restored undo tablespace).

  • Log archiving off (NOARCHIVELOG mode)—The restore script restores the database to its state at the last backup. Any transactions that took place after the last backup are lost.

You run the restore script by running the Restore Database command from the desktop.

See Also:

Enabling ARCHIVELOG Mode for Media Failure Protection

This section describes how to turn on ARCHIVELOG mode so that your database is fully protected not only against operating system and Oracle instance failure, but also against media (disk) failure. The following topics are covered:

Viewing the Current ARCHIVELOG Mode Setting

To view the current ARCHIVELOG mode setting:

  1. Access the Database Home Page.

    See "Accessing the Database Home Page" for instructions.

  2. Examine the Usage Monitor.

    Description of usage_monitor.gif follows
    Description of the illustration usage_monitor.gif

    Note:

    If the Usage Monitor does not appear on the Database Home Page, click the Customize link near the upper right-hand corner of the page, and then enable the Usage Monitor.

    The Log Archiving mode is displayed in the last line of the Usage Monitor. In this example, the text "Log Archiving: Off" indicates that ARCHIVELOG mode is turned off.

    Note:

    Click the Log Archiving: {On|Off} link to view a page that provides details on the online redo logs.

Turning on ARCHIVELOG Mode

Turning on ARCHIVELOG mode is a one-time operation. After it is turned on, it remains on until you turn it off. (You turn off ARCHIVELOG mode by setting NOARCHIVELOG mode.) Restarting the database does not change the ARCHIVELOG mode setting.

Note:

If you turn on ARCHIVELOG mode, you must perform regular backups of the database to avoid completely filling the flash recovery area. A completely filled flash recovery area can lead to database failure.

To turn on ARCHIVELOG mode:

  1. Using SQL Command Line, log in to the database and connect as SYSDBA, as described in "Logging In and Connecting to the Database as SYSDBA".

  2. At the SQL Command Line prompt, enter the following command:

    SHUTDOWN IMMEDIATE
    
    

    If the command is successful, it displays the following output.

    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    
    
  3. At the SQL Command Line prompt, enter the following command:

    STARTUP MOUNT
    
    

    If the command is successful, it displays the following output. (System global area sizes will vary depending on the amount of physical memory in your Oracle Database XE host computer.)

    ORACLE instance started.
     
    Total System Global Area  599785472 bytes
    Fixed Size                  1220804 bytes
    Variable Size             180358972 bytes
    Database Buffers          415236096 bytes
    Redo Buffers                2969600 bytes
    Database mounted.
    
    
  4. Enter the following command:

    ALTER DATABASE ARCHIVELOG;
    
    

    If the command is successful, it displays the following output:

    Database altered.
    
    
  5. Enter the following command:

    ALTER DATABASE OPEN;
    
    

    If the command is successful, it displays the following output:

    Database altered.
    
    

    The database is now running with the new ARCHIVELOG mode setting.

  6. Change the size of the flash recovery area to at least 15 gigabytes to allow for the extra space required for archived log files.

    See "Changing the Flash Recovery Area Size" for instructions.

Note:

To turn off ARCHIVELOG mode (that is, to set NOARCHIVELOG mode), follow the previous steps 1 through 5, but enter the following command in Step 4:
ALTER DATABASE NOARCHIVELOG;

Caution:

When you change the ARCHIVELOG mode setting for your database, all of your existing backups become unusable. You must immediately perform a backup after changing the ARCHIVELOG mode, as described in "Backing Up the Database".

See Also:

"Shutting Down the Database Using SQL Command Line" for information on how to handle a failed SHUTDOWN IMMEDIATE command.

Backing Up the Database

To back up the database:

  1. Do one of the following:

    • On Windows: Log in to the Oracle Database XE host computer as a user who is a member of the ORA_DBA user group. This is typically the user that installed Oracle Database XE.

    • On Linux: Log in to the Oracle Database XE host computer as a user who is a member of the dba user group. This is typically the oracle user.

    See "Operating System Authentication" for more information.

  2. Do one of the following:

    • On Windows: Click Start, point to Programs (or All Programs), point to Oracle Database 10g Express Edition, and then select Backup Database.

    • On Linux with Gnome: In the Applications menu, point to Oracle Database 10g Express Edition, and then select Backup Database.

    • On Linux with KDE: Click the icon for the K Menu, point to Oracle Database 10g Express Edition, and then select Backup Database.

    A console window opens so that you can interact with the backup script.

    If running in ARCHIVELOG mode, the script displays the following output:

    Doing online backup of the database.
    
    

    If running in NOARCHIVELOG mode, the script displays the following output:

    Warning: Log archiving (ARCHIVELOG mode) is currently disabled. If
    you restore the database from this backup, any transactions that take
    place between this backup and the next backup will be lost. It is
    recommended that you enable ARCHIVELOG mode before proceeding so
    that all transactions can be recovered upon restore. See the section
    'Enabling ARCHIVELOG Mode...' in the online help for instructions.
    Backup with log archiving disabled will shut down and restart the
    database. Are you sure [Y/N]?
    
    
  3. If prompted, enter y and press Enter to confirm the database shutdown and begin the backup.

    After the backup is complete, the script displays the following output:

    Backup of the database succeeded.
    Log file is at location
    Press ENTER key to exit
    
    

    where location is the location of the log file.

  4. Press Enter to close the Backup Database window.

Logs containing the output from the last two backups are stored in the locations listed in Table 11-1.

Table 11-1 Backup Script Output Log Locations

Platform Location

Linux

$HOME/oxe_backup_current.log

$HOME/oxe_backup_previous.log

Windows

C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\OXE_BACKUP_CURRENT.LOG.

C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\OXE_BACKUP_PREVIOUS.LOG.


You can review the output of the two most recent backup attempts in the files OXE_BACKUP_CURRENT.LOG and OXE_BACKUP_PREVIOUS.LOG.

Scheduling Automatic Backups

To schedule automatic backups, use any operating system or third party task scheduling software to run the supplied backup script for your platform. Table 11-2 shows the name and path of this script for each platform.

Table 11-2 Name and Path of the Backup Script for Each Platform

Platform Backup Script Name and Path

Linux

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

Windows

C:\oraclexe\app\oracle\product\10.2.0\server\BIN\BACKUP.BAT


Restoring and Recovering the Database

You restore and recover the database with the supplied restore script. The instructions in this section are based on the following assumptions:

  • A software failure, media (disk) failure, or operator error caused the loss or corruption of one or more database files, rendering the database unusable.

  • In the flash recovery area, the backup sets and, if in ARCHIVELOG mode, archived logs, are intact and available.

  • If in ARCHIVELOG mode, the online redo logs are intact and available.

    Note:

    If they are not available, the database is restored to the point of the last transaction included in an archived log. See Oracle Database Backup and Recovery Basics for more information.
  • The Oracle Database XE host computer and operating system are operational.

  • The Oracle Database XE installed software (binaries) are intact and operational.

In situations where not all of these assumptions are true, before proceeding with the following steps to restore and recover the database, you may first have to complete one or more of the following tasks: repair or replace computer hardware, reinstall operating system software, or reinstall Oracle Database XE. After reinstalling Oracle Database XE, if your flash recovery area was previously on a separate disk from your Oracle Database XE installation and is still available, you must specify the location of the flash recovery area so that the restore script can find the required files. See "Setting Flash Recovery Area Location and Size" for instructions.

To restore the database:

  1. Do one of the following:

    • On Windows: Log in to the Oracle Database XE host computer as a user who is a member of the ORA_DBA user group. This is typically the user that installed Oracle Database XE.

    • On Linux: Log in to the Oracle Database XE host computer as a user who is a member of the dba user group. This is typically the oracle user.

    See "Operating System Authentication" for more information.

  2. Do one of the following:

    • On Windows: Click Start, point to Programs (or All Programs), point to Oracle Database 10g Express Edition, and then select Restore Database.

    • On Linux with Gnome: In the Applications menu, point to Oracle Database 10g Express Edition, and then select Restore Database.

    • On Linux with KDE: Click the icon for the K Menu, point to Oracle Database 10g Express Edition, and then select Restore Database.

    A console window opens so that you can interact with the restore script. The script displays the following output:

    This operation will shut down and restore the database. Are you sure [Y/N]?
    
    
  3. Enter y and press Enter to confirm the database restore.

    The database is shut down, and the script runs RMAN to restore the database and, if running in ARCHIVELOG mode, recover all changes since the last backup.

    Note:

    In some situations, such as when you run the Restore Database command in a new Oracle Database XE installation before having backed up, the restore script may prompt you for the location of the flash recovery area:
    Enter the flash recovery area location:
    
    

    If so prompted, enter the complete path to the location of the flash recovery area. (The default location for each platform is listed in Table 6-2.) The restore script then restores the database from the backup files in this location.

    If the restore and recovery process is completed successfully, the database is opened again. The script then displays the following output:

    Restore of the database succeeded.
    Log file is at location
    Press ENTER key to exit
    
    

    where location is the location of the log file.

    If the restore and recovery process fails, messages describing the error are displayed.

    ====================   ERROR =============================
              Restore of the database Failed.
              RMAN Error - See log for details
              Log file is at location
     ====================   ERROR =============================
    
    

    Refer to the log file for details on the cause of the error.

  4. Press Enter to close the Restore Database window.

Viewing and Restoring Historical Data with Flashback Query

The Flashback Query feature of Oracle Database Express Edition (Oracle Database XE) enables you to view data at a point in time in the past. You can then reconstruct lost data that was deleted or changed by accident.

This section contains the following topics:

About Flashback Query

When you write a Flashback Query, you add a clause to the SELECT statement that specifies either a time or a system change number (SCN). The query then uses the committed data from the corresponding time. The Flashback Query feature does not change any data; it queries only. It is up to you to analyze the historical data and then construct and issue data manipulation language (DML) statements to restore data.

The Flashback Query feature retrieves historical data by applying undo data as needed. The length of time that you can flash back therefore depends on the amount of undo data that is available. For more information on the Flashback Query feature, see the discussion of the AS OF clause for the SELECT statement in Oracle Database SQL Reference.

Example: Recovering Data with Flashback Query

This example uses a Flashback Query to examine the state of a table at a previous time. Suppose that you discover at 12:30 p.m. that the row for employee Chung was deleted from the employees table. You also know that at 9:30 a.m. the data for Chung was correctly stored in the database. You can use a Flashback Query to examine the contents of the table at 9:30 a.m. to find out what data was lost. If appropriate, you can then reinsert the lost data.

Example 11-1 retrieves the state of the record for Chung at 9:30 a.m., April 4, 2005.

Example 11-1 Retrieving a Row with Flashback Query

SELECT * FROM employees AS OF TIMESTAMP 
   TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE last_name = 'Chung';

The update in Example 11-2 restores Chung's information to the employees table.

Example 11-2 Reinserting a Row After a Flashback Query

INSERT INTO employees 
    (SELECT * FROM employees AS OF TIMESTAMP 
     TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
     WHERE last_name = 'Chung');

Tips for Using Flashback Query

Keep the following in mind when using a Flashback Query (SELECT ... AS OF):

  • You can specify or omit the AS OF clause for each table in the query and specify different times for different tables. Use an AS OF clause in a query to perform data definition language (DDL) operations (such as creating and truncating tables) or DML operations (such as inserting and deleting) in the same session as the query.

  • To use the results of a Flashback Query in a DDL or DML statement that affects the current state of the database, use an AS OF clause inside an INSERT or CREATE TABLE AS SELECT statement.

Recovering Dropped Tables

When you drop (delete) a table, the database does not immediately remove the space associated with the table. Instead, the database renames the table and places it and any dependent objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time.

This section contains the following topics:

About the Recycle Bin

The recycle bin is a data dictionary table containing information about dropped objects. Dropped objects and any dependent objects (such as indexes, constraints, nested tables, and so on) are not removed and still occupy space until you purge them from the recycle bin or until they are automatically purged by the database when available space becomes low.

You can restore objects from the recycle bin, which is equivalent to "undropping" them. When you restore an object, it is returned to the state that it was in before the drop operation. When you restore a table, all of the table's dependent objects are also automatically restored.

You can use the Oracle Database XE graphical user interface to view the contents of the recycle bin, restore dropped objects, and purge objects from the recycle bin. To view, purge, or restore objects owned by a particular user, you must log in as that user.

Note:

If you log in and connect as SYSDBA with SQL Command Line (SQL*Plus), you can view, restore, and purge objects owned by other users. See Oracle Database Administrator's Guide for details.

Viewing Recycle Bin Contents

To view recycle bin contents:

  1. Access the Database Home Page, and log in as the owner of the schema of interest.

    See "Accessing the Database Home Page" for instructions.

  2. Click the Utilities icon, and then click the Recycle Bin icon.

  3. Click the Dropped Objects icon.

    The Dropped Objects page appears, showing a list of dropped objects and dependent objects. (For example, if a table appears in the list, all of its indexes and other dependent objects appear also.)

    Description of dropped_objects.gif follows
    Description of the illustration dropped_objects.gif

  4. (Optional) Narrow down the list contents by selecting an object type from the Object Type list and clicking Go.

  5. (Optional) Narrow down the list by entering search text into the Original Name field and clicking Go.

    All objects that include the search text anywhere in their original name are displayed. For example, assuming that the list of dropped objects is the one shown in the previous screen snapshot, if you enter JOB for your search text, only the objects JOB_HISTORY and JHIST_JOB_IX appear in the list.

  6. (Optional) Click an object link (under the Original Name column) to view object details.

Example: Restoring a Table from the Recycle Bin

Suppose you drop the JOB_HISTORY table in the HR schema, and then decide that you want to recover it. You can recover (undrop) the table by restoring it from the recycle bin.

To drop the JOB_HISTORY table and then restore it from the recycle bin:

  1. Access the Database Home Page, and log in to the database as user HR.

    See "Accessing the Database Home Page" for instructions.

    Note:

    You must have previously unlocked the HR account and assigned it a password. See "Altering Users" and "Locking and Unlocking User Accounts" for more information.
  2. Click the Object Browser icon.

    The Object Browser page appears, showing the tables in the HR schema.

  3. Click the JOB_HISTORY table.

    Details on this table appear in the right side of the page.

    Description of job_history.gif follows
    Description of the illustration job_history.gif

  4. Click the Drop button (underneath the Triggers link).

    A Drop Object Confirmation window appears.

  5. Click Finish to drop the table.

    The table is removed from the list of tables at the left-hand side of the page.

  6. Click Home in the breadcrumb trail at the top of the page to return to the Database Home Page.

  7. Click the Utilities icon, and then click the Recycle Bin icon.

  8. Click the Dropped Objects icon.

    The Dropped Objects page appears, displaying the JOB_HISTORY table and its indexes in a list.

    Description of dropped_objects.gif follows
    Description of the illustration dropped_objects.gif

  9. Under the Original Name column, click the table name JOB_HISTORY.

    The Object Details page appears, displaying information on the JOB_HISTORY table.

    Description of dropped_object_details.gif follows
    Description of the illustration dropped_object_details.gif

  10. Click Restore Object.

    A confirmation page appears. The table and all of its dependent objects are restored.

Purging the Recycle Bin

When you drop objects, space is not freed until you purge the recycle bin, or until the database automatically purges the recycle bin when it detects a low space condition. You can purge individual objects or you can purge all contents of the recycle bin. When you purge all contents of the recycle bin, only the objects that you own are purged.

Purging All Contents of the Recycle Bin

To purge all contents of the recycle bin:

  1. Access the Database Home Page and log in as the owner of the schema of interest.

    See "Accessing the Database Home Page" for instructions.

    For example, if you want to purge the recycle bin of objects owned by user NICK, log in as NICK.

  2. Click the Utilities icon, and then click the Recycle Bin icon.

  3. Click the Purge Recycle Bin icon.

    A page appears that prompts you to confirm the operation.

  4. Click the Purge Recycle Bin icon again.

    A confirmation page appears.

Purging an Individual Object from the Recycle Bin

To purge an individual object from the recycle bin:

  1. Access the Database Home Page, and log in as the owner of the schema of interest.

    See "Accessing the Database Home Page" for instructions.

  2. Click the Utilities icon, and then click the Recycle Bin icon.

  3. Click the Dropped Objects icon.

    The Dropped Objects page appears, displaying dropped objects in a list.

    Description of dropped_objects.gif follows
    Description of the illustration dropped_objects.gif

  4. Under the Original Name column, click the name of the object that you want to purge.

    The Object Details page appears, displaying information on the object.

    Description of dropped_object_details.gif follows
    Description of the illustration dropped_object_details.gif

  5. Click Purge Object.

    A confirmation page appears. The object and all of its dependent objects are purged.