COMPLETE RECOVERY

Complete Recovery

Recovery in Noarchivelog Mode

Steps	Explanation and Commands
1	Restore all datafiles, control files, and redo log files from the backup to the original location.  $ cp   -r   /u01/backup/*    /u02/oracle/data
2	Restart the database  SVRMGR> startup open
3	Notify users that they will need to reenter data since the time of the last backup.

Advantages

·         Easy to perform, with low risk of error.

·         Recovery time = Time to restore all files.

Disadvantages

·         The entire database is restored to the point of the last whole closed backup.

·         Data is lost and must be reapplied manually.

Example :             The hard disk 1 is damaged and you have one Oracle datafile named user_01.dbf in this disk. You need to recover the database and restore user_01.dbf to another hard disk.

$ cp  <backup directory>  <data directory>

SVRMGR> connect / as sysdba

Connected.

SVRMGR> startup mount

Oracle instance started.

SVRMGR> alter database rename file

     2> ‘/disk1/data/user_01.dbf’

     3> to ‘/disk2/data/user_01.dbf’;

Statement Processed.

SVRMGR> alter database open;

Statement Processed.

Complete Recovery in Archivelog Mode

Steps	Explanation
1	Restore only lost or damaged datafiles.
2	Do not restore the control file, redo logs,  password, or parameter files.
3	Recover the datafiles.

Advantages

 ·         Only need to restore lost files

·         Recovers all data to the time of failure

·         Recovery time = Time to restore lost files and apply all archived logs

Disadvantages

 ·         Must have all archived logs since the  backup from which you are restoring

Cases Study

 CASE 1 : Further investigation reveals that corrupt blocks were found on disk 2 where datafile 2 is stored. From your well documented database records, you have determined that datafile 2 is one of the files belonging to the system tablespace.

Steps	Explanation and Commands
1	Restore the damaged file from backup (the most recent if available): $ cp /disk1/backup/df2.dbf /disk2/data/
2	Start the instance in mount mode SVRMGR> startup mount

3	To bring the datafile to the point of failure, all needed archived logs and redo logs are applied. SVRMGR> recover datafile ‘/disk2/data/df2.dbf‘; or SVRMGR> recover database;
4	When recovery is finished, all datafiles are synchronized. Open the database. SVRMGR> alter database open;

CASE 2 : Your training DBA immediately informs you that it was not media failure he accidentally removed datafile number 2 using operating system commands. The database is currently open and you don’t want to shut down the database.

Steps	Explanation and Commands
1	The database is currently open, so to determine which tablespace the datafile belongs to, use the following command: SQL > select file_id f#, file_name,   2 > tablespace_name tablespace, status   3 > from dba_data_files; F#   FILE_NAME                   TABLESPACE  STATUS ---  --------------------------  ----------  ---------- 1    /disk1/data/system_01.dbf   SYSTEM      AVAILABLE 2    /disk2/data/df2.dbf         USER_DATA   AVAILABLE 3    /disk1/data/rbs01.dbf       RBS         AVAILABLE ...
2	Determine whether we need to take datafile 2 offline (in this case, Oracle has already taken the file offline): SQL > select d.file# f#, d.name, d.status, h.status   2 > from v$datafile d, v$datafile_header h   3 > where d.file# = h.file#; F#   D.NAME                       D.STATUS   H.STATUS ---  --------------------------   ---------  ---------- 1    /disk1/data/system_01.dbf    SYSTEM     ONLINE 2    /disk2/data/df2.dbf          RECOVER    OFFLINE 3    /disk1/data/rbs_01.dbf       ONLINE     ONLINE ...
3	Since the file is offline, the file can now be restored successfully: cp /disk1/backup/df2.dbf /disk2/data/
4	Use the “recover” commands to apply the archives and the redo logs to the restored data file. SVRMGR> recover datafile ‘/disk2/backup/df2.dbf‘; or SVRMGR> recover tablespace USER_DATA;
5	When recovery is finished, all datafiles are synchronized. Bring the datafile online: SVRMGR> alter database datafile ‘/disk2/data/df2.dbf‘ online; or SVRMGR> alter tablespace USER_DATA online;

Case 3 : From your investigation, you have known that datafile 2 is damaged. From your familiarity with the database, you know datafile 2 is not a system or rollback segment datafile, nor will it prevent users running their end-of-month reports. You want to recover the database and minimize the down time also.

Steps	Explanation and Commands
1	Mount the database. It will not open because datafile 2 cannot be opened. SVRMGR > startup mount Database mounted.
2	If the datafile is not offline, the database will not open. Therefore the file must be taken offline. You have queried V$DATAFILE and determined that the file is online. The following command must therefore be issued: SVRMGR > alter database datafile ‘/disk2/data/df2.dbf‘ offline; Statement processed. Note: The “alter tablespace” command cannot be used here since the database is not opened yet.
3	The database can now be opened: SVRMGR > alter database open; Statement processed.
4	Now that users can access the system, you have minimized the down time. Then you restore the file from the backup directory. $ cp /disk1/backup/df2.dbf /disk3/data/

5	Use the “recover” commands to start applying the archives and the redo logs to the restored datafile. SVRMGR> recover datafile ‘/disk3/data/df2.dbf‘; or SVRMGR> recover tablespace USER_DATA;
6	When recovery is finished, all datafiles are synchronized. Bring the datafile online: SVRMGR> alter database datafile ‘/disk3/data/df2.dbf‘ online; or SVRMGR> alter tablespace USER_DATA online;