SCN Structure & Generation Schemes

We have discussed about SCN in our last post. Let’s explore about structure of SCN as well as generation/propagation schemes.

SCN Structure

SCN is a 6 Byte (48 bit) number with max value is 281,474,976,710,656 (281 Trillion) and represented as 2 parts – SCN_BASE and SCN_WRAP. An SCN_BASE is a 4 Byte (32 bit) number and SCN_WRAP is a 2 Byte (16 bit) number. Whenever SCN_BASE reaches its maximum (2 power 32 = 4294967296), SCN_WRAP is increased by one and SCN_BASE will be reset to 0. This continues until SCN_WRAP reaches its maximum, i.e. 2 power 16 = 65536.

SCN = (SCN_WRAP * 2^32) + SCN_BASE

SQL> select max(scn_wrp),max(SCN_bas) from smon_scn_time group by scn_wrp;

MAX(SCN_WRP) MAX(SCN_BAS)

------------ ------------

          0       2227514

 

Even if the SCN value does reach its maximum, then SCN will be reset to 0, thus causing a new incarnated database. So, all your old backups and archived logs become useless and you need to take fresh backups. From 12c onwards Oracle might use 8 Byte SCN format.

Current SCN can be obtained by either of the following queries:

• select dbms_flashback.get_system_change_number scn from dual;
  
• select current_scn from v$database;
  
• select scn_wrp*power(2,32) +SCN_bas SCN from smon_scn_time where scn_bas=( select max(scn_bas) SCN_BASE from sys.smon_scn_time); /* This query will show delayed output so only used for older releases where aforesaid methods will not work */
  

Will change in incarnation reset the SCN?

The answer is absolutely NO in this case. Let’s try to check that if reset logs will be able to do anything with SCN.

 

SQL> select max(scn_wrp),max(SCN_bas) from smon_scn_time group by scn_wrp;

MAX(SCN_WRP) MAX(SCN_BAS)

------------ ------------

     0       2219651

SQL> select RESETLOGS_CHANGE#,PRIOR_RESETLOGS_CHANGE#, CHECKPOINT_CHANGE# from v$database;

RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# CHECKPOINT_CHANGE#

----------------- ----------------------- ------------------

           945184                       1            2220839

 

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 2220839 generated at 11/03/2014 14:24:10 needed for thread 1

ORA-00289: suggestion : /u02/app/archive/arch1_35_782696129.arc

ORA-00280: change 2220839 for thread 1 is in sequence #35

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

 

SQL> alter database open resetlogs;

Database altered.

 

SQL> select RESETLOGS_CHANGE#,PRIOR_RESETLOGS_CHANGE#, CHECKPOINT_CHANGE# from v$database;

RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# CHECKPOINT_CHANGE#

----------------- ----------------------- ------------------ 

          2220840                  945184            2220843

SQL> select max(scn_wrp),max(SCN_bas) from smon_scn_time group by scn_wrp;

MAX(SCN_WRP) MAX(SCN_BAS)

------------ ------------

           0      2219651

 

So it’s clear that SCN will not be reset even in case of reset logs. SCN will only be reset once it will touch the hard limit 281 Trillion, post that Oracle will reset it and incarnation will be changed.

Some (Un)Supported ways to bump SCN

There is no way of resetting SCN number, while couple of unsupported ways to bump SCN by a large gap, we are giving an example of one in this discussion just to see it will work or not. There is one undocumented parameter _minimum_giga_scn, which can be used to bump SCN in order to overcome a situation of serious corruption when if SCN is showing ahead of database SCN. This parameter is not advised to be used on production without Oracle support analyst’s guidance. Once database is online it’s advised to take full export and import to a new database. Also from 11.2.0.2.5 onwards this parameter is deprecated.

Let’s check current SCN from v$database

 

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    2227504

 

Check if _minimum_giga_scn parameter is available on your database version

 

SQL> select ksppinm from x$ksppi where ksppinm like '%giga%';

KSPPINM

--------------------

_minimum_giga_scn

 

Pick some SCN value where you need to jump SCN, basically round integer value in approx Giga (2^30).

 

SQL> select ceil(10987654321/1024/1024/1024) from dual;

CEIL(10987654321/1024/1024/1024)

--------------------------------

                              11

 

Set value in pfile _minimum_giga_scn=11 in order to bump SCN by 11 Giga and start database from this parameter file.

 

SQL> startup nomount pfile='/tmp/init_MinGigaScn.ora'

ORACLE instance started.

Total System Global Area 939495424 bytes

Fixed Size 2218952 bytes

Variable Size 704644152 bytes

Database Buffers 226492416 bytes

Redo Buffers 6139904 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

 

Let’s verify the modified SCN value

 

SQL> col current_scn for 99999999999999

SQL> select current_scn from v$database;

CURRENT_SCN

---------------

11811160292

 

In alert log following message is written

 

Advancing SCN to 11811160064 according to _minimum_giga_scn

 

At this point if we check smon_scn_time it will verify the bump, as we can see there is no entry with SCN_WRP 1.

 

SQL> select max(scn_wrp),max(SCN_bas) from smon_scn_time group by scn_wrp;

MAX(SCN_WRP) MAX(SCN_BAS)

------------ ------------

           2   3221225981

           0      2227514

 

There are couple of other ways as well like setting ADJUST_SCN event to bump SCN by N billion records i.e. “alter session set events ‘IMMEDIATE trace name ADJUST_SCN level N’;” where N -> integer number represents the bump in billions, so 1 means SCN will bump by 1 billion numbers. Another technique is by updating datafile header by some large SCN value then recreate control file so new control file will assume our new value as max SCN number and then you can open database with resetlogs to get higher scn number. Whatever options we have just listed will put you into situation where Oracle will not support your environment so don’t try these in your work environment at all.

While one safest way to see bumping SCN is by accessing your database from a remote database having higher SCN, Oracle will assume this type of activity as part of distributed transaction so will synchronize SCNs. Last but not the least, why should we are willing to bump the SCN , it doesn’t make sense to increase it as it may lead to another situation where SCN headroom might be crossed ( if you are not having (_external_scn_logging_threshold_seconds & _external_scn_rejection_delta_threshold_minutes) and your database might not be able to allow further transactions until kind of moving window for your limit will give you some flex.

Max limit of SCN

SCN will only be reset once it will touch the hard limit 281 Trillion, post that Oracle will reset it and incarnation will be changed. In addition to that, there is also a soft limit imposed by Oracle code as a protection mechanism. If the next SCN is more than the soft limit, ORA-600[2252] is emitted and the operation cancelled. For example, in the case of database link based distributed transaction, if the co-ordinate SCN is greater than the soft limit ORA-600 emitted.

This soft limit is calculated using the formula (number of seconds from 1/1/1988) * 16384. As the number of seconds from 1/1/1988 is continuously increasing, soft limit is increasing at the rate of 16K per second continuously. Unless, your database is running full steam generating over 16K SCNs, you won’t run in to that soft limit that easily. [ But, you could create ORA-600[2252] by resetting your server clock to 1/1/1988].

Problem comes if many interconnected databases each generating at higher rate in kind of round-robin fashion. Database A generates 20K SCNs per second in the first 5 minutes, database B generates 20K SCNs per second in the next 5 minutes, database C generates 20K SCNs per second in the next 5 minutes etc. In this case, all three Databases will have a sustained 20K SCNs per second rate. Database is slowly catching up to soft limit (1 second per every 4 second exactly) and again, it will take many years for them to catch up to the soft limit assuming the databases are active, continuously. To reach hard limit, it will take 544 years to run out of SCN at 16K rate normally (65536*4*1024*1024*1024 / 16384 / 60/60/24/365). In 10g, this 16K per second was hard coded. But, 11gR2, this limit is controlled by an underscore parameter _max_reasonable_scn_rate defaulting to 32K.

MAX_COMMIT_PROPAGATION_DELAY

This parameter specifies the maximum amount of time allowed before the system change number (SCN) held in the SGA of an instance is refreshed by the log writer process (LGWR). It determines whether the local SCN should be refreshed by Global Cache Services when getting the snapshot SCN for a query. Units are in hundredths of seconds. Under unusual circumstances involving rapid updates and queries of the same data from different instances, the SCN might not be refreshed in a timely manner. Setting the parameter to zero causes the SCN to be refreshed immediately after a commit. The default value (700 hundredths of a second, or seven seconds) is an upper bound that enables the preferred existing high performance mechanism to remain in place. Note that by changing this value you change the SCN generator used by your cluster database. The SCN can be managed by the GCS, by the Lamport SCN generation scheme, or by using a hardware clock or dedicated SCN server.

Examine the alert.log after instance startup to determine whether the Lamport SCN generation scheme is in use. If the value for MAX_COMMIT_PROPAGATION_DELAY is smaller than the threshold value, then Oracle uses the hardware clock SCN generation scheme. Sometime due to performance or stability reasons scn generation scheme could be changed manually under guidance of Oracle support analyst by setting _scn_scheme parameter.

Lamport SCN Generation Scheme

One of the popular SCN generation scheme is Lamport SCN generation, it is efficient and scalable because it generates SCNs in parallel on all instances. In this scheme, all messages between instances carry SCNs. Multiple instances can generate SCNs in parallel without additional communication among these instances. In single instance Oracle, the System Global Area (SGA) maintains and increments SCNs from an instance that has mounted the database in exclusive mode. In Real Application Clusters, the SCN must be maintained globally and its implementation can vary by platform.

Lock SCN Generation Scheme

One of the schemes Oracle uses to generate SCNs is the lock scheme. The lock SCN scheme keeps the global SCN in the value block of the SCN lock. This value is incremented in response to many database events, most notably COMMIT WORK. A process incrementing the global SCN will get the SCN lock in exclusive mode, increment the SCN, write the lock value block, and downgrade the lock. Access to the SCN lock value is batched. Oracle keeps a cache copy of the global SCN in memory. A process may get an SCN without any communication overhead by reading the SCN fetched by other processes.

Broadcast-on-Commit Scheme

Broadcast on Commit scheme is marginally more resource intensive than the Lamport Scheme. Whenever LGWR writes to the redo log (that is, with every commit) it also sends a message to update the global SCN as well as sends a message to every active instance to update local SCN. This effectively defines a separate mechanism for keeping SCNs synchronized globally in the cluster. However, this scheme has proven to be a requirement for a small percentage of the applications spectrum that exhibit the problematic behavior with Lamport scheme.

Broadcast on commit (BOC) is the default commit propagation mechanism used in RAC. When a transaction commits at a certain SCN (“commit SCN”) from an instance, the commit SCN should be propagated to the other instances so that the changes made by the transaction can be visible from other instances as well. With BOC, LGWR broadcasts the current SCN at each log write to other instances who haven’t seen the SCN yet, enabling immediate commit propagation. Upon receiving an SCN via either an explicit SCN broadcast or a message/block transfer (see below), each instance compares the received SCN to its current Lamport SCN and adjust it if necessary. (Thus “commit propagation” is essentially “SCN propagation” and in this article both terms are used interchangeably.)

Oracle also allows SCNs to be propagated in a delayed fashion. This can be done by setting “_immediate_commit_propagation” init.ora parameter to FALSE (TRUE means BOC and is the default). This “Lamport propagation scheme” relies any inter-instance messages or block transfers from GCS (cache fusion) or GES (global enqueue). Adjusting the Lamport SCN upon receiving an SCN is identical as BOC. This scheme is more lightweight than BOC since there are no SCN broadcast at each log write. However, commited changes from another instance may not be visible immediately and thus it is not suitable for every application.

With BOC, a commit for a transaction is not finished until its commit SCN has been broadcast to the other instances and the acknowledgements been received from them. Thus, BOC is a crucial component in RAC performance for its direct tie to the commit latency.

Determining SCN Generation Scheme:

Since the implementation is platform specific, the SCN propagation scheme that was actually selected can be determined from the alert.log. e.g. “Picked” scheme to generate SCNs” or check the few entries in alert log when startup of instance has been logged.

 

From 9i database

LICENSE_SESSIONS_WARNING = 0
SCN scheme 1
Using log_archive_dest parameter default value

LICENSE_SESSIONS_WARNING = 0
SCN scheme 3
Using log_archive_dest parameter default value

 

From 10g & 11g Single instance DB

LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2 
Using LOG_ARCHIVE_DEST_1 parameter default value

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

 

From 11gR2 RAC database

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

……………………………………………………………

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

Sat Oct 13 00:05:53 2012

ALTER DATABASE OPEN

……………………………………………………………

Picked broadcast on commit scheme to generate SCNs (This is because of RAC)

Here are links to our other posts in SCN series

System Change Number (SCN)
Checkpoint & SCN
Role of SCN in Instance Recovery & Media Recovery
SCN on Scale of Time & SCN of a ROW
Read Consistency & SCN
Block Sequence Number & SCN

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of either the companies I have worked for or Oracle Corporation and its affiliates. The comments, views and opinions expressed by visitors on this blog are theirs alone and may not reflect mine. Whatever scenarios suggested under this blog were simulated only on demo environment, so it’s advisable to test those in test systems before pushing to your production environment.