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 nologgingOracle DML with direct path insertsOracle insert with the append hintOracle 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.
RMAN> report unrecoverable database;
Report of files that need backup due to unrecoverable operationsFile Type of Backup Required Name---- ----------------------- -----------------------------------
RMAN> SQL> create table scott.empfromhr nologging tablespace example as select * from hr.employees;
Table created.
SQL> RMAN> report unrecoverable database;
Report of files that need backup due to unrecoverable operationsFile Type of Backup Required Name---- ----------------------- -----------------------------------5 full or incremental +ASMDATA/asmorcl/datafile/example.265.967328481
RMAN>
