Understanding the SCN 7

For the DBAs who want to have a refreser on SCN (system change number), this article article is very nice and explained clearly written by Sandeep Makol. It started on where you ‘ll find info for SCN (controlfile and datafile headers) then goes to the backup and recovery scenarios where knowledge of this “magic number” is very useful.

Below are some useful scripts (with sample output) as well

-- get specific datafile
col name format a50
select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',name,checkpoint_change#
from v$datafile where lower(name) like '%&&datafile%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where lower(name) like '%&&datafile%';

SCN location	    NAME					       CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile	    SYSTEM checkpoint						  1151614
file header	    +DATA_1/ivrs/datafile/sysaux.258.652821943			  1151614
file header	    +DATA_1/ivrs/datafile/system.267.652821909			  1151614
file header	    +DATA_1/ivrs/datafile/system_02.dbf 			  1151614
file header	    +DATA_1/ivrs/datafile/undotbs1.257.652821933		  1151614
file header	    +DATA_1/ivrs/datafile/users.263.652821963			  1151614
file in controlfile +DATA_1/ivrs/datafile/sysaux.258.652821943			  1151614
file in controlfile +DATA_1/ivrs/datafile/system.267.652821909			  1151614
file in controlfile +DATA_1/ivrs/datafile/system_02.dbf 			  1151614
file in controlfile +DATA_1/ivrs/datafile/undotbs1.257.652821933		  1151614
file in controlfile +DATA_1/ivrs/datafile/users.263.652821963			  1151614

-- get distinct checkpoint_change#
select checkpoint_change#, 'SYSTEM checkpoint in controlfile' "SCN location"
from v$database
union
select distinct checkpoint_change#, 'file in controlfile'
from v$datafile
union
select distinct checkpoint_change#, 'file header'
from v$datafile_header;

CHECKPOINT_CHANGE# SCN location
------------------ --------------------------------
	   1151614 SYSTEM checkpoint in controlfile
	   1151614 file header
	   1151614 file in controlfile

-- get distinct datafile count
select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',to_char(count(*)),checkpoint_change#
from v$datafile
group by checkpoint_change#
union
select 'file header',to_char(count(*)),checkpoint_change#
from v$datafile_header
group by checkpoint_change#;

SCN location	    NAME					       CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile	    SYSTEM checkpoint						  1151614
file header	    5								  1151614
file in controlfile 5								  1151614

-- info from x$kcvfh (All file headers)
SELECT hxfil file_num,substr(hxfnm,1,40) file_name,fhtyp type,hxerr validity, fhscn chk_ch#, fhtnm tablespace_name,fhsta status,fhrba_seq sequence
FROM x$kcvfh;

  FILE_NUM FILE_NAME					  TYPE	 VALIDITY CHK_CH#	   TABLESPACE_NAME		      STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- ------------------------------ ---------- ----------
	 1 +DATA_1/ivrs/datafile/system.267.6528219	     3		0 1151614	   SYSTEM				8196	    146
	 2 +DATA_1/ivrs/datafile/undotbs1.257.65282	     3		0 1151614	   UNDOTBS1				   4	    146
	 3 +DATA_1/ivrs/datafile/sysaux.258.6528219	     3		0 1151614	   SYSAUX				   4	    146
	 4 +DATA_1/ivrs/datafile/users.263.65282196	     3		0 1151614	   USERS				   4	    146
	 5 +DATA_1/ivrs/datafile/system_02.dbf		     3		0 1151614	   SYSTEM				   4	    146

.

7 comments

  1. Really interesting topics..

    I have one question concerning redolog sequence, what would it mean if I have a gap in between consecutive sequence number.

    THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
    ———- ———- ————- ————
    2 118 5966403 5966562
    2 119 5966562 5966577
    2 120 5966577 5970293
    2 121 5970293 5970310
    2 122 5977084 5977094

    There seems to be a gap between sequence 121 and 122. I have noticed that after a dataguard switchover on a RAC configuration.

    Do I have to consider this as normal or this is normal after a switchover ?

    Thanks for any inputs.

    • Hmm.. interesting, how many nodes you have on the physical standby?
      I will check on some of my notes regarding switchover on RAC standby and I’ll get back to you.

  2. I have 2 nodes on the primary site and 2 nodes on the standby site.

    I can reproduce this behaviour, i checked alert.log and differents v$ view, and even a SET log_archive_trace=8191, but i don’t see why i have this type of gap.

    I noticed this since I have a backup script which chech possible gap in archivelog.. otherwise just checking sequence#, I would have not noticed it.

    Except that when I tried RMAN recovery, I have a weird behaviour.

    Thanks for your help.

    • What was the weird behavior that you encountered on recovery?

      Yes, you are right.. I’m more on checking the gap for the sequence# too and actually checking the data when doing the switchover.
      BTW, I’m interested to see you backup script. Can you mail it at karlarao@gmail.com ?

      Thanks!

  3. By the way, I loggued an SR since I found this really weird.. I guess most people won’t check the gap at the SCN level, thet rather check the gap at the sequence number.

  4. Here is the SR answer :

    This is not a bug – development analyzed this under

    Bug 9027206: NEXT_CHANGE# DOES NOT MATCH FIRST_CHANGE# IN RAC

    Status92 – Closed, Not a Bug

    When a thread get disabled, it is possible to have a gap between the
    next_change# and the following first_change# because the thread is known as
    inactive/disabled and don’t need to record any scn history during that time.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s