Several common SCN in Oracle database

SCN in the control file

SCN for data file header

SCN in a data block

SCN in the log file header

Transaction SCN

In-memory SCN

The SCN in a control file

1.1 Database SCN

Database SCN represents the SCN for the most recent full-volume checkpoint operation

Sql> select checkpoint_change# from V$database;

checkpoint_change#
------------------
1744125

Dump control File Syntax

Alter session SET Events ' immediate trace name Controlf level n ';

1 File header information

2 level 1+ Database information + checkpoint information

3 level the Reusable section information

Level 3

The dump control file gets the database SCN to 0X00000000001A9CFD, which is converted to decimal 1744125

2017-02-15t10:59:12.367312+08:00
DUMP of CONTROL FILES, Seq # 1522 = 0x5f2
V10 STYLE FILE HEADER:
Compatibility Vsn = 203423744=0xc200000
DB ID=1463703229=0X573E56BD, DB name= ' ORCL '
Activation id=0=0x0
Control Seq=1522=0x5f2, File size=646=0x286
File number=0, blksiz=16384, file type=1 CONTROL




***************************************************************************
DATABASE ENTRY
***************************************************************************
(size = A, compat size = A, section max = 1, section in-use = 1,
last-recid= 0, Old-recno = 0, last-recno = 0)
(extent = 1, Blkno = 1, numrecs = 1)
02/14/2017 10:28:13
DB Name "ORCL"
Database flags = 0x00404000 0x00001000 0x00000080
Controlfile Creation Timestamp 02/14/2017 10:28:13
INCMPLT Recovery scn:0x0000000000000000
resetlogs scn:0x0000000000157e2e resetlogs Timestamp 02/14/2017 10:28:15
Prior resetlogs scn:0x0000000000000001 Prior resetlogs Timestamp 01/26/2017 13:52:29
Redo version:compatible=0xc200000
#Data files = 4, #Online files = 4
Database checkpoint:thread=1 scn:0x00000000001a9cfd
Threads: #Enabled =1, #Open =1, head=1, tail=1

1.2 Data file SCN

Querying data files stored in the control file SCN through V$datafile

• Data File Header SCN

v$datafile.checkpoint_change#

Sql> select checkpoint_change# from V$datafile;

checkpoint_change#
------------------
1744125
1744125
1744125
1744125

• STOP SCN

The database opens or closes unexpectedly, the value is empty or infinity 0XFFFFFFFFFFFFFFFF

Sql> select last_change# from V$datafile;

last_change#
------------

Sql>

• CREATION SCN

SCN when data file is created

Sql> select creation_change# from V$datafile;

creation_change#
----------------
7
4665
1406609
29999

Gets the SCN for the data file from the dump's control file. The SCN in the control file for data file number 1th

***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = +, section In-use = 7,
last-recid=, Old-recno = 0, last-recno = 0)
(extent = 1, Blkno = one, numrecs = 100)
DATA FILE #1:
Name #6:/oradata/orcl/datafile/o1_mf_system_db4tp1o9_.dbf
Creation size=0 block size=8192 status=0xe flg=0x1 head=6 tail=6 dup=1
pdb_id 0, Tablespace 0, index=2 krfil=1 prev_file_in_ts=0 prev_file_in_pdb=0
Unrecoverable scn:0x0000000000000000 01/01/1988 00:00:00
Checkpoint cnt:61 scn:0x00000000001a9cfd 02/15/2017 10:20:16
Stop scn:0xffffffffffffffff 02/15/2017 10:17:27
Creation checkpointed at scn:0x0000000000000007 01/26/2017 13:52:40
thread:0 RBA: (0x0.0.0)

1.3 CHECKPOINT PROGRESS Records in the SCN

The on disk SCN in the CHECKPOINT PROGRESS records represents the latest RBA corresponding SCN for the current system, with the CKPT process updated every 3 seconds.

If the database is abnormally down, the server process must be applied to the SCN at least until crash recovery

The dump control file can then obtain the SCN in the Checkpoint PROGRESS records as follows

***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
(size = 8180, compat size = 8180, section max = one, section in-use = 0,
last-recid= 0, Old-recno = 0, last-recno = 0)
(extent = 1, Blkno = 2, numrecs = 11)
THREAD #1-status:0x2 flags:0x0 dirty:6
Low cache RBA: (0x4.853.0) on disk RBA: (0x4.86b.0)
On disk Scn:0x00000000001aa3f5 02/15/2017 10:58:28
Resetlogs scn:0x0000000000157e2e 02/14/2017 10:28:15
heartbeat:936031722 Mount id:1463768667

SCN in the header of two data files

V$datafile_header

• creation_change#

SCN when data file is created

Sql> select file#,creation_change# from V$datafile_header;

file# creation_change#
---------- ----------------
1 7
3 4665
4 1406609
7 29999

• checkpoint_change#

Represents the current SCN for the data file header

Sql> select file#,checkpoint_change# from V$datafile_header;

file# checkpoint_change#
---------- ------------------
1 1744125
3 1744125
4 1744125
7 1744125

• resetlogs_change#

The SCN that indicates that the database opens in Resetlogs mode can also be seen in the dump control file resetlogs scn:0x0000000000157e2e

Sql> select file#,resetlogs_change# from V$datafile_header;

file# resetlogs_change#
---------- -----------------
1 1408558
3 1408558
4 1408558
7 1408558

• change#

Represents the SCN when a data file freezes. When doing a data file online hot standby, the common begin backup command freezes the data file header, indicating that the chang# point begins to back up the data file

Sql> select file#,change# from V$backup;

file# change#
---------- ----------
1 0
3 0
4 0
7 0

sql> ALTER DATABASE begin backup;

Database altered.

Sql> select file#,change# from V$backup;

file# change#
---------- ----------
1 1957900
3 1957900
4 1957900
7 1957900

The above SCN can be observed by the dump data file header 0x00000000001de00c conversion to decimal is 1957900

Sql> Oradebug Setmypid
Statement processed.
Sql> Oradebug Tracefile_name
/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_24591.trc
Sql> alter session SET events ' immediate trace name File_hdrs Level 3 ';

Session altered.

V10 STYLE FILE HEADER:
Compatibility Vsn = 203423744=0xc200000
DB ID=1463703229=0X573E56BD, DB name= ' ORCL '
Activation id=0=0x0
Control seq=1740=0x6cc, File size=102400=0x19000
File Number=1, blksiz=8192, file type=3 DATA
Tablespace #0-system Rel_fn:1
Creation at scn:0x0000000000000007 01/26/2017 13:52:40
Backup taken at scn:0x00000000001de00c 02/15/2017 13:56:00 thread:1
Reset Logs count:0x37c90b3f scn:0x0000000000157e2e
Prev Reset Logs count:0x37b02e9d scn:0x0000000000000001
Recovered at 02/15/2017 13:53:24
status:0x2001 root dba:0x00400208 chkpt cnt:70 ctl cnt:69
Begin-hot-backup file size:102400
checkpointed at scn:0x00000000001de00c 02/15/2017 13:56:00

SCN in three data blocks

• SCN when data block changes

Many operations can cause changes in data blocks, such as changes in business data, block cleanup, and so on.

Sql> Oradebug Setmypid
Statement processed.
Sql> Oradebug Tracefile_name
/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_29430.trc
sql> alter system dump DATAFILE 1 block;

System altered.

Sql>!MORE/ORACLE/DIAG/RDBMS/ORCL/ORCL/TRACE/ORCL_ORA_29430.TRC

Start dump data blocks tsn:0 file#:1 minblk maxblk 16
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=0 rdba=4194320
Block dump from disk:
Buffer tsn:0 rdba:0x00400010 (1/16)
scn:0xa seq:0x01 flg:0x04 tail:0x000a1e01
frmt:0x02 chkval:0x80ab type:0x1e=ktfb bitmapped File Space Bitmap
Hex dump of Block:st=0, typ_found=1

......

• SCN in a data block transaction slot

• SCN for data rows in a data block

You can see the SCN when data rows change by pseudo-column ORA_ROWSCN

Sql> select Id,ora_rowscn from AA;

ID ORA_ROWSCN
---------- ----------
1 1960725
2 1960725
3 1960725

4th SCN in the log file header

• fisrt_change#

Indicates the SCN when the online days file is reused

• next_change#

Indicates the SCN at the end of the log file reuse

• resetlogs_change#

Represents the SCN when the database has been resetlogs open. Usually the same as the resetlogs_change# of the data file header.

Sql> Select first_change#,next_change#,resetlogs_change# from v$log_history where sequence#=5;

first_change# next_change# resetlogs_change#
------------- ------------ -----------------
1856486 1956862 1408558

Dump log file header gets the above SCN,

0x00000000001c53e6 decimal 1856486,0x00000000001ddbfe decimal 1956862,0x0000000000157e2e decimal 1408558

Sql> alter session SET events ' immediate trace name REDOHDR Level 3 ';

Session altered.

LOG FILE #2:
Name #2:/oradata/orcl/onlinelog/o1_mf_2_db4tt00w_.log
Thread 1 redo Log Links:forward:3 backward:1
siz:0x64000 seq:0x00000005 hws:0x6 bsz:512 nab:0x478 flg:0x1 dup:1
Archive links:fwrd:0 back:0 Prev SCN:0X00000000001A9CFC
Low Scn:0x00000000001c53e6 02/15/2017 13:52:08
Next Scn:0x00000000001ddbfe 02/15/2017 13:53:25
FILE HEADER:
Compatibility Vsn = 203423744=0xc200000
DB ID=1463703229=0X573E56BD, DB name= ' ORCL '
Activation ID=1463733693=0X573ECDBD
Control Seq=1735=0x6c7, File size=409600=0x64000
File number=2, blksiz=512, file type=2 LOG
Format ID is 18
Redo Log key is 30c0dccb5d4341b7f1be4b600b3604e7
Redo Log Key flag is 5
Descrip: "T 0001, S 0000000005, SCN 0x00000000001c53e6-0x00000000001ddbfe"
Thread:1 nab:0x478 seq:0x00000005 hws:0x6 eot:0 dis:0
Reset Logs count:0x37c90b3f
Reset Logs scn:0x0000000000157e2e
Low scn:0x00000000001c53e602/15/2017 13:52:08
Next Scn:0x00000000001ddbfe02/15/2017 13:53:25
Enabled scn:0x0000000000157e2e 02/14/2017 10:28:15
Thread closed SCN:0X00000000001DDBFC 02/15/2017 13:52:30
Disk cksum:0x6c0a Calc cksum:0x0
Terminal Recovery Stop scn:0x0000000000000000
Terminal Recovery Stamp 01/01/1988 00:00:00
Most recent redo scn:0x0000000000000000
Largest lwn:0 blocks
Real Next scn:0x00000000001c555c
Previous Resetlogs scn:0x0000000000000001
Miscellaneous flags:0x800000
Miscellanous Second flags:0x0
Thread internal enable indicator:thr:0, seq:0 scn:0x0000000000000000
Zero Blocks:8
Enabled Redo Threads:1

SCN at the start of five transactions

sql> Update AA set id=10 where rownum=1;

1 row updated.

Select Xidusn, START_SCNB, START_SCNW
  from v$transaction
WHERE ses_addr = (SELECT saddr
   ;                   From V$session
                     WHERE sid = (SELECT sid
                                     from V$mystat
  7                                      WHERE ROWNUM = 1);

Xidusn START_SCNB START_SCNW
---------- ---------- ----------
1 1960725 0

Sql> select name from V$rollname where usn=10;

NAME
------------------------------
_syssmu10_2925533193$

Sql> alter system dump Undo header ' _syssmu10_2925533193$ ';

System altered.

Current SCN for six databases

sql> oradebug Dumpvar SGA KCSGSCN
KCSLF Kcsgscn_ [0600113b8, 0600113E8) = 001df7c8 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0 0000000 60049740 00000000

Sql> select Current_scn from V$database;

Current_scn
-----------
1963968

Sql> select Dbms_flashback.get_system_change_number from dual;

Get_system_change_number
------------------------
1964014

Several common SCN in Oracle database