Recovery :- Open Database Recovery with Offline Tablespace

 'Data recovery' refers to the process of recovering data from a hard drive, removable disk, or other type of electronic storage media when the data is no longer accessible via normal means.Data recovery can be a simple process in many cases, and in some cases may require exhaustive, detailed work to recover the data.Here we are trying to explain Open Database Recovery with Offline Tablespaces

If your system suffers a media failure but the database stays up, you can perform an open database recovery and recover only the tablespaces that are damaged. Users can continue to access the online tablespaces and datafiles that were not damaged by the failure. Oracle automatically takes damaged datafiles offline.

Follow these steps to execute an open database recovery with offline tablespaces: 
1) The database should be started and open. 

2) Take all tablespaces containing damaged datafiles offline using the ALTER TABLESPACE tablespace_name OFFLINE command. You can query the V$DATAFILE view to see which datafiles are offline.

3) Correct the problem that caused the media failure. If the problem cannot be corrected in a reasonable amount of time, your other option is to restore the damaged files to another location. Follow these three steps to relocate the datafile(s):

• Make sure the tablespace that contains the datafiles is offline.
• Make sure that the new, fully specified file names are different from the old file names.
• Use the ALTER DATABASE command with the RENAME FILE option to change the file names within the database.

If the media problem can be corrected, restore the most recent backup files of only the datafiles damaged by the media failure. Remember that the database is open. Do NOT try to restore undamaged datafiles, logfiles, or control files!

4) Use the RECOVER TABLESPACE command to start offline tablespace recovery of all damaged datafiles in one or more offline tablespaces.

Oracle will now start the roll forward by applying the archived redo logfiles and the online redo logfile. If AUTORECOVERY is set to ON, the applying of the log files is automatic. If it is not set to ON, you will be prompted for each logfile.

5) The damaged tablespaces of the open database are now recovered up to the point of failure. You can bring the offline tablespaces online using ALTER TABLESPACE command with the ONLINE option.

Read More

Types of problem that can occur during Media Recovery

Media recovery encounters a problem while recovering a database backup. There are some problems occur during media recovery.

1) Missing ArchivedLog:
In this database cannot find the archived log recorded in the control file so it stops the recovery.

2) When you attempt to open the database, error ORA-01113 Indicates that a datafile needs media recovery:  

- You are performing incomplete recovery but failed to restore all needed datafile backups. 

- Incomplete recovery stopped before datafiles reached a consistent SCN. 

- You are recovering datafiles from an online backup, but not enough redo was applied to make the datafiles consistent. 

- You are performing recovery with a backup control file, and did not specify the location of a needed online redo log. 

- A datafile is undergoing media recovery when you attempt to open the database. 

- Datafiles needing recovery were not brought online before executing RECOVER DATABASE, and so were not recovered.

 3) Corrupted archived logs:
Logs may be corrupted while they are stored on or copied between storage systems. If DB_BLOCK_CHECKSUM is enabled, then the database usually signals checksum errors. If checksumming is not on, then log corruption may appear as a problem with redo.

4) Redo record problems:
- In this, stuck recovery problem occur because of failed consistency checks thats why it stops recovery.
- The database signals an internal error when applying the redo. This problem can be caused by an Oracle bug. If checksums are not being used, it can also be caused by corruptions to the redo or data blocks.

5) Corrupted data blocks:
A datafile backup may have contained a corrupted data block, or the data block may become corrupted either during recovery or when it was copied to the backup. If checksums are being used, then the database signals a checksum error. Otherwise, the problem may also appear as a redo corruption.

6) Random problems:
Memory corruptions and other transient problems can occur during recovery.

Read More

Solution of Inconsistent Control File

Inconsistent error occur when Oracle detects an inconsistency between the multiplex copies of the control file. All copies of the control file must have the same internal sequence number Typical scenarios in which you may receive this inconsistencu error include:

1. You have restored the control file from backup, but you have forgot to copy it to other locations of the control file as listed in the "CONTROL_FILES" parameter in the initialization parameter.

2. You have moved one or more copies of the control file to a different location while the database was up and running.

3. You someone overwrote one of the copies of the control file with an old copy.

4. You are restoring a database backup that was improperly taken with the database up and running.


To get the solution of these problem start your database with single copy of the control file and then shut the database down and then copy of this good copy of control file to the other locations as listed in the control_files parameter.
Following these steps to solve this problem:

Step 1.  If database is still up, shutdown abort.

Step 2. If you use pfile then edit the CONTROL_FILES parameter from init.ora and modify it with one copy of control file.

If you use spfile then after startup nomount use show control_files to see existing controlfiles inside spfile and then you can use ALTER SYSTEM SET CONTROL_FILES=file_name; in order to point just one copy of control file.

Step 3. startup restrict

If instead you get ORA-1122, ORA-1110, and ORA-1207, go back to step 2 and try with another control file.

If you have already tried each and every one of the mirrored copies unsuccessfully, you must create a new control file for the database.

If you get ORA-1113 and ORA-1110 pointing to one of the datafiles, it means the copy of the control file you picked is good, but the referenced datafile must be recovered before the database can be opened.Then RECOVER DATBASE, apply the log it prompt and ALTER DATABASE OPEN.

Step 4.  Shutdown the database.
SQL>shutdown

Step 5. Copy this control file and locate to the different locations.

Step 6. Edit the init.ora file CONTROL_FILES parameter to include all location of the copy control files again.

Step 7. Startup  database.

Read More

User Managed Restore Procedure in Oracle

If someone has deleted datafile,there is user errors or disk is crushed then there is necessary to restore a datafile from backup. If you restore any datafile then just copy it to the location in the parameter specified in the control_files. If you restore any control file then copy in the destination which spfile or pfile points in control_files parameter.
In the following section you can know what you do if you choose user managed recovery:
 

1) If all copies of the control file have lost and you have backup of the control file then simply copy the backup of control file to the location as located in the control_files parameter in initialization parameter file pfile/spfile. After do this you have to open your database with resetlogs option. And if you don't have backup of the control file then you have to create new control file.

2) If you lose one or more datafiles then copy the datafiles to the location where it was. You can copy it to another location and point the control file to the new location by ALTER DATABASE RENAME FILE ' old' TO 'New '.

3) If you have backup of archived log then you can copy it to the location and perform recovery from there. But if the deleted archived log that is needed for media recovery and you have no backup then you must perform incomplete recovery (UNTIL SCN) and open the database with RESETLOGS open.

Related Post:- Solution of Inconsistent Control File

Read More

Recovering a Database in NOARCHIVELOG Mode

In NOARCHIVELOG mode the recovery is very simple. All you have to do is to restore the database files from your backup to the original location  and start the database. If one of your data file got corrupted. The time try to start the Oracle Instance, it will give you an error. In this case shutdown the database. Restore all the data files, redo log files and control file back to their original location from your last whole/complete cold backup.Then start the Oracle database again and then connecting as SYSDBA.There are some steps as following:

1) If the database is open then shutdown the database
Shutdown immediate

2) If possible, correct the media problem so that the backup database files can be
restored to their original locations.
3) Restore the most recent whole database backup. Restore all of the
datafiles and control files of the whole database backup, not just the damaged
files. The following example restores a whole database backup:
% cp /opt/luck1/BACKUP/tbs* /opt/luck # restores datafiles
% cp /opt/luck1/BACKUP/cf.f /opt/luck # restores control file
 

4) Because online redo logs are not backed up, you cannot restore them with the
datafiles and control files. In order to allow Oracle to reset the online redo logs,
you must have to do incomplete recovery:
RECOVER DATABASE UNTIL CANCEL
CANCEL
 

5) Open the database in RESETLOGS mode.
ALTER DATABASE OPEN RESETLOGS;

Read More