You Asked

Hi team,

when I am querying V$Datafile, I am seeing an entry with unrecoverable_change# as below we dont have any Primary standby configuration and this is Standalone DB.

1. how this Column updated (UNRECOVERABLE_CHANGE#) ?

FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
1 756842 20/07/2012 21:28:52 14 14 ONLINE READ WRITE 3600136082 17/08/2016 08:53:16 3585775573 11/08/2016 22:05:30

UNRECOVERABLE_CHANGE# ---- For TS# 14 i have this value under this column ? why ?

2. What is the impact of getting value under UNRECOVERABLE_CHANGE# ?

3. Do i need to recover / restore DB ?

4. Is there any steps to overcome this ? is this a real issue ?

Thanks in advance ,

and Connor said...

When your database is in ARCHIVELOG mode, all changes are recorded in the redo logs (and hence the archived redo logs). So if you have those logs, you can be 100% sure that you will be able to recover your database.

But........ when if I did some operations where I told the database *not* to log my changes. Let's look at an example

SQL> select name, UNRECOVERABLE_CHANGE# from v$datafile;

NAME                                               UNRECOVERABLE_CHANGE#
-------------------------------------------------- ---------------------
C:\ORACLE\ORADATA\NP12\SYSTEM01.DBF                                    0
C:\ORACLE\ORADATA\NP12\SYSAUX01.DBF                                    0
C:\ORACLE\ORADATA\NP12\SOE.DBF                                         0
C:\ORACLE\ORADATA\NP12\USERS01.DBF                                     0
C:\ORACLE\ORADATA\NP12\ASKTOM01.DBF                                    0 
C:\ORACLE\ORADATA\NP12\UNDOTS01.DBF                                    0
C:\ORACLE\ORADATA\NP12\WHS_MD01.DBF                                    0
C:\ORACLE\ORADATA\NP12\TS1.DBF                                         0
C:\ORACLE\ORADATA\NP12\TS2.DBF                                         0
C:\ORACLE\ORADATA\NP12\TS3.DBF                                         0


SQL> create table T (x int, y int ) tablespace users;

Table created.

SQL> alter table t nologging;    <===== DONT LOG DIRECT MODE OPERATIONS

Table altered.

SQL>
SQL> insert /*+ APPEND */ into T
  2  select rownum, rownum from dual
  3  connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select name, UNRECOVERABLE_CHANGE# from v$datafile;

NAME                                               UNRECOVERABLE_CHANGE#
-------------------------------------------------- ---------------------
C:\ORACLE\ORADATA\NP12\SYSTEM01.DBF                                    0
C:\ORACLE\ORADATA\NP12\SYSAUX01.DBF                                    0
C:\ORACLE\ORADATA\NP12\SOE.DBF                                         0
C:\ORACLE\ORADATA\NP12\USERS01.DBF                              57545316
C:\ORACLE\ORADATA\NP12\ASKTOM01.DBF                                    0
C:\ORACLE\ORADATA\NP12\UNDOTS01.DBF                                    0
C:\ORACLE\ORADATA\NP12\WHS_MD01.DBF                                    0
C:\ORACLE\ORADATA\NP12\TS1.DBF                                         0
C:\ORACLE\ORADATA\NP12\TS2.DBF                                         0
C:\ORACLE\ORADATA\NP12\TS3.DBF                                         0

So you can see - I made some changes to "T", and hence the USERS tablespaces that were NOT logged. If I lost the datafile now, I would not be able to recover it because I'm "missing" some log information.

This is a flag to me that I should take a backup of that datafile, so that in the event of a restore being required, I know that my changes (which were not loggged) are captured in the backup. Or at least confirm that I have a backup of that file AFTER the unrecoverable scn#

RMAN> list backup of datafile 6;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    2.21G      DISK        00:00:59     24-AUG-16
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20160824T092330
        Piece Name: C:\ORACLE\PRODUCT\12.1.0.2\DATABASE\19RE1T0I_1_1
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  6       Full 57545933   24-AUG-16 C:\ORACLE\ORADATA\NP12\USERS01.DBF

Rating

(2 ratings)

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

Comments

Hariharan Senthil pandiyan, August 24, 2016 - 6:13 am UTC

Thanks for the update and its clear now how and why its happened . But to overcome the situation do we need to take a valid backup ? is that backup up the file is the solution for this ?

Thanks,

August 24, 2016 - 6:21 am UTC

yes, just do "backup datafile x" with RMAN where 'x' is the file number in question

(This is assuming you have a regular backup strategy in place, that takes care of archives etc etc)

Hariharan Senthil pandiyan, August 24, 2016 - 7:25 am UTC

Thank you so much for your clear explanation .. Great .. :)

August 25, 2016 - 2:48 am UTC

glad we could help

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.

Breadcrumb

Question and Answer