Oracle® Database Express Edition 2 Day DBA 10g Release 2 (10.2) Part Number B25107-01 |
|
|
View PDF |
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 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 Basics 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:
Access the Database Home Page.
See "Accessing the Database Home Page" for instructions.
Examine the Usage Monitor.
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 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 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 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 599785472 bytes Fixed Size 1220804 bytes Variable Size 180358972 bytes Database Buffers 415236096 bytes Redo Buffers 2969600 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.
See "Changing the Flash Recovery Area Size" for instructions.
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 SQL Command Line" for information on how to handle a failedSHUTDOWN
IMMEDIATE
command.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 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]?
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 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:
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 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]?
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.
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 AS
OF
clause for the SELECT
statement in Oracle Database SQL 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.
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 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 asSYSDBA
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.Access the Database Home Page, and log in as the owner of the schema of interest.
See "Accessing the Database Home Page" for instructions.
Click the Utilities icon, and then click the Recycle Bin icon.
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.)
(Optional) Narrow down the list contents by selecting an object type from the Object Type list and clicking Go.
(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.
(Optional) Click an object link (under the Original Name column) to view object details.
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:
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 theHR
account and assigned it a password. See "Altering Users" and "Locking and Unlocking User Accounts" for more information.Click the Object Browser icon.
The Object Browser page appears, showing the tables in the HR
schema.
Click the JOB_HISTORY
table.
Details on this table appear in the right side of the page.
Click the Drop button (underneath the Triggers link).
A Drop Object Confirmation window appears.
Click Finish to drop the table.
The table is removed from the list of tables at the left-hand side of the page.
Click Home in the breadcrumb trail at the top of the page to return to the Database Home Page.
Click the Utilities icon, and then click the Recycle Bin icon.
Click the Dropped Objects icon.
The Dropped Objects page appears, displaying the JOB_HISTORY
table and its indexes in a list.
Under the Original Name column, click the table name JOB_HISTORY
.
The Object Details page appears, displaying information on the JOB_HISTORY
table.
Click Restore Object.
A confirmation page appears. The table and all of its dependent objects are restored.
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:
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
.
Click the Utilities icon, and then click the Recycle Bin icon.
Click the Purge Recycle Bin icon.
A page appears that prompts you to confirm the operation.
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:
Access the Database Home Page, and log in as the owner of the schema of interest.
See "Accessing the Database Home Page" for instructions.
Click the Utilities icon, and then click the Recycle Bin icon.
Click the Dropped Objects icon.
The Dropped Objects page appears, displaying dropped objects in a list.
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.
Click Purge Object.
A confirmation page appears. The object and all of its dependent objects are purged.