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
  5. INSERT /*+APPEND*/
  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 df.name 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 = vts.name
and ss.value != 0
and df.unrecoverable_change# != 0
and dfs.file_name = df.name
and ts.tablespace_name = dfs.tablespace_name
and dbt.owner = dbo.owner
and dbt.table_name = dbo.object_name

OWNER           OBJECT_NAME                    OBJECT_TYPE         TABLESPACE_NAME  TAB TABLESPAC

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

APPLSYS         WF_LOCAL_ROLES_STAGE           TABLE               APPS_TS_TX_DATA  YES LOGGING

APPLSYS         WF_LOCAL_USER_ROLES_STAGE      TABLE               APPS_TS_TX_DATA  NO  LOGGING

APPLSYS         WF_UR_ASSIGNMENTS_STAGE        TABLE               APPS_TS_TX_DATA  YES LOGGING

APPS            DR$IBE_CT_IMEDIA_SEARCH_IM$I   TABLE               APPS_TS_TX_DATA  YES LOGGING

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     GL_EXPENDITURES                TABLE               A1_OBIEE_DATA    NO  LOGGING

OBIEE_OWNER     A1_DEPARTMENT_DIM              TABLE               A1_OBIEE_DATA    NO  LOGGING

OBIEE_OWNER     A1_FUNCTION_CHANNEL            TABLE               A1_OBIEE_DATA    NO  LOGGING

OBIEE_OWNER     A1_GEOGRAPHY                   TABLE               A1_OBIEE_DATA    NO  LOGGING

OWNER           OBJECT_NAME                    OBJECT_TYPE         TABLESPACE_NAME  TAB TABLESPAC

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

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.

Conclusion

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.

6 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
    from
    v$segment_statistics ss, dba_tablespaces ts,v$datafile df
    where
    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.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8289905224297

  • 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.

  • Robkol says:

    Hi I think the ‘physical writes direct’ stat does not tell us if the statement was logged or not.

    Here’s the proof. The results are the same both in logging or in nologging case:

    ———————————
    — SETUP
    ———————————
    A@a11203ef>create table l (n number);
    insert into l values (13);
    Table created.

    A@a11203ef>

    1 row created.

    A@a11203ef>

    A@a11203ef>set lines 1111
    A@a11203ef>@stat;
    @segstat;

    NAME VALUE
    —————————————————————- ———-
    physical write IO requests 0
    physical write bytes 0
    physical write total IO requests 0
    physical write total bytes 0
    physical write total multi block requests 0
    physical writes 0
    physical writes direct 0
    physical writes direct (lob) 0
    physical writes direct temporary tablespace 0
    physical writes from cache 0
    physical writes non checkpoint 0

    11 rows selected.

    A@a11203ef>
    OWNER OBJECT_NAME STATISTIC_NAME VALUE
    —————————— —————————— —————————————————————- ———-
    A L physical writes 0
    A L physical write requests 0
    A L physical writes direct 0

    ———————————
    — FORCE LOGGING
    ———————————
    A@a11203ef>alter database force logging;
    select force_logging from v$database;
    create table l_forcelogging as select * from l;
    set lines 1111
    @stat
    @segstat

    Database altered.

    A@a11203ef>
    FOR

    YES

    A@a11203ef>
    Table created.

    A@a11203ef>A@a11203ef>
    NAME VALUE
    —————————————————————- ———-
    physical write IO requests 1
    physical write bytes 8192
    physical write total IO requests 7
    physical write total bytes 106496
    physical write total multi block requests 0
    physical writes 1
    physical writes direct 1
    physical writes direct (lob) 0
    physical writes direct temporary tablespace 0
    physical writes from cache 0
    physical writes non checkpoint 1

    11 rows selected.

    A@a11203ef>
    OWNER OBJECT_NAME STATISTIC_NAME VALUE
    —————————— —————————— —————————————————————- ———-
    A L physical writes 0
    A L physical write requests 0
    A L physical writes direct 0
    A L_FORCELOGGING physical writes 1
    A L_FORCELOGGING physical write requests 1
    A L_FORCELOGGING physical writes direct 1

    6 rows selected.

    ———————————
    — NOLOGGING
    ———————————
    A@a11203ef>exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing options
    [oracle@ebs_appl ~]$ sqlplus a/a

    SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 21 16:42:26 2014

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing options

    A@a11203ef>
    A@a11203ef>
    A@a11203ef>alter database no force logging;
    select force_logging from v$database;
    create table l_nologging as select * from l nologging;
    set lines 1111
    @stat
    @segstat

    Database altered.

    A@a11203ef>
    FOR

    NO

    A@a11203ef>
    Table created.

    A@a11203ef>A@a11203ef>
    NAME VALUE
    —————————————————————- ———-
    physical write IO requests 1
    physical write bytes 8192
    physical write total IO requests 7
    physical write total bytes 106496
    physical write total multi block requests 0
    physical writes 1
    physical writes direct 1
    physical writes direct (lob) 0
    physical writes direct temporary tablespace 0
    physical writes from cache 0
    physical writes non checkpoint 1

    11 rows selected.

    A@a11203ef>
    OWNER OBJECT_NAME STATISTIC_NAME VALUE
    —————————— —————————— —————————————————————- ———-
    A L physical writes 0
    A L physical write requests 0
    A L physical writes direct 0
    A L_FORCELOGGING physical writes 1
    A L_FORCELOGGING physical write requests 1
    A L_FORCELOGGING physical writes direct 1
    A L_NOLOGGING physical writes 1
    A L_NOLOGGING physical write requests 1
    A L_NOLOGGING physical writes direct 1

    9 rows selected.

    My segments were on users tablespace. And here are the properties:

    @a11203ef>select TABLESPACE_NAME, logging, force_logging from dba_tablespaces;

    TABLESPACE_NAME LOGGING FOR
    —————————— ——— —
    USERS LOGGING NO

    Cheers,
    Rob.

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>