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)
[...] 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 |
[...] 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 |
[...] My first post was a short one :) http://tonguc.wordpress.com/2006/12/26/detecting-unrecoverable-operations/ [...]
Pingback by Now one and a half year passed and this blog is running to a quarter million views « H.Tonguç Yılmaz - Oracle Blog — July 12, 2008 @ 5:37 pm |
– 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 |