Skip to Main Content
  • Questions

  • Please explain unrecoverable_time field in v$datafile.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ravi.

Asked: October 14, 2000 - 8:17 pm UTC

Last updated: April 03, 2007 - 9:02 pm UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

Tom,

I created table t1 with nologging option using "create table as subquery". Then queried unrecoverable_change#, unrecoverable_time columns v$datafile view. I am not able to find the last SCN and unrecoverable_time corresponding to my operation.

I also tried the same with "insert into t1 select * from scott.emp nologgig". Then I quried from v$datafile to see the last unrecoverable_time. I am not seeing the time corresponding to the time I did the operation.

I expect unrecoverable_time of v$datafile to show the time when I did the nologging operation. Is my expectaion correct. If so, could you please explain what mistake I am doing.

Log of my activities:
---- ---- ----------

system@oracle> create table t1 as select * from scott.emp nologging;

Table created.

system@oracle> select file#, unrecoverable_change#, to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:
ss') from v$datafile;

FILE# UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERAB
--------- --------------------- -------------------
1 0
2 0
3 0
4 0
5 0
6 0 07-19-2000 12:04:14
7 0
8 0

8 rows selected.

system@oracle> select to_char(sysdate, 'mm-dd-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'MM
-------------------
10-14-2000 17:13:48

system@oracle> commit;

Commit complete.

system@oracle> insert into t1 select * from scott.emp nologging;

14 rows created.

system@oracle> commit;

Commit complete.


system@oracle> select file#, unrecoverable_change#, to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:
ss') from v$datafile;

FILE# UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERAB
--------- --------------------- -------------------
1 0
2 0
3 0
4 0
5 0
6 0 07-19-2000 12:04:14
7 0
8 0

8 rows selected.

system@oracle>



Regards
Ravi

and Tom said...

You've used the word "nologging" in the wrong place. In the above, it is being used as a TABLE ALIAS. Your:

create table t1 as select * from scott.emp nologging;

is the same as:

create table t1 as select * from scott.emp A;


You want to:

create table t1 NOLOGGING as select * from scott.emp;

Here is an example that shows the create table as select and how to do an unrecoverable INSERT (please read about the APPEND hint and make SURE you understand the ramifications of using it before using it!)

ops$tkyte@DEV816> drop table t;

Table dropped.

ops$tkyte@DEV816> select file#,
2 to_char(unrecoverable_change#,'99999999999999999999'),
3 to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss')
4 from v$datafile
5 where file# = 7
6 /

FILE# TO_CHAR(UNRECOVERABLE TO_CHAR(UNRECOVERAB
---------- --------------------- -------------------
7 5966356510632 10-15-2000 09:53:22

ops$tkyte@DEV816>
ops$tkyte@DEV816> exec dbms_lock.sleep(2)

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> create table t nologging as select * from scott.emp;

Table created.

ops$tkyte@DEV816>
ops$tkyte@DEV816> select file#,
2 to_char(unrecoverable_change#,'99999999999999999999'),
3 to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss')
4 from v$datafile
5 where file# = 7
6 /

FILE# TO_CHAR(UNRECOVERABLE TO_CHAR(UNRECOVERAB
---------- --------------------- -------------------
7 5966356510645 10-15-2000 09:53:48

ops$tkyte@DEV816>
ops$tkyte@DEV816> exec dbms_lock.sleep(2)

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> insert /*+ APPEND */ into t select * from scott.emp;

14 rows created.

ops$tkyte@DEV816>
ops$tkyte@DEV816> select file#,
2 to_char(unrecoverable_change#,'99999999999999999999'),
3 to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss')
4 from v$datafile
5 where file# = 7
6 /

FILE# TO_CHAR(UNRECOVERABLE TO_CHAR(UNRECOVERAB
---------- --------------------- -------------------
7 5966356510655 10-15-2000 09:53:51






Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

unrecoverable time on which object ?

Yoav, November 01, 2006 - 7:51 am UTC

Hi,

select file#, UNRECOVERABLE_CHANGE#, unrecoverable_time from v$datafile;

Thats teel me on which datafiles nologging-Operations happended.
Is it possible to know on which objects it occured?

Thanks

Tom Kyte
November 01, 2006 - 8:46 am UTC

no, just the set of possible objects.

O/S File "Date Modified" not up-to-date

Alex, April 02, 2007 - 1:04 pm UTC

Tom

Our is a Windows 2003 Server environment.

Looking at the .dbf files via Window Explorer, the "Date Modified" shows date as early as 3/13/2007, even though the checkpoint time show today's date as indicated below.

select file#, last_time, checkpoint_time from v$datafile;

FILE# LAST_TIME CHECKPOIN
---------- --------- ---------
1 02-APR-07
2 02-APR-07
3 02-APR-07
4 02-APR-07
5 02-APR-07
6 02-APR-07
7 02-APR-07
8 02-APR-07
9 02-APR-07
10 02-APR-07
11 02-APR-07


Can you please tell me why is that so ?

What can I do to make the O/S file reflect the current date and time; I already tried inserting a record, commit, and switch log file, but to no avail.

Even the Redo log files show 3/14/2007. The database is running and alive.
Tom Kyte
April 03, 2007 - 9:02 pm UTC

why do you care?

windows is "unique", we haven't closed the files in a long time - that is all.

Alex, April 04, 2007 - 11:05 am UTC

Thanks Tom.

I was alarmed for a while as it looked like the database activities were "not updated" into the files.

Anyway, I did a 100,000 records insert thereafter and the Windows timestamp shows the current date and time.

I thought there would be a command in Oracle similar to the Unix "touch".

REPORT UNRECOVERABLE;

Kev B, August 28, 2012 - 4:37 am UTC

Hi Tom!

While performing a test restore of a database on a test server I encountered problems performing the recovery. It was a basically straightforward RMAN restore to the most recent point in time.

Because of this problem I went back to the production server and ran REPORT UNRECOVERABLE at which point the 6 datafiles that comprise the database were returned as unrecoverable.

I don't understand why this may have happened. The backups that have been taken (a weekly INC0 and daily INC1 cumulative) have been taken successfully (at least, no errors have been returned) and our hardware monitoring system has shown no hardware defects in the storage arrays.

What else may cause a backup to become unrecoverable?

Many thanks for your answer in advance.

Kind regards,

Kev
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library