Oracle Restoration & Recovery Scenarios

Hello There,

The following are the typical scenarios for restoration in Oracle databases and easiest way while answering in your interviews, (ofcourse you must practice those)

Restorations using User Managed and RMAN Based recovery nicely written here - Read in this blog and here

• Complete database reconstruction
• Recovery of a particular tablespace
• Recovering from the loss of a single datafile
• Recovering from the loss of a particular table
• Recovering from the loss of a control file
• Recovering from the loss of all control files
• Recovering from the loss of datafiles and indexes
• Recovering from the loss of the inactive redo log
• Recovering from the loss of the active redo log
• Recovering from the loss of archive log files
• Recovering from the loss of the active rollback segment
• Recovering from the loss of all rollback segments
• Recovering with corrupted export files
• Recovering from shutdown during hot backup
• Recovering to a particular point in time
• Recovering to a particular event or activity

The following assumptions are made regarding the backup strategy in place and the operational conditions of the database:

• Control files are mirrored.
• The database is in the archivelog mode.
• Cold backups are taken every week.
• Hot backups are taken every day.
• A full database export is taken every day.

Scenario 1: Complete Database Reconstruction

In this scenario, you can re-create the database using the full database export or the cold and hot backup combination—whichever gives you the latest data. Note that the transactions in the online redo log will be lost no matter which option you choose.

Scenario 2: Recovering a Particular Tablespace

The following steps can be used to recover:

1. Start the database in the restrict mode.

2. Re-create the tablespace.

3. Import using the latest full database export and the option ignore=y.

4. Shut down and restart the database instance in the normal mode.

Restorations using User Managed and RMAN Based recovery nicely written here - Read in this blog and here

Scenario 3: Loss of General Datafiles

The procedure to recover from the loss of general datafiles depends on the type of tablespace from which the datafile is lost; for example, rollback tablespace, user tablespace, index tablespace, or read-only tablespace. You might get the following errors:

• You're trying to start up the database and get error message ORA-1157, ORA-1110, and possibly an operating system error.

• You're trying to shut down the database in normal or immediate mode and get error message ORA-1116, ORA-1110, and possibly an operating system error.

The following steps can be used to recover:

1. Shut down the database.

2. Restore the lost datafile from the hot backup.

3. Startup mount the database.

4. Execute the following query to determine all your online redo log files and their respective sequence and first change numbers:

   SELECT X.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
   FROM V$LOG X, V$LOGILE Y
   WHERE X.GROUP# = Y.GROUP#;

5. If the CHANGE# obtained is less than the minimum FIRST_CHANGE# of your online redo logs, the file can't be completely recovered and you have two choices:

   • If you can afford to lose the database changes since the most recent cold backup, restore the backup and continue with the recovery.

   • If you can't afford to lose the database changes, you'll have to re-create the tablespace.

6. Recover the datafile by using the archived and the online redo logs.

7. Open the database.

Restorations using User Managed and RMAN Based recovery nicely written here - Read in this blog and here

Scenario 4: Recovering a Particular Table

The following steps can be used to recover:

1. Import the table using the latest full database export and the option owner=<schema_owner> and tables=<table_name>.

2. You may need to rebuild indexes on the table for performance reasons.

Scenario 5: Loss of a Control File

Control file problems are usually not detected while the database is up and running. If the control file is lost or damaged in such a way that Oracle can't recognize it, a subsequent database startup will result in ORA-205 (error in identifying control file "%s"), along with an operating system level error.

When only one control file is lost, the following steps can be used to recover:

1. Shut down the instance if it's still running.

2. Find the cause of the loss of the control file. Is it due to a hardware problem (disk or controller)?

3. If the hardware isn't a problem, copy a good copy of the control file to the location of the lost control file and skip to step 5.

4. If the hardware is the problem, copy a good copy of the control file to a reliable location.

5. Edit initsid.ora or configsid.ora, updating the CONTROL_FILES parameter to reflect the new location of the control file.

6. Start up the database.

Scenario 6: Loss of All Control Files

The following steps can be used to recover:

1. Shut down the database.

2. Take a full database backup, including all the datafiles and redo log files.

3. Start up the database in the NOMOUNT state.

4. Re-create the control file using CREATE CONTROLFILE. Alternatively, you can back up the control file to a trace file and execute the file.

5. Perform media recovery on the database.

6. Open the database.

7. Do a shutdown normal of the database.

8. Take a cold backup of the database.

Scenario 7: Loss of an Index

The easiest approach is to re-create the lost index.

Scenario 8: Loss of an Inactive Redo Log

If redo data is missing, the recovery won't be complete and you'll have to re-create the tablespace(s) involved. To re-create the tablespace, you can use the full database export that can easily load the data and re-create the objects in that tablespace(s). The following steps can be used to recover:

1. Alter the system to switch the redo log file.

2. Shut down the database.

3. Startup mount the database.

4. Offline drop the datafile(s) involved.

5. Open the database.

6. Drop the user tablespace(s), including contents.

7. Re-create the tablespace and the tablespace objects using the full database export.

Restorations using User Managed and RMAN Based recovery nicely written here - Read in this blog and here

Scenario 9: Loss of Active Redo Log

As discussed in scenario 8, if redo data is missing, the recovery won't be complete and you'll have to re-create the tablespace(s) involved. The following steps can be used to recover:

1. Shut down the database.

2. Startup mount the database.

3. Offline drop the datafile(s) involved.

4. Open the database.

5. Drop the user tablespace(s), including contents.

6. Re-create the tablespace and the tablespace objects using the full database export.

Note that active transactions will be lost.

Scenario 10: Loss of Archived Redo Log Files

If archived redo log files are lost, a cold backup of the database should immediately be taken. A full database export will also be desirable. Any recovery performed without the lost archived redo log files will be incomplete.

Scenario 11: Loss of Active Rollback Segment

This scenario refers to the loss of a datafile in a rollback segment. This is a critical recovery process and it essentially focuses on saving the active transactions. It assumes that the database is still up and running and you really want to save the currently running transactions. For the recovery procedure described below to work, the database must be running in the archivelog mode.

The following steps can be used to recover:

1. Don't shut down the database. It's simpler to resolve the situation with the database up than with the database down.

2. Offline all the rollback segments in the tablespace to which the datafile belongs.

3. Drop all offlined rollback segments.

4. In step 2 above, you won't be able to offline rollback segments with active transactions. Run the following query to determine the active transactions:

   SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
   FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
   WHERE TABLESPACE_NAME = 'tablespace_name' AND
   SEGMENT_ID = USN;

   If the above query returns no rows, all the rollback segments are offline. However, if the above query returns one or more rows with a status of PENDING OFFLINE, check the ACTIVE_TX column for these rollback segments. Segments with a value of zero will soon go offline; however, a nonzero value indicates that you have active transactions that need to be committed or rolled back.

5. Deal with the active transactions. Execute the following query to identify users who have transactions assigned to the rollback segments:

   SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"
   FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
   WHERE R.NAME IN ('pending_rollback1','pending_rollback2', .... 'pending_rollbackN') AND
   S.TADDR = T.ADDR AND
     T.XIDUSN = R.USN;

   After you determine which users have active transactions in the "pending offline" rollback segments, you can ask them to commit or roll back their transactions, or you can kill their sessions by executing the following:

   ALTER SYSTEM KILL SESSION 'sid, serial#';

6. After you have taken care of the active transactions, follow these steps:

   Drop the tablespace, including contents.

   Re-create the rollback tablespace.

   Re-create the rollback segments and bring them online.

Scenario 12: Loss of All Rollback Segments

In this situation, you'll lose all active transactions and you'll have to re-create the rollback segments. Usually this may be the result of a more widespread database corruption (potentially a hardware problem). The following steps can be used to recover:

1. Shut down the database.

2. Run DBVERIFY against all the datafiles.

3. Resolve any other hardware problems or datafile corruption.

4. Startup mount the database instance.

5. Perform media recovery on the database.

6. Open the database.

7. Create new rollback segments as needed.

Scenario 13: Corrupted Export Files

If the export files get corrupted, a cold backup of the database should be taken as well as a full database export. This is assuming that the database itself is not corrupted. If the database is corrupted as well, the following steps should be performed.

1. ORA-1157 error message would usually identify that one or more of the datafiles are corrupted. Identify the tables affected by the corruption. These should be the tables in the datafiles indicated by the error message.

2. Select the data from the tables into temporary tables by bypassing the bad data blocks.

3. Drop the corrupted tables.

4. Rename the temporary table to the dropped table.

5. Rebuild all indexes on the affected tables.

6. Analyze all corrupted tables with the VALIDATE STRUCTURE CASCADE option.

It should be noted that data in the corrupted blocks will be lost and cannot be recovered.

Scenario 14: Shutdown During Hot Backup

Restorations using User Managed and RMAN Based recovery nicely written here - Read in this blog and here

If a database shutdown occurs while a hot backup is in progress, some of the tablespaces may be left in the backup mode. When you try to open the database, it will only mount, indicating that there are tablespaces in hot backup mode. Since the database can't be opened, you won't be able to take the tablespace out of hot backup mode. The following steps can be used to recover:

1. Startup mount the database.

2. Query v$backup to determine those datafiles that are in the ACTIVE state.

3. Place these datafiles out of hot backup mode by using the command ALTER DATABASE DATAFILE <FILENAME> END BACKUP.

4. Open the database.

Scenario 15: Recovering to a Particular Point in Time

The following steps can be used to perform point-in-time recovery:

1. Shut down the database instance.

2. Startup the database instance in the NOMOUNT state.

3. Recover the database using the UNTIL <time> option.

4. Open the database.

5. Shutdown NORMAL the database instance.

6. Start up the database instance.

Scenario 16: Recovering to a Particular Event or Activity

The following steps can be used to recover:

1. Shut down the database instance.

2. Start up the database instance in the NOMOUNT state.

3. Recover the database using the UNTIL CANCEL option. Supply the archived redo logs requested until the desired activity/event.

4. Type CANCEL to cancel the recovery.

5. Open the database.

6. Shutdown NORMAL the database instance.

7. Start up the database instance.

Scenario 17: Flash Back Table

Scenario 18: Flash Back Transaction

Scenario 19: Flash Back Database

Hope this helps !!!!!

Restorations using User Managed and RMAN Based recovery nicely written here - Read in this blog and here