Is there a CHECKPOINT with SHUTDOWN IMMEDIATE?

Today I presented a little 2 hours presentation for some DBAs at Turkcell Academy.

I introduced them to the infrastructure of ASM and explained Oracle’s unique locking concept.

After this  Tonguç and his brothers invited me for a drink and dinner downtown and we had some intersting discussions.

One interesting questions we talked about was what happens exactly with a SHUTDOWN IMMEDIATE – is there a checkpoint before shutdown or is it just a flushing of the redolog buffer to the logfiles?

Here is the proof for the CHECKPOINT happening before shutdown:

SYS AS SYSDBA @ lutz1 SQL> create table t as select * from all_objects;

Table created.

SYS AS SYSDBA @ lutz1 SQL> select current_scn from v$database;

CURRENT_SCN
———–
     202799

SYS AS SYSDBA @ lutz1 SQL> select file#, checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             197655
         2             197655
         3             197655
         4             197655

SYS AS SYSDBA @ lutz1 SQL> select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             197655
         2             197655
         3             197655
         4             197655

SYS AS SYSDBA @ lutz1 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA @ lutz1 SQL> startup mount
ORACLE instance started.

Total System Global Area  507510784 bytes
Fixed Size                  1220264 bytes
Variable Size             155189592 bytes
Database Buffers          348127232 bytes
Redo Buffers                2973696 bytes
Database mounted.
SYS AS SYSDBA @ lutz1 SQL> select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             203071
         2             203071
         3             203071
         4             203071

SYS AS SYSDBA @ lutz1 SQL> alter database open;

Database altered.

SYS AS SYSDBA @ lutz1 SQL>  select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             203072
         2             203072
         3             203072
         4             203072

SYS AS SYSDBA @ lutz1 SQL>

#######here is the content of the alertlog for the startup
####### as you can see there is no instance recovery!!!
####### there was a checkpoint before shutdown!!

Completed: ALTER DATABASE   MOUNT
Fri May 18 01:13:36 2007
alter database open
Fri May 18 01:13:36 2007
Thread 1 opened at log sequence 12
  Current log# 3 seq# 12 mem# 0: +DATA/lutz1/onlinelog/group_3.286.622819249
  Current log# 3 seq# 12 mem# 1: +FRA/lutz1/onlinelog/group_3.259.622819253
Successful open of redo thread 1
Fri May 18 01:13:36 2007
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 18 01:13:36 2007
SMON: enabling cache recovery
Fri May 18 01:13:38 2007
Successfully onlined Undo Tablespace 1.
Fri May 18 01:13:38 2007
SMON: enabling tx recovery
Fri May 18 01:13:38 2007
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=18, OS id=4284
Fri May 18 01:13:44 2007
Completed: alter database open