Skip to Main Content
  • Questions

  • Is it possible to reset the Oracle SCN in any way?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ahmed.

Asked: August 18, 2016 - 3:37 pm UTC

Last updated: August 23, 2019 - 8:33 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

We have a load of legacy code that uses ORA_ROWSCN as an optimistic locking field. Unfortunately, the legacy (COBOL) code uses a 10(?)-digit field for the host variable, and we experience horrible problems when the SCN ticks past the size that this can accommodate. It's exacerbated by the fact that we use a database link for pushing some test data in and out, and this causes regular large jumps which make the problem recur even faster.

Rebuilding the database by importing and exporting sorts things out for a while, but is a major, major pain.

Is there any way to reset the database SCN (rather like a cycling sequence)?

and Chris said...

No. This is fundamental for Oracle being able to generate read consistent views of data. As the docs say:

A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction. Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.

SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time, and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time in the database. Several events may share the same SCN, which means that they occurred at the same time in the database.

http://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT039

If you could set it to a lower value, you'd break your database...

Rating

  (3 ratings)

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

Comments

resetlogs

Charlie, August 23, 2019 - 1:31 am UTC

I (still) have 32-bit 8.1.7.4 and 10.2.0.5 that remain mission-critical.

We only got off 7.3.4 in 2013, migrating critical data to 10g just in time for the very last CPU.

We do maintain database links into modern, cloud-hosted Oracle instances. I know our HR connections moved from PeopleSoft into the Oracle cloud this way.

Fortunately (?), this focus on Oracle 7 led me to rman BACKUP AS COPY, and this is my preferred strategy on all platforms wher available (in 8i I still ALTER TABLESPACE BEGIN BACKUP).

What are my risks of catastrophic SCN failure? Are these somewhat ameliorated by not using rman channels?

Yes, I know I should upgrade. I really say this to developers and management with increasing hysteria.
Chris Saxon
August 23, 2019 - 8:33 am UTC

Only upgraded from 7 in 2013?! Yikes!

What are my risks of catastrophic SCN failure? Are these somewhat ameliorated by not using rman channels?

You mean the problem where using database links can lead to syncing SCNs beyond their allowed limit?

If so, I'll point you to Mike Dietrich's blog:

https://mikedietrichde.com/2019/02/25/you-must-patch-12-1-0-1-and-11-2-0-3-and-older-databases-before-june-2019/

As he says:

What is the risk of NOT patching?

You should be aware about potential database link issues in future and consider about upgrading the databases or not using database links with newer versions of databases. If you continue to have such database links after June 2019, you may get run-time errors during database link operations and you would need to disconnect those database links at that time.

What should you do now?

For owners of a large number of databases, across earlier versions, where you are not able to patch or upgrade and these databases use dblinks with very databases of newer releases, please contact Oracle Support immediately for guidance.

SCN and dumping data to another database

lh, August 26, 2019 - 8:49 am UTC

Hi

If problem is that fields reserved for SCN are too small, could one create a new empty database, export/import all data and users to it and then rename database.

Would SCN start from 0 after doing this (and after suffering long service break).

lh

ups.

lh, August 26, 2019 - 8:51 am UTC

"Rebuilding the database by importing and exporting sorts things out for a while, but is a major, major pain."

Sorry, didn't notice at first this statement. Ignore my previous posting.
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.