does it mean when I have an unrecoverable Oracle data file? How can I
prevent an unrecoverable data file?
Oracle data files maintain their internal consistency by being synchronized
with the redo logs, and then an "unrecoverable" operation is performed, no
redo logs are generated and the data file is said to be unrecoverable.
An unrecoverable data file can become recoverable again after a full backup
has been taken.
Unrecoverable data files are commonly caused by these nologging operations:
SQL*loader with nologging
Oracle DML with direct path inserts
Oracle insert with the append hint
Oracle DDL (create table, alter table)
keeps track of unrecoverable data files and you can issue this RMAN command
to see unrecoverable files. The report unrecoverable command
inspects transactions performed since the latest backup SCN. If any
objects have been affected by nologging transactions, they are
report unrecoverable command will tell us which datafiles have had an
unrecoverable operation performed against an object residing in the datafile
after the last full backup.
REPORT UNRECOVERABLE DATABASE; # examines all datafiles
REPORT UNRECOVERABLE TABLESPACE 'users'; # examines a specific tablespace
After issuing this RMAN command, you can run this query to see the file names in v$datafile:
to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss')
The unrecoverable datafile RMAN
command must be specified if the tablespace has a data file offline, and the
un-archived redo log must be cleared to bring the tablespace online. If so,
then the data file and entire tablespace must be dropped once the RMAN
clear logfile command completes.
report unrecoverable database;
also query for unrecoverable data files using the
df.unrecoverable_change <> 0
input_type in ('DB FULL' ,'DB INCR')