Thanks very much for the answer.

I agree that SCN # 9105 in incarnation-1 and SCN #9105 in incarnation-2 are not the same.

But i could not get your point about old changes ( in previous incarnation ) are consistent with new incarnation.

Could you please elaborate a little bit on your point "But the changes BEFORE the new incarnation are consistent, and hence accessible."

My question is :
what is the lowest SCN in incarnation-2 to which i can restore the database to ?

and what output we should expect from the following query ?

select scn_to_timestamp(7000) from dual;

the above query fired while in incarnation-2 ( which is created by restoring to SCN 9000) .

According to Oracle doc , the starting SCN of incarnaion-2 will be 9000 .
So i feel i should always get error from the above query because the SCN 7000 is not present in current incarnation-2. But strangely i get some output ( in terms of time stamp ) that makes me confused.

thanks a lot for your valuable time !!

Excellent example. Makes to understand very clearly. Thanks.

Hi ,

Thank you so much for this amazingly simple explanation :)

The equivalence picture on database side should be like this :

i should be able to fetch all SCNs starting from SCN#1 to SCN# 9000, isn't it ? ( as restore done to SCN # 9000 )

But it does not happen !!.

I only can fetch to certain point say upto SCN#6500 in current incarnation-2.

Any SCN prior to SCN#6500 if i try to find with "scn_to_timestamp" query, i get an error like " SCN is before RESETLOGS time "

My question is why upto #6500 it shows,
Could you please put some light on the concept involved here ?

SQL> select scn_to_timestamp(11548016) from dual;

SCN_TO_TIMESTAMP(11548016)
-----------------------------------------------------------------------
09-OCT-15 01.23.34.000000000 PM

SQL> select scn_to_timestamp(11408016) from dual;

SCN_TO_TIMESTAMP(11408016)
-----------------------------------------------------------------------
08-OCT-15 11.55.49.000000000 AM

SQL> select scn_to_timestamp(10208016) from dual;
select scn_to_timestamp(10208016) from dual
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

yes exactly.. :)

thanks a lot for your insightful and very useful concept about the mappings on which scn_to_timestamp is based.

Could you please clarify the mappings point a little bit further.

Let me share the exact output of my database here.

==============================================================================

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1279924695 PARENT 1 22-JAN-15
2 2 ORCL 1279924695 PARENT 1899059 02-MAR-15
3 3 ORCL 1279924695 PARENT 179122425 21-MAY-15
4 4 ORCL 1279924695 CURRENT 779369056 03-OCT-15 >>> I did restore to 29th Sep ( 779369056 ) which can be evident from the first scn-to-timestamp "QUERY # 1.
(
Now database is currently in the Inc #4 : and i am executiing the followiing query.

Query #1 :

SYS@ORCL > select SCN_TO_TIMESTAMP(779369056) from dual;

SCN_TO_TIMESTAMP(779369056)
---------------------------------------------------------------------------
2015-09-29 19:10:56 >>>>>>>>>>>>>>>> This is Okay becaause i restored to 29th sep and evidence that i resored to 29th as this SC

QUERY #2 :
SYS@ORCL > select SCN_TO_TIMESTAMP(775139460) from dual;

SCN_TO_TIMESTAMP(775139460)
---------------------------------------------------------------------------
2015-09-28 19:10:30 >>>>> i am surprised why it is able to show 28th sep ( which is before 29th sep ) in current incarnation ( inc # 4 as in above list incarnation o/p)

QUERY # 3:

SYS@orcl > select SCN_TO_TIMESTAMP(770773642) from dual; >>>>>> this SCN is of 27th Sep ( I have cheked with "list backup" RMAN command.) but it does not shows up.
select SCN_TO_TIMESTAMP(770773642) from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1


==============================================================================

Could you please have a look at my above out put and suggest me where i am getting stuck in understanding the concept.

in my example above : oracle is storing the mapping of 28th Sep but not of date 27th Sep or before.

As you said that not all scn-to_timestamp mapping are stored.
I understand that oracle must be using some internal algorithm to store/delete the mappings.

As a DBA is there any way we can know beforehand that which mappings ( scn to timestamp mappings ) will be available at a particular point of time so that we can plan the recovery process accordingly.

I hope this answer of yours is the critical concept that will clear all my confusions. :)

Thanks once again for your answer !! :)

great !!

thanks for this useful info.

could you please answer my old question :

is there a certain Lowest SCN in a particular incarnation to which we can restore the database to or there is no concept like that ?

[ please take my example i shared just above : I did restore to say SCN# 779369056 and now current incarnation is 4 ]

I think I can restore upto 779369056 in current incarnation-4, am I right ?

thanks a lot for the answers and follow ups.. :)

Yes it clears some confusions and concepts.