Understanding SCN and checkpoint in Oracle

I have found that the following articles are extremely useful:

SCN and Checkpoint (1431133.1)
Automatic Checkpoint Tuning (270571.1)
Checkpoint Tuning and Troubleshooting Guide (147468.1)
Master Note: Overview of Database Checkpoints (1490838.1)
http://oracledbascriptsfromajith.blogspot.jp/2009/05/understanding-scnsystem-change-number.html
https://orainternals.wordpress.com/2012/01/19/scn-what-why-and-how/

The current SCN can be obtained by either of the following queries:

select dbms_flashback.get_system_change_number scn from dual;
select current_scn from v$database;
It is worth noting that after a log switch on one instance of a RAC database,  the checkpoint SCN would be recorded in v$thread rather than v$datafile and v$datafile_header.

See below:

1. Check the current thread number for instance on node1:

SQL> select INSTANCE_NUMBER,INSTANCE_NAME,THREAD#,STATUS from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME THREAD# STATUS
--------------- ---------------- ---------- ------------
1 ora11g1 1 OPEN <<<<<<<<<<<<<<<<<<<< Thread# is 1 
2 ora11g2 2 OPEN 

Switch the logfile on instance ora11g1 and check the v$datafile/alert log:

SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 11616270
2 11616270
3 11616270
4 11616270
5 11616270
6 11616270

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 11616270
2 11616270
3 11616270
4 11616270
5 11616270
6 11616270 <<<<<<<<<<<<<<<<< Not changed.

6 rows selected.

In alert log:

Thu Jan 21 02:22:52 2016
Beginning log switch checkpoint up to RBA [0x23a.2.10], SCN: 11621392
Thread 1 advanced to log sequence 570 (LGWR switch)
Current log# 2 seq# 570 mem# 0: +DATA1/ora11g/onlinelog/group_2.258.827893183
Thu Jan 21 02:22:53 2016
Archived Log entry 358 added for thread 1 sequence 569 ID 0xfbc6d574 dest 1:
Thu Jan 21 02:22:55 2016
Completed checkpoint up to RBA [0x23a.2.10], SCN: 11621392 <<<<<<<<<<<<< Thread checkpoint to 11621392 3. 

Check the v$thread:

SQL> select THREAD#,CHECKPOINT_CHANGE# from v$thread;

THREAD# CHECKPOINT_CHANGE#
---------- ------------------
1 11621392 <<<<<<<<<<<<<<<< Thread 1 checkpoint up-to 11621392 which is indicated in alert log.
2 11619485
Advertisements

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