In the view V$DATAFILE, there are two columns that are updated when an unrecoverable operation is run in the database and modified the specific datafile.
The columns are:
* UNRECOVERABLE_CHANGE# – Change number at point unrecoverable change completes.
* UNRECOVERABLE_TIME – Timestamp at point unrecoverable change completes.
Monitoring these columns is useful if you are running physical standby database in your environment and can give you a heads up on any files that may need to be copied from the primary database.
This is also useful, since a number of bugs in Oracle partitioning cause indexes to be rebuilt in nologging mode, even when the system is supposed to be logging.
Code Listing 5 : Detecting Unrecoverable Operations
Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0
References Used : Oracle® Database Reference 10g Release 2 (10.2)
Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)
— lists all objects in tablespaces belonging to datafiles
— that have received unrecoverable data where the object
— has received “physical writes direct” since instance startup
— detecting NOLOGGING objects
—
select o.owner,o.object_name,dfs.tablespace_name,s.value, t.logging
from v$segstat s, v$tablespace ts, dba_objects o, dba_tables t,
v$datafile df, dba_data_files dfs
where s.statistic_name = ‘physical writes direct’
and o.object_id = s.obj#
and ts.ts# = s.ts#
and s.value != 0
and df.unrecoverable_change# != 0
and dfs.file_name = df.name
and ts.name = dfs.tablespace_name
and t.owner = o.owner
and t.table_name = o.object_name
order by 2,3,1 ;