Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

SCN – What, why, and how?

Posted by Riyaj Shamsudeen on January 19, 2012

In this blog entry, we will explore the wonderful world of SCNs and how Oracle database uses SCN internally. We will also explore few new bugs and clarify few misconceptions about SCN itself.

What is SCN?

SCN (System Change Number) is a primary mechanism to maintain data consistency in Oracle database. SCN is used primarily in the following areas, of course, this is not a complete list:

  1. Every redo record has an SCN version of the redo record in the redo header (and redo records can have non-unique SCN). Given redo records from two threads (as in the case of RAC), Recovery will order them in SCN order, essentially maintaining a strict sequential order. As explained in my paper, every redo record has multiple change vectors too.
  2. Every data block also has block SCN (aka block version). In addition to that, a change vector in a redo record also has expected block SCN. This means that a change vector can be applied to one and only version of the block. Code checks if the target SCN in a change vector is matching with the block SCN before applying the redo record. If there is a mismatch, corruption errors are thrown.
  3. Read consistency also uses SCN. Every query has query environment which includes an SCN at the start of the query. A session can see the transactional changes only if that transaction commit SCN is lower then the query environment SCN.
  4. Commit. Every commit will generate SCN, aka commit SCN, that marks a transaction boundary. Group commits are possible too.

SCN format

SCN is a huge number with two components to it: Base and wrap. Wrap is a 16 bit number and base is a 32 bit number. It is of the format wrap.base. When the base exceeds 4 billion, then the wrap is incremented by 1. Essentially, wrap counts the number of  times base wrapped around 4 billion. Few simple SQL script will enumerate this better:

In the SQL statement below, we use dbms_flashback package call to get the current system change number, we also convert that number to hex format to breakdown the SCN.

col curscn format 99999999999999999999999

select to_char(dbms_flashback.get_system_change_number,'xxxxxxxxxxxxxxxxxxxxxx'),
dbms_flashback.get_system_change_number curscn from dual;

———————– ————————
280000371 10737419121

Here, hex value of the SCN is 0x280000371 and  decimal format is 10737419121. Let’s review the hex value 0x280000371, this value can be split in to two components, better written as 0x2.80000371, where 0x2 is the wrap and 0x80000371 is the hex representation of base. To verify the base and wrap, we can put them back together to get the SCN value. Essentially, multiply wrap by 4 billion and add base to get the SCN in number format. Script shows the output and see that these two numbers are matching.

col n2 format  99999999999999999999999

select to_number(2,'xxxxxxx') * 4 * power(2,30) + to_number(80000371,'xxxxxxxxxxxxxxxxxxxxxx') n2 from dual

If you continue the discussion logically, then maximum value of the wrap defines the maximum value of SCN i.e. maximum value of wrap*4 billion = 65536* 4* power(2,30) = 281,474,976,710,656 = 281 trillion values.

Does each change increment SCN?

Not necessarily. The SCN increment is  not for every change. For example, in the script below, we will change the table 1000 times, but the generated SCN will be very few.

create table  rs.dropme (n1 number , n2 number);
--------------cut --------------
col curscn format 99999999999999999999999
select dbms_flashback.get_system_change_number curscn from dual;
 for i in 1 .. 1000
 insert into rs.dropme values(i, i);
 end loop;
select dbms_flashback.get_system_change_number curscn from dual;
------------cut -----------------
alter system switch log file;
SQL> @test_case_scn
PL/SQL procedure successfully completed.

SQL> alter system switch logfile;
System altered.
Even though there were 1000 changes to the table, just 9 SCNs increased. If we dump the redo record using the script dump_last_log.sql (script is inline at the end of this post) then we can see redo records have both SCN and SUBSCN below too. Many REDO records are having same SCN and SUBSCN combo.
REDO RECORD - Thread:1 RBA: 0x000010.0000001c.018c LEN: 0x00fc VLD: 0x01
SCN: 0x0002.8000fb87 SUBSCN:  1 01/19/2012 09:14:27
REDO RECORD - Thread:1 RBA: 0x000010.0000001d.0098 LEN: 0x00fc VLD: 0x01
SCN: 0x0002.8000fb87 SUBSCN:  1 01/19/2012 09:14:27
REDO RECORD - Thread:1 RBA: 0x000010.0000001d.0194 LEN: 0x00fc VLD: 0x01
SCN: 0x0002.8000fb87 SUBSCN:  1 01/19/2012 09:14:27
REDO RECORD - Thread:1 RBA: 0x000010.0000001e.00a0 LEN: 0x00fc VLD: 0x01
SCN: 0x0002.8000fb87 SUBSCN:  1 01/19/2012 09:14:27

Database link and SCNS

Database link based transactions can cause SCN increases too. For example, let’s say that, three databases db1, db2, and db3 participate in a distributed transaction and let’s say that their current SCN is 1000, 2000, 5000 respectively in these databases. At commit time, a co-ordinated SCN is needed for the distributed transaction and maximum SCN value from all participating databases is chosen; SCN value of these three databases will be increased to 5000.

Can you run out of SCN?

As you saw earlier, maximum SCN hard limit is 281 trillion. 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-ordinated 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.DB1 generates 20K SCNs per second in the first 5 minutes, DB2 generates 20K SCNs per second in the next 5 minutes, DB3 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. But, there is that  infamous, hated by my client,  hot backup bug.

(BTW, 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)).

Here is an example of ORA-600 [2252] error. In this example lines printed below, 2838 is the SCN wrap and 395527372 is the SCN base. If we convert this to decimal SCN it is in the 12 Trillion range. Database link based connection was trying to increase the SCN over 12 Trillion value, but it was rejected by the database as the SCN was exceeding the soft limit.

 ORA-00600: internal error code, arguments: [2252], [2838], [395527372], [], [], [], [], [], [], [], [], []
BTW, 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.

Hot backup bug

Most DBAs use RMAN to do backup. But, still, there are few databases that use hot backup mode, primarily because of disk mirror based backups. It is a common behavior to see higher SCN rate if the database is altered to hot backup mode. A SGA variable array keeps track of the backup mode at file level. When you alter the database out of backup mode, SGA variables are reset and the higher SCN rate goes back to normal. Due to a bug (12371955), that SGA variable is not reset leaving the database to think that it is still in hot backup mode. Database generates SCN at higher rate. (if you recycle the database later, of course, the variable is reset to normal rate). There is way to dump the SGA variable to check if the database currently thinks if it is in hot backup mode or not.

Due to this bug, an highly active database can create increased SCN rate over 16K. Over a long period of time (in fact, it probably will take many years) the SCN catches up to the soft limit. Once soft limit is reached, next SCN update will throw ORA-660[2252] errors. Of course, this SCN growth is propagated to other databases over database link. As the soft limit calculation is time based, time zone of the server is also important. For example, if the values are close enough to soft limit, then the databases running in US Eastern time zone will have an higher soft limit by (4*60*60*16384 =235 million ) then the databases running in Pacific Time Zone.

Salient points of the bug are:
  1. There is no corruption danger, sessions might die or the databases might throw ORA-600 errors. In rare cases, databases have to be kept down for few hours or distributed transaction removed from the database so that the head room between the soft limit and the current SCN is widen.
  2. This bug affects only if you use ‘ALTER DATABASE’ command. If you use, ‘ALTER TABLESPACE’ command for backup, you are not affected by this bug.
  3. SCN rate is also directly relevant to activity. If the database has lower activity, SCN rate is also lower, even when the database is altered to backup mode with this bug.
There is a script released by Oracle that can tell you how close your database is to the soft limit,aka SCN headroom. So, first check if your database is having any SCN issue or not, that script is available as bug 13498243 and tells you how many days of SCN headroom you have.

How to check SCN rate?

There are multiple ways to check SCN rate in your database.
Method 1: smon_scn_time keeps track of the mapping between time and SCN at approximately 5 minutes granularity. That can be used to measure SCN rate, see code below. Although, this is easier to check, remember that there is no easy way to identify if the SCN increase is due to intrinsic activity in the database or is it due to an external database increasing the SCN by a distributed transaction activity. We will discuss this differentiation later.
with t1 as(
select time_dp , 24*60*60*(time_dp - lag(time_dp) over (order by time_dp)) timediff,
  scn - lag(scn) over(order by time_dp) scndiff
from smon_scn_time
select time_dp , timediff, scndiff,
       trunc(scndiff/timediff) rate_per_sec
from t1
order by 1
-------------------- ---------- ---------- ------------
19-JAN-2012 15:23:21        315       2931            9
19-JAN-2012 15:25:46        145        708            4
19-JAN-2012 15:28:00        134       1268            9
19-JAN-2012 15:30:48        168        597            3
19-JAN-2012 15:35:51        303       4148           13
19-JAN-2012 15:36:47         56        103            1
19-JAN-2012 15:42:14        327        671            2
Method 2:
v$log_history also can be used to check the SCN rate of the database. In this code below, you can see the SCN rate per second queried from v$log_history. Even if you are running in RAC, query against v$log_history is sufficient as it holds the archive logs from all threads. If there is a SCN spike, say from a remote database, then you will see a SCN spike in the output of this query below.
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col first_change# format 99999999999999999999
col next_change# format 99999999999999999999
select  thread#,  first_time, next_time, first_change# ,next_change#, sequence#,
   next_change#-first_change# diff, round ((next_change#-first_change#)/(next_time-first_time)/24/60/60) rt
from (
select thread#, first_time, first_change#,next_time,  next_change#, sequence#,dest_id from v$archived_log
where next_time > sysdate-30 and dest_id=1
order by next_time
order by  first_time, thread#

   THREAD# FIRST_TIME                   FIRST_CHANGE#          NEXT_CHANGE#  SEQUENCE#       DIFF         RT
---------- -------------------- --------------------- --------------------- ---------- ---------- ----------
         2 12-JAN-2012 16:10:30              25995867              26026647        308      30780          0
         1 17-JAN-2012 14:05:00              26026649              26028427        555       1778          1
         1 17-JAN-2012 14:05:00              26026649              26028427        555       1778          1
         2 17-JAN-2012 14:05:00              26026647              26028432        309       1785          1
         2 17-JAN-2012 14:05:00              26026647              26028432        309       1785          1
         1 17-JAN-2012 14:27:21              26028427            1073743815        556 1047715388     814076
         2 17-JAN-2012 14:48:48              26028157              26028230          1         73          3
         2 18-JAN-2012 14:22:23              26076103           10737418303          3 1.0711E+10    7448778
         1 18-JAN-2012 14:22:24              26076106           10737427850          5 1.0711E+10    1458319
         1 18-JAN-2012 16:24:49           10737427850           10737427884          6         34          2
         1 18-JAN-2012 16:25:03           10737427884           10737428252          7        368          1

In the output above,  there was a SCN jump by 10 Billion between 14:27 and 14:05. You can’t differentiate if that increase came from external systems or is it due to intrinsic activity easily. In this specific case, because this is an extreme SCN increase, and I would guess that it came from external systems. ( But usually this level of SCN increase will not happen in your production site and my example is to just explain the concept).

What happens in RAC?

In RAC, instance that receive the update from external system will increase the SCN of the database SCN to the new higher SCN. When other instances query for next SCN, immediately that SCN increase will be propagated to other instances too.

Can two threads get same SCN?

Obvious answer is No. Correct answer is yes. For example, redo records from two threads shows that they have exact same SCN and subSCN. This is not a problem or concern, as the buffer changes are protected by GCS layer code, and the row changes are protected by locking mechanism.
node 1:

REDO RECORD - Thread:1 RBA: 0x000010.0000007f.0114 LEN: 0x0138 VLD: 0x01
SCN: 0x0002.8000fb91 SUBSCN:  1 01/19/2012 09:14:27

node 2:
REDO RECORD - Thread:2 RBA: 0x000007.00000003.0010 LEN: 0x0068 VLD: 0x05
SCN: 0x0002.8000fb91 SUBSCN:  1 01/19/2012 09:14:27

Intrinsic vs Extrinsic SCN growth

There is a statistic that can also guide us to determine if the SCN increase is intrinsic or extrinsic or not. Statistics ‘calls to kcmgas’ gives an approximate number of calls to allocate SCNs. This statistics is an estimate only, not an absolute count of generated SCNs. We will understand this stats with a script and an helper function.

create or replace function get_my_statistics (l_stat_name varchar2)
return number as
l_value number;
select ses.value into l_value
from v$sesstat ses , v$statname stat
where stat.statistic#=ses.statistic# and
ses.sid=(select sid from v$mystat where rownum <=1) and = l_stat_name;
return l_value;
alter system switch logfile;
host sleep 5
create table rs.dropme (n1 number , n2 number);
col curscn format 99999999999999999999999
select dbms_flashback.get_system_change_number curscn , get_my_statistics('calls to kcmgas') kcmgas from dual;
for i in 1 .. 100000
insert into rs.dropme values(i, i);
end loop;
select dbms_flashback.get_system_change_number curscn , get_my_statistics('calls to kcmgas') kcmgas from dual;
alter system switch logfile;

Output of the above script is:
                  CURSCN     KCMGAS
------------------------ ----------
             10737522265          0
 PL/SQL procedure successfully completed.
                  CURSCN     KCMGAS
------------------------ ----------
             10737523122        826
From the output, we can see that 857 SCN differences vs 826 kcmgas calls form this session. There could be other background processes generating SCN which would explain this difference. Even at instance level, it doesn’t match exactly, but multiplying ‘kcmgas calls’ statistics by 1.1 gives you better estimate. This method can be used to identify if the SCN growth is intrinsic or extrinsic in a database. It can be also  used to identify the instance generating more SCNs in a RAC cluster or the database generating more SCNs in a complex interconnected environment.

SCN Vulnerability issue

I am not going to discuss details about this vulnerability issue at all. But, this vulnerability require access to production database. DBAs with security in mind, don’t allow production access that easily anyway. So, In my opinion, it is a problem that must be addressed, but you would need a malicious DBA with expert level knowledge to misuse this vulnerability. Follow Oracle support direction on this one as I usually stay away from talking about security vulnerability issues. Check here for details

How to check the hot backup state from SGA variables

In the past, many of you have asked me about details for checking the flag using SGA variables. Following is the method to do that.

SGA variable kcvblg is an array data type and keeps track of the status of hot backup at file level. Length of each array element is 8 bytes and so, by dumping the array for a length of (db_files*8), you can see backup status for each file.

oradebug setmypid
REM 1600 below is the length of kcvblg array. It is calculated as db_files*8 
REM In my test database, db_files set to 200.
oradebug dumpvar sga kcvblg 1600
oradebug tracefile_name

If you review the trace file, you will see following lines printed for kcvblg array. All elements of array is set to 0 indicating that hot backup is not on for those data files.

ub4 * kcvblg_ [698CF34, 698CF38) = 3B5A0724
Dump of memory from 0x3B5A0724 to 0x3B5A0D64
3B5A0720          00000000 00000000 00000000      [............]
3B5A0730 00000000 00000000 00000000 00000000  [................]
        Repeat 98 times
3B5A0D60 00000000                             [....]           

After altering the database in to backup mode, we dump the kcvblg array again.

alter database begin backup;
oradebug setmypid
REM 1600 below is the length of kcvblg array. It is calculated as db_files*8 
REM In my test database, db_files set to 200.
oradebug dumpvar sga kcvblg 1600
oradebug tracefile_name

From the new trace file, we can see that many array element value is set to 1 indicating that hot backup is Enabled for those data files. BTW, array kcvblg is fully allocated for the size of db_files*8. But, the flag is altered only if a datafile is assigned for that array slot. So, If I have 100 data files with db_files=200, then only 100 elements are altered to 1 or 0.

ub4 * kcvblg_ [698CF34, 698CF38) = 3B5A0724
Dump of memory from 0x3B5A0724 to 0x3B5A0D64
3B5A0720          00000001 00000001 00000001      [............]
3B5A0730 00000001 00000001 00000001 00000001  [................]
       Repeat 48 times
3B5A0A40 00000001 00000001 00000000 00000000  [................]
3B5A0A50 00000000 00000000 00000000 00000000  [................]
        Repeat 48 times
3B5A0D60 00000000                             [....]           

Using this method, you can identify if the hot backup is enabled at data file level or not. Essentially, alter database or alter tablespace commands, identify all affected datafiles and alter the kcvblg array element associated with the data file. Command ‘alter database end backup’ or ‘alter tablespace end backup’ command will reset the flag to 0. Specific Bug I discussed in this blog was that alter database command forgets to reset the flag causing increased SCN usage.


I have been holding on publishing this blog entry for many months now. Since this issue is in the public knowledge domain, I can share the knowledge without any repercussions. In a nutshell, understanding SCN generation and intrinsic details about it is important. Armed with scripts, you can review your environment.

Dump_last_log script is not printing properly in html format.

printing in line>

--  Script : dump_last_log.sql
-- This script will dump the last log file.
--   If the log file is big with enormous activity, this might take much resource.
--  Author : Riyaj Shamsudeen
--  No implied or explicit warranty !
set serveroutput on size 1000000
  v_sqltext varchar2(255);
 select 'alter system dump logfile '||chr(39)||member||chr(39)
 into v_sqltext
v$log  lg, v$logfile lgfile
where = and
lg.sequence# = (select sequence#-1 from v$log where status='CURRENT' and
                  thread#=(select thread# from v$instance )
and lg.thread#=(select thread# from v$instance)
and rownum <2
  dbms_output.put_line ('Executing :'||v_sqltext);
  execute immediate v_sqltext;

Oracle support has published a note describing this issue 1376995.1. You might want to refer to that note too.

update 1: Fixed formatting and typo.

update 2: Fixed to read “Essentially, multiply base by 4 billion and add wrap to get the SCN in number format”
update 3: updated a typo in a code fragment.
update 4: Adding a section about how to use SGA variable to check backup status (This was not in public domain when I posted it originally, but now it is and so, posting details here).
update 5: added MOS document id.

40 Responses to “SCN – What, why, and how?”

  1. […] SCN – What, why, and how? 17.047762 80.098187 Share thisFacebookPrintEmailLike this:LikeBe the first to like this post. […]

  2. johann said

    At “Does each change increment SCN?” you are commiting only once during “alter system switch logfile” and small SCN growth is caused by background operations and not your dml.

  3. BTW, if you would like to quickly identify maximum SCN in your database, you can use the following SQL statement.

    col current_scn format 99999999999999999999999
    col maxscn format  9999999999999999999999
       dbms_flashback.get_system_change_number current_scn,
        ((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +
        ((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
        (((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +
        (to_number(to_char(sysdate,'HH24'))*60*60) +
        (to_number(to_char(sysdate,'MI'))*60) +
        ) * (16*1024)) maxscn 
     from v$instance


  4. gramnirman said

    Sorry ,I got it working by changing DATE_TIME to STARTUP_TIME column of v$version.

  5. Denis said

    My opinion:
    scn – backup and recovery,
    scn – instance crashes,
    scn – RAC (more detail).

    Sorry, you post about scn is not interesting.

    • Hello Denis
      Thanks for stopping by.
      If I understand correctly, you want more details about SCN and how it is used in backup & recovery, instance crash, thread recovery, etc.
      Well, I can’t cover that much details in the blog entry, it would require couple of chapters in a book. Even then, audience who will be interested in that level of depth is very small.


  6. “Can two threads get same SCN?”

    Yes but Oracle differ them as well as RMAN.

  7. […] In the meantime i found another artice dealing with the problem here. […]

  8. stephen said

    Riyaj, have you applied/tested the PSU5 patch? Patched databases will reject dblinks that will elevate the scn too close to the scn softlimit. My question is if you’ve played with it in this use case. Will a patched database reject a formerly good dblink that has already been established/connected but then some time later becomes a dblink that has a dangerous level of scn headroom? Wondering if the patch just protects against bad dblinks at connection time or is it any ongoing check throughout life of link?

    • Hello Stephen
      Thank you for reading my blog.
      Nope, I haven’t had opportunity to test this.
      My understanding is that, at any time, there is a SCN increase from a database link, a sanity check is performed and exception raised if exceeding a threshold rate.


  9. Andrey said

    Hi Riyaj.

    “There is way to dump the SGA variable to check if the database currently thinks if it is in hot backup mode or not.”
    What exactly SGA variable ?
    Please, help

    • Hello Andrey
      A SGA variable array keeps track of the state at a file level. Meaning, each file has 8 bytes allocated in the SGA and the status of backup mode is indicated in that byte.


  10. Robert said

    Hello Riyaj,

    col current_scn format 99999999999999999999999
    col maxscn format 9999999999999999999999
    dbms_flashback.get_system_change_number current_scn,
    ((to_number(to_char(sysdate,’YYYY’))-1988)*12*31*24*60*60) +
    ((to_number(to_char(sysdate,’MM’))-1)*31*24*60*60) +
    (((to_number(to_char(sysdate,’DD’))-1))*24*60*60) +
    (to_number(to_char(sysdate,’HH24′))*60*60) +
    (to_number(to_char(sysdate,’MI’))*60) +
    ) * (16*1024)) maxscn
    from v$instance

    In your Query ‘maxscn’ ist calculated fix with 31 Days per month over timeperiod – is this the way oracle calculates the ‘scn softlimit’ ?
    We have calculated with the real count of Days per month over timeperiod – is this wrong?


    • Hello Robert
      Yes, that’s correct. That’s the infamous 31-day bug. Oracle code assumes 31 days while calculating number of seconds elapsed from 1-1-1989. This does not mean that Oracle Date calculation is incorrect, it is just an code optimization to calculate elapsed seconds quickly.


  11. […] Please read it from SCN~> What,why and How it funtions […]

  12. […] […]

  13. […] Permanent link to SCN – What, why, and how- […]

  14. udit said

    how many times scn generated during create command….thks in adv …

    • Hello Udit
      There is no specific number of SCNs generated for a DDL command such as create table, create index etc. It depends upon many variables, such as table definition, index definition, data etc.


  15. Hrishikesh said

    I want to alter below SCN parameters , but it’s not happening.Help is much appreciated
    _external_scn_logging_threshold_seconds= 600;
    _external_scn_rejection_delta_threshold_minutes= 10080;
    I used below alter command to set the parameters, but it’s not helping out
    SQL> alter system set “_external_scn_logging_threshold_seconds”= 600 comment=’Set threshold on 31/05/2013 – See SR#497386-326095900′ scope=spfile;

    System altered.

    SQL> alter system set “_external_scn_rejection_delta_threshold_minutes”= 10080 comment=’Set threshold on 31/05/2013 – See SR#497386-326095900′ scope=spfile;

    System altered.



  16. Good blog. SCN relation and usages with Database recovery and sync will enhance its depth.

  17. Shireesh Kumar Reddy said

    hi Riyaj,

    1)what happens in background when i issue a command called ‘alter system switch logfile’?
    2)explain me interm of which BG processes would get invoke?

  18. dario said

    hello Riyaj
    am jus beginning to learnt oracle can help sql statement how to flashback records deleted 3 days ago without specific SCN or timestamp noted down at the time of deleting please.

  19. […] In 9i there was also a means of mapping an SCN to a TIMESTAMP. This involves the system view SYS.SMON_SCN_TIME. The view no longer exists in 12c. One limitation is that it only holds 1440 rows (well it does at 9i but I can see 2711 rows in an 11GR1 database and 2500 in an 11GR2 one) and therefore cannot hold more than 5 days worth of history. There is a good blog entry explaining this at […]

  20. Surya Prakash said

    how to get SCN number for past 1 year. but i bon’t know the exact time when SCN has generated

  21. […] Permanent link to SCN – What, why, and how- […]

  22. […] SCN and Checkpoint (1431133.1) Automatic Checkpoint Tuning (270571.1) Checkpoint Tuning and Troubleshooting Guide (147468.1) Master Note: Overview of Database Checkpoints (1490838.1) SCN – What, why, and how? […]

  23. robert freeman said

    Note that scn_smon_time is available in (again if previous posts are correct)

  24. Abhishek Ranjan said

    How to check database instance that SCN Bump came from (Source). please share any script.

  25. […] OF SCN – Query “AS OF” a System Change Number to see committed […]

  26. Kenneth Ko said

    Hello, How can I solve this problem at recovery time when the RAC does not write the log to be read in the following order and the SCN and subSCN are the same?

  27. FireExit said

    You’ve said the v$LOG_HISTORY in the “code below” is being queried, but the “code below” is a join between v$LOG and v$ARCHIVED_LOG.

  28. ameen said

    how to solve this ora-600 [2252] ? I am clueless as the solution below is asking to set the system date equal or higher of the shutdown ? shutdown of what ? my system date of server and database matches:

    Fri Jul 5 12:27:12 MYT 2019


    ————————————————————————— —————————————————————————
    +08:00 05-JUL-19 PM +08:00

    Can someone help provide detail steps how to fix this ? FYI, I had restored the database for 2 times from a working backup but still gives an error …

    ORA-600 [2252] Occurs During Alter Database Open (Doc ID 253977.1) To BottomTo Bottom
    Modified:Dec 6, 2011Type:PROBLEMStatus:ARCHIVED


    After shutdown of the database you receive the following error during
    alter database open: ora-600 [2252]

    You did not restore the database before receiving this error.


    The system date is lower than the date of the shutdown.

    While opening the database, Oracle compares the given SCN value
    with the reasonable upper limit value calculated based on the
    system date. If Oracle detects the provided scn is too large,
    ORA-600[2252] would be raised.


    Set the system date equal or higher the date of the shutdown.

  29. chinaxing said

    1. what about scn propagate in data guard between primary and standby ?

    2. the SCN is global in an CDB (container database) ?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s