LOG_CHECKPOINTS_TO_ALERT – Don’t expect too much

Today i came across with some basic thing: Oracle issues a checkpoint on datafiles when you offline a tablespace (and corresponding datafile). Before putting a tablespace offline i’ve decided to query v$datafile to take note of current scn for that particular datafile:

 SQL> select NAME, checkpoint_change#, to_char(checkpoint_time, 'YYYY-MM-DD HH24:MI:SS') from v$datafile where file#=7;
CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_
------------------ -------------------
1526927 2012-06-20 22:53:19

Then place datafile offline and online again and check CHECKPOINT_CHANGE# column:

SQL> alter tablespace ts_test offline;
Tablespace altered.
SQL> alter tablespace ts_test online;
Tablespace altered.

SQL> select NAME, checkpoint_change#, to_char(checkpoint_time, 'YYYY-MM-DD HH24:MI:SS') from v$datafile_header where file#=7;
CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_
------------------ -------------------
1527210 2012-06-20 22:58:31

As you can notice CHECKPOINT_CHANGE# changed and that is because Oracle issues a checkpoint every time a tablespace (and datafile(s)) go offline mode to avoid any data inconsistency. As this make perfect sense for me, i needed to make sure that checkpoint really occurred just to waste more time 🙂 For this i’ve decided to log checkpoints on alert.log:

SQL> alter system set LOG_CHECKPOINTS_TO_ALERT=true;
System altered.
SQL> alter tablespace ts_test offline;
Tablespace altered.
SQL> alter tablespace ts_test online;
Tablespace altered.

ALERT.LOG

…
ALTER SYSTEM SET log_checkpoints_to_alert=TRUE SCOPE=BOTH;
Wed Jun 20 23:25:30 2012
alter tablespace ts_test offline
Completed: alter tablespace ts_test offline
alter tablespace ts_test online Completed: alter tablespace ts_test online
…

I was expecting to see checkpoint log on offline/online operation, but for some reason Oracle doesn’t want to, so maybe the correct name for parameter is log_some_checkpoints_to_alert 🙂
Just to make sure that parameter is working if you force or wait for dbwr to checkpoint you will eventually see this:

Wed Jun 20 23:27:03 2012 Incremental checkpoint up to RBA [0x22.8830.0], current log tail at RBA [0x22.898d.0]
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