Oracle: What is an Unrecoverable Data File?

Nice one Catherine,

For the second query (unrecoverable objects) . you assume that it is a table (join to dba_tables) you wrote the query but it might as well be an index and also some of the unnecessary joins can be avoided by using v$segment_statistics instead of v$segstats.

select distinct ss.owner,ss.object_name, ss.object_type
,ss.tablespace_name, ts.logging tablespace_level_logging
from
v$segment_statistics ss, dba_tablespaces ts,v$datafile df
where
ss.statistic_name =’physical writes direct’
and ss.value >0
and df.unrecoverable_change# >0
and ss.ts#=df.ts#
and ss.tablespace_name=ts.tablespace_name;

Thanks for your comment and the simplified query. You are correct, unrecoverable objects can also be indexes, and this should also be identified. Please note, unrecoverable indexes will only become an issue if they cannot be reconstructed by the underlying tables. Cheers…

[…] on Solaris 17-How to report if there was an unrecoverable operation happened? Catherine Chow-Oracle: What is an Unrecoverable Data File? 18-How to use latch classes in case you really want to change the spin count and cause minimal […]

Insert Append operations are logged unless you use the nologging clause. If you use force logging it will happen anyways.

Here is the testcase that was tried.

Archive log mode
Take hot backup
insert records into a table with the append hint
shutdown the DB
delete the tablespace
mount the DB
restore the tablespace
recover it
open the db
query the table to validate the records that were added after the backup by the insert append hint are there.

If you use the no logging option then it won’t be logged.

Here is a link to asktom on this very same question.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8289905224297

In the B query,
select max(start_time) is a safer assumption. FULL backup can take hours, and while a datafile that was backed up first may get unrecoverable status while full backup was still running.

Hi I think the ‘physical writes direct’ stat does not tell us if the statement was logged or not.

Here’s the proof. The results are the same both in logging or in nologging case:

———————————
— SETUP
———————————
[email protected]>create table l (n number);
insert into l values (13);
Table created.

[email protected]>

1 row created.

[email protected]>

[email protected]>set lines 1111
[email protected]>@stat;
@segstat;

NAME VALUE
—————————————————————- ———-
physical write IO requests 0
physical write bytes 0
physical write total IO requests 0
physical write total bytes 0
physical write total multi block requests 0
physical writes 0
physical writes direct 0
physical writes direct (lob) 0
physical writes direct temporary tablespace 0
physical writes from cache 0
physical writes non checkpoint 0

11 rows selected.

[email protected]>
OWNER OBJECT_NAME STATISTIC_NAME VALUE
—————————— —————————— —————————————————————- ———-
A L physical writes 0
A L physical write requests 0
A L physical writes direct 0

———————————
— FORCE LOGGING
———————————
[email protected]>alter database force logging;
select force_logging from v$database;
create table l_forcelogging as select * from l;
set lines 1111
@stat
@segstat

Database altered.

[email protected]>
FOR
—
YES

[email protected]>
Table created.

[email protected]>[email protected]>
NAME VALUE
—————————————————————- ———-
physical write IO requests 1
physical write bytes 8192
physical write total IO requests 7
physical write total bytes 106496
physical write total multi block requests 0
physical writes 1
physical writes direct 1
physical writes direct (lob) 0
physical writes direct temporary tablespace 0
physical writes from cache 0
physical writes non checkpoint 1

11 rows selected.

[email protected]>
OWNER OBJECT_NAME STATISTIC_NAME VALUE
—————————— —————————— —————————————————————- ———-
A L physical writes 0
A L physical write requests 0
A L physical writes direct 0
A L_FORCELOGGING physical writes 1
A L_FORCELOGGING physical write requests 1
A L_FORCELOGGING physical writes direct 1

6 rows selected.

———————————
— NOLOGGING
———————————
[email protected]>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[[email protected]_appl ~]$ sqlplus a/a

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 21 16:42:26 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

[email protected]>
[email protected]>
[email protected]>alter database no force logging;
select force_logging from v$database;
create table l_nologging as select * from l nologging;
set lines 1111
@stat
@segstat

Database altered.

[email protected]>
FOR
—
NO

[email protected]>
Table created.

[email protected]>[email protected]>
NAME VALUE
—————————————————————- ———-
physical write IO requests 1
physical write bytes 8192
physical write total IO requests 7
physical write total bytes 106496
physical write total multi block requests 0
physical writes 1
physical writes direct 1
physical writes direct (lob) 0
physical writes direct temporary tablespace 0
physical writes from cache 0
physical writes non checkpoint 1

11 rows selected.

[email protected]>
OWNER OBJECT_NAME STATISTIC_NAME VALUE
—————————— —————————— —————————————————————- ———-
A L physical writes 0
A L physical write requests 0
A L physical writes direct 0
A L_FORCELOGGING physical writes 1
A L_FORCELOGGING physical write requests 1
A L_FORCELOGGING physical writes direct 1
A L_NOLOGGING physical writes 1
A L_NOLOGGING physical write requests 1
A L_NOLOGGING physical writes direct 1

9 rows selected.

My segments were on users tablespace. And here are the properties:

@a11203ef>select TABLESPACE_NAME, logging, force_logging from dba_tablespaces;

TABLESPACE_NAME LOGGING FOR
—————————— ——— —
USERS LOGGING NO

Cheers,
Rob.