Learn Oracle


I have created this learn oracle "The step 10 process" Oracle Tutorial to help Database aspirants to accomplish their dream of working as Oracle Database administrator. I have tried to keep it simple and User friendly. Use NEXT and PREVIOUS Button to navaigate away and to a chapter. Since i myself had been a Database adminstrator trainer and has got real life experience of working as a Oracle DBA, I would welcome any sort of queries and doubts or modifications to webmaster@oracleonline.info.

Step 9 - Restore and Recovery operations

Learn Oracle - Restore and Recovery Operations

Restoring and recovering your whole database from a backup is completely automated, thanks to the record of your existing backups kept in the RMAN repository and the intelligence built into Recovery Manager. As long as Oracle DBA have taken your backups through Recovery Manager (whether at the command line or through Enterprise Manager), the entire restore and media recovery operation can be performed through the Enterprise Manager.

To access the restore and recovery tasks, from the database home page, choose the Maintenance property page, then under the heading Backup/Recovery choose Perform Recovery to start the Recovery wizard.

On the Perform Recovery: Type page, in the Type field, select the type of restore operation to perform. Oracle DBA can choose to recover a whole database or selected tablespaces, datafiles, archived logs, or tables. Recovering a Whole Database from Backup

On the Perform Recovery: Type page, set the Type to Whole Database. The Operation Type drop-down menu will update to offer Oracle DBA the choices appropriate to a whole database restore: restoring files only, recovery only, and both restoring and recovering your database. Select Restore and Recover. Also, provide the requested host credentials at this time if necessary and click Continue. At this point, Oracle shuts down your database, and Oracle DBA arrive at a Database page where Oracle DBA are given the opportunity to Startup or Perform Recovery. Choose Perform Recovery.

After the database reaches the MOUNTED state, Enterprise Manager responds with a page warning that the state of the database is unavailable (meaning that Enterprise Manager cannot determine the state), but that the database may be in a NOMOUNT or MOUNTED state. At this time Oracle DBA will be offered two choices: Startup and Perform Recovery. Because you know that the database is down so that you can perform your recovery, do not choose Startup. Click Perform Recovery to resume your recovery session. You may be prompted for host and database credentials. Then Oracle DBA will reach recovery of your database, the database state will be altered by steps you take during the wizard. Changes, some of them irrevocable, are made to your database at certain steps. For example, the database may be shut down and brought to MOUNTED state, or datafiles may be overwritten with versions from backup.

Oracle Enterprise Manager will display warnings each time a significant database change will result from pressing Continue during the recovery process. Pay close attention to these warnings. Note: The discussion which follows assumes that you are restoring and recovering your database after the loss of one or more datafiles, but still have a usable SPFILE and control file. Enterprise Manager can also be used to restore a lost SPFILE or control file.

Enterprise Manager may respond with an error page during the period when the database is being shut down and brought to the MOUNTED state. Wait a while and refresh the page until the error no longer occurs.

the Perform Recovery: Type page again, only now the database is in a MOUNTED state (as is required for this operation) instead of being open. At this point, as you did before, select Whole Database for Object Type and Restore and Recover for Operation, and click Continue.

Next, Oracle DBA see the Perform Recovery: Credentials page, where you are again prompted for operating system-level and Oracle database credentials. Provide these, and click Continue to begin the recovery process. In the first step of performing whole database recovery, Perform Recovery:Point in Time, you specify whether to recover all transactions to your database as of the present time (which is called complete recovery), or only transactions up through some point in time in the past (which is called point-in-time recovery). Point-in-time recovery is an advanced recovery technique that lets you return the database to its state before some unwanted major change. For more details about point-in-time recovery, see Oracle Database Backup and Recovery Advanced User's Guide. For this example, however, recover the database to the current point in time. Click Continue after making your selection.

In the next step, Perform Recovery: Rename, Oracle DBA can rename the files being restored from backup. You can specify a new path for the files, restoring them to a different directory. For this example, however, restore the files to the default location, which is its location before the restore operation. Click Next to continue.

Finally, on the Perform Recovery: Review page, you can review the options you chose, and view the RMAN script that will be run to carry out your requested restore and recovery action. Click Submit to start the recovery process.

Recovering from a Lost SPFILE or Control File

The process for recovering a database with a lost SPFILE or control file is similar to recovery of all datafiles. If Oracle DBA have lost a control file, your database is definitely down. On attempting to start an Enterprise Manager session to your database, you will encounter the warning that the database state is unavailable, and you must choose between Startup and Perform Recovery. Because you do not know why your database is down, click Startup. Enterprise Manager attempts to open the database. In the process, it will detect a lost or unusable SPFILE or control file. If the startup fails, choose Perform Recovery and Enterprise Manager will prompt Oracle DBA to restore the lost files. If you are not using a recovery catalog, you will have to restore these files from autobackup. You may also need to provide Enterprise Manager the location where Oracle DBA directed control file autobackups, which can be the location of the flash recovery area or some other location on disk of your choosing.

Returning a Table to a Past State: Flashback Table

Oracle Flashback Table lets you revert one or more tables back to their contents at a previous time, without affecting other objects in your database. This recovery technique lets you recover from logical data corruptions, such as erroneously inserting rows into a table or deleting data from a table. Flashback Table lets you return tables Oracle DBA select to their state at a past point in time, without undoing desired changes to the other objects in your database, as would be required by a point-in-time recovery of the entire database. Also, unlike point-in-time recovery, your database remains available during the operation.

For this example, we will perform Flashback Table on the EMPLOYEES table in the HR schema. Assume that an erroneous update shortly after 23 October 2004, 15:30:00 has changed the LASTNAME column for all employees to an empty string and you need to return the original LASTNAME values to the table. Before you can perform Flashback Table, you must ensure that row movement is enabled on the table to be flashed back.

Enabling Row Movement on a Table

To enable row movement, or if you do not know whether row movement is enabled, follow these steps:

1. From the database home page, choose the Administration page. Then, under Schema, click Tables to administer tables. To find the target table for flashback table, you can enter one or both of the schema name in the Type field and the table name in the Object Name field. Then click OK to search for the table. Oracle DBA may need to page through the search results to find your table.

2. After you find your table in the schema, click the name of the table in the Table Name column of the search results. Enterprise Manager displays a page with several property pages for administering different aspects of the table. Select the Options property page.

3. Make sure Enable Row Movement is set to Yes, and click Apply to update the options for the table. When the page has refreshed, you can click Tables in the breadcrumb at the top of the page to return to the search results, and enable row movement on more tables by repeating these steps for each table.

Performing Flashback Table

To perform the Flashback Table operation, use the following steps:

1. From the Maintenance property page of the database home page, under the Backup/Recovery heading, select Perform Recovery, and on the Perform Recovery: Type page, select Tables for the object type. Choose the Flashback Existing Tables option and click Continue.

2. On the Perform Recovery: Point-in-time page, you must choose the target time for your Flashback Table operation. If you do not know the time at which the unwanted changes occurred, you can investigate the history of transactions affecting this table by choosing Evaluate row changes and transactions to decide upon a point in time. A feature called Oracle Flashback Versions Query lets Oracle DBA review all recent changes to the target table.

For this example, assume that the time of the corruption is known to be Oct. 3, 2004, 11:53AM. In the form offered, select Flashback to a timestamp, and enter your target time. Click Next to continue with the Flashback Table process.

3. Now, on the Perform Recovery: Flashback Tables page, specify the target tables for Flashback Table, by entering table names (one on each line) in the Tables to Flashback text box. You can also click Add Tables and search for more tables to add. For this example, manually enter the HR.EMPLOYEES table in the Tables to Flashback text box. Click Next to continue with the Flashback Table process.

4. If your table has other dependent tables, you next see the Dependency Options page, where you are asked how they should be handled. Your choices are Cascade (flashing back any dependent tables), Restrict (flashing back only the target table), or Customize (selecting which dependent tables to flashback and which to leave as they are). You can click Show Dependencies to see which tables will be affected. How you proceed at this point will depend upon your application.

HR.EMPLOYEES has dependent tables HR.JOBS and HR.DEPARTMENTS. For this example, assume that it is safe to cascade any changes, flashing back those two tables as well as the HR.EMPLOYEES table. Note that row movement must be enabled on all affected tables, not just the initial target table. Click Next to continue. The next page to appear is the Perform Recovery: Review page, where Oracle DBA have a chance to confirm the Flashback Table operation to be performed. The target timestamp and tables to be flashed back are displayed. Click Submit to perform the actual Flashback Table operation. When the operation is completed, a Confirmation page appears announcing the results. Click OK to return to the database home page.

Recovering Dropped Tables: Flashback Drop

Oracle Flashback Drop lets you reverse the effects of dropping a table, returning the dropped table to the database along with its dependent objects such as indexes and triggers. It works by storing dropped objects in a Recycle Bin, from which they may be retrieved until the Recycle Bin is purged, either explicitly or because space is needed for new database objects.

As with Flashback Table, Flashback Drop can be used while the rest of your database remains open, and without undoing desired changes in objects not affected by the Flashback Drop operation. It is more convenient than forms of recovery that require taking the database offline and restoring files from backup.

Follow these steps to perform Flashback Drop:

1. From the Maintenance property page of the database home page, under the Backup/Recovery heading, select Perform Recovery, and on the Perform Recovery: Type page, select Tables for the object type. For the Operation Type, choose Flashback dropped tables. Then click Continue. This takes you to the Perform Recovery: Dropped Objects Selection page.

2. On the Perform Recovery: Dropped Objects Selection page, you can gain access to the objects in the Recycle Bin. The Search form lets you search among the dropped objects in the Recycle Bin for the objects you want to recover. Provide values for one or both of the Schema Name and Table fields, and click Go to search. When the page refreshes, the Results section lists the objects matching your search. If you only see the Recycle Bin listed, click the arrow next to the Recycle Bin to expand its contents by one level, showing dropped tables matching your search but not their dependent objects. You can also click Expand All to see all objects in the Recycle Bin, including both dropped tables and dependent objects such as indexes and triggers. For each table listed, Oracle DBA can click View Content in the Operation column, to see its contents.

To select one or more tables for Flashback Drop, click the checkbox next to each table. (When a table is retrieved from the Recycle Bin, all of the dependent objects for the table that are in the recycle bin are brought back as well. They cannot be retrieved separately.) When you have selected all of your objects to restore, click Next.

Note: For a table to be recoverable using Flashback Drop, it must reside in a locally managed tablespace. Also, tables in the SYSTEM tablespaces cannot recovered using Flashback Drop regardless of the tablespace type.

3. On the Perform Recovery: Rename page, you have the opportunity to specify new names for any dropped objects you are returning to your database. The primary reason for renaming objects being retrieved from the recycle bin is if you have created new tables with the same names as tables being retrieved. Enter new names as needed in the New Name field in the list of tables being flashed back. Then click Next to continue.

4. On the Perform Recovery: Review page, you are offered an impact analysis, showing the full set of objects to be flashed back, including the dependent objects, as well as the names they will have when the Flashback Drop operation is complete. If Oracle DBA are satisfied with the changes listed, click Submit to perform the Flashback Drop. When the process is complete, you arrive at a confirmation page indicating the success of the operation. Click OK to return to the database home page.
Oracle Tutorial - BLOGS

STEP 9 - Next Topics
This introduces Oracle DBA to Oracle backup and recovery as performed through Enterprise Manager. The goals are to introduce the basic concepts of Oracle backup and recovery, show how to configure your database for backup and recovery using the Oracle-suggested disk-based backup strategy, and then walk Oracle DBA through recovery of a full database backup. Click on Oracle tuorial links below:
Backup, Restore and Recovery ConceptsBackup and Restore
Configuring Database for Basic Backup and RecoveryBackup and Restore
Backing Up Your DatabaseBackup and Restore
Restore and Recovery OperationsBackup and Restore
Oracle Database - PODCASTS
More Tutorials on Oracle dba ...

Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Source : Oracle Documentation | Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info