SCN and Checkpoint in Oracle

SCN and Checkpoint in Oracle

What is SCN and why it is used? What happens if the SCN in datafile header is not matching with the Control file?

The system change number (SCN) is Oracle's clock, every time we commit the clock increments. The SCN just marks a consistent point in time in the database. The checkpoint SCN in datafile headers are updated after checkpoint. The SCN is incremented whenever a transaction commits. Suppose i do update in one table which is stored in two different datafiles it will update all datafiles header & write information in control file after commit. Before opening the database SMON will check the control file & datafile headers for the same SCN. If the SCN in datafile header is not matching with the Control file that means datafile need recovery.

How to find Current SCN?

SQL> select dbms_flashback.get_system_change_number from dual; VER. Oracle 9i

SQL> select to_char(current_scn) from v$database;                              VER. Oracle 10g

What is checkpoint? Is checkpoint is related to SCN? Why checkpoint numbers get increased non sequence to higher value?

LGWR or CKPT writes the redo log sequence to the datafile headers and control files and tells the DBWR to write dirty buffers from the dirty buffer write queue (buffer cache) to disk. It is a record indicating the point in the redo log where all DB changes prior to this point have been saved in the datafiles.

The database ALWAYS has transactions going on, ALWAYS.  SMON and many other background processes are always doing work, the database (unless it is opened read only) is always doing transactions. Now, since the database never sleeps. Most of those other “programs” do transactions and commit. SQL>select username, program from v$session;

The justification against the question, is SCN number, is it a number to identify a committed transaction? or is it a number just to identify the sequence of statements executed against the database ?

SQL> create table s ( x int );

Table created.

SQL> Select dbms_flashback.get_system_change_number scn from dual;

SCN

----------

79178265

SQL> begin
            for i in 1 .. 1000

            Loop
                 insert into s values ( i );
            end loop;

    end;
    /

PL/SQL procedure successfully completed.

SQL> select dbms_flashback.get_system_change_number - &SCN from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER-79178265

------------------------------------------------

5

It only advanced by 5 - but we did over 1,000 DML statements thus the SCN is not assigned to a SQL statement. The SCN is incremented upon commit.

SQL>

SQL> select dbms_flashback.get_system_change_number scn from dual;

SCN

----------

79178271

SQL> begin

            for i in 1 .. 1000

            loop

               insert into s values ( i );

            COMMIT;

            end loop;

    end;

PL/SQL procedure successfully completed.

SQL> select dbms_flashback.get_system_change_number - &SCN from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER-79178271

------------------------------------------------

1016     

See, now if you COMMIT 1,000 times, the SCN does jump by 1,000 (the other jumps are the background processes, they are always doing stuff - SMON, MMON, PMON, etc. they do SQL all of the time - the database never rests

Is there a limitation on the number of SCN that can be generated in a second?

It is depends upon number of commit you are doing

How can we check precision of SCN Timing?

SQL> select time_mp,time_dp,  scn_wrp, scn_bas from smon_scn_time;

It is internally done if you look at that table all of the columns - there is a field TIM_SCN_MAP, it is hidden in there, by using the APIs you can access that information.

SQL>select scn_to_timestamp(scn) ts, min(scn), max(scn)

        from (

           select dbms_flashback.get_system_change_number()-level scn

        from dual

         connect by level <= 100

         )

    Group by scn_to_timestamp(SCN);

    Order by scn_to_timestamp(SCN);

What if the transaction is rolled-back? Does the SCN again increase?

Yes it is, check out this Example

SQL> CREATE TABLE S1 (ENO NUMBER(4), ENAME VARCHAR2(20));

Table created.

SQL> Select dbms_flashback.get_system_change_number scn from dual;

SCN

----------

8806085

SQL> begin

   for i in 1 .. 1000

   loop

   insert into S1 values ( 1, 'SHAAN' );

   rollback;

   end loop;

   end;

   /

PL/SQL procedure successfully completed.

SQL> select scn, scn-8806085 from (

       select dbms_flashback.get_system_change_number scn from dual

      );

SCN SCN-8806085

---------- -------------

8806085    2014

SQL> Select dbms_flashback.get_system_change_number scn from dual;

SCN

----------

8806085

SQL> begin

   for i in 1 .. 10000

   loop

   insert into S1 values ( 1, 'SHAAN' );

   rollback;

   end loop;

   end;

   /

PL/SQL procedure successfully completed.

SQL> select scn, scn-8806085 from (

 select dbms_flashback.get_system_change_number scn from dual

  );

SCN SCN-8806085

---------- -------------

155317184  20180

Even more than if you do not rollback but commit instead

SQL> Create table S2 ( eno number(4));

Table created.

SQL> select dbms_flashback.get_system_change_number scn from dual;

SCN

----------

8828432

SQL> begin

   for i in 1 .. 1000

   loop

     insert into s2 values ( i );

     commit;

   end loop;

  end;

  /

PL/SQL procedure successfully completed.

SQL> select scn, scn-8828432 from (

  select dbms_flashback.get_system_change_number scn from dual

  );

SCN SCN-8828432

---------- -------------

8830391    1959

SQL> select dbms_flashback.get_system_change_number scn from dual;

SCN

----------

8830447

SQL> begin

    for i in 1 .. 1000

    loop

    insert into s2 values ( i );

    commit;

   end loop;

   end;

   /

PL/SQL procedure successfully completed.

SQL> select scn, scn-8830447 from (

  select dbms_flashback.get_system_change_number scn from dual

  );

SCN SCN-8830447

---------- -------------

8842825    12378

Is there any difference between select CURRENT_SCN from v$database and select dbms_flashback.get_system_change_number scn from dual?

For a "normal" database (not standby) they are for all intents and purposes the same. They could be a LITTLE different if you do something like:

SQL>select current_scn, dbms_flashback.get_system_change_number from v$database; since they would be evaluated at two slightly different points in time, but consider them "the same"

What is the difference or similarity between SCN and ORA_ROWSCN? Where does oracle store SCN?

The SCN is like a clock - it is always advancing (use the command “dbms_flashback.get_system_change_number” and wait for few seconds, print it again, it will  have advanced). So, just think of the SCN like a ticker, like time - every time a transaction ends - another unit of time is added, like adding seconds to time where as ora_rowscn is an observed point in time. The ora_rowscn is a value associated to a block or a row on a block that represents the “time” the block/row was last modified.

When alter system checkpoint” command is used?

When we have few dirty buffers of one table in the buffer cache and we issue the command

The checkpoint SCN of the data block is updated and ITL is also updated as:

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0005.020.00002b46  0x00c00235.0d0f.15  --U-    3  fsc 0x0000.00ddffee

0x02   0x0001.012.00002088  0x00c0021d.0b70.07  --U-    1  fsc 0x0000.00df1407

But the header block (after we dump and see) of the file still contains the same SCN as before irrespective of the change in the data block

Where the SCN number resides? Does archive and redo logs also contain SCN numbers?

SCN doe not really reside anywhere, it is like time itself. A value of the SCN, taken at various times, representing the time something happened is stored in many places, sort of like a timestamp would be. Datafiles have SCNs associated with them (times of various operations) control files have them (times of various operations) log files have them (to record times of various operations) undo segments have them (......) they are littered all over the place, they are like timestamps.

Overview DATA DICTIONARY: CHECKPOINT

V$INSTANCE_RECOVERY, V$LOG, V$LOG_HISTORY

V$INSTANCE_RECOVERY: lowest value in last four columns controls checkpoints

redo log file size, log_checkpoint_timeout, log_checkpoint_interval, fast_start_io_target

init parameter: log_checkpoint_interval, log_checkpoint_timeout, log_checkpoints_to_alert

log_checkpoint_interval

– redo log blocks (OS blocks not DB blocks) written before a checkpoint

– If set greater than redo log file size, checkpoints occur at log switches

– Ignored if set to zero.

log_checkpoint_timeout

– number of seconds since last checkpoint before another is performed

– ignored if set to zero

– default = 1800 seconds (30 minutes)

– log_checkpoints_to_alert if true, write checkpoints to alert log

To decrease checkpoints:

– set log_checkpoint_interval larger than the size of the online redo logs

– eliminate time-based checkpoints by setting log_checkpoint_timeout = 0

– increase size of online redo logs

Note: checkpoints DO NOT cause log switches, but log switches cause checkpoints. For Manual check point use “alter system checkpoint”.