Understanding checkpoints, system change number SCN and recovery

How does Oracle know that a database needs recovering? What checks does it carry out when a database is opened? This article explains a couple of common cases of Oracle recovery and the role of the system change number SCN in the recovery.

What is the SCN system change number?

Every modification in the database (for example, an update) is given an unique system change number SCN, which is, therefore, an ever increasing integer. The latest SCN is immediately recorded in the controlfile.

The modifications are logged in the online redo log by the log writer LGWR, but not necessarily in the datafiles. Infact, there are times when the modified data is still in memory in a block that has become "dirty", meaning that it no longer contains the same data as the block in the datafile.

There are moments, though, when all modifications are written onto all the datafiles: this happens by a checkpoint, shutdown of the database, when a redo log is switched.
At that point the header of the datafiles is updated with the SCN of the system, which will be the same as recorded in the controlfile. The Oracle process CKPT (the checkpoint process) is responsible for this update. When the checkpoint is completed the database reaches a "consistent" state, meaning that it is clean, there is no contraddiction between the SCN and timestamps of the various components.

The V$DATAFILE_HEADER dynamic view

For the purpose of our discussion, it is important to familiarise ourselves with the dynamic view V$DATAFILE_HEADER, which contains the information that Oracle writes in the datafile headers.


SQL> descr V$DATAFILE_HEADER
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE#                                              NUMBER
 STATUS                                             VARCHAR2(7)
 ERROR                                              VARCHAR2(18)
 FORMAT                                             NUMBER
 RECOVER                                            VARCHAR2(3)
 FUZZY                                              VARCHAR2(3)
 CREATION_CHANGE#                                   NUMBER
 CREATION_TIME                                      DATE
 TABLESPACE_NAME                                    VARCHAR2(30)
 TS#                                                NUMBER
 RFILE#                                             NUMBER
 RESETLOGS_CHANGE#                                  NUMBER
 RESETLOGS_TIME                                     DATE
 CHECKPOINT_CHANGE#                                 NUMBER
 CHECKPOINT_TIME                                    DATE
 CHECKPOINT_COUNT                                   NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 NAME                                               VARCHAR2(513)

SQL> column CHECKPOINT_CHANGE#  format 99999999999999
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOINT_TIME      CHECKPOINT_CHANGE#
---------- -------------------- ------------------
         1 13:47:58 02-AUG-2004            6488359
         2 13:47:58 02-AUG-2004            6488359
         3 13:47:58 02-AUG-2004            6488359
         4 13:47:58 02-AUG-2004            6488359
         5 13:47:58 02-AUG-2004            6488359
         6 13:47:58 02-AUG-2004            6488359
         8 13:47:58 02-AUG-2004            6488359
         9 13:47:58 02-AUG-2004            6488359
8 rows selected.

The view v$database contains information that comes from the controlfile.

SQL> descr v$database
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DBID                                               NUMBER
 NAME                                               VARCHAR2(9)
 CREATED                                            DATE
 RESETLOGS_CHANGE#                                  NUMBER
 RESETLOGS_TIME                                     DATE
 PRIOR_RESETLOGS_CHANGE#                            NUMBER
 PRIOR_RESETLOGS_TIME                               DATE
 LOG_MODE                                           VARCHAR2(12)
 CHECKPOINT_CHANGE#                                 NUMBER
 ARCHIVE_CHANGE#                                    NUMBER
 CONTROLFILE_TYPE                                   VARCHAR2(7)
 CONTROLFILE_CREATED                                DATE
 CONTROLFILE_SEQUENCE#                              NUMBER
 CONTROLFILE_CHANGE#                                NUMBER
 CONTROLFILE_TIME                                   DATE
 OPEN_RESETLOGS                                     VARCHAR2(11)
 VERSION_TIME                                       DATE
 OPEN_MODE                                          VARCHAR2(10)
 PROTECTION_MODE                                    VARCHAR2(20)
 PROTECTION_LEVEL                                   VARCHAR2(20)
 REMOTE_ARCHIVE                                     VARCHAR2(8)
 ACTIVATION#                                        NUMBER
 DATABASE_ROLE                                      VARCHAR2(16)
 ARCHIVELOG_CHANGE#                                 NUMBER
 SWITCHOVER_STATUS                                  VARCHAR2(18)
 DATAGUARD_BROKER                                   VARCHAR2(8)
 GUARD_STATUS                                       VARCHAR2(7)
 SUPPLEMENTAL_LOG_DATA_MIN                          VARCHAR2(3)
 SUPPLEMENTAL_LOG_DATA_PK                           VARCHAR2(3)
 SUPPLEMENTAL_LOG_DATA_UI                           VARCHAR2(3)
 FORCE_LOGGING                                      VARCHAR2(3)

SQL> select CONTROLFILE_CHANGE# from v$database;

CONTROLFILE_CHANGE#
-------------------
            6488361

SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
           6488359

We notice that the CHECKPOINT_CHANGE# in the datafile headers is identical to the CHECKPOINT_CHANGE# in the controlfile(s) because when the queries were executed, the database had just been opened..

CKPT process

1 case: datafile that needs recovery

To understand how things work, let's make a couple of experiments. Warning: these are operation that should be avoided in real life. Their sole purpose here is to explain the concept, they are not recommended procedures.

Being on UNIX, it is possible to make copies of datafiles and controlfiles when the database is open. Since we don't want to break anything, our database is in archive mode, we have already taken a good rman backup and we are therefore ready to restore if something goes wrong.
These are the steps of our exercise to simulate the restore and recovery of a datafile:

A copy of one of the datafiles is taken.
Normal activity continues.
The database is closed.
The copy taken at step 1 is put back onto its original place; this file is therefore "older" than the others (even if the timestamp might be more recent)
The database is open

Let's have a look at our test database:

  1* select file#, name from v$datafile

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 /u05/oradata/DEVDB/system01DEVDB.dbf
         2 /u04/oradata/DEVDB/rbs01DEVDB.dbf
         3 /u05/oradata/DEVDB/temp01DEVDB.dbf
         4 /u04/oradata/DEVDB/tools01DEVDB.dbf
         5 /u05/oradata/DEVDB/users01DEVDB.dbf
         6 /u05/oradata/DEVDB/data01DEVDB.dbf
         8 /u04/oradata/DEVDB/appidx01DEVDB.dbf
         9 /u05/oradata/DEVDB/appdata01DEVDB.dbf

8 rows selected.

We create a table t_test before copying the datafile and a table t_test2 after copying it:

SQL> create table t_test(c1 number) tablespace tools;

Table created.

SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOINT_TIME      CHECKPOINT_CHANGE#
---------- -------------------- ------------------
         1 13:47:58 02-AUG-2004            6488359
         2 13:47:58 02-AUG-2004            6488359
         3 13:47:58 02-AUG-2004            6488359
         4 13:47:58 02-AUG-2004            6488359
         5 13:47:58 02-AUG-2004            6488359
         6 13:47:58 02-AUG-2004            6488359
         8 13:47:58 02-AUG-2004            6488359
         9 13:47:58 02-AUG-2004            6488359

8 rows selected.

SQL> insert into t_test values(1);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOINT_TIME      CHECKPOINT_CHANGE#
---------- -------------------- ------------------
         1 13:47:58 02-AUG-2004            6488359
         2 13:47:58 02-AUG-2004            6488359
         3 13:47:58 02-AUG-2004            6488359
         4 13:47:58 02-AUG-2004            6488359
         5 13:47:58 02-AUG-2004            6488359
         6 13:47:58 02-AUG-2004            6488359
         8 13:47:58 02-AUG-2004            6488359
         9 13:47:58 02-AUG-2004            6488359

8 rows selected.

The SCN hasn't changed yet. We now make the copy

SQL> !cp /u04/oradata/DEVDB/tools01DEVDB.dbf /tmp

SQL> alter system switch logfile;

System altered.

SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOINT_TIME      CHECKPOINT_CHANGE#
---------- -------------------- ------------------
         1 13:57:20 02-AUG-2004            6488566
         2 13:57:20 02-AUG-2004            6488566
         3 13:57:20 02-AUG-2004            6488566
         4 13:57:20 02-AUG-2004            6488566
         5 13:57:20 02-AUG-2004            6488566
         6 13:57:20 02-AUG-2004            6488566
         8 13:57:20 02-AUG-2004            6488566
         9 13:57:20 02-AUG-2004            6488566

8 rows selected.

SQL> shutdown immediate
Database closed. 
Database dismounted.
ORACLE instance shut down.
SQL> exit

At this point as a safety measure we take a copy /u04/oradata/DEVDB/tools01DEVDB.dbf.save of the datafile 4 as it is now and copy back from /tmp:

oracle-localora@# cp /u04/oradata/DEVDB/tools01DEVDB.dbf /u04/oradata/DEVDB/tools01DEVDB.dbf.save

oracle-localora@# cp /tmp/tools01DEVDB.dbf /u04/oradata/DEVDB/tools01DEVDB.dbf

cp: overwrite /u04/oradata/DEVDB/tools01DEVDB.dbf (yes/no)? y

The database can now be opened: let's see what happens:

SQL> startup
ORACLE instance started.

Total System Global Area  189409824 bytes
Fixed Size                   731680 bytes
Variable Size             104857600 bytes
Database Buffers           81920000 bytes
Redo Buffers                1900544 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u04/oradata/DEVDB/tools01DEVDB.dbf'

So, why does Oracle (rightly) complain about file 4? Because the SCN in its header is different from the others:

SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOINT_TIME      CHECKPOINT_CHANGE#
---------- -------------------- ------------------
         1 13:57:57 02-AUG-2004            6488582
         2 13:57:57 02-AUG-2004            6488582
         3 13:57:57 02-AUG-2004            6488582
         4 13:47:58 02-AUG-2004            6488359 
         5 13:57:57 02-AUG-2004            6488582
         6 13:57:57 02-AUG-2004            6488582 
         8 13:57:57 02-AUG-2004            6488582
         9 13:57:57 02-AUG-2004            6488582

The datafile 4 has a "delay", since its SCN number is smaller than the one in the other datafiles and controlfile.
We can also find out which archived (or online) redo logs will be necessary for the recovery: they are the ones (or one) whose FIRST_CHANGE# is less or equal 6488359 AND next_change is greater

SQL> select NAME, FIRST_CHANGE# from v$archived_log where FIRST_CHANGE# >= 6488359;

NAME                                        FIRST_CHANGE#
--------------------------------------------------------------------------------
/opt/oracle/admin/DEVDB/arch/1_60.dbf   6488563

SQL> RECOVER DATABASE;

ORA-00279: change 6488359 generated at 08/02/2004 13:47:58 needed for thread 1
ORA-00289: suggestion : /opt/oracle/admin/DEVDB/arch/1_60.dbf
ORA-00280: change 6488359 for thread 1 is in sequence #60


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}  [we press return]
Log applied.
Media recovery complete.  

SQL>ALTER DATABASE OPEN

The database is back to normal.

2. When is command "recover database using backup controlfile" required?

Let's take a copy of the controlfile before any DDL activity, such as creating a new table and populating it. This will simulate the restore of a controlfile:

SQL> !cp /u01/oradata/DEVDB/ctl1DEVDB.dbf /tmp

SQL> create table t_test2(c1 number) tablespace tools;
Table created.

SQL> insert into t_test2   values(10);
1 row created.

SQL> insert into t_test2   values(11);
1 row created.

SQL> insert into t_test2   values(12);
1 row created.

SQL> commit;

Commit complete.

We now shutdown the database; this will cause a checkpoint to happen, meaning that all datafiles headers will be updated with the SCN contained in the controlfile.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Before any dangerous experiment, the wise DBA will always takes a copy of the controlfile(s)

oracle-localora@# cp /u01/oradata/DEVDB/ctl1DEVDB.dbf /u01/oradata/DEVDB/ctl1DEVDB.dbf.save
oracle-localora@# cp /u02/oradata/DEVDB/ctl2DEVDB.dbf /u02/oradata/DEVDB/ctl2DEVDB.dbf.save
oracle-localora@# cp /u03/oradata/DEVDB/ctl3DEVDB.dbf /u03/oradata/DEVDB/ctl3DEVDB.dbf.save

Let's now overwrite the controlfiles with the copy we created on the /tmp directory:

oracle-localora@# cp /tmp/ctl1DEVDB.dbf /u01/oradata/DEVDB/ctl1DEVDB.dbf
cp: overwrite /u01/oradata/DEVDB/ctl1DEVDB.dbf (yes/no)? y
oracle-localora@# cp /tmp/ctl1DEVDB.dbf /u02/oradata/DEVDB/ctl2DEVDB.dbf
cp: overwrite /u02/oradata/DEVDB/ctl2DEVDB.dbf (yes/no)? y
oracle-localora@# cp /tmp/ctl1DEVDB.dbf /u03/oradata/DEVDB/ctl3DEVDB.dbf
cp: overwrite /u03/oradata/DEVDB/ctl3DEVDB.dbf (yes/no)? y
oracle-localora@#

Let's try to open the database.

SQL> startup
ORACLE instance started. 

Total System Global Area  189409824 bytes
Fixed Size                   731680 bytes
Variable Size             104857600 bytes
Database Buffers           81920000 bytes
Redo Buffers                1900544 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u05/oradata/DEVDB/system01DEVDB.dbf'
ORA-01207: file is more recent than controlfile - old controlfile

SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOINT_TIME      CHECKPOINT_CHANGE#
---------- -------------------- ------------------
         1 14:20:22 02-AUG-2004            6488634
         2 14:20:22 02-AUG-2004            6488634
         3 14:20:22 02-AUG-2004            6488634
         4 14:20:22 02-AUG-2004            6488634
         5 14:20:22 02-AUG-2004            6488634
         6 14:20:22 02-AUG-2004            6488634
         8 14:20:22 02-AUG-2004            6488634
         9 14:20:22 02-AUG-2004            6488634

8 rows selected.

SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
           6488582

Oracle complains saying the the controlfile is an old copy judging from the SCN; during other kinds of recovery you will be prompted that the command "recover database using backup controlfile" is needed essentially for the same reason. Let's see what happens if we try a normal recovery:

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u05/oradata/DEVDB/system01DEVDB.dbf'
ORA-01207: file is more recent than controlfile - old controlfile

Instead we use:

SQL> recover database using backup controlfile;
ORA-00279: change 6488582 generated at 08/02/2004 13:57:20 needed for thread 1
ORA-00289: suggestion : /opt/oracle/admin/DEVDB/arch/1_61.dbf
ORA-00280: change 6488582 for thread 1 is in sequence #61


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/opt/oracle/admin/DEVDB/arch/1_61.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

This is an interesting case because the redo log needed for the recovery hasn't been archived yet and it is one of the online redo logs. We can see it because the latest archived log is sequence 60, but Oracle is looking for the sequence 61.

SQL> !ls -ltr /opt/oracle/admin/DEVDB/arch
total 10298
-rw-r-----   1 oracle   dba        18944 Aug  2 13:57 1_59.dbf 
-rw-r-----   1 oracle   dba         1536 Aug  2 13:57 1_60.dbf  
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u03/oradata/DEVDB/redog2m1DEVDB.dbf
/u04/oradata/DEVDB/redog2m2DEVDB.dbf
/u01/oradata/DEVDB/redog1m1DEVDB.dbf
/u02/oradata/DEVDB/redog1m2DEVDB.dbf

SQL> recover database using backup controlfile;
ORA-00279: change 6488582 generated at 08/02/2004 13:57:20 needed for thread 1
ORA-00289: suggestion : /opt/oracle/admin/DEVDB/arch/1_61.dbf
ORA-00280: change 6488582 for thread 1 is in sequence #61

Specify log: {<RET>=suggested | filename | AUTO | CANCEL} [we type the name of the online redo log]
/u03/oradata/DEVDB/redog2m1DEVDB.dbf
Log applied.
Media recovery complete.

We have now almost finished; the final remark is that when recovering with a backup controlfile, the database must be opened with the clause "resetlogs"

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.

SQL> select * from t_test2;

        C1
----------
        10
        11
        12

SQL> select * from t_test;

        C1
----------
         1
         1

The contents of the database are as expected.

Please consult our article on rman backups as well.

[Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic]

Rate this article ...
Very poor	Poor	Average	Good	Very good