Oracle: What is an Unrecoverable Data File?

Feb 4, 2010 / By Catherine Chow

Tags: , ,

A data file is considered unrecoverable if an unrecoverable operation has been performed on an object residing in the data file since the last backup of the data file. Operations will become unrecoverable if they are not logged in the redo log. These “nologging” operations that suppress the generation of redo log, include the following:

  1. direct load/SQL load
  2. direct-path inserts result from insert or merge statement
  3. ALTER TABLE commands
  4. CREATE and ALTER INDEX commands
  6. partition manipulation
  7. database object that has explicitly set with nologging option
  8. Oracle eBusiness Suite concurrent job execution identified in Oracle metalink note: 216211.1
  9. Oracle eBusiness Suite patches activities that involve database object manipulation

The database recovery operations will look completed, but those data blocks used by the nologging objects in the data file will be marked corrupted when they are recovered. Accessing those nologging data objects in the recovered database instance will return a data block reading error such as ORA-1578 and ORA-26040, and the logical corruption in the data file will prevent the database object from being useful in the recovered database instance.

How do we detect unrecoverable operations?

Unrecoverable data files are those that involve nologging operations since the last successful backup took place. There are several ways to identify them. You can locate those data files either with RMAN or by querying V$ tables.

A. Retrieve unrecoverable data file information from RMAN

RMAN> report unrecoverable database;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

14   full or incremental     +DGDATA/A1/data file/apps_ts_tx_data.286.662131793

32   full or incremental     +DGDATA/A1/data file/apps_ts_tx_data.326.667991737

33   full or incremental     +DGDATA/A1/data file/apps_ts_tx_data.327.667991741


80   full or incremental     +DGDATA/A1/data file/A1_obiee_data.386.682596961

81   full or incremental     +DGDATA/A1/data file/A1_obiee_data.387.682596961

82   full or incremental     +DGDATA/A1/data file/A1_obiee_data.388.684801365

RMAN> exit

B. Retrieve unrecoverable data file information directly from v$ table

SQL> SELECT data file_name, df.unrecoverable_time
FROM v$data file 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'))

DATAFILE_NAME                                                UNRECOVERABLE_TIME

------------------------------------------------------------ -------------------

+DGDATA/A1/data file/apps_ts_tx_data.286.662131793          2010-01-08:00:13:13

+DGDATA/A1/data file/apps_ts_tx_data.326.667991737          2010-01-08:00:13:13

+DGDATA/A1/data file/apps_ts_tx_data.327.667991741          2010-01-08:00:13:10


+DGDATA/A1/data file/A1_obiee_data.386.682596961           2010-01-08:06:18:34

+DGDATA/A1/data file/A1_obiee_data.387.682596961           2010-01-08:06:18:34

+DGDATA/A1/data file/A1_obiee_data.388.684801365           2010-01-08:06:18:34

21 rows selected.

What database objects are affected by the unrecoverable operation?

We can locate the database objects affected by these unrecoverable operations by retrieving these objects with the following script:

select distinct dbo.owner,dbo.object_name, dbo.object_type, dfs.tablespace_name,
dbt.logging table_level_logging, ts.logging tablespace_level_logging
from v$segstat ss, dba_tablespaces ts, dba_objects dbo, dba_tables dbt,
v$data file df, dba_data_files dfs, v$tablespace vts
where ss.statistic_name ='physical writes direct'
and dbo.object_id = ss.obj#
and vts.ts# = ss.ts#
and ts.tablespace_name =
and ss.value != 0
and df.unrecoverable_change# != 0
and dfs.file_name =
and ts.tablespace_name = dfs.tablespace_name
and dbt.owner = dbo.owner
and dbt.table_name = dbo.object_name


--------------- ------------------------------ ------------------- ---------------- --- ---------





MSC             MSC_TP_ID_LID                  TABLE               APPS_TS_TX_DATA  YES LOGGING

MSC             MSC_TP_SITE_ID_LID             TABLE               APPS_TS_TX_DATA  YES LOGGING

OBIEE_OWNER     GL_DETAIL                      TABLE               A1_OBIEE_DATA    NO  LOGGING




OBIEE_OWNER     A1_GEOGRAPHY                   TABLE               A1_OBIEE_DATA    NO  LOGGING


--------------- ------------------------------ ------------------- ---------------- --- ---------

OBIEE_OWNER     A1_PRODUCT_CLASS               TABLE               A1_OBIEE_DATA    NO  LOGGING

12 rows selected.

Directly knowing the objects or tables affected in the recovery operation due to nologging, can help both us and the data owners to make an informed decision on whether nologging needs to be disabled, based on the transactional or analytical nature of the data in those tables.

How do we fix unrecoverable data files?

One way to force these nologging objects operation to be captured in the redo log, is to implement FORCE_LOGGING, a feature introduced in Oracle 9i to allow Oracle to force logging in redo even if nologging operations are performed. Force logging can be enforced at the database and tablespace level. In Oracle ERP e-Business suite, the default installation of EBS sets the database FORCE_LOGGING option to NO. The intent of this setup is to enhance overall system performance. If this setting is not changed to YES, all “nologging” operations mentioned in this section will suppress the generation of redo log, causing unrecoverable operations issues with all nologging objects.

Before altering the database or tablespace to force logging, it is important to evaluate the benefit of force logging versus those of nologging.  Once force logging at the database level is turned on, redos are generated for all operations, dramatically increasing redo size. In addition, since there is an extra operation of capturing to redo log, performance of data inserts and updates will also be affected.

If a decision has been made to keep the database and tablespace in NO force logging mode, it is imperative for the business owner and database administrator to collectively ensure a good backup is taken immediately prior to any patching activities, direct load, and other nologging operations take place. In addition, DBAs should periodically query database to identify data files that contain nologging activities, and whenever that happens, to perform a database backup to ensure data file recovery integrity. For example, in the case of Oracle ERP e-Business Suite, this would mean a database backup will need to be performed every time prior to any patching activities.


If there are unrecoverable data files identified in the database, it is important for DBAs to provide the  affected non-recoverable nologging objects to the business owner to determine if these objects are of important recovery value. Examples of nologging objects which can be safely ignored includes tables that can easily be generated from source table, or tables designed to be temporary in nature.

It is important for business owners to decide if the performance gain from setting database or tablespace to NO force logging, offsets the recoverability of nologging database objects identified. Should a decision be made to keep the database and tablespace in NO force logging mode–which makes data files unrecoverable–both DBA and business owner need to be vigilant to ensure that database recoverability is achievable by taking more frequent backups.

5 Responses to “Oracle: What is an Unrecoverable Data File?”

  • coskan says:

    Nice one Catherine,

    For the second query (unrecoverable objects) . you assume that it is a table (join to dba_tables) you wrote the query but it might as well be an index and also some of the unnecessary joins can be avoided by using v$segment_statistics instead of v$segstats.

    select distinct ss.owner,ss.object_name, ss.object_type
    ,ss.tablespace_name, ts.logging tablespace_level_logging
    v$segment_statistics ss, dba_tablespaces ts,v$datafile df
    ss.statistic_name =’physical writes direct’
    and ss.value >0
    and df.unrecoverable_change# >0
    and ss.ts#=df.ts#
    and ss.tablespace_name=ts.tablespace_name;

  • Catherine Chow says:

    Thanks for your comment and the simplified query. You are correct, unrecoverable objects can also be indexes, and this should also be identified. Please note, unrecoverable indexes will only become an issue if they cannot be reconstructed by the underlying tables. Cheers…

  • [...] on Solaris 17-How to report if there was an unrecoverable operation happened? Catherine Chow-Oracle: What is an Unrecoverable Data File? 18-How to use latch classes in case you really want to change the spin count and cause minimal [...]

  • Tom says:

    Insert Append operations are logged unless you use the nologging clause. If you use force logging it will happen anyways.

    Here is the testcase that was tried.

    Archive log mode
    Take hot backup
    insert records into a table with the append hint
    shutdown the DB
    delete the tablespace
    mount the DB
    restore the tablespace
    recover it
    open the db
    query the table to validate the records that were added after the backup by the insert append hint are there.

    If you use the no logging option then it won’t be logged.

    Here is a link to asktom on this very same question.

  • Ruslan says:

    In the B query,
    select max(start_time) is a safer assumption. FULL backup can take hours, and while a datafile that was backed up first may get unrecoverable status while full backup was still running.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>