Oracle: What is an Unrecoverable Data File?

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 gutter=”false”] 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.
[/sql]

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:

[sql gutter=”false”] 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.
[/sql]

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.