Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 26, 2006

Detecting Unrecoverable Operations

Filed under: Oracle How To — H.Tonguç Yılmaz @ 2:38 pm

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

References Used : Oracle® Database Reference 10g Release 2 (10.2)
Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)



  1. […] 26, 2006 Detecting Unrecoverable Operations Filed under: Oracle How […]

    Pingback by Celebrating the coming 100,000th hit « H.Tonguç YILMAZ Oracle Blog — October 15, 2007 @ 3:21 pm | Reply

  2. […] pm This blog will be a year old soon. After my very first post on 2006-12-26 2:38:29 pm subjected Detecting Unrecoverable Operations a lot has changed for me during last year. I think blogging is like body building, now I feel much […]

    Pingback by Again religious festivals colide and this blog is now a year old « H.Tonguç YILMAZ Oracle Blog — December 14, 2007 @ 12:15 pm | Reply

  3. — 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 ;

    Comment by H.Tonguç Yılmaz — September 6, 2008 @ 9:36 am | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: