CODEWITHSUNDEEP
oracle-databaserecoverytablespacerman
user961532
user961532

Reputation: 87

Oracle SCN clarification

I would like to know why I am getting different SCN number for the below quires

  1. SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL - I USE this for POINT IN TIME RECOVER FOR TABLESPACE .

  2. SELECT CURRENT_SCN FROM V$DATABASE. - I use this for Database Recovery(RMAN)

WHY I AM GETTING TWO DIFFERENT SCN ?

I know the basic of SCN , but still I am confused . Can anyone clarify what is the exact meaning of the query

Upvotes: 2

Views: 1051

Answers (2)

Hard Developer
Hard Developer

Reputation: 319

It not about 2 different queries , even if you try with any one of the query the SCN is different every time as it get generated every real time second.Even running of your query forces the database to create the new SCN evry time.Hence Every time you fires the query you get different SCN number of Database.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231681

timestamp_to_scn gives an approximate result. In any given second, a database is likely to go through thousands of SCNs so the result cannot be exact. And it would be terribly expensive to maintain a table that associated a timestamp with every SCN that the system had ever encountered. Under the covers, Oracle maintains a table that stores the current SCN every few seconds and keeps that data for a few days. In recent versions, the granularity of that table is 1 SCN every 3 seconds though that may change over time.

When you call timestamp_to_scn, therefore, you get an SCN that was created within a few seconds of the date you're interested in but it's never going to be exact and it's not going to work forever. That's generally close enough for a point in time recovery-- you know that you want to restore to May 20, 2015 at 12:05:00 am but you don't really care if you restore to a state a second or two earlier or later. If you're identifying a particular bad transaction that you want to restore the system to (or to just before), you wouldn't want to use timestamp_to_scn.

Upvotes: 2

Related Questions