This section discusses backing up and restoring the entire database and recovering data from individual schema objects. It includes the following topics:
This section includes the following topics:
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 the 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.
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.
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:
Oracle Database Backup and Recovery User's Guide for more information on Oracle database backup and recovery with RMAN
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:
See Also:
"Archived Redo Log Files"To view the current ARCHIVELOG mode setting:
Using the SQL Command Line, log in to the database and connect as SYSDBA, as described in "Logging In and Connecting to the Database as SYSDBA".
Enter the following command:
SELECT log_mode FROM v$database;
The LOG_MODE value will be either ARCHIVELOG (that is, on) or NOARCHIVELOG (that is, off).
Turning on ARCHIVELOG mode is a one-time operation. After it is turned on, it remains on until you turn it off. Restarting the database does not change the ARCHIVELOG mode setting.
Note:
If you turn onARCHIVELOG 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:
Using the SQL Command Line, log in to the database and connect as SYSDBA, as described in "Logging In and Connecting to the Database as SYSDBA".
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.
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 803500032 bytes Fixed Size 1386556 bytes Variable Size 222300100 bytes Database Buffers 574619648 bytes Redo Buffers 5193728 bytes Database mounted.
Enter the following command:
ALTER DATABASE ARCHIVELOG;
If the command is successful, it displays the following output:
Database altered.
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.
Change the size of the flash recovery area to at least 15 gigabytes to allow for the extra space required for archived log files.
For example, to set the flash recovery area size to 20 gigabytes, enter the following command:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G;
See "Changing the Flash Recovery Area Size" for more information.
Note:
To turn offARCHIVELOG 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 theARCHIVELOG 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 the SQL Command Line" for information on how to handle a failedSHUTDOWN IMMEDIATE command.Note:
IfARCHIVELOG mode is on, the script performs an online backup. The database is available during the backup.
If ARCHIVELOG mode is off, the script performs an offline backup. The database is shut down during the backup and restarted afterwards. Your applications are unavailable during the backup.
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.
Do one of the following:
On Windows: Click Start, point to Programs (or All Programs), point to Oracle Database 11g Express Edition, and then select Backup Database.
On Linux with Gnome: In the Applications menu, point to Oracle Database 11g Express Edition, and then select Backup Database.
On Linux with KDE: Click the icon for the K Menu, point to Oracle Database 11g 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]?
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.
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 |
|
|
Windows |
|
You can review the output of the two most recent backup attempts in the files OXE_BACKUP_CURRENT.LOG and OXE_BACKUP_PREVIOUS.LOG.
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.
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 User's Guide 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:
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.
Do one of the following:
On Windows: Click Start, point to Programs (or All Programs), point to Oracle Database 11g Express Edition, and then select Restore Database.
On Linux with Gnome: In the Applications menu, point to Oracle Database 11g Express Edition, and then select Restore Database.
On Linux with KDE: Click the icon for the K Menu, point to Oracle Database 11g 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]?
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 any key to continue . . .
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.
Press Enter to close the Restore Database window.
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:
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 flashback_query_clause, including the AS OF clause, for the SELECT statement in Oracle Database SQL Language Reference.
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');
See Also:
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.
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:
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 SQL Developer 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 asSYSDBA with the SQL Command Line (SQL*Plus), you can view, restore, and purge objects owned by other users. See Oracle Database Administrator's Guide for details.In SQL Developer, open a database connection to the schema of interest.
In the Connections navigator, expand the connection and select the Recycle Bin node.
(Optional) Click a table name to see its properties.
See Also:
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:
In SQL Developer, connect to the HR user.
Note:
You must have previously unlocked theHR account and assigned it a password. See "Altering Users" and "Locking and Unlocking User Accounts" for more information.In the Connections navigator under HR, expand Tables.
Right-click JOB_HISTORY and select Drop.
In the Drop dialog box, click Apply.
In the Connections navigator, right-click Recycle Bin and select Refresh.
The JOB_HISTORY table is listed in the Recycle Bin. You can now proceed to restore it ("undrop" it) or to purge it (delete it completely).
To restore the table, in the Recycle Bin, right-click JOB_HISTORY (and no other objects), and select Flashback to Before Drop.
In the Flashback to Before Drop dialog box, for Flashback to Object Name specify the desired name for the restored table: JOB_HISTORY
Click Apply.
To see JOB_HISTORY now listed among the tables, right-click the Tables node and select Refresh.
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 objects from the recycle bin.
Purging an Object from the Recycle Bin
To purge an object from the recycle bin:
In SQL Developer, connect to the database user that owns the object.
In the Connections navigator under that connection, expand Recycle Bin.
Right-click object that you want to purge and select Purge.
In the Purge confirmation box, click Apply.