Database Reference
In-Depth Information
Restoring and Recovering with a Database Online
If you lose a data file associated with a tablespace other than
SYSTEM
and
UNDO
, you can restore and recover the
damaged data file while leaving the database online. For this to work, any data files being restored and recovered must
be taken offline first. You may be alerted to an issue with a data file in which a user is attempting to update a table and
sees an error such as this:
SQL> insert into foo values(2);
ORA-01116: error in opening database file ...
You navigate to the OS directory that contains the data file and determine that it has been erroneously removed
by a system administrator.
In this example the data file associated with the
USERS
tablespace is taken offline and subsequently restored and
recovered while the rest of the database remains online. First, place take the data file offline:
SQL> alter database datafile '/u01/dbfile/O12C/users01.dbf' offline;
Now, restore the appropriate data file from the backup location:
$ cp /u01/hbackup/O12C/users01.dbf /u01/dbfile/O12C/users01.dbf
In this situation, you can't use
RECOVER DATABASE
. The
RECOVER DATABASE
statement attempts to recover all
data files in the database, of which the
SYSTEM
tablespace is part. The
SYSTEM
tablespace can't be recovered while the
database is online. If you use the
RECOVER TABLESPACE
, all data files associated with the tablespace must be offline.
In this case, it's more appropriate to recover at the data file level of granularity:
SQL> recover datafile '/u01/dbfile/O12C/users01.dbf';
Oracle inspects the SCN in the data file header and determines which archive redo log or online redo log to use to
start applying redo. If all redo required is in the online redo logs, you see this message:
Media recovery complete.
If the starting point for redo is contained only in an archive redo log file, Oracle suggests which file to start with:
ORA-00279: change 3502285 generated at 11/02/2012 10:49:39 needed for thread 1
ORA-00289: suggestion : /u01/oraarch/O12C/1_1_798283209.dbf
ORA-00280: change 3502285 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
You can type
AUTO
to have Oracle apply all required redo in archive redo log files and online redo log files:
AUTO
If successful, you should see this message:
Log applied.
Media recovery complete.
