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:
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;
TO_CHAR(DBMS_FLASHBACK. CURSCN
———————– ————————
280000371 10737419121
Here, hex value of the SCN is 0×280000371 and decimal format is 10737419121. Let’s review the hex value 0×280000371, this value can be split in to two components, better written as 0×2.80000371, where 0×2 is the wrap and 0×80000371 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 dualN2 ------------------- 10737419121
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);test_case_scn.sql:--------------cut --------------col curscn format 99999999999999999999999select dbms_flashback.get_system_change_number curscn from dual;begin for i in 1 .. 1000 loop insert into rs.dropme values(i, i); end loop;end;/select dbms_flashback.get_system_change_number curscn from dual;------------cut -----------------alter system switch log file;SQL> @test_case_scn CURSCN------------------------10737428262PL/SQL procedure successfully completed.CURSCN------------------------10737428271SQL> alter system switch logfile;System altered.
REDO RECORD - Thread:1 RBA: 0x000010.0000001c.018c LEN: 0x00fc VLD: 0x01SCN: 0x0002.8000fb87 SUBSCN: 1 01/19/2012 09:14:27REDO RECORD - Thread:1 RBA: 0x000010.0000001d.0098 LEN: 0x00fc VLD: 0x01SCN: 0x0002.8000fb87 SUBSCN: 1 01/19/2012 09:14:27REDO RECORD - Thread:1 RBA: 0x000010.0000001d.0194 LEN: 0x00fc VLD: 0x01SCN: 0x0002.8000fb87 SUBSCN: 1 01/19/2012 09:14:27REDO RECORD - Thread:1 RBA: 0x000010.0000001e.00a0 LEN: 0x00fc VLD: 0x01SCN: 0x0002.8000fb87 SUBSCN: 1 01/19/2012 09:14:27
...
Database link and SCNS
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], [], [], [], [], [], [], [], [], []
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.
How to check SCN rate?
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) scndifffrom smon_scn_time)select time_dp , timediff, scndiff, trunc(scndiff/timediff) rate_per_secfrom t1order by 1/TIME_DP TIMEDIFF SCNDIFF RATE_PER_SEC-------------------- ---------- ---------- ------------19-JAN-2012 15:23:21 315 2931 919-JAN-2012 15:25:46 145 708 419-JAN-2012 15:28:00 134 1268 919-JAN-2012 15:30:48 168 597 319-JAN-2012 15:35:51 303 4148 1319-JAN-2012 15:36:47 56 103 119-JAN-2012 15:42:14 327 671 2
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';col first_change# format 99999999999999999999col next_change# format 99999999999999999999select 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) rtfrom (select thread#, first_time, first_change#,next_time, next_change#, sequence#,dest_id from v$archived_logwhere next_time > sysdate-30 and dest_id=1order 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?
Can two threads get same SCN?
node 1:REDO RECORD - Thread:1 RBA: 0x000010.0000007f.0114 LEN: 0x0138 VLD: 0x01SCN: 0x0002.8000fb91 SUBSCN: 1 01/19/2012 09:14:27node 2:REDO RECORD - Thread:2 RBA: 0x000007.00000003.0010 LEN: 0x0068 VLD: 0x05SCN: 0x0002.8000fb91 SUBSCN: 1 01/19/2012 09:14:27
Intrinsic vs Extrinsic SCN growth
create or replace function get_my_statistics (l_stat_name varchar2)
return number as
l_value number;
begin
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 stat.name = l_stat_name;
return l_value;
end;
/
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;
begin
for i in 1 .. 100000
loop
insert into rs.dropme values(i, i);
end loop;
end;
/
select dbms_flashback.get_system_change_number curscn , get_my_statistics('calls to kcmgas') kcmgas from dual;
alter system switch logfile;
CURSCN KCMGAS------------------------ ---------- 10737522265 0 PL/SQL procedure successfully completed. CURSCN KCMGAS------------------------ ---------- 10737523122 826
SCN Vulnerability issue
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 setmypidREM 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 1600oradebug 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) = 3B5A0724Dump of memory from 0x3B5A0724 to 0x3B5A0D643B5A0720 00000000 00000000 00000000 [............]3B5A0730 00000000 00000000 00000000 00000000 [................] Repeat 98 times3B5A0D60 00000000 [....]
After altering the database in to backup mode, we dump the kcvblg array again.
alter database begin backup;oradebug setmypidREM 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 1600oradebug 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) = 3B5A0724Dump of memory from 0x3B5A0724 to 0x3B5A0D643B5A0720 00000001 00000001 00000001 [............]3B5A0730 00000001 00000001 00000001 00000001 [................] Repeat 48 times3B5A0A40 00000001 00000001 00000000 00000000 [................]3B5A0A50 00000000 00000000 00000000 00000000 [................] Repeat 48 times3B5A0D60 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.
Summary
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 1000000declare v_sqltext varchar2(255);begin select 'alter system dump logfile '||chr(39)||member||chr(39) into v_sqltext fromv$log lg, v$logfile lgfilewhere lg.group# = lgfile.group# andlg.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;end;/
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).
联系客服
SCN – What, why, and how? ? Sriram Sanka said
[...] SCN – What, why, and how? 17.047762 80.098187 Share thisFacebookPrintEmailLike this:LikeBe the first to like this post. [...]
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.
Riyaj Shamsudeen said
BTW, if you would like to quickly identify maximum SCN in your database, you can use the following SQL statement.
Cheers
Riyaj
gramnirman said
I get error running this script to get max scn
ERROR at line 3:
ORA-00904: “DATE_TIME”: invalid identifier
gramnirman said
Sorry ,I got it working by changing DATE_TIME to STARTUP_TIME column of v$version.
Riyaj Shamsudeen said
Thank you. I made a mistake while copying code.
Denis said
My opinion:
scn – backup and recovery,
scn – instance crashes,
scn – RAC (more detail).
Sorry, you post about scn is not interesting.
Riyaj Shamsudeen said
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.
Cheers
Riyaj
Damir Vadas said
“Can two threads get same SCN?”
Yes but Oracle differ them as well as RMAN.
Riyaj Shamsudeen said
Hello Damir
Thanks for reading my blog. I am not exactly sure what you meant by that. Would you please clarify?
Cheers
Riyaj
Ronny Egners Blog ? The NEXT major outage could be caused by a fundamental Oracle design problem – the SCN said
[...] In the meantime i found another artice dealing with the problem here. [...]
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?
Riyaj Shamsudeen said
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.
Cheers
Riyaj
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
Riyaj Shamsudeen said
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.
Cheers
Riyaj
Robert said
Hello Riyaj,
col current_scn format 99999999999999999999999
col maxscn format 9999999999999999999999
select
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) +
(to_number(to_char(sysdate,’SS’)))
) * (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?
VG
Robert
Riyaj Shamsudeen said
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.
Cheers
Riyaj