Pourquoi mettre une base de données Oracle en force logging ?

Certaines requêtes DDL offrent la possibilité de ne pas générer des enregistrements dans les redo log via la clause NOLOGGING. Le mode nologging permet donc dans certains cas d’accélérer des traitements, ceux-ci pouvant facilement être récupérables à travers des mécanismes externes à la base de données. Ce mode peut cependant avoir des conséquences fâcheuses comme nous allons le constater et ce pas uniquement dans le contexte de configurations comme Oracle DataGuard.

Dans le cadre de mon activité de consultant, je rencontre fréquemment des bases de données qui sont en mode NOLOGGING. Quelle en est la signification et quelles peuvent être les implications d’une telle configuration ?

Pour répondre à cette question nous allons nous intéresser au comportement d’une instance de base de données en mode NOLOGGING.

SQL> select force_logging from v$database; 
 FORCE_LOGGING
---
 NO

Les tablespaces de l’instance sont eux par contre en mode LOGGING :

SQL> select tablespace_name , logging from dba_tablespaces;
 
 TABLESPACE_NAME                      LOGGING
 ------------------------------      ---------
 SYSTEM                               LOGGING
 SYSAUX                               LOGGING
 UNDOTBS1                             LOGGING
 TEMP                                 NOLOGGING
 USERS                                LOGGING
 REPO_RMAN                            LOGGING
 REPO_RMAN2                           LOGGING

Nous créons une table test_redo et nous la plaçons en mode NOLOGGING:

SQL> create table test_redo as select * from all_objects where 1=2;
 Table created.
 
 SQL> alter table test_redo nologging;
 Table altered.

En insérant des données dans la table redo_test, on visualise une activité des redologs :

SQL> set autotrace on statistics;
 SQL> insert into test_redo select * from all_objects;
 71890 rows created.
 
 Statistics
 ----------------------------------------------------------
 1527 recursive calls
 9938 db block gets
 50166 consistent gets
 859 physical reads
 8430816 redo size
 674 bytes sent via SQL*Net to client
 619 bytes received via SQL*Net from client
 3 SQL*Net roundtrips to/from client
 1523 sorts (memory)
 0 sorts (disk)
 71890 rows processed

Si on réalise une insertion en direct load via le hint APPEND, l’activité des redologs est moins importante, il y a eu beaucoup moins d’écriture dans les redologs :

SQL> insert /*+ APPEND */ into test_redo select * from all_objects;
 
 71890 rows created.
 
 Statistics
 ----------------------------------------------------------
 225 recursive calls
 1380 db block gets
 47267 consistent gets
 0 physical reads
 28304 redo size
 663 bytes sent via SQL*Net to client
 633 bytes received via SQL*Net from client
 3 SQL*Net roundtrips to/from client
 1501 sorts (memory)
 0 sorts (disk)
 71890 rows processed

Si nous interrogeons la vue v$datafile, nous pouvons visualiser qu’il y a eu une activité qui n’a pas été écrite dans les redologs :

SQL> SELECT NAME, UNRECOVERABLE_CHANGE#, 
 2    TO_CHAR (UNRECOVERABLE_TIME,'DD-MON-YYYY HH:MI:SS') 
 3    FROM V$DATAFILE; 
 
 NAME                      UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERABLE_TIME 
 /u01/database/DB112/system01.dbf        0 
 /u01/database/DB112/sysaux01.dbf        0 
 /u01/database/DB112/undotbs01.dbf      0 
 /u01/database/DB112/users01.dbf   2048947             18-APR-2011 05:07:10

A présent, restaurons l’instance de base de données à un instant t postérieur à l’insertion en direct load, ces données ne seront pas restaurées un message de bloc corrompu apparaitra :

SQL> select count(*) from redo_test; 
 select count(*) from redo_test 
 * 
 ERROR at line 1: 
 ORA-01578: ORACLE data block corrupted (file # 4, block # 1410) 
 ORA-01110: data file 4: '/u01/database/DB112/users01.dbf' 
 ORA-26040: Data block was loaded using the NOLOGGING option

Les opérations en nologging sont plus rapides puisqu’il n’y a pas d’écriture dans les redologs :

SQL> set timing on 
 SQL> create table redo_test as select * from all_objects where 1=2; 
 Table created. 
 Elapsed: 00:00:01.29 
 
 SQL> create table redo_test_nolog nologging as select * from all_objects where 1=2; 
 Table created. 
 Elapsed: 00:00:00.56 
 
 SQL> insert into redo_test select * from all_objects; 
 71892 rows created. 
 Elapsed: 00:00:05.58 
 
 SQL> insert /* +APPEND */ into redo_test_nolog select * from all_objects; 
 71892 rows created. 
 Elapsed: 00:00:03.62

Regardons maintenant si une table créée en mode NOLOGGING a des conséquences sur toutes les opérations DML. Nous allons détruire toutes les lignes de la table redo_test_nolog et effectuer un rollback :

SQL> create table redo_test_nolog nologging as select * from all_objects; 
 Table created. 
 
 SQL> delete from redo_test_nolog; 
 71892 rows deleted. 
 
 SQL> rollback; 
 Rollback complete. 
 
 SQL> select count(*) from redo_test_nolog; 
 COUNT(*) 
 
 71892

Comme précisé dans la documentation Oracle :

“The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.”

En résumé les opérations en nologging peuvent faire gagner du temps, mais il faut être conscient du risque pris lors de telles opérations. Toute opération effectuée en direct load insert implique nécessairement un backup de l’instance.