Normalisation d'une instance Oracle sous Solaris (mode archivelog)

1- Introduction

Cette documentation présente un exemple de normalisation des instances Oracle sous Solaris. Dans cet exemple de norme, l'instance est en mode archivelog automatique.

2- Organisation des répertoires et variables Shells

Dans toute la documentation, $DBA est une variable d'environnement qui est positionnée dans le fichier .profile du user Oracle. Pour tous les environnements Oracle : $DBA=/Software/oracle/dba.

2-1- Environnement d'une instance (fichiers instance.inst et defaults_inst.ksh)

Dans le répertoire $DBA/bin, le shell USE permet de prendre l'environnement d'une instance.

shell > USE -I <INSTANCE_NAME>

Le shell USE prend en compte le fichier INSTANCE_NAME.inst localisé dans le répertoire $DBA/etc, fichier dans lequel sont positionnées les variables propres à l'instance (ORACLE_SID, version d'Oracle, DBENV, NLS_LANG etc…).

Exemple : /Software/oracle/dba/etc/OEMD1ORA.inst

# Putting the version of Oracle for the instance
ORA_VERSION="9.2.0"
export ORA_VERSION
# Putting the APPNAME for the instance
export APPNAME=oem

# Putting the type of environment
export DBENV=DEV

# Putting the SID Oracle and the NLS_LANG variable
ORACLE_SID="OEMD1ORA"
NLS_LANG=FRENCH_FRANCE.WE8ISO8859P15

 . ~oracle/dba/etc/defaults_inst.ksh

Les valeurs pour la variable DBENV, variable destinée aux envois des alertes sur les consoles, sont : PROD, UAT ou DEV.

Le shell defaults_inst.ksh est générique pour toutes les instances pour positionner la plupart des variables d'environnement de l'instance.

#!/bin/ksh
################################################################################
###
# @(#) Fichier : defaults_inst.ksh
# @(#) Auteur  : DBA team
# @(#) Objet   : Set default environment
################################################################################
###
export DBA=~oracle/dba
export RINST=~oracle/Instances/$ORACLE_SID
export CFG=$RINST/pfile
export SCRIPT=$RINST/scriptexport DMP=$RINST/dmp
export RUN=$RINST/run
export LOG=$RINST/bdump
export TRC=$RINST/udump
export ARC=/$APPNAME/oracle/$ORACLE_SID/archivelog

ORACLE_HOME=$ORACLE_ROOT/app/product/$ORA_VERSION

ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data
ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
ORA_NLS10=$ORACLE_HOME/nls/data
ORA_NLS11=$ORACLE_HOME/nls/data

#
# Applying the variables LD_LIBRARY_PATH and PATH with $ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH
export PATH LD_LIBRARY_PATH ORACLE_SID ORACLE_HOME NLS_LANG
 
export ORA_NLS10 ORA_NLS11 ORA_NLS33 ORA_NLS32 ORA_NLS

export PS1="$LOGNAME@$ORACLE_SID > "

Les variables ORA_NLS% définissent la localisation des fichiers de définition des langues, jeux de caractères etc... dans la distribution Oracle. En fonction de la version d'Oracle, la bonne variable est prise en charge par l'instance Oracle :

2-2- Organisation des répertoires

Les fichiers de données (fichiers de redo log, fichiers de contrôle, fichiers de tablespaces) sont localisés dans le répertoire /$APPNAME/oracle/$ORACLE_SID.

Les fichiers de redo log archivés sont localisés dans le répertoire /$APPNAME/oracle/$ORACLE_SID/archivelog, répertoire identifié par la variable d'environnement $ARC.

Le répertoire de backup de l'instance Oracle est identifié par la variable d'environnement $DMP et correspond au répertoire /$APPNAME/oracle/$ORACLE_SID/export.

2-3- Fichiers init<Instance_name>.ora et config<Instance_name>.ora

Les fichiers d'initialisation de l'instance sont localisés dans le répertoire $CFG (/Software/oracle/Instances/<INSTANCE_NAME>/pfile).

• le fichier config<INSTANCE_NAME>.ora contient des paramètres d'initialisation pour lesquels des codages en dur de répertoire sont spécifiés (paramètres control_files, user_dump_dest, core_dump_dest, log_archive_dest, background_dump_dest, utl_file_dir etc…).
• le fichier init<INSTANCE_NAME>.ora ne contient quant à lui que des paramètres non liés à l'arborescence de l'instance (shared_pool_size, sort_area_size, optimizer_mode etc…).

Le fichier d'initialisation init<INSTANCE_NAME>.ora appelle le fichier config<INSTANCE_NAME>.ora avec le paramètre ifile :

Exemple pour initOEMD1ORA.ora :

# include database configuration parameters
ifile                           = /Software/oracle/Instances/OEMD1ORA/pfile/configOEMD1ORA.ora

Exemple de fichier configOEMD1ORA.ora :

control_files = (/oem/oracle/OEMD1ORA/control01.ctl,
                /oem/oracle/OEMD1ORA/control02.ctl,
                /oem/oracle/OEMD1ORA/control03.ctl)

background_dump_dest    = /Software/oracle/Instances/OEMD1ORA/bdump
core_dump_dest          = /Software/oracle/Instances/OEMD1ORA/cdump
user_dump_dest          = /Software/oracle/Instances/OEMD1ORA/udump
utl_file_dir            = /Software/oracle/Instances/OEMD1ORA/udump

log_archive_dest        = /oem/oracle/OEMD1ORA/archivelog

pwd : /Software/oracle/app/product/9.2.0

lrwxrwxrwx   1 oracle   dba           58 Feb  7 12:36 initOEMD1ORA.ora -> /Software/oracle/Instances/OEMD1ORA/pfile/initOEMD1ORA.ora

3- Nomenclature des fichiers de données

3-1- Nomenclature des fichiers de redo log

Pour chaque instance, 3 groupes de 2 fichiers de redo log en multiplexing sont créés avec la nomenclature redo<# group>_XX.log :

select group#, member from v$logfile;

1     /oem/oracle/OEMD1ORA/redolog/redo1_01.log
1     /oem/oracle/OEMD1ORA/redolog/redo1_02.log
2     /oem/oracle/OEMD1ORA/redolog/redo2_01.log
2     /oem/oracle/OEMD1ORA/redolog/redo2_02.log
3     /oem/oracle/OEMD1ORA/redolog/redo3_01.log
3     /oem/oracle/OEMD1ORA/redolog/redo3_02.log

Lors de la création de la base de données, il faut s'assurer que le paramètre MAXLOGMEMBERS soit supérieur ou égal à 2 pour pouvoir s'assurer qu'un groupe de fichiers de redo log puisse au moins contenir 2 fichiers de redo log multiplexés.

Il faut également respecter la taille minimale des fichiers de redo log en fonction de la version d'Oracle : par exemple 4Mb minimum pour Oracle 10g. Une taille de 10 Mb minimum est recommandée par Oracle pour toutes les versions (8i, 9i et 10g).

3-2- Nomenclature des fichiers de contrôle

Les fichiers de contrôle ont la nomenclature controlXX.ctl. Chaque instance possède 3 fichiers de contrôle dans le répertoire /$APPNAME/oracle/$ORACLE_SID : control01.ctl, control02.ctl et control03.ctl.

select value from v$parameter where name='control_files';

/oem/oracle/OEMD1ORA/control01.ctl,/oem/oracle/OEMD1ORA/control02.ctl,
/oem/oracle/OEMD1ORA/control03.ctl

3-3- Nomenclature des fichiers des tablespaces de données et temporaires

Les fichiers de données des tablespaces temporaires ou de données ont la nomenclature <tablespace_name>_XX.dbf. Si par exemple, le tablespace USERS est réparti sur 4 fichiers de données, cela correspond dans le répertoire /$APPNAME/oracle/$ORACLE_SID à 4 fichiers : USERS_01.dbf, USERS_02.dbf, USERS_03.dbf, USERS_04.dbf.

select name from v$datafile;

/oem/oracle/OEMD1ORA/USERS_01.dbf
/oem/oracle/OEMD1ORA/USERS_02.dbf
/oem/oracle/OEMD1ORA/USERS_03.dbf
/oem/oracle/OEMD1ORA/USERS_04.dbf

3-4- Nomenclature des tablespaces des segments d'annulation (RBS et UNDO)

La gestion des segments d'annulation (rollback segments) entre la version 8i d'une part et les versions 9i et 10g d'autre part est radicalement différente.

Lorsque les segments d'annulation sont gérés manuellement, le tablespace supportant les segments d'annulation a pour nomenclature RBS. En mode automatique UNDO (9i et 10g), le tablespace d'undo a pour nomenclature UNDO.

3-4-1- Oracle 8i (tablespace RBS)

Avec la version 8i, les segments d'annulation sont créés manuellement avec la commande CREATE ROLLBACK SEGMENT et référencés dans le fichier d'initialisation de l'instance <Instance_name>.ora.

Dans la configuration 8i, le tablespace supportant les segments d'annulation s'appelle RBS, tablespace qui est créé comme un tablespace utilisateur normal. Les fichiers de données du tablespace RBS sont localisés dans le répertoire /$APPNAME/oracle/$ORACLE_SID et ont pour nomenclature RBS_xx.dbf.

Les segments d'annulation ont pour nomenclature RBSxx et sont créés avec les syntaxes CREATE ROLLBACK SEGMENT et ALTER ROLLBACK SEGMENT .. ONLINE :

SQL > create rollback segment RBS01 tablespace rbs;
SQL > alter rollback segment RBS01 online;

Les segments d'annulations sont également renseignés dans le fichier <Instance_name>.ora pour leur activation automatique au démarrage de l'instance :

cat $CFG/initOEMD1ORA.ora

...
rollback_segments=(RBS01,RBS02,RBS03,RBS04)
 ...

3-4-2- Oracle 9i et 10g (tablespace UNDO)

A partir de la version 9i, les segments d'annulation sont gérés automatiquement dans un tablespace dit de type undo. Le tablespace est appelé UNDO et les fichiers supportant le tablespace UNDO sont localisés dans le répertoire /$APPNAME/oracle/$ORACLE_SID et ont pour nomenclature UNDO_xx.dbf.

Pour utiliser la fonctionnalité des tablespaces d'UNDO, le paramètre compatible de l'instance doit être positionné à 9.2.0.0.0 au minimum et les paramètres undo_management et undo_tablespace doivent être obligatoirement renseignés dans le fichier d'initialisation de l'instance <Instance_name>.ora :

cat $CFG/initOEMD1ORA.ora

undo_management=auto
undo_tablespace=UNDO
compatible=9.2.0.0.0 # au minimum

3-5- Création de bases de données 8i, 9i et 10g avec la commande CREATE DATABASE

Lors de la création des bases de données, quelle que soit la version, les paramètres mémoire comme les paramètres shared_pool_size, library cache etc... doivent respecter les pré-requis pour la version Oracle concernée. Dans le cas contraire l'exécution des scripts catproc.sql et catalog.sql peut être en échec faute de mémoire.

3-5-1- Création d'une base de données Oracle 8i

Avec la version 8i, la commande CREATE DATABASE ne crée que le tablespace SYSTEM. Le tablespace temporaire et le tablespace RBS contenant les segments d'annulation (rollback segments) sont créés à des étapes ultérieures.

oracle@OEMD1ORA > sqlplus "/ as sysdba"
SQL > startup nomount pfile=/Software/oracle/Instances/OEMD1ORA/pfile/initOEMD1ORA.ora;
SQL >
create database OEM
  maxlogfiles 32
  maxlogmembers 4
  maxloghistory 1
  maxdatafiles 254
  maxinstances 1
  archivelog
  controlfile reuse
logfile  
  group 1 ('/oem/oracle/OEMD1ORA/redolog/redo1_01.log',
           '/oem/oracle/OEMD1ORA/redolog/redo1_02.log') SIZE 1024K,
  group 2 ('/oem/oracle/OEMD1ORA/redolog/redo2_01.log',
           '/oem/oracle/OEMD1ORA/redolog/redo2_02.log') SIZE 1024K,
  group 3 ('/oem/oracle/OEMD1ORA/redolog/redo3_01.log',
           '/oem/oracle/OEMD1ORA/redolog/redo3_02.log') SIZE 1024K
datafile '/oem/oracle/OEMD1ORA/SYSTEM_01.dbf' size 264M  reuse autoextend off
character set WE8ISO8859P1

Pour pouvoir créer les tablespaces RBS et TEMP, un segment d'annulation temporaire RBS_TEMP est défini dans le tablespace SYSTEM :

SQL > create rollback segment rbs_temp tablespace system;
SQL > alter rollback segment rbs_temp online;

Le tablespace RBS est créé avec ses 4 segments d'annulations RBS01, RBS02, RBS03 et RBS04, segments définis dans le fichier d'initialisation de l'instance <Instance_name>.ora.

SQL > create tablespace RBS
      datafile '/oem/oracle/OEMD1ORA/RBS_01.dbf' size 500M;

SQL > create rollback segment RBS01 tablespace RBS;
SQL > create rollback segment RBS02 tablespace RBS;
SQL > create rollback segment RBS03 tablespace RBS;
SQL > create rollback segment RBS04 tablespace RBS;

SQL > alter rollback segment RBS01 online;
SQL > alter rollback segment RBS02 online;
SQL > alter rollback segment RBS03 online;
SQL > alter rollback segment RBS04 online;

Le tablespace temporaire TEMP est ensuite créé avec la commande CREATE TEMPORARY TABLESPACE d'Oracle 8i (uniform size doit être un multiple du paramètre sort_area_size) :

SQL > create temporary tablespace TEMP
      tempfile '/oem/oracle/OEMD1ORA/TEMP_01.dbf' size 500M
      extent management local uniform size 4M;

Lorsque les tablespace RBS et TEMP sont construits, le segment d'annulation RBS_TEMP créé précédemment dans le tablespace SYSTEM peut être détruit :

SQL > alter rollback segment rbs_temp offline;
SQL > drop rollback segment rbs_temp;

3-5-2- Création d'une base de données Oracle 9i

À partir de la version 9i, les créations des tablespaces temporaires TEMP et d'UNDO ansi que la définition de l'option extent management local au niveau de l'instance peuvent être réalisés directement dans la commande CREATE DATABASE.

oracle@OEMD1ORA > sqlplus "/ as sysdba"
SQL > startup nomount pfile=/Software/oracle/Instances/OEMD1ORA/pfile/initOEMD1ORA.ora;
SQL >
create database OEM
    noarchivelog
    maxinstances 1
    maxlogfiles  32
    maxdatafiles  32
    maxlogmembers 4
    controlfile reuse
    datafile
        '/oem/oracle/OEMD1ORA/SYSTEM_01.dbf'    size   400M
    logfile
        group 1
        (
        '/oem/oracle/OEMD1ORA/redolog/redo1_01.log',
        '/oem/oracle/OEMD1ORA/redolog/redo1_02.log')   size 100M,
        group 2
        (
        '/oem/oracle/OEMD1ORA/redolog/redo2_01.log',
        '/oem/oracle/OEMD1ORA/redolog/redo2_02.log')   size 100M,
        group 3
        (
        '/oem/oracle/OEMD1ORA/redolog/redo3_01.log',
        '/oem/oracle/OEMD1ORA/redolog/redo3_02.log')   size 100M
        character set "WE8ISO8859P15"
        national character set AL16UTF16
        extent management local
        undo tablespace UNDO
        datafile '/oem/oracle/OEMD1ORA/UNDO_01.dbf' size 500M
        default temporary tablespace TEMP
        tempfile '/oem/oracle/OEMD1ORA/TEMP_01.dbf' size 500M

Pour le tablespace d'UNDO, les paramètres undo_management=auto et undo_tablespace=undo doivent être renseignés dans le fichier d'initialisation de l'instance lors du lancement de la commande de création de la base de données 9i.

3-5-3- Création d'une base de données Oracle 10g

La syntaxe pour la création d'une base de données Oracle 10g est sensiblement la même que pour une version 9i, à l'exception près du nouveau tablespace SYSAUX, nouveau tablespace système d'Oracle 10g.

oracle@OEMD1ORA > sqlplus "/ as sysdba"
SQL > startup nomount pfile=/Software/oracle/Instances/OEMD1ORA/pfile/initOEMD1ORA.ora;
SQL >
create database OEM
    noarchivelog
    maxinstances 1
    maxlogfiles  32
    maxdatafiles  32
    maxlogmembers 4
    controlfile reuse
    datafile
        '/oem/oracle/OEMD1ORA/SYSTEM_01.dbf'    size   400M
    sysaux datafile '/oem/oracle/RISK/SYSAUX_01.dbf' size 500M
    logfile
        group 1
        (
        '/oem/oracle/OEMD1ORA/redolog/redo1_01.log',
        '/oem/oracle/OEMD1ORA/redolog/redo1_02.log')   size 100M,
        group 2
        (
        '/oem/oracle/OEMD1ORA/redolog/redo2_01.log',
        '/oem/oracle/OEMD1ORA/redolog/redo2_02.log')   size 100M,
        group 3
        (
        '/oem/oracle/OEMD1ORA/redolog/redo3_01.log',
        '/oem/oracle/OEMD1ORA/redolog/redo3_02.log')   size 100M
        character set "WE8ISO8859P15"
        national character set AL16UTF16
        extent management local
        undo tablespace UNDO
        datafile '/oem/oracle/OEMD1ORA/UNDO_01.dbf' size 500M
        default temporary tablespace TEMP
        tempfile '/oem/oracle/OEMD1ORA/TEMP_01.dbf' size 500M

Pour le tablespace d'UNDO, comme en version 9i, les paramètres undo_management=auto et undo_tablespace=undo doivent être renseignés dans le fichier d'initialisation de l'instance lors du lancement de la commande de création de la base de données 10g.

3-5-4- Etapes post création (catalog.sql, catproc.sql)

Pour toutes les versions, après la création de la base de données, les scripts catproc.sql et catalog.sql localisés dans $ORACLE_HOME/rdbms/admin sont exécutés.

SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql

4- Normalisation du mode archivelog

4-1- Localisation des fichiers de redo log archivés (LOG_ARCHIVE_DEST)

La localisation des fichiers de redo log archivés avec le paramètre LOG_ARCHIVE_DEST est spécifié dans le fichier config<INSTANCE_NAME>.ora et pointe sur le filesystem /$APPNAME/oracle/$ORACLE_SID/archivelog.

Fichier de configuration configOEMD1ORA.ora :

log_archive_dest = /oem/oracle/OEMD1ORA/archivelog

4-2- Mode archive log automatique (LOG_ARCHIVE_START), Oracle 8i et Oracle 9i

L'archivage automatique est spécifié dans le fichier d'initialisation de l'instance init<INSTANCE_NAME>.ora avec le paramètre LOG_ARCHIVE_START.

Fichier d'initialisation initOEMD1ORA.ora :

log_archive_start = true

Le paramètre log_archive_start n'est valable que pour Oracle 8i et 9i. À partir de la version 10g, ce paramètre est obsolète et n'est plus nécessaire.

4-3- Nomenclature des fichiers de redo log archivés (LOG_ARCHIVE_FORMAT)

La nomenclature des fichiers de redo log archivés est spécifié dans le fichier d'initialisation de l'instance init<INSTANCE_NAME>.ora avec le paramètre LOG_ARCHIVE_FORMAT. La nomenclature ne doit pas faire référence au nom de l'instance pour aisément rafraîchir une autre instance.

La nomenclature adoptée est la suivante : T%t_S%s.arc. %t est le numéro de thread qui écrit l'archive du fichier de redo log et %s est le numéro de séquence (SCN).

Fichier d'initialisation initOEMD1ORA.ora :

log_archive_format = T%t_S%s.arc

4-4- Nombre de processus ARC (LOG_ARCHIVE_MAX_PROCESSES)

Le nombre de threads ARC (archiveurs) est positionné à 1 dans un premier temps, ce paramètre doit suffire car les fichiers de redo log online sont redimensionnés pour minimiser le nombre de switches.

Fichier d'initialisation initOEMD1ORA.ora :

log_archive_max_processes = 1

5- Paramétrage nécessaire pour l'utilisation du LogMiner (UTL_FILE_DIR)

Pour être en mesure d'utiliser le LogMiner, utilitaire qui permet de lire les fichiers de redo log online ou archivés, le paramètre d'initialisation UTL_FILE_DIR doit être correctement paramétré. UTL_FILE_DIR doit pointer sur un répertoire existant.

Dans la nouvelle normalisation, utl_file_dir pointe sur le répertoire de trace udump.

Fichier de configuration configOEMD1ORA.ora :
utl_file_dir=/Software/oracle/Instances/OEMD1ORA/udump

Sans le positionnement du paramètre d'initialisation utl_file_dir, la procédure stockée BUILD du package DBMS_LOGMNR_D ne peut être utilisée pour la création du fichier dictionary.ora :

EXECUTE DBMS_LOGMNR_D.BUILD( -
DICTIONARY_FILENAME =>'dictionary.ora', -
DICTIONARY_LOCATION => '/Software/oracle/Instances/OEMD1ORA/udump');

Annexe