Skip to Main Content
  • Questions

  • v$log first_change# not same as checkpoint_change# in v$datafile

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: August 14, 2002 - 12:49 pm UTC

Last updated: August 22, 2002 - 9:08 am UTC

Version: 8.1.7.4, 9.2.0.1

Viewed 1000+ times

You Asked

Hi Tom

I am doing some research about the difference of checkpoints between alter system switch logfile and alter system checkpoint. At every log switch a checkpoints occurs so:

>select group#, status, FIRST_CHANGE# from v$log;

GROUP# STATUS FIRST_CHANGE#
---------- ---------------- -------------
1 CURRENT 1956755
2 INACTIVE 1916403
3 INACTIVE 1936678

>select file#, checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1956835
2 1956835
3 1956835
4 1956835
5 1956835
6 1956835
7 1956835

however if I do alter system switch logfile I see

>select group#, status, FIRST_CHANGE# from v$log;

GROUP# STATUS FIRST_CHANGE#
---------- ---------------- -------------
1 ACTIVE 1956755
2 CURRENT 1956893
3 INACTIVE 1936678

>select file#, checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1956835
2 1956835
3 1956835
4 1956835
5 1956835
6 1956835
7 1956835

checkpoint_change# stayed same even the implicit checkpoint occured in a log switch and to syncronize this I have to do alter system checkpoint

Why a *manual* redo log switch's implicit checkpoint doesnt update my datafile headers? (an normal log switch does seems to update datafile headers)
Is the checkpoint process different?

and Tom said...

These days, the database is almost constantly checkpointing (incremental checkpoints) as well. Checkpoints are not the discrete operations of yesteryear.

The checkpoint fired by a log switch happens in the background, it does not complete with the switch logfile command necessarily.

Also, there are many types of checkpoints -- the one done by the alter system switch logfile is a "local" checkpoint. We update the datafile headers in a "lazy fashion" if you will -- we batch up and wait for some other IO's to happen. The failure to update the data file header is "low cost".

If you play with the log_checkpoint_timeout parameter, you'll control the length of laziness we will use.

You just didn't wait long enough. Consider (single user system, I'm the only one on it):

ops$tkyte@ORA817DEV.US.ORACLE.COM> set numformat 999999999999999
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 for i in 1 .. 100
3 loop
4 update emp set ename = ename;
5 commit;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select file#, checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------------- ------------------
1 6532308938862
2 6532308938862
3 6532308938862
4 6532308938862
5 6532308938862
6 6532308938862
7 6532308938862
8 6532308938862
9 6532308938862
10 6532308938862
11 6532308938862

11 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select group#, status, FIRST_CHANGE# from v$log;

GROUP# STATUS FIRST_CHANGE#
---------------- ---------------- ----------------
1 CURRENT 6532308938970
2 INACTIVE 6532308938750
3 ACTIVE 6532308938860

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter system switch logfile;

System altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select file#, checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------------- ------------------
1 6532308938862
2 6532308938862
3 6532308938862
4 6532308938862
5 6532308938862
6 6532308938862
7 6532308938862
8 6532308938862
9 6532308938862
10 6532308938862
11 6532308938862

11 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select group#, status, FIRST_CHANGE# from v$log;

GROUP# STATUS FIRST_CHANGE#
---------------- ---------------- ----------------
1 ACTIVE 6532308938970
2 CURRENT 6532308939077
3 ACTIVE 6532308938860

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select file#, checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------------- ------------------
1 6532308938970
2 6532308938970
3 6532308938970
4 6532308938970
5 6532308938970
6 6532308938970
7 6532308938970
8 6532308938970
9 6532308938970
10 6532308938970
11 6532308938970

11 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM>


And it hasn't even caught upto the current log yet.

This is ok, it is normal, it is to be expected.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 14, 2002 - 4:24 pm UTC

Hi

if alter system switch logfile triggers a local checkpoint what kind of checkpoint does a normal log switch trigger?

Tom Kyte
August 14, 2002 - 7:07 pm UTC

I believe it is the same, 99.9% on that. Shouldn't be any different. Too lazy right now to actually test it out ;)

You would have to set up a test that monitor the alerts after setting the init.ora parameter to log checkpoints to alerts and watch a busy system (my test system won't switch logs unless I setup lots of activity)

alter system switch logfile does not trigger checkpoint

A reader, August 21, 2002 - 11:42 am UTC

hi Tom

from Oracle 9i DBA Fundamentals 2 ILT course it states that checkpoint occurs when

1. every log switch
2. when fast start checkpointing is set
3. log_checkpoint_interval
4. log_checkpoint_timeout
5. instance shutdown cleanly
6. alter system checkpoint commnad
7. when tablespace is taken offline or online backup starts

since none talks about alter system switch logfile I guess that´s why it doesnt triggers checkpoint?



Tom Kyte
August 21, 2002 - 11:53 am UTC

log switches trigger checkpoints. See #1 in your list above -- "every log switch"

I tried your test but

A reader, August 22, 2002 - 6:13 am UTC

Hi Tom

I carried out your test in my etst database (I am the only user) but after

exec dbms_lock.sleep(60);

I queried again v$datafile but I still see CHECKPOINT_CHANGE# unchanged....

how so?

Tom Kyte
August 22, 2002 - 9:08 am UTC

As I said -- it is a lazy write, it can take a long long long time.

There is nothing wrong here, it is doing exactly what the database coders coded it to do.




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library