Thursday 28 June 2012

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;


No comments:

Post a Comment