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:
- direct load/SQL load
- direct-path inserts result from insert or merge statement
ALTER TABLE commandsCREATE and ALTER INDEX commandsINSERT /*+APPEND*/- partition manipulation
- database object that has explicitly set with nologging option
- Oracle eBusiness Suite concurrent job execution identified in Oracle metalink note: 216211.1
- 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.
No Comments Yet
Let us know what you think