SCN : System Change Number

SCN est un nombre qui peut définir une version enregistrée (commit) de la base dans un temps bien précis. Quand il y a un commit sur une transaction, oracle lui assigne un nombre unique SCN qui identifiera cette transaction.

SCN est une sorte d'horloge logique d'oracle et il ne faut pas le confondre avec l'horloge système, il est unique et s'accroît dans le temps, mais pas séquentiellement et il ne prend jamais la valeur 0 tant que la base n'est pas recréée.

Oracle effectue une restauration uniquement par rapport au SCN. Par contre vous pouvez choisir l'une des méthodes suivantes : par SCN, par HORDATE, ou par fichier de contrôle. Voir article : Sauvegarde et restauration de données

Pour un commit de 16 transactions par seconde, il faudrait 500 ans pour dépasser le SCN autorisé dans oracle. SCN désigne bien «  SYSTEM CHANGE NUMBER » et non «  SYSTEM COMMIT NUMBER », il suffit de voir dans les vues V$ on utilise la colonne CHANGE# pour désigner le SCN.

Pour trouver le SCN courant dans une base 10g, on utilise le package DBMS_FLASHBACK :

SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                                  2542736

SQL>

SCN joue un rôle important sur la consistance de la base. Au démarrage de la base, le processus SMON vérifie le SCN dans tous les entêtes des fichiers de données. Tout se passe bien si les SCN sont les mêmes que celui du fichier de contrôle.

Le SCN joue encore un rôle important pendant la lecture des blocs Oracle. Au début, un SCN est attribué à la requête (SCN1), après elle lit le SCN de la dernière modification dans le bloc (SCN2), si SCN2 est supérieur à SCN1, cela signifie que le bloc a été modifié après le démarrage de la requête. Dans ce cas, Oracle cherche une ancienne version du bloc dans le roll back segment.

Oracle 10g fournit un nouveau pseudo colonne ORA_ROWSCN, qui consiste en un timestamp comité ou en un SCN qui fournit aux applications et aux utilisateurs la capacité d'appliquer efficacement des verrous optimistes.

Dans les versions antécédentes, quand on envoyait des mises à jour à la base, les applications devaient lire les valeurs de toutes les colonnes ou d'un indicateur de colonne spécifié par l'utilisateur, et les comparer avec ceux précédemment rapportés. Avec ce nouveau dispositif, seule la ligne SCN est exigée d'être comparée pour vérifier qu'une ligne n'a pas été modifiée depuis le select jusqu'aux mises à jour.

Le pseudo colonne renvoie, pour chaque version de chaque ligne, le SCN de la ligne. On ne peut pas l'utiliser pour les vues.

Toutefois, vous pouvez l'utiliser pour se référer aux tables sous-jacentes pendant la création des vues. Et vous pouvez aussi l'utiliser dans la clause WHERE dans l'instruction UPDATE ou DELETE.

Par exemple :

SQL> SELECT ora_rowscn FROM tab_test;

ORA_ROWSCN
----------
351744
351744
351744
351744
351744
351744
6 rows selected.

Dans cette requête, on remarque que tous les enregistrements de la table TAB_TEST sont comités par la même transaction.

Maintenant, on va modifier certaines lignes dans la table :

UPDATE tab_test SET valeur=valeur*1.1 WHERE col_id=1;

3 rows updated.

SQL> commit;

Commit complete

SQL> SELECT ora_rowscn FROM tab_test:

ORA_ROWSCN
----------

351744
351744
351744
371423
371423
371423

6 rows selected.

Une fonction très pratique qui nous permet de retrouver la date de la dernière modification d'une ligne, appelée : SCN_TO_TIMESTAMP. Par exemple :

SQL> select scn_to_timestamp(ora_rowscn) from tab_test;

SCN_TO_TIMESTAMP(ORA_ROWSCN) 
27-SEP-05 11.06.08.000000000 PM 
27-SEP-05 11.06.08.000000000 PM 
27-SEP-05 11.06.08.000000000 PM 
27-SEP-05 04.33.19.000000000 PM 
27-SEP-05 04.33.19.000000000 PM 
27-SEP-05 04.33.19.000000000 PM

6 rows selected.

ORA_ROWSCN n'est pas supporté pour les tables externes. Mais il est très utilisé dans le flashback.

La table SMON_SCN_TIME est disponible à partir d'Oracle 9i. En 9i le processus SMON remplit cette table toutes les cinq minutes avec la date (timestamp) et le SCN courant. Toutefois, il ne garde que cinq jours (1440 lignes = 5*24*12) de lignes.

SQL> select count(*) from smon_scn_time;

  COUNT(*)
----------
      1440

On remarque que dans Oracle 9i, le processus SMON se réveille toutes les cinq minutes (presque).

Par contre, sous 10g, la table SMON_SCN_TIME est remplie chaque +/- trois secondes.

SQL> select count(*) from smon_scn_time;

  COUNT(*)
----------
      1597

Une minute après, on a :

SQL> select count(*) from smon_scn_time;

  COUNT(*)
----------
      1595

Ce qui montre que le nombre de lignes dans la table SMON_SCN_TIME n'est pas fixe.

Dans oracle 9i, la description de la table SMON_SCN_TIME est :

SQL> desc SMON_SCN_TIME
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------
 THREAD                                             NUMBER
 TIME_MP                                            NUMBER
 TIME_DP                                            DATE
 SCN_WRP                                            NUMBER
 SCN_BAS                                            NUMBER

Dans Oracle 10g, on a d'autres lignes en plus :

SQL> desc SMON_SCN_TIME
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------

 THREAD                                             NUMBER
 TIME_MP                                            NUMBER
 TIME_DP                                            DATE
 SCN_WRP                                            NUMBER
 SCN_BAS                                            NUMBER
 NUM_MAPPINGS                                       NUMBER
 TIM_SCN_MAP                                        RAW(1200)
 SCN                                                NUMBER
 ORIG_THREAD                                        NUMBER

Le SCN est constitué de deux parties : YYYY.XXXXXXXX. La partie YYYY s'appelle SCN Wrap (2 bytes) et la partie XXXXXXXX s'appelle SCN Bas (4 bytes). En général, on appelle SCN juste la partie SCN Bas.

Chaque fichier journal contient un SCN Haut et un SCN Bas.

On remarque dans la requête que le SCN haut d'une séquence est égal au SCN bas de la séquence suivante sauf s'il y a un RESETLOGS (par exemple entre la séquence 22 et 1).

SQL> select sequence#,first_change#,next_change#,resetlogs_change# from v$log_history;


 SEQUENCE#         FIRST_CHANGE#     NEXT_CHANGE#         RESETLOGS_CHANGE#
----------         -------------     ------------         -----------------
18                    454728           454730                    318842
19                    454730           454732                    318842
20                    454732           454734                    318842
21                    454734           454736                    318842
22                    454736           454747                    318842
1                    458239           461610                    458239
2                    461610           462733                    458239
3                    462733           467288                    458239
4                    467288           476095                    458239
5                    476095           496820                    458239

Un RESETLOGS initialise le numéro de séquence à 1, mais le SCN continue à s'accroître.

Chaque fois qu'un tablespace est mis hors ligne normalement, un SCN hors-ligne normal est mis dans le tablespace TS$. Même chose quand un tablespace est mis en mode BEGIN BACKUP. Voir l'article : Sauvegarde et restauration

SQL> select name,scnbas,scnwrp from ts$ where ts#=4;

NAME                               SCNBAS     SCNWRP
------------------------------ ---------- ----------
USERS                                   0          0

SQL> alter tablespace users offline;

SQL> select name,scnbas,scnwrp from ts$ where ts#=4;

NAME                               SCNBAS     SCNWRP
------------------------------ ---------- ----------
USERS                             1067840          0

On convertit la valeur 1067840 en hexadécimal :

SQL> select to_char(1067840,'xxxxxxxx') from dual;

TO_CHAR(1
---------
   104b40


SQL> alter tablespace users online;

Et si on fait un dump du fichier de contrôle, on voit bien :

Offline scn: 0x0000.00104b40 prev_range: 0

Après un checkpoint complet, Oracle stocke le SCN individuellement dans le fichier de contrôle pour chaque fichier de données. On l'appelle Checkpoint SCN ou Start SCN. Voir Fichier de contrôle pour plus de précisions.

SQL> select name,checkpoint_change# from v$datafile where name like '%US%';

NAME                                                 CHECKPOINT_CHANGE#
--------------------------------------------------   ------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\B10G2\USERS01.DBF              2578898


SQL> alter system checkpoint;

Système modifié.

SQL> select name,checkpoint_change# from v$datafile where name like '%US%';

NAME                                                 CHECKPOINT_CHANGE#
--------------------------------------------------   ------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\B10G2\USERS01.DBF              2594034


SQL> select name,checkpoint_change# from v$datafile where name like '%SYST%';

NAME                                                 CHECKPOINT_CHANGE#
--------------------------------------------------   ------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\B10G2\SYSTEM01.DBF             2594034

On voit bien que le SCN a été modifié après le checkpoint et que tous les fichiers de données ont le même SCN.

Dans le fichier de contrôle, il y a un stop SCN pour chacun des fichiers de données. Quand un fichier de données est en ligne et que la base est ouverte, le Stop SCN pour ce fichier de données prend la valeur infinie (0xfff.ffffff). Si un tablespace est mis hors-ligne tous ces fichiers de données auront un Stop SCN dans le fichier de contrôle. Il n'y aura aucun redo généré pour ces fichiers de données quand le Stop SCN est alloué.

Faire un dump du fichier de contrôle

SQL> alter session set events 'immediate trace name CONTROLF level 12';

Dans USER_DUMP_DEST on a la trace suivante :

DATA FILE #4: 
  (name #4) D:\ORACLE\PRODUCT\10.1.0\ORADATA\AYMAN\USERS01.DBF
 creation size=0 block size=8192 status=0xe head=4 tail=4 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:318 scn: 0x0000.00103843 03/28/2005 13:00:23
 Stop scn: 0xffff.ffffffff 03/28/2005 04:05:01
 Creation Checkpointed at scn:  0x0000.00002288 03/09/2004 23:58:42
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000
 Offline scn: 0x0000.0006fdfe prev_range: 0
 Online Checkpointed at scn:  0x0000.0006fdff 03/16/2005 01:00:58

Quand le fichier de données est en ligne, on a le Stop SCN = 0xFFFF.FFFFFFFF et le start SCN = 0x0000.00103843.

On peut trouver le même résultat en utilisant la vue v$datafile, seulement la valeur du SCN est vide.

select name,last_change# from v$datafile where name like '%USER%';

NAME                                                  LAST_CHANGE#
----------------------------------------------------  ------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\B10G2\USERS01.DBF

Mettre le tablespace USERS hors-ligne

SQL> alter tablespace users offline;

Puis encore une fois un dump du fichier de contrôle

SQL> alter session set events 'immediate trace name CONTROLF level 12';

On a la trace suivante :

DATA FILE #4: 
  (name #4) D:\ORACLE\PRODUCT\10.1.0\ORADATA\AYMAN\USERS01.DBF
creation size=0 block size=8192 status=0x80 head=4 tail=4 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:319 scn: 0x0000.00104207 03/28/2005 14:18:13
 Stop scn: 0x0000.00104207 03/28/2005 14:18:13
 Creation Checkpointed at scn:  0x0000.00002288 03/09/2004 23:58:42
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000
 Offline scn: 0x0000.0006fdfe prev_range: 0
 Online Checkpointed at scn:  0x0000.0006fdff 03/16/2005 01:00:58

Ou en utilisant la requête suivante :

select name,last_change# from v$datafile where name like '%USER%';

Quand le fichier de données est hors ligne le Stop SCN = 0x0000.00104207 et le Start SCN = 0x0000.00104207.

On remarque que le compteur du checkpoint a été incrémenté de 1, ce qui signifie que mettre un tablespace offline conduira automatiquement à un checkpoint pour ces fichiers de données.

La restauration de la base en utilisant le BACKUP du fichier de contrôle oblige toujours un RESETLOGS pour ouvrir la base. Le fichier de contrôle (dans le secteur de fichier de données) a un Start SCN et un Stop SCN dans chaque fichier de données. Quand vous utilisez le bon fichier de contrôle (courant) avec les redo logs en cours pour restaurer la base, le processus de restauration vérifie le changement du SCN dans les redo logs avec le Stop SCN dans le fichier de contrôle et arrête la restauration. Dans ce cas, la restauration est complète et vous pouvez ouvrir la base sans utiliser le RESETLOGS.

En général, le Stop SCN est mis à jour dans tous les entêtes du fichier de données et dans le fichier de contrôle dans la section fichier de données. Si la base est arrêtée anormalement (Shutdown Abort ou un crash), le Stop SCN n'est pas mis à jour. Il restera 0xffff.ffffffff. Si Oracle trouve que le SCN est égal à 0x.ffff.fffffff pendant le démarrage, il conclut que la base n'a pas été arrêtée normalement et lance une restauration.

Oracle conserve un unrecoverable SCN pour chaque fichier de données. C'est le SCN de la modification la plus récente pour ce fichier de données par l'opération NOLOGGING.

Le unrecoverable SCN de chaque fichier de données est enregistré dans la section fichier de données du fichier de contrôle. Il est mis à jour chaque fois que le fichier de données est affecté par l'opération NOLOGGING, a moins que l'évènement 10359 a été mis. Unrecoverable SCN est utilisé par RMAN pour déterminer si le fichier de données a été affecté par l'opération NOLOGGING depuis le dernier backup complet ou incrémental du fichier de données.