Oracle unrecoverable datafile tips

Question:  What does it mean when I have an unrecoverable Oracle data file?  How can I prevent an unrecoverable data file?

Answer:  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)

Oracle RMAN 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 flagged. 

The RMAN 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:

select 

   file#,  

   unrecoverable_change#, 

   to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss') 

from 

   v$datafile;

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.

RMAN> report unrecoverable database; 

You can also query for unrecoverable data files using the v$rman_backup_job_details  view:

select
   df.name,
   df.unrecoverable_time 
from
   v$datafile df,
   v$backup bk 
where
   df.file#=bk.file# 
and
   df.unrecoverable_change <> 0 
and
   df.unrecoverable_time >
   (select
      max(end_time)
   from
      v$rman_backup_job_details
   where
      input_type in ('DB FULL' ,'DB INCR')
   );