Understanding Oracle System Change Numbers and Their Role in Database Recovery

System Change Number (SCN) Fundamentals

The SCN (System Change Number) serves as Oracle's internal timestamp mechanism for tracking data base modifications. It represents a logical ordering point at which database changes occur, enabling the database to query and track modifications with precision.

Each transaction within Oracle follows a sequential SCN-based ordering. SCN values remain unique across the database and increment monotonically over time. When a row is modified, Oracle records the SCN at that precise moment, creating an immutable audit trail of database activity.

SCN to Timestamp Mapping

Oracle maintains an internal mapping between SCN values and system timestamps in the sys.smon_scn_time table. This relationship enables conversion between SCN numbers and wall-clock time.

Query the most recent mappings:

SELECT time_mp, time_dp, scn 
FROM sys.smon_scn_time t 
WHERE rownum < 10 
ORDER BY scn DESC;

Sample output:

        TIME_MP TIME_DP                           SCN
  - -------------- ------------------- -----------------
     1564632734 2019-08-01 04:12:14    16010028042706
     1564456246 2019-07-30 03:10:46    16010026847230

Oracle's maximmum theoretical SCN ceiling is 2^48, providing an effectively inexhaustible sequence for database operations. If this maximum were ever reached, the database would become inaccessible.

Retrieve the current SCN:

SELECT dbms_flashback.get_system_change_number FROM dual;

SELECT current_scn FROM v$database;

SCN in Recovery Operations

Redo Log SCN Boundaries

Each redo log file contains FIRST_CHANGE# and NEXT_CHANGE# columns defining the transaction range within that log member. When v$log.status shows CURRENT, the range spans from FIRST_CHANGE# to the database's current SCN.

SELECT group#, status, first_change#, next_change# FROM v$log;

         GROUP# STATUS             FIRST_CHANGE#    NEXT_CHANGE#
--------------- ---------------- --------------- ---------------
              1 CURRENT           16010028041386 281474976710655
              2 INACTIVE          16010028041287  16010028041386
              3 INACTIVE          16010028041190  16010028041287

During instance recovery after a crash, Oracle recovers data between the FIRST_CHANGE# of the current log group and v$database.current_scn.

Reading SCN from SGA Memory

Access the current SCN directly from the SGA using oradebug:

SQL> oradebug setmypid
Statement processed.
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = A0090B26 00000E8F 00000000 ...

Convert the hex representation to decimal:

SELECT TO_CHAR('16010028059450', 'xxxxxxxxxxxx') FROM dual;

TO_CHAR('1601
-------------
  e8fa0090b3a

Decimal-Hexadecimal Conversion Utilities

-- Decimal to hexadecimal
SELECT TO_CHAR('12', 'xx') FROM dual;

-- Hexadecimal to decimal
SELECT TO_NUMBER('7D', 'xxxx') FROM dual;

SCN Storage Locations

Oracle records SCN values across multiple critical structures:

Component	SCN Role
SGA	current_scn (memory)
Control File	Database checkpoint SCN
Data Files	Per-file checkpoint SCN
Redo Logs	Low/High SCN boundaries

SCN-Timestamp-Date Conversions

Convert SCN to timestamp:

SELECT file#, scn_to_timestamp(checkpoint_change#) 
FROM v$datafile_header;

          FILE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
--------------- ----------------------------------------
              1 01-AUG-19 03.00.59.000000000 PM
              2 01-AUG-19 03.00.59.000000000 PM

Reverse conversion (timestamp to SCN):

SELECT timestamp_to_scn(scn_to_timestamp(checkpoint_change#)) 
FROM v$datafile_header;

TIMESTAMP_TO_SCN(SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#))
------------------------------------------------------
                                        16010028064443

Format as date string:

SELECT TO_CHAR(scn_to_timestamp(checkpoint_change#), 'yyyy-mm-dd hh24:mi:ss') 
FROM v$datafile_header;

TO_CHAR((SCN_TO_TIM
-------------------
2019-08-01 15:00:59

SCN Internal Structure

SCN consists of 48 bits organized as:

SCN Wrap: 2 high-order bytes
SCN Base: 4 low-order bytes

SCN in Control Files

Dump control file contents to inspect stored SCN values:

ORADEBUG DUMP controlf 2
-- or
ALTER SESSION SET events 'immediate trace name controlf level 2';
SELECT * FROM v$diag_info;

Key control file SCN entries:

DATABASE ENTRY
--------------------------------------------------------------------------
 Controlfile Creation Timestamp  05/17/2018 10:53:38
 Resetlogs scn: 0x0000.000e2006 Resetlogs Timestamp  05/17/2018 10:53:38
 Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp  08/24/2013 11:37:30
 Database checkpoint: Thread=1 scn: 0x0e8f.a0095d7e

SCN in Data File Headers

Inspect data file header SCN information:

ORADEBUG DUMP file_hdrs 10
-- or
ALTER SESSION SET events 'immediate trace name file_hdrs level 10';

Data file header SCN fields:

DATA FILE #1:
  name #7: /app/oracle/oradata/HSTEST/system01.dbf
 Checkpoint cnt:11774 scn: 0x0e8f.a0095d7e 08/01/2019 17:01:33
 Stop scn: 0xffff.ffffffff 07/26/2019 22:40:13
 Creation Checkpointed at scn:  0x0000.00000007 08/24/2013 11:37:33

SCN in Redo Log Files

Dump redo log headers to examine SCN boundaries:

ALTER SESSION SET events 'immediate trace name redohdr level 2';

Redo log SCN structure:

LOG FILE #1:
  name #3: /app/oracle/oradata/HSTEST/redo01.log
 siz: 0x19000 seq: 0x00002df7 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0e8f.a0095d2b
 Low scn: 0x0e8f.a0095d7e 08/01/2019 17:01:33
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

SCN Consistency Analysis

Examining SCN values across database structures reveals alignment:

SELECT group#, 
       TO_CHAR(first_change#, 'xxxxxxxxxxxx') AS start_scn,
       TO_CHAR(next_change#, 'xxxxxxxxxxxx') AS end_scn, 
       status 
FROM v$log;

         GROUP# TO_CHAR(FIRST TO_CHAR(NEXT_ STATUS
--------------- ------------- ------------- ----------------
              1   e8fa0095d7e  ffffffffffff CURRENT
              2   e8fa0095cfd   e8fa0095d2b INACTIVE
              3   e8fa0095d2b   e8fa0095d7e INACTIVE

Start SCN vs End SCN for Recovery

Data file Start SCN (stored in data file headers):

Retrieved via v$datafile_header.checkpoint_change#
Determines whether media recovery is required at startup

Control file End SCN (stored in control file):

Retrieved via v$datafile.last_change#
Determines whether instance recovery is required at startup

If start SCN equals end SCN, the database opens without recovery. The checkpoint SCN indicates that all modification before that point have been written to disk. Following an instance crash, Oracle performs recovery by applying redo from groups logged after that checkpoint, then rolling back uncommitted transactions.

When the CURRENT log group shows FIRST_CHANGE# matching the control file's database checkpoint SCN, it confirms that preceding transaction data resides in archived or inactive log groups, with the current group capturing new modifications.

Tags: Oracle scn database-recovery Checkpoint redo-log

Posted on Tue, 12 May 2026 23:03:21 +0000 by jumpfroggy

Freaks City