You have a choice between two basic methods for recovering physical files. Oracle DBA can:
Whichever method Oracle DBA choose, you can recover a database, tablespace, or datafile. Before performing media recovery, you need to determine which datafiles to recover. Often Oracle DBA can use the fixed view V$RECOVER_FILE. This view lists all files that require recovery and explains the error that necessitates recovery.
- Use the RMAN utility to restore and recover the database
- Restore backups by means of operating system utilities, and then recover by executing the SQL*Plus RECOVER command
Learn Oracle - Highlights
Learn Oracle - RMAN Restore and Recovery
The basic RMAN recovery commands are RESTORE and RECOVER. Use RESTORE to restore datafiles from backup sets or from image copies on disk, either to their current location or to a new location. Oracle DBA can also restore backup sets containing archived redo logs. Use the RMAN RECOVER command to perform media recovery and apply archived logs or incremental backups.
RMAN automates the procedure for recovering and restoring your backups and copies. For example, run the following commands from within RMAN to restore and recover the database to its current time:
SHUTDOWN IMMEDIATE; # shuts down database
STARTUP MOUNT; # starts and mounts database
RESTORE DATABASE; # restores all datafiles
RECOVER DATABASE; # recovers database using all available redo
ALTER DATABASE OPEN; # reopens the database
Learn Oracle - User-Managed Restore and Recovery
If you do not use RMAN, then you can restore backups with operating system utilities and then run the SQL*Plus RECOVER command to recover the database. Oracle DBA should follow these basic steps:
For example, assume that you lose the /oracle/dbs/users1.dbf datafile, which is contained in the users tablespace, to a media failure. Also, assume that you have a backup called /dsk2/backup/users1.dbf on a separate disk drive. You discover that the datafile is missing because a query returns an error saying that this file (and only this file) is missing.
- After identifying which files are damaged, place the database in the appropriate state for restore and recovery. For example, if some but not all datafiles are damaged, then take the affected tablespaces offline while the database is open.
- Restore the files with an operating system utility. If you do not have a backup, it is sometimes possible to perform recovery if you have the necessary redo logs dating from the time when the datafiles were first created and the control file contains the name of the damaged file.
- If Oracle DBA cannot restore a datafile to its original location, then relocate the restored datafile and change the location in the control file.
- Restore any necessary archived redo log files.
- Use the SQL*Plus RECOVER command to recover the datafile backups.
Your first step is to take the users tablespace offline. For example, Oracle DBA run this SQL statement:
SQL> ALTER TABLESPACE users OFFLINE TEMPORARY;
Then, you restore the backup of users1.dbf using an operating system utility. For example, you run this UNIX command:
% cp /dsk2/backup/users1.dbf /oracle/dbs/users1.dbf
Assuming that you have all necessary archived redo logs, you can recover the datafile with the following SQL*Plus command:
SQL> RECOVER AUTOMATIC DATAFILE '/oracle/dbs/users1.dbf';
Finally, bring the tablespace online as follows:
SQL> ALTER TABLESPACE users ONLINE;
Back to Top