STOP! On n'indexe pas ces mots

Les index d'Oracle Text peuvent être configurés afin d'exclure une liste de mots qui ne sont pas nécessaires d'être indexés tels que les articles, les pronoms, les adverbes, etc...

En excluant des mots non significatifs, les indexes deviennent moins volumineux et plus optimal lors de leurs utilisations.

Dans mon cas, j'utilise Oracle Portal et j'ai modifié les paramètres de certains index pour exclure une liste de mots français communément appelé une "stoplist" qui contient des "stopword".

Oracle fournit un script qui nous simplifie la vie. Ce script s'intitule "drdeffrc.sql". Il contient toutes les commandes nécessaires pour la mise en place de cette liste. Cependant, je n'ai pas tout exécuté.

Pour débuter, j'ai créé les préférences liées au "wordlist"

PROMPT Creating wordlist preference...
begin
CTX_DDL.drop_preference('DEFAULT_WORDLIST');
CTX_DDL.create_preference('DEFAULT_WORDLIST','BASIC_WORDLIST');
CTX_DDL.set_attribute('DEFAULT_WORDLIST','STEMMER', 'FRENCH');
CTX_DDL.set_attribute('DEFAULT_WORDLIST','FUZZY_MATCH', 'FRENCH');
end;
/

Ensuite, j'ai créé la "stoplist". Ceci correspond au plus long bloc PL/SQL du script. Avant de l'exécuter, j'ai ajouté la commande qui détruit la "stoplist" autrement, la création n'aurait pas fonctionné car elle existe. Voici une aperçu du bloc PL/SQL :

PROMPT Creating stoplist...
declare
db_charset VARCHAR2(500);

procedure add_utf8_stopword(hexstring in VARCHAR2) is
begin
CTX_DDL.add_stopword('DEFAULT_STOPLIST', UTL_RAW.cast_to_varchar2(
UTL_RAW.convert(HEXTORAW(hexstring), db_charset,
'AMERICAN_AMERICA.UTF8')));
end add_utf8_stopword;

begin
SELECT 'AMERICAN_AMERICA.' || value
INTO db_charset
FROM v$nls_parameters
WHERE parameter = 'NLS_CHARACTERSET';

ctx_ddl.drop_stoplist('DEFAULT_STOPLIST');
ctx_ddl.create_stoplist('DEFAULT_STOPLIST');
ctx_ddl.add_stopword('DEFAULT_STOPLIST','a');
ctx_ddl.add_stopword('DEFAULT_STOPLIST','afin');
...
ctx_ddl.add_stopword('DEFAULT_STOPLIST','vu');
ctx_ddl.add_stopword('DEFAULT_STOPLIST','y');
end;
/

Suite aux changements effectués, on doit rebâtir tous les index pour que tous nos changements soient en vigueur :

ALTER INDEX "PORTAL"."WWSBR_CORNER_CTX_INDX"
REBUILD PARAMETERS ('replace stoplist default_stoplist');

En cas de besoin, vous pouvez exécuter les scripts suivants pour

• Créer tous les index Oracle Text de Portal : ORACLE_HOME/portal/admin/plsql/wws/ctxcrind.sql

• Détruire tous les index Oracle Text de Portal : ORACLE_HOME/portal/admin/plsql/wws/ctxdrind.sql

• Modifier les préférences : ORACLE_HOME/portal/admin/plsql/wws/sbrimtlx.sql

Aussi, sachez qu'il existe un package CTX_REPORT qui permet de vérifier la configuration. Par exemple, vous pouvez utiliser ces procédures :

• CTX_REPORT.DESCRIBE_INDEX
• CTX_REPORT.CREATE_INDEX_SCRIPT
• CTX_REPORT.INDEX_SIZE

Et, comme à l'habitude, il y a une panoplie de vues dans le dictionnaire qui
vous fourniront les informations désirées, en voici quelques unes :

• CTX_PREFERENCES
• CTX_PARAMETERS
• CTX_STOPLISTS
• CTX_STOPWORDS
• CTX_INDEXES
• CTX_INDEX_ERRORS

Rétention de l'AWR et la taille de SYSAUX

Dernièrement, nous avons rencontré un problème de performance. Dans le but d'identifier et résoudre le problème, nous avions voulu comparer des clichés (snapshots) sur différentes périodes cependant, nous n'avons pas pu reculer assez loin dans le passé car les données de l'historique ne sont pas conservées suffisamment longtemps.

Pour éviter que cela se reproduise, nous voulons conserver une année complète d'information afin d'être en mesure de comparer des clichés (snapshots) à différentes périodes durant la dernière année.

Par défaut, les clichés (snapshots) sont collectés à toutes les heures et ils sont conservés pour une période de 7 jours.

Pour afficher la configuration actuelle :

Select extract (day from snap_interval) * 24 * 60 +
extract (hour from snap_interval) * 60 +
extract (minute from snap_interval) "Snapshot Interval",
extract (day from retention) * 24 * 60 +
extract (hour from retention) * 60 +
extract (minute from retention) "Retention Interval"
from dba_hist_wr_control;

Pour modifier ces valeurs, il suffit d’utiliser le package suivant en spécifiant les valeurs désirées :

begin
dbms_workload_repository.modify_snapshot_settings (
interval => 60,
retention => 365*24*60
);
end;
/

Oracle a su nous faciliter la vie pour estimer la taille du tablespace SYSAUX. Il fournit un script (utlsyxsz.sql) qui évalue la taille que devrait être le tablespace SYSAUX. Son utilisation est fort simple. L'exécution s'effectue en mode interactif et on doit tout simplement fournir certaines valeurs pendant l'exécution.

Suite à l'exécution, l'estimation me recommande de définir un tablespace d'environ 60 GB. Voici un extrait de l'exécution du script :

...
~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 60,143.6 MB
| Estimated size of AWR per instance: 20,047.9 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 365.00 days
| Num Instances - 3
| Active Sessions - 20.00
| Datafiles - 139
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 182.7 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 5,205
| Indexes - 10,294
| Columns - 64,429
| Partitions - 17
| Indexes on Partitions - 51
| Columns in Partitions - 136
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
...

En passant. ce script prend en compte un environnement RAC. Lors de l'exécution, vous pouvez spécifier le nombre d'instance.

Maintenant, il reste à voir si l'estimation est réaliste.

ORA-00600 en utilisant un Database link

Je travaille à mettre en place un environnement répliqué via Oracle Streams sous Oracle 10g R2 (10.20.3). Pour ce faire, je devais créer des Database links qui seront utilisés par des vues matérialisées.

Lors de la création des vues matérialisées, j'ai rencontré l'erreur suivante :

ORA-00600: internal error code, arguments: [kzdlk_zt2 err],
[18446744073709551603], [], [], [], [], [], []

Cette erreur est provoqué par le fait que le Database link a été créé avec le mot clé "VALUES".

Pour contourner le problème, j'ai utilisé les mots clés "CONNECT TO" et "IDENTIFIED BY".

J'en profite pour vous refiler le script que j'utilise pour extraire la commande DDL d'un Database link :

REM *******************************************************************
REM * Script: extr_dl.sql
REM * Titre : Extraire un DB Link
REM * Auteur: Eric Cloutier
REM * Date modif. : 06-08-2009
REM *******************************************************************
set pagesize 0 feed off linesize 5000 trimspoo on
set verify off long 200000 longchunksize 100000
set autoprint on

accept vDl prompt 'Entrez le nom du DB Link : '

spool extr_dl_&vDl..log

Declare
ExcpNotExist exception;
pragma exception_init( ExcpNotExist, -31608 );
Begin
-- Initialisation
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

For i in (select OWNER Prop, db_link nm_objet from dba_db_links where db_link = upper('&vDl'))
LOOP
-- Extraire l'objet
Begin
dbms_output.put_line(DBMS_METADATA.GET_DDL('DB_LINK', I.nm_objet,I.prop));
Exception when ExcpNotExist then null; -- Objet inexistant';
End;
END LOOP;
End;
/

spool off
set pagesize 999 feed on linesize 5000 verify on
Prompt
Prompt Executer le script : @extr_dl_&vDl..log
Prompt

Il existe une note sur Oracle Metalink qui explique le bug (#456320.1).

Surveillance de l'OMS

Voici une façon de mettre en place un processus de surveillance de l'installation d'Oracle 10g Grid Control. À l'aide de scripts « perl », l'agent résidant sur le même serveur qu'Oracle Management Server peut envoyer un courriel lorsque l'OMS ne répond plus.

Cette méthode de surveillance est aussi nommée « Notification OOB (Out-of-Bound) ».

La configuration de ce processus a été effectuée sur un Oracle Management Server (OMS), un référentiel de base de données et un agent tous de la version 10.2.0.4 et sur un environnement Sun Solaris.

Avant de débuter, sachez que cette configuration peut être effectuée seulement sur l'agent où réside l'OMS et que vous devez aoir une configuration existante et fonctionnelle d'envoi de courriel.

Voici comment j'ai procédé pour activer la surveillance.

VÉRIFIER LA PRÉSENCE DE LA CIBLE OMS

Initialiser les variables d'environnement pour qu'elles soient sur l'ORACLE_HOME correspondant à l'agent puis exécuter la commande suivante :

AGENT_HOME/bin/emctl config agent listtargets grep oracle_emrep

Le résultat de cette commande doit être :

[Management Services and Repository, oracle_emrep]

Autrement, cela signifie que la cible correspondante à l'OMS n'est pas présente. Dans ce cas, il doit être ajouté dans le fichier « targets.xml » qui est sous le répertoire « AGENT_HOME/sysman/emd ».

Tout changement à la configuration de l'agent nécessite un redémarrage de l'agent pour la prise en compte des nouvelles valeurs.

CONFIGURATION DE L'AGENT

Le fichier de paramètres de l'agent (emd.properties) doit être modifié pour y fournir les informations relatives à l'envoi de courriel.

Ouvrir le fichier « emd.properties » qui est sous le répertoire « $AGENT_HOME/sysman/config » et indiquer des valeurs aux paramètres ci-dessous :

#
# The email address for out-of-band notifications
#
emd_email_address=prenom.nom@domaine.com
emd_email_gateway=localhost
#
# The return email address for out-of-band notifications
#
emd_from_email_address=oms_agent@domaine.com

REDÉMARRER L'AGENT

Pour que les nouvelles valeurs des paramètres soient actives, il faut redémarrer l'agent :

emctl reload agent

VÉRIFIER LE FONCTIONNEMENT

Pour vérifier le bon fonctionnement de la notification, Oracle Management Server doit être arrêté :

$ORACLE_HOME/opmn/bin/opmnctl stopall

Si tout est configuré adéquatement, vous devriez recevoir un message semblable à ceci :

Expéditeur : oms_agent@domaine.com
Objet : Severe Enterprise Manager problem
Texte du courriel :
Fri Sep 18 14:20:07 2009
Severe Enterprise Manager problem
Error message: No active Management Services were found

RÉFÉRENCES

Oracle Metalink, Note 429257.1

Voyage dans le... passé

La mise à niveau d'une base de données Oracle nous force à se questionner sur la possibilité d'un retour arrière. En effet, malgré que nous prenions toutes les précautions possibles et que
nous testions les applications, un problème important peut surgir et nous forcer à réaliser un retour arrière vers les versions de bases de données originales.

Le retour arrière n'est jamais l'idéal sauf qu'il vaut mieux prévoir une méthode afin de minimiser les impacts et les problèmes. C'est à ce moment que la fonctionnalité "FLASHBACK DATABASE" attire mon attention.

Flashback Database permet de ramener la base de données entière à un état dans le passé. Elle utilise des journaux (Flashback log) et, quand elle est activée, ces journaux sont créés à l'emplacement nommé « flash recovery area ». Oracle s'occupe de créer, détruire et redimensionner les journaux automatiquement. À l'occasion, il faut vérifier l'espace qui est disponible pour le flashback area.

Pour utiliser le « flashback database », le « flash recovery area » doit être mis en place.

Voici quelques prés requis :

ACTIVATION DES FONCTIONNALITÉS
Pour utiliser la fonctionnalité « FLASHBACK DATABASE », Le mode d'archive et la fonctionnalité « flashback database » doit être activé sur la base de données.

Voici comment vérifier l'état des fonctionnalités :

SELECT flashback_on, log_mode
FROM v$database;

Vérifier la valeur des paramètres lies à la fonctionnalité « flashback
database » :

col name format A30 wrap
col value format A20 wrap
SELECT inst.instance_name, parm.name, parm.value
FROM gv$parameter parm, gv$instance inst
WHERE parm.inst_id = inst.inst_id
and (name LIKE '%flashback%'or name LIKE '%recovery%')
order by 1,2;

Initialiser les paramètres pour le « Flashback » :

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+FRAGRP01' SCOPE= BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G SCOPE=BOTH;

Fermer l'instance :

Si c'est une instance faisant partie d'un cluster (RAC), veuillez fermer
toutes les instances en suivant l'exemple ci-dessous :

srvctl stop database -d BD

Autrement, fermer l'instance comme suit:

shutdown immediate

Démarrer l'instance en mode « mount »

startup mount exclusive;

Activer le mode ARCHIVE (si nécessaire)

Alter database archivelog;

Activer la fonctionnalité « FLASHBACK DATABASE »

Alter database flashback on;

CRÉATION DU POINT DE RESTAURATION
Un point de restauration doit être marqué dans le temps pour être en mesure de le référer dans l'éventualité où nous en aurions besoin. Ce point de restauration doit être créé avant la migration de la base de données.

Voici comment procéder pour créer le point de restauration :

Fermer l'instance :

shutdown immediate

Démarrer l'instance en mode « mount » :

startup mount exclusive;

Créer le point de restauration :

CREATE RESTORE POINT avant_upgrade GUARANTEE FLASHBACK DATABASE;

Vérifier l'existence de point de restauration créé précédemment :

SELECT NAME, SCN, TIME,
DATABASE_INCARNATION# DI,
GUARANTEE_FLASHBACK_DATABASE,
STORAGE_SIZE FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

Vérifier l'état actuel des composantes de la base de données :

select comp_name, status, version from dba_registry;

Fermer l'instance :

shutdown immediate

À partir de ce point, la migration peut débuter.

PARTICULARITÉS
Il ne faut pas modifier la valeur du paramètre « COMPATIBLE ». Autrement, le mécanisme de retour arrière peut ne pas fonctionner adéquatement. Le changement de valeur va identifier tous les fichiers de la base de données comme étant des fichiers de la nouvelle version et, vous
observerez une erreur semblable lors du démarrage de l'instance :

ORA-00201: control file version 10.2.0.4.0 incompatible with ORACLE version
10.2.0.3.0
ORA-00202: control file: '+FRAGRP01/orcl/controlfile/current.263.660837815'

RESTAURATION
Si vous devez ramener votre base de données à l'état où elle était avant la migration, il vous suffit de suivre ces étapes :

Fermer l'instance:

shutdown immediate

Démarrer l'instance en mode « mount » :

startup mount

Restaurer la base de données au point de restauration :

Flashback database to restore point avant_upgrade;

Fermer l'instance :

shutdown immediate

N'oubliez pas de réinitialiser toutes les variables d'environnement incluant ceux des autres noeuds si vous êtes sur un environnement RAC.

Après avoir réinitialisé les variables d'environnements, il faut :

Démarrer l'instance:

startup mount

Ouvrir la base de données en initialisant les journaux :

Alter database open resetlogs;

Vérifier l'état actuel des composantes de la base de données :

select comp_name, status, version from dba_registry;

N'oubliez pas de reprendre une copie de sauvegarde de la base de données.

DÉSACTIVATION DE LA FONCTIONNALITÉ
Pour désactiver la fonctionnalité « FLASHBACK DATABASE », voici les étapes à
suivre :

Voici comment le vérifier :

SELECT flashback_on FROM v$database;

Fermer l'instance :
Si c'est une instance faisant partie d'un cluster (RAC), veuillez fermer toutes les instances en suivant l'exemple ci-dessous :

srvctl stop database -d BD

Autrement, fermer l'instance comme suit:

shutdown immediate

Démarrer l'instance en mode « mount » :

startup mount exclusive;

Désactiver la fonctionnalité « FLASHBACK DATABASE » :

Alter database flashback off;

Cette solution de retour en arrière s'applique seulement dans le cas où la migration ne s'effectue pas correctement. Toutes les transactions suivant la migration, incluant celles provenant des applications maisons, seront perdues si un retour en arrière est effectué.

Arrêter de vous compliquer la vie... Keep it simple !

Depuis quelques temps, j'effectue des essais de performance sur des bases de données 10.2.0.4 et 10.2.0.3. Je compare plusieurs résultats obtenus suite à l'exécution de requêtes SQL et des blocs PL/SQL.

Malheureusement, les résultats démontrent que 10.2.0.4.0 est plus lent dans plusieurs situations... je trouve ça très dommage car j'en ai besoin pour utiliser certaines fonctionnalités de cette version.

Aujourd'hui, un de mes collègues m'a donné un coup de main et il m'a demandé si j'avais refait le calcul des statistiques système sur la base de données suite à la migration de celle-ci à 10.2.0.4. Je lui ai répondu que oui mais, pour éliminer tout doute, je lui ai dit qu'il pouvait les recalculer et refaire des essais.

Suite à cette mise à jour des statistiques, le temps réponse des requêtes figurant dans mes essais de performance ont diminué magistralement. Certaines s'exécutent en 2 à 3 fois moins de temps. Je n'y comprennait rien !?!

J'ai alors demandé à mon collègue de me montrer la commande qu'il avait utilisée pour refaire les statistiques système. Je constatait qu'il exécutait la même commande que moi mais sans aucun paramètre... donc, il utilisait les valeurs par défaut de ceux-ci. Le mode de collecte par défaut est "NOWORKLOAD".

exec dbms_stats.gather_system_stats;

Quand j'effectuais une collecte, je la planifiais pour qu'elle s'échelonne sur une période significative de charge de travail pour avoir des statisitques représentatives... finalement pas sûre que ça valait le coup de me cassé la tête à exécuter, par exemple, ceci :

exec dbms_stats.gather_system_stats(gathering_mode=>'START', stattab=>'stats', statid=>'stats', statown=>'SYS');

À l'avenir, je vérifierai le mode simple (sans aucun paramètre) et le mode avancé pour la collecte des statistiques système.

Jouons à cache-cache

À l'occasion, il peut être très payant de conserver en mémoire certains objets qui sont accédés fréquemment.

Objets PL/SQL
Les objets sont conservés dans l'espace mémoire partagé (shared pool) en utilisant le package « dbms_shared_pool ». Ce dernier doit être installé à partir du script « dbmspool.sql ».

Voici comment mettre en mémoire un objet :

execute dbms_shared_pool.keep('propriétaire.nom_objet');

Pour lister tous les objets qui sont conservés dans l'espace mémoire partagé :

select owner,name,type,sharable_mem from v$db_object_cache where kept='YES';

Pour identifier les objets qui pourrait être conservés en mémoire :

select substr(owner,1,10)'.'substr(name,1,35) "ObjectName",
type, sharable_mem,loads, executions, kept
from v$db_object_cache
where type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
and executions > 0
order by executions desc,loads desc,sharable_mem desc;

Certains objets appartenant à SYS pourraient être des candidats très intéressants, dépendamment de leur utilisation.

Tables et Indexes

Initialiser le cache « KEEP »

Alter system set db_keep_cache_size = 208M scope=spfile;
-- Peut être très intéressant pour les grosses tables qui sont chargées à 'occasion. Les données misent en cache n'affecteront pas les caches habituelles.
-- alter system set db_recycle_cache_size = 50M;
shutdown immediate
startup

Générer la commande mettant les tables candidates en cache « KEEP »

select 'Alter table 'owner'.'table_name
' storage (buffer_pool keep);', num_rows
from dba_tables
where owner in ('SCHEMA_1','SCHEMA_2')
and table_name in ('TABLE_1','TABLE_2')
order by num_rows
/

Générer la commande mettant les indexes candidates en cache « KEEP »

select 'Alter index 'owner'.'index_name
' storage (buffer_pool keep);', num_rows
from dba_indexes
where owner in ('SCHEMA_1','SCHEMA_2') and table_name in
('TABLE_1','TABLE_2')
order by num_rows
/

Charger les données dans le pool « KEEP »

select 'Select /*+ FULL(TABL) */ * from '
owner'.'table_name' TABL;'
from dba_tables
where owner in ('SCHEMA_1','SCHEMA_2')
and table_name in ('TABLE_1','TABLE_2')
order by num_rows
/

Vérification interminable du contenu d'un fichier log

Suite à une migration de base de données, le log de la mise à jour du catalogue d'Oracle (catupgrd.sql) est assez volumineux et il est primordiale de le fouiller pour s'assurer qu'aucune erreur n'a été rencontrée.

Pour me simplifier la tâche, j'effectue cette simple commande sous Unix :

cat mig10204.log grep ORA-

La commande affiche toutes les lignes qui comportent la chaîne "ORA-".

Dire que je cherchais une façon beaucoup plus compliqué et, j'avais la solution en plein devant les yeux...

Extraction des commandes DDL d'un schéma Oracle

Dernièrement, j'ai eu à déplacer un système sur différentes bases de
données. J'ai alors conçu un petit script pour me simplifier la vie pour la
création de schéma. Ce script génère un script contenant les commandes DDL
pour créer un schéma, ses droits et ses synonymes :

REM *********************************************************
REM * Script: extr_schm.sql
REM * Titre : Extraire les schemas
REM * Auteur: Eric Cloutier
REM * Date modif. : 06-08-2009
REM * Exemple : @extr_schm MDSYS
REM *********************************************************
set pagesize 0 feed off linesize 200 trimspoo on verify off
spool extr_schm_&1..log
DECLARE
i number;
ValResult varchar2(1000);
BEGIN
FOR I in (Select du.username from dba_users du
where du.username like '&1%'
order by username)
LOOP
-- Schema
dbms_output.put_line('-- Schema');
Select 'CREATE USER 'usernamechr(10)
'IDENTIFIED BY VALUES '''password''''chr(10)
'DEFAULT TABLESPACE 'default_tablespacechr(10)
'TEMPORARY TABLESPACE 'temporary_tablespacechr(10)
'PROFILE 'profilechr(10)
'ACCOUNT UNLOCK;'chr(10)
into ValResult
FROM DBA_USERS
WHERE username = i.username;
dbms_output.put_line(ValResult);
-- Privileges sur objets
dbms_output.put_line('-- Privileges sur objets');
For J in (SELECT 'GRANT ' m$.name
decode(m$.name,
'READ', ' ON DIRECTORY ',
'WRITE',' ON DIRECTORY ',
' ON ')
lower(uo$.name) '.' lower(o$.name)
' TO ' lower(ue$.name)
decode(NVL(t$.option$,0), 1, ' WITH GRANT
OPTION;',';') cmd_sql
FROM sys.objauth$ t$,
sys.obj$ o$,
sys.user$ ur$,
sys.table_privilege_map m$,
sys.user$ ue$,
sys.user$ uo$
WHERE o$.obj# = t$.obj#
AND t$.privilege# = m$.privilege
AND t$.col# IS NULL
AND t$.grantor# = ur$.user#
AND t$.grantee# = ue$.user#
AND o$.owner# = uo$.user#
-- Grant de SYS
AND ue$.name = i.username
order by ue$.name, uo$.name, o$.name, sequence#)
LOOP
dbms_output.put_line(j.cmd_sql);
END LOOP;
-- Roles
dbms_output.put_line('-- Roles');
For J in (select 'GRANT 'drp.granted_role' to '
drp.grantee';' cmd_sql
from dba_role_privs DRP
where grantee = i.username)
LOOP
dbms_output.put_line(j.cmd_sql);
END LOOP;
-- PrivilÞges SystÞme
dbms_output.put_line('-- Privileges Systeme');
For J in (select 'GRANT 'privilege' to '
dsp.grantee';' cmd_sql
from dba_sys_privs DSP
where grantee = i.username)
LOOP
dbms_output.put_line(j.cmd_sql);
END LOOP;
-- Default Role
dbms_output.put_line('-- Default Role');
dbms_output.put_line('Alter user 'i.username
' default role all;');
-- Synonymes
dbms_output.put_line('-- Synonymes');
For J in (Select 'Create or Replace synonym '
owner'.'synonym_name
' for 'table_owner'.'
table_name
decode(DB_LINK,null,';','@'
DB_LINK';') cmd_sql
from dba_synonyms
where owner = i.username
order by synonym_name)
LOOP
dbms_output.put_line(j.cmd_sql);
END LOOP;
dbms_output.put_line('--
===========================================================');
END LOOP;
END;
/
spool off
set pagesize 40 feed on linesize 5000
Prompt
Prompt -- Executer le script : @extr_schm_&1..log
Prompt

Optimisation de requête utilisant DB Link

Je ne sais pas si c'est un problème généralisé mais sachez que j'ai dû utiliser le hint "driving_site" pour améliorer le temps d'exécution d'une requête.

Dernièrement, nous avons migrés certaines bases de données à la version 10.2.0.4 et un utilisateur m'a mentionné que le temps d'exécution de l'une de ses requêtes nécessitait plus de 15 secondes. Auparavant, elle se terminait en moins d'une seconde... Ouch!

Après avoir vérifié le plan d'exécution de la requête, j'ai remarqué qu'à l'étape qui accède à une vue sur une autre base de données, nécessitait beaucoup de temps. J'ai pu observé ce temps en utilisant mon script d'analyse de performance de requête. Le voici :

-- À désactiver sinon ne fonctionnera pas
set serveroutput off

ALTER SESSION SET STATISTICS_LEVEL = ALL;
-- OU Ajouter le hint /*+ gather_plan_statistics */ dans la requete

-- ----------------------------------------------
-- Requête à optimiser
-- ----------------------------------------------
Select * from dual;

-- ****************
-- Plan d'execution
-- ****************
Prompt -- ==============================================================
Prompt -- Starts : nombre de fois que l'etape est executee
Prompt -- E-Rows : nombre de lignes que l'optimisateur estime retourner
Prompt -- E-Time : temps prevu par l'optimisateur pour accomplir l'étape
Prompt -- A-Rows : nombre de lignes retournees par la requete
Prompt -- A-Time : temps ecoule pour chaque etape
Prompt -- Buffers : nombre d'operations au buffer cache
Prompt -- E-Rows et A-Rows doivent etre egaux.
Prompt -- ==============================================================
--
-- 'All' : Affiche la section "Query block/Object Alias" , information sur les Predicate, et projection de colonne
-- après le plan.
-- 'Advanced': Comme l'option 'All', mais inclut l'infornation sur les
"Outline".
-- 'Outline' : Affiche seulement l'information "Outline" et l'information
sur les predicate après le plan.
--
-- Exemples :
-- dbms_xplan.display_cursor(null, null,'ADVANCED +ALLSTATS LAST
+MEMSTATS LAST')
-- dbms_xplan.display_cursor(null, null,'ADVANCED +MEMSTATS LAST')
-- dbms_xplan.display_cursor(null, null,'BASIC IOSTATS -ROWS LAST')
-- dbms_xplan.display_cursor(null, null,'ALLSTATS LAST')
--
select * from table(
dbms_xplan.display_cursor(null, null, 'ADVANCED +ALLSTATS LAST'));

Suite à l'exécution, le temps sous la colonne "A-Time" lors de l'accès à la table distante m'a clairement indiqué que c'est à cet endroit que la requête perd son temps.

Il n'y a pas 56 façons d'optimiser des requêtes qui mette en jointure des tables sur différentes instances alors, j'ai opté rapidement à utiliser le hint "Driving_Site"

À l'occasion, ramener la table distante sous la forme d'un énoncé SQL au niveau du SELECT comme si c'était une colonne, peut être très bénéfique. Par contre, il faut que cette solution s'y prête tel que dans un cas où la requête est exécutée à quelques reprises et non des milliers de fois.

Composante "Oracle Database Packages and Types" invalide dans DBA_REGISTRY

Après l'exécution du script « catupgrd.sql », on doit vérifier le statut des composantes avec la requête suivante :

Select comp_name,version,status
from sys.dba_registry;

Lors de la vérification des celles-ci sur une instance fraichement migrée, il s'est avéré que la composante « Oracle Database Packages and Types » était invalide.

Après vérification du statut des objets de la base de données, le package DBMS_SQLPA était invalide.

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba

-- Detruire la table si elle a été mise à jour
drop table plan_table$;
-- Detruire la PLAN_TABLE du schema si elle existe
drop table plan_table;
-- Recréer la PLAN_TABLE
@catplan.sql
-- Recharger les packages impactés
@dbmsxpln.sql
@prvtxpln.plb
@prvtspao.plb
-- Recompiler les objets invalides en tenant compte des dépendances
@utlrp

Référence : Oracle Metalink, Note 605317.1

ORA-00600 suite à une migration à 10.2.0.4.0

Suite à la migration d'une base de données de la version 10.2.0.3.0 à 10.2.0.4.0, l'exécution du script de recompilation "utlrp.sql" provoquait l'erreur suivante :

ORA-00600: internal error code, arguments: [psdmsc.c: spawned type invalid],
[], [], [], [], [], [], []

Pour résoudre ce problème, il suffit de procéder comme suit :

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
shutdown immediate
startup upgrade
@utlirp

Declare
MASK constant varchar2(80) := 'SYS_PLSQL_[0-9]+_([0-9]+DUMMY)_[12]';
dep_count number;
Begin
select count(*) into dep_count from dba_dependencies
where (referenced_owner, referenced_name, referenced_type) in
(select owner, object_name, object_type from dba_objects
where object_type = 'TYPE'
and regexp_like(object_name, MASK))
and not (type = 'TYPE' and regexp_like(name, MASK));

if (dep_count > 0) then
raise_application_error(-20001,
'Unknown dependent objects on system-generated types');
end if;

for r in (select owner, object_name from dba_objects
where object_type = 'TYPE'
and regexp_like(object_name, MASK))
loop
execute immediate 'drop type "'r.owner'"."'r.object_name
'" force';
end loop;
End;
/

Shutdown immediate
startup
@utlrp.sql

Réf. : Oracle Metalink, Note 726623.1

Extraire et émettre les mêmes droits (permissions) Java

Voici une petite requête fort simple mais qui m'a sauvé du temps. Je devais émettre les mêmes droits d'un schéma sur une autre base de données. Les droits en questions étaient ceux liés aux permissions Java :

select 'EXEC dbms_java.grant_permission('''DJP.GRANTEE''', '''DJP.TYPE_SCHEMA':'DJP.TYPE_NAME''', '''DJP.NAME''', '''DJP.ACTION''');' from DBA_JAVA_POLICY DJP
/

Pour comparer les résultats entre les 2 bases de données, j'ai ajouté cette clause :

where not exists
( select 'x'
from DBA_JAVA_POLICY@DBLINK DJP2
where 'EXEC dbms_java.grant_permission('''DJP2.GRANTEE''', '''
DJP2.TYPE_SCHEMA':'DJP2.TYPE_NAME''', '''DJP2.NAME''', '''DJP.ACTION''')'=
'EXEC dbms_java.grant_permission('''DJP.GRANTEE''', '''
DJP.TYPE_SCHEMA':'DJP.TYPE_NAME''', '''DJP.NAME''', '''DJP.ACTION''')'
)
/

Je n'ai pas vérifié mais il existe peut-être une façon d'extraire ces droits avec le package DBMS_METADATA. C'est à voir !

Autentification sous Windows avec SYS

Ça faisait un bon bout de temps que je n'avais pas créé une base de données sous un environnement Windows.

Après en avoir créée une avec l'assistant DBCA, j'ai ouvert une fenêtre de lignes de commandes pour démarrer une session Oracle sous le compte SYS. Tout d'abord, j'ai débuté par initialisé mes variables d'environnement ORACLE_SID, ORACLE_HOME et j'ai ajouté le répertoire BIN au PATH.

Évidemment, je me suis frappé au même foutu problème que j'avais déjà rencontré auparavant :

E:\Oracle\oradb111>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mar. Avr. 21 21:14:21 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:ORA-01031: privilÞges insuffisants

À ce moment, je me suis gratté la tête et je me suis dit: "Qu'est-ce qui faut que je fasse pour que ça fonctionne ?"

Je me souvenais qu'il y avait un paramètre à configurer dans le fichier "SQLNET.ORA" mais lequel ? Alors, pour me rafraichir la mémoire, j'ai "googolisé" avec des mots clés. Je suis alors tombé sur ce qui me manquait.

Il suffit de mettre la ligne suivante dans le fichier SQLNET.ORA pour être en mesure de s'authentifier avec le compte SYS sans mot de passe :

SQLNET.AUTHENTICATION_SERVICES=(NTS)

De plus, votre compte Windows doit être membre du groupe "ORA_DBA" autrement, vous ne pourrez pas établir une connexion.

La prochaine fois, j'aurais mon blog pour me le rappeler !

L'agent a perdu la tête !

Salut,

Dernièrement, j’ai migré l’OMS (Oracle Management Service) ainsi que tous les agents à la version 10.2.0.4.0. Et, actuellement, nous sommes entrain de migrer les bases de données à la version 10.2.0.4.0.

Depuis que certaines bases de données sont passées à la nouvelle version (10.2.0.4.0), les agents sur les hôtes réagissent d’une façon très étrange. En l’espace de 2 à 3 minutes, le mécanisme de notification m’informe que l’agent ne peut pas être contacté puis, par la suite, je reçois un autre message m’indiquant que le problème est résolu.
Voici des exemples de messages de notification reçus :

Agent is Unreachable (REASON = Connection refused) but the host is UP.
Agent is Unreachable (REASON = Received unexpected response text : EMDClient request Error:nmemdisp_main Internal Error)

Ce message provenant de la notification n’indique absolument rien de précis. Je me suis pencher sur le problème et en fouillant davantage dans le fichier de trace de l’agent, j’ai trouvé une erreur qui se répétait à plusieurs reprises :

2009-03-30 16:08:37,105 Thread-1931 ERROR upload: nmehursf_logError:lfiflu failed -2 rawdata.dat 2009-03-30 16:08:37,105 Thread-1931 ERROR upload: rawdata.dat rename failed 2009-03-30 16:08:37,105 Thread-1931 ERROR upload: rawdata.dat deleted, it will not be merged 2009-03-30 16:08:37,105 Thread-1931 ERROR upload: ERROR: nmehursf_Rowset_write - lfiopn failed -2 rawdata.dat 2009-03-30 16:08:37,106 Thread-1931 ERROR upload: Error happened in nmehursf_Rowset_write:lfiopn, error = 24: Too many open files for rawdata.dat

Comme toute bonne chose à une fin, ce problème est connu chez Oracle et, un patch est disponible.

L’agent nécessite qu’un correctif soit installé sur les cibles de type base de données de la version 10.2.0.3.0. Ce correctif porte le numéro #5872000. Le correctif s’intitule :

HEALTHCHECK ERROR OCCURS FOR 32BIT DATABASE ON 64BIT OS DUE TO BUG4526916 FIX

Ce problème est perceptible seulement si l’agent doit surveiller des bases de données 10.2.0.3.0 et 10.2.0.4.0 sur un même hôte.

RMAN ne veut rien savoir, "Incarnation is not current"

Hier après-midi, je m’apprêtais à faire des essais de sauvegarde/récupération sur un serveur Unix.

Après avoir enregistré les bases de données MIG10203 et MIG10204 au catalogue RMAN, j’ai eu un problème lors d’une tentative de sauvegarde.

J’obtenais l’erreur suivante :

RMAN-20011: target database incarnation is not current in recovery catalog

Après quelques recherches, je me suis rendu compte que l’identifiant unique de la base de données (DBID) était le même pour les 2 bases de données.

C’est normal car la base de données MIG10204 a été créée en dupliquant manuellement la base de données MIG10203.

Donc, pour corriger le tout, j’ai désinscrit les 2 bases de données :

RMAN> unregister database;

Puis, j’ai suivi les étapes ci-dessous pour changer le DBID d’une des bases de données :

1. Prendre une copie de sécurité de la base de données

2. Fermer la base de données

shutdown immediate

3. Démarrer en mode « mount »

startup mount

4. démarrer une session Unix et exécuter "NID" avec les privilèges SYSDBA

$ nid TARGET=SYS/password@MIG10204

5. Fermer la base de données

shutdown immediate

6. si non fait, initialiser le paramètre "db_name" au nom désiré

7. créer un fichier de mot de passe

orapwd file=/juliet_u01/home/dba/oracle/product/10.2.0.4.0/dbs/orapwmig10204 password= entries=20

8. Démarrer et ouvrir la base de données en réinitialisant les journaux

startup mount
alter database open resetlogs

Archiver error... Quel est la cause ?

Durant le weekend, une base de données a rencontrée des problèmes. Elle générait plein d'écritures donc, plein de fichiers d'archive jusqu’à remplir le disque réservé aux archives.

J'avais remarqué que c'était une job (DBMS_JOB) qui exécutait un rafraichissement de vues matérialisées via un groupe de rafraichissement. Voici le code exécuté par la job :

dbms_refresh.refresh('"ABC"."ABC_GRP_REPLC"');

La job plantait et elle s'exécutait de nouveau pour effectuer une reprise automatique. Je l’ai donc interrompu, le temps de trouver la cause exacte.

Pour débuter, j’ai tenté de rafraichir les vues, à tour de rôle, pour finalement rencontrer cette erreur lors du rafraichissement manuelle d'une d'entre-elle :

ABC@ORCL> exec dbms_mview.refresh('ABC.ABC_V_DIRCT_TERRT_GENRL','C');
ERROR:
ORA-03114: pas connecté à ORACLE

BEGIN dbms_mview.refresh('ABC.ABC_V_DIRCT_TERRT_GENRL','C'); END;

*
ERROR at line 1:
ORA-03113: fin de fichier sur canal de communication
Process ID: 0
Session ID: 412 Serial number: 3177


Suite à cette erreur rencontré dans l'outil SQL*Plus, j’ai consulté le fichier « alertSID.log » de la base de données et, j’ai trouvé cette erreur :

ORA-07445: exception encountered: core dump [qcdlgcd()+116] [SIGSEGV] [Address not mapped to object] [0x000000037] [] []

Après avoir faire quelques recherches, je suis tombé sur la note 459323.1 du site Oracle Metalink qui explique ce problème. La cause provient de l’énoncé SQL qui constitue la vue matérialisée. Cet énoncé est invalide. Elle fait référence à une colonne qui n’existe plus. Pour résoudre ce problème, on doit détruire et recréer la vue matérialisée.

Étape de résolution :

- Détruire la vue matérialisée
DROP MATERIALIZED VIEW "ABC"."ABC_V_DIRCT_TERRT_GENRL";

- Créer la vue matérialisée
CREATE MATERIALIZED VIEW "ABC"."ABC_V_DIRCT_TERRT_GENRL"
TABLESPACE "ABC_D01"
USING INDEX TABLESPACE "ABC_D01"
REFRESH FORCE AS
SELECT…

- Rétablir les droits sur la vue matérialisée
GRANT SELECT ON ABC.ABC_v_dirct_terrt_genrl TO abcpool;

- Ajouter la vue matérialisée au groupe de rafraichissement
BEGIN
DBMS_REFRESH.ADD(
name => '"ABC"."ABC_GRP_REPLC"',
list => '"ABC"."ABC_V_DIRCT_TERRT_GENRL"',
lax => TRUE);
END;
/

- Exécuter la job
exec DBMS_JOB.RUN(job => 372);

ORA-07445 sur compilation avec UTLRP

La recompilation avec l’utilitaire « UTLRP » peut planter avec l’erreur suivante :

ORA-07445: exception encountered: core dump [kglsget()+140] [SIGSEGV] [Address not mapped to object] [0x000000008] [] []

Cette erreur est causé par un manque de privilège sur un objet, par exemple une table, qui est référé dans un objet PL/SQL (procédure, fonction, package, trigger).

C’est un bug connu chez Oracle. Pour contourner le problème, il suffit de donner les droits manquants au schéma concerné.

Ce problème a été observé sur une base de données 10.2.0.3.0 sous Sun Solaris.

Pour plus de détails, voir la note « 465095.1 » sur Oracle Metalink

Spatial : Vérifier la présence des métadonnées et index

Lorsqu'on manipule des données spatiales, des métadonnées doivent être définies et insérées dans la table "USER_SDO_GEOM_METADATA".

Pour me simplifier la vie, j'ai conçu un requête qui me permet de vérifier la présence de ces métadonnées pour chaque colonne de type spatiale. Si elle est absente, la requête ci-dessous retourne un enregistrement :

Select 'TABLE' Type_Obj,utc.TABLE_NAME, utc.COLUMN_NAME
from user_tab_columns utc,
user_tables ut
where utc.data_type = 'SDO_GEOMETRY'
and ut.table_name = utc.table_name
and not exists
(
select 'x'
from user_sdo_geom_metadata usgm
where usgm.table_name = utc.table_name
and usgm.column_name = utc.column_name
)
Union
Select 'VUE',utc.TABLE_NAME, utc.COLUMN_NAME
from user_tab_columns utc,
user_views ut
where utc.data_type = 'SDO_GEOMETRY'
and ut.view_name = utc.table_name
and not exists
(
select 'x'
from user_sdo_geom_metadata usgm
where usgm.table_name = utc.table_name
and usgm.column_name = utc.column_name
)
order by 1,2,3;


Dans la même ordre d'idée, un index de type "DOMAIN" est nécessaire pour accéder aux données spatiales d'une colonne. La requête suivante me permet de vérifier la présence d'un index sur toutes les colonnes de type spatiale pour un schéma donné :

Select *
from user_tab_columns utc,
user_tables ut
where utc.data_type = 'SDO_GEOMETRY'
and ut.table_name = utc.table_name
and not exists
(
select 'x'
from user_ind_columns uic, user_indexes ui
where uic.table_name = utc.table_name
and uic.column_name = utc.column_name
and uic.table_name = ui.table_name
and uic.index_name = ui.index_name
and ui.index_type = 'DOMAIN'
)
order by utc.table_name, utc.column_name;


Ce n'est pas des requêtes très complexes mais elles m'évitent bien des problèmes.

Créer une base de données à l'image d'une existante

Je devais recréer (écraser) une base de données déjà existante. Pour me simplifier la vie, j'ai utilisé l'assistant de configuration de base de données.

Cet assistant me permet de générer un modèle (template) basé sur une installation. Il suffit d'exécuter cette ligne de commandes :

./dbca -silent -createTemplateFromDB -sourceDB -sysDBAUserName sys -sysDBAPassword -templateName .dbt -maintainFileLocations true

Ensuite, on doit exécuter une commande qui permettra de généréer les scripts que nous pourrons exécuter pour crééer la nouvelle base de données :

./dbca -silent -generateScripts -templateName .dbt -gdbName -scriptDest /tmp/oracle

Notification sur un tablespace... inexistant !

En testant la notification à propos de l'espace utilisé des tablespaces, je me suis trouvé à provoquer un dépassement de seuil pour recevoir un message électronique.

Le message concernait un tablespace qui consommait plus que 85% de l'espace alloué.

Après avoir constaté que ça fonctionnait correctement, j'ai détruit le tablespace dont le seuil a été dépassé.

À ma grande surprise, j'ai continué à recevoir des messages électroniques même si le tablespace a été complètement détruit !?!

J'ai alors décidé de recréer le tablespace pour voir comment ça allait se comporter. Après quelques minutes, je ne recevais plus de messages. Afin d'être complètement certain, j'ai attendu près de 24 heures avant de détruire à nouveau le tablespace.

Probleme de notification : Tablespace Space Used % sur instance RAC

Depuis quelque temps, je soupçonnais des problèmes de notification avec les instances RAC définies dans Oracle EM Grid Control.

Je me suis alors bâtit un cas d'essai. J'ai créé un tablespace contenant une seule table. Celle-ci a été remplie jusqu'au bouchon en m'aidant de la vue DBA_OBJECTS. (Insert into ... Select * from dba_objects)

La requête ci-dessous permet d'afficher les alertes que la base de données a rencontrées :

SELECT REASON
, METRIC_VALUE
, MESSAGE_TYPE
, TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS')
, HOST_ID
FROM SYS.DBA_OUTSTANDING_ALERTS;

J'y ai alors trouvé une alerte à propos du tablespace créé pour mon cas d'essai :

REASON
-------------------------------------------------------
METRIC_VALUE MESSAGE_TYPE TO_CHAR(CREATION_TIM HOST_ID
------------ ------------ -------------------- --------
Tablespace [TEST_OEM_EC] is [90 percent] full
90 Warning 28-FEB-2009 20:48:04 MOMSVR


Étrange! l'alerte est inscrite dans la base de données mais elle n'est pas envoyé vers Enterprise Manager ?!?

J'ai décidé de vérifier si l'agent était ble et bien enregistrer à la base de données ORCL :

select agent_name from SYSTEM.AQ$_INTERNET_AGENTS order by agent_name;

Cette requête doit retourner au minimum un enregistrement qui contient un nom d'agent dont le nom est constitué du nom de l'hôte, du port et du SID de la base de données.

Ex : MOMSRV_1830_ORCL1

Ce n'était pas mon cas... La requête m'a affiché un autre agent avec un port différent. En fait, c'était un ancien agent qui fut déjà installé auparavant.

J'ai finalement trouvé de l'information à propos d'un bug à ce sujet et Oracle recommande de faire ce qui suit et ce, même si la configuration est déjà présente dans Oracle EM Grid Control.

Dans la console EM :

1. Cliquer sur le "cluster" en question
2. Au bas de la page, cliquer sur le lien intitulé "Monitoring Configuration"
3. Parcourir les étapes de configuration même si elles ont déjà été faites

Sur chacun des noeuds :

$ emctl stop agent
$ emctl start agent
$ emctl clearstate agent

J'ai refait mon test de notification et tout à fonctionné correctement.

Pour terminer en beauté, j'ai décidé de supprimer l'ancien agent inscrit. Pour ce faire, j'ai exécuté la commande suivante :

exec dbms_aqadm.DROP_AQ_AGENT('MOMSRV_3872_ORCL1');

Migrer une base de données vers un autre ORACLE_HOME

J'ai eu à migrer une bases de données 10.2.0.3 à 10.2.0.4.

La façon préconisée a été d'installer un nouveau "ORACLE_HOME" et tant qu'à faire, j'en ai profité pour déployer le CPU d'Oracle (Critical Patch Update Jan2009).

Installation
1. Installer le logiciel "Oracle Database 10.2.0.1"
2. Installer le patchset "Oracle Database 10.2.0.4"
3. Installer le "CPU January 2009"

Migration
1. Arrêt de la base de données

$ sqlplus / as sysdba
SQL> shutdown immediate

2. Création d'un fichier de paramètres PFILE à partir du SPFILE

$ sqlplus / as sysdba
SQL> create pfile='ORACLE_HOME/dbs/initORCL.ora' from spfile;
$ cd ORACLE_HOME/dbs
$ cp initORCL.ora NOUV_ORACLE_HOME/dbs/initORCL.ora

3. Editer le PFILE et modifier les paramètres dont ceux qui pointent dans l'ancien ORACLE_HOME ( compatible, optimizer_features_enable, etc...)

$ cd NOUV_ORACLE_HOME/dbs
$ vi initORCL.ora

4. Modifier le fichier ORATAB

Changer l'ORACLE_HOME correspondant à la base de données

$vi oratab
ORCL:NOUV_ORACLE_HOME:Y

5. Modifier le fichier de configuration du listener

(SID_DESC =
(GLOBAL_DBNAME = ORCL.world)
(ORACLE_HOME = NOUV_ORACLE_HOME)
(ENVS = 'LD_LIBRARY_PATH=NOUV_ORACLE_HOME/lib')
(SID_NAME = ORCL)

6. Redémarrer le listener

$ lsnrctl reload lsnr1020

7. Création des liens pour des fichiers (optionel)

Si vous n'utilisez pas les emplacements par défaut de certains fichiers (c'est mon cas!) alors vous devez créer des liens symboliques sous Unix.

Il suffit de se positionner dans le répertoire par défaut puis d'exécuter la commande:

- Pour l' ALERTSID.LOG
cd NOUV_ORACLE_HOME/rdbms/log
ln -s REPERTOIRE_DE_DESTN/alert_ORCL.log alert_ORCL.log

- Pour les fichiers de configuration (PFILE, SPFILE)
cd NOUV_ORACLE_HOME/dbs
ln –s REPERTOIRE_DE_DESTN/initORCL.ora initORCL.ora
ln –s REPERTOIRE_DE_DESTN/spfileORCL.ora spfileORCL.ora

8. Créer le fichier de mot de passe

$ orapwd file=NOUV_ORACLE_HOME/dbs/orapwORCL password= entries=20

9. Démarrer la base de données sous le nouveau ORACLE_HOME

$ sqlplus / as sysdba
SQL> create spfile from pfile='NOUV_ORACLE_HOME/dbs/initORCL.ora';

10. Démarrer la migration de la base de données

SQL> startup upgrade
SQL> SPOOL upgrade_info.log
SQL> @?/rdbms/admin/utlu102i.sql
SQL> SPOOL OFF
-- Vérifier le log précédent pour tout problème

SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
-- Vérifier le log précédent pour tout problème

11. Redémarrer la base de données en mode normal

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

12. Recompiler les objets invalides

SQL> @?/rdbms/admin/utlrp.sql

13. Vérifier le statut des composantes de la base de données

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;


Cette façon permet de réinstaller proprement le logiciel de la base de données. Aussi, l'avantage de celle-ci est que si votre ORACLE_HOME actuel est partagé par plusieurs bases de données, vous pourrez migrer une base de données sans impacter les autres. Cette méthode peut aussi être utilisé pour déplacer une base de données vers un autre ORACLE_HOME.

Reconfiguration d'un Management Agent pour OMS (Grid)

Pour que l'Agent communique correctement avec Oracle Management Service, j'ai dû reconfigurer complètement l'agent et toutes les composantes du Grid propre à cet hôte.

1. Arrêter l'agent

cd $ORACLE_HOME
emctl stop agent

2. Supprimer toutes les composantes liés à l'hôte dont l'agent y est installé.

Sous SQL*Plus, se connecter avec SYSMAN au référentiel de l'OMS pour supprimer complètement l'agent

exec mgmt_admin.cleanup_agent('NomAgent:NoPort');

3. Nettoyer les répertoires de l'agent

cd $ORACLE_HOME/sysman/emd
/usr/bin/rm -rf agntstmp.txt lastupld.xml recv/* collection/* upload/* state/*

cd $ORACLE_HOME/hôte/sysman/emd
/usr/bin/rm -rf agntstmp.txt lastupld.xml recv/* collection/* upload/* state/*

La commande "emctl clearstate agent" peut aussi être utilisé pour faire le ménage cependant, je ne crois pas qu'elle soit aussi efficace que la commande "rm -rf".

4. Configurer l'agent sur l'hôte

Dans mon cas, c'est un agent installé sur un cluster RAC à 3 noeuds

agentca -f -n nom_cluster -c "hôte1,hôte2,hôte3"

4. Sécuriser la communication entre l'Agent et OMS

emctl secure agent

5. Charger les informations dans OMS

emctl upload

Et, pour terminer. il suffit de naviguer dans la console Oracle EM Grid Control et de configurer les cibles découvertes :

- Se connecter à la console
- Cliquer sur "Setup", "Agents"
- Choisir un agent en particulier puis cliquer sur le bouton configurer

Exécution préautorisée (SSH) entre noeud d'un cluster RAC

Aujourd’hui, j‘ai rencontré l’erreur ci-dessous en tentant de déployer un correctif de Management Agent pour Oracle EM Grid Control :

Error PRKC 1044- failed to check remote command execution setup for node… using shells /usr/bin/ssh and /usr/bin/rsh
: Connection Refused

Cette erreur est occasionnée par le fait que les serveurs ne peuvent pas communiquer ensemble avec l’utilitaire SSH sans être obligé de saisir un mot de passe.

Pour remédier à ce problème, il faut configurer SSH entre les nœuds d’un cluster. Voici comment faire :

En supposant que les nœuds du cluster s’appellent RAC1, RAC2 et RAC3.

Il faut se connecter avec le compte Unix que vous utiliser pour installer les logiciels Oracle et effectuer ce qui suit sur chaque nœud du cluster:

cd $HOME
mkdir ~/.ssh
chmod 700 ~/.ssh
/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa

Ensuite, sur chacun des nœuds, il faut ajouter les identifiants, générés par l’étape précédente, au fichier « authorized_keys » puis le copier afin que chacun des nœuds en possède une copie :

Nœud : RAC1

cd $HOME/.ssh
cat id_rsa.pub >> authorized_keys
cat id_dsa.pub >> authorized_keys
scp authorized_keys rac2:/u01/home/dba/oracle/.ssh

Nœud : RAC2

cd $HOME/.ssh
cat id_rsa.pub >> authorized_keys
cat id_dsa.pub >> authorized_keys
scp authorized_keys rac3:/u01/home/dba/oracle/.ssh

Nœud : RAC3

cd $HOME/.ssh
cat id_rsa.pub >> authorized_keys
cat id_dsa.pub >> authorized_keys
scp authorized_keys rac1:/u01/home/dba/oracle/.ssh
scp authorized_keys rac2:/u01/home/dba/oracle/.ssh


Pour vérifier le fonctionnement du SSH sans que celui-ci demande la saisie d’un mot de passe, établissez une connexion entre chacun des nœuds, par exemple, à partir du noeud RAC1, effectuez la commande suivante :

$ ssh rac2 date

Cette commande doit retourner la date sans demander de saisir un mot de passe.

Que des problèmes avec ces cibles

Dernièrement, j'ai détruit, de peine et de misère, toutes les cibles (targets) liés à trois noeuds d'un cluster RAC dans Oracle EM Grid Control.

Lors de la découvert des cibles, je recevais une erreur à propos d'une violation de contrainte unique :

ORCL.WORLD: Saving ORCL.WORLD_ORCL1 ...java.sql.SQLException:
ORA-00001: unique constraint (SYSMAN.MGMT_TARGET_PROPERTIES_PK) violated
ORA-06512: at "SYSMAN.EM_TARGET", line 1918
ORA-06512: at "SYSMAN.MGMT_TARGET", line 2705
ORA-06512: at line 1 -

La solution qui m'a permit de sortir de cette impasse fut la note "728650.1" sur Oracle Metalink.

Voici les étapes à suivre :

1. Connect to repository database as sysman user and execute:
CREATE TABLE mgmt_tgt_prop_bug6884963 AS
SELECT * FROM mgmt_target_properties WHERE
TARGET_GUID not in (select target_guid from mgmt_targets) AND
TARGET_GUID not in (select target_guid from mgmt_targets_delete);

Verify that this table has been created properly by checking the numberof rows in this table and compare it with this query:

SELECT * FROM mgmt_target_properties
WHERE TARGET_GUID not in
(select target_guid from mgmt_targets)
AND TARGET_GUID not in
(select target_guid from mgmt_targets_delete);

2. Now remove these rows from mgmt_target_properties:

DELETE FROM mgmt_target_properties WHERE
TARGET_GUID not in (select target_guid from mgmt_targets) AND
TARGET_GUID not in (select target_guid from mgmt_targets_delete);

commit;

Où est le listener ?

J’utilise Oracle Enterprise Manager Grid Control 10g pour la gestion de toutes les composantes Oracles. À ma grande surprise, j’ai remarqué que les programmes d’écoute (listeners) n’étaient pas ajoutés dans les cibles (target) de chaque hôte.

Je me suis alors penché sur ce problème. J’ai essayé l’assistant de configuration d’agent « agentca » avec l’option « -d » pour forcer une redécouverte des composantes sur l’hôte.

Bingo! Ça l’a fonctionné ! En plus de découvrir mes programmes d’écoute, des composantes du répertoire OID (LDAP) présent sur cet hôte ont été ajoutées.

Voici les options pouvant être utilisées :

-n Spécifier le nom du cluster (CLUSTER_NAME)
-c Spécifier la liste des nœuds d’un cluster
-t Ne pas redémarrer l’agent après une reconfiguration ou une redécouverte
-d Redécouvrir les cibles sur l’hôte
-f Reconfigurer l’agent
-i Spécifier l’emplacement du fichier « oraInst.loc »
-h Afficher l’aide sur les options disponibles

Message d'erreur qui ne veut rien dire

Lors de la création d'une vue matérialisée, je me suis cassé la noix à comprendre pourquoi je recevais un message d’erreur à propos d’un « deadlock ».

Ce message n’avait aucun rapport avec le problème. L’origine du problème se situait au niveau du format de la date (NLS Parameters) utilisé par ma session versus une valeur littérale (date) dont le format n’était pas spécifiée dans la vue accédée par l’énoncé SQL constituant la vue matérialisée.

Voici une reproduction du problème rencontré :

Morale de cette histoire : Il faut toujours spécifier un format de date à une valeur littérale !

Statistiques non mises à jour... Pourquoi ?!?

Si vous importez seulement la structure (métadonnée) donc, aucune donnée, les statistiques seront verrouillées. Vous ne pourrez plus mettre à jour les statistiques. Ce comportement a été observé sur une base de données Oracle 10g Release 2.

Pour résoudre ce problème, vous pouvez déverrouiller les statistiques après l’import ou exclure les statistiques de l’import en utilisant les paramètres de l’utilitaire IMPDP.

Voici un exemple de comment déverrouiller les statistiques sur les objets appartenant à un schéma :

execute DBMS_STATS.UNLOCK_SCHEMA_STATS('NomSchéma');

Attends ! Ben oui, je n'ai que ça à faire !

Voici un script que j’aime bien utilisé pour visualiser les événements d’attente propre à une session qui est en cours d’exécution :

REM ****************************************************
REM * Script: sess_wait.sql
REM * Titre : Afficher les wait events d'une session
-- ****************************************************
set pagesi 999 feed off linesi 500 trimspoo on veri off

col event format A40

-- ****************************************************
-- Afficher les sessions en cours
-- ****************************************************
select SID,
substr(username,1,15) username,
status,
substr(machine,1,20) machine,
program
from v$session
where username is not null
and status != 'KILLED'
order by program, username, status;

-- Choisir une session
Accept L_sid prompt 'Entrez le SID (Enter = Tous) : '

-- ****************************************************
-- Afficher les événements propres à la session choisie
-- ****************************************************
Select substr(event,1,30) event,total_waits,time_waited
from v$session_event
where sid=nvl('&L_sid',sid)
order by total_waits;

-- ****************************************************
-- Afficher l'événement d'attente actuel de la session
-- ****************************************************
Select *
from v$session_wait
where sid=nvl('&L_sid',sid);

ORA-06502 lors d’import via DB Link (IMPDP)

Lors d’un import, je devais exclure plusieurs tables. J’ai alors procédé de la façon habituelle, c’est-à-dire, en déclarant le paramètre TABLES puis une condition dans mon fichier de paramètres :

TABLES = 'NOT IN (''NomSchema.NomTable'', ''NomSchema.NomTable'', ''NomSchema.NomTable''…

Lors de l’exécution de l’import, j’ai reçu l’erreur suivante :

ORA-06502 (PL/SQL: numeric or value error: character string buffer too small

C’est à ce moment que j’ai constaté qu’il y avait une limite de caractères que l’on pouvait passer au paramètre.

L’alternative à cette limite est d’utiliser une table dans laquelle nous y insérons le nom des tables que nous désirons exclure et, par la suite, nous effectuerons une condition sous forme de requêtes.

Pour débuter, j’ai créé une nouvelle table et j’ai inséré le nom des tables à exclure :

CREATE TABLE impdp_t_objet
(
NomPropr VARCHAR2(30),
TypeObjet VARCHAR2(30),
NomObjet VARCHAR2(30)
);

INSERT INTO impdp_t_objet (NomPropr, TypeObjet, NomObjet)
VALUES ('NomSchema','TABLE','NomTable');
INSERT INTO impdp_t_objet (NomPropr, TypeObjet, NomObjet)
VALUES ('NomSchema','TABLE','NomTable');
…
Commit;

Ensuite, j’ai ajouté la ligne suivante dans le fichier de paramètre qui a été utilisé lors de l’import :

TABLES='NOT IN (SELECT NomObjet FROM impdp.impdp_t_objet WHERE NomPropr=''NomSchema'' AND TypeObjet =''TABLE'')'

Et, là, je suis prêt à exécuter l’import.

J’aime bien cette façon de procéder car dans ma situation, je dois rafraichir les données de plusieurs systèmes sous plusieurs environnements et je dois fréquemment exclure des objets. Ça me permet de conserver les objets à exclure d'une fois à l'autre.

Bug CONNECT BY sous 10.2.0.3.0

Lors de l'exécution d'une requête hiérarchique sur une vue qui utilise la clause CONNECT BY... START WITH, l'erreur suivante se produit :

Dans SQL*Plus :
ORA-03113: end-of-file on communication channel

Dans l'Alert log :
ORA-07445: exception encountered: core dump [qknLazOpn()+4] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []

Cette erreur est un Bug documenté chez Oracle (bug #5234379)

Voici un cas test pour reproduire le problème:

CREATE TABLE FOO (ID NUMBER, PARENT_ID NUMBER);

CREATE VIEW BAR (ID, PARENT_ID) AS
SELECT ID, PARENT_ID
FROM FOO
WHERE EXISTS (
SELECT 'x' FROM DUAL
UNION ALL
SELECT 'x' FROM DUAL);

SELECT ID, PARENT_ID
FROM BAR
CONNECT BY PARENT_ID = PRIOR ID
START WITH PARENT_ID = 0;


Ce bug n'est pas corrigé avec le Patchset 10.2.0.3.0. Par contre, il existe une alternative (workaround) :

Alter system set "_optimizer_connect_by_cost_based" = false scope=both;

Redimensionner les journaux (redo log files)

Voici un script que j'ai créé pour me simplifier la vie lorsque je veux redimensionner les journaux (redo logs). Vous pouvez l'exécuter sans crainte car il n'exécute aucune commande qui modifiera les fichiers. Ce script bâtit tout simplement les commandes à exécuter ultérieurement pour redimensionner les redo log files :

set define on wrap on numwidth 12 serveroutput on
set pagesize 999 termout on arraysize 2 linesize 2000
Accept vTailRedo prompt 'Taille (en Mbytes) des Redolog (Nul=20M): ' default 20

Declare
vTailRedo number := &&vTailRedo; -- Taille (en Mbytes) des redo files

vLigne varchar2(4000);
vCommt varchar2(20);
vMember varchar2(4000);

Cursor cur_log is
Select i.instance_name,l.group#,l.thread#,l.status,
round((l.bytes/1024/1024),0) taill
from v$log l, v$instance i
order by l.thread#, l.group#;

Cursor cur_member(pNoGroup Number) is
Select rownum,l.group#,l.member
from v$logfile l
where l.group# = pNoGroup
order by l.group#,l.member;

Begin
dbms_output.put_line('-- ****************************');
dbms_output.put_line('-- Redimensionner les redo logs');
dbms_output.put_line('-- ****************************');
dbms_output.put_line('-- => Le statut du redolog file doit être INACTIF');
dbms_output.put_line(chr(10));
For i in cur_log
-- Extraire les groupes
Loop
dbms_output.put_line('-- Thread/Groupe # 'i.thread#'/'i.group#
' => Status: 'i.status
' => Taille actuelle : 'i.taill'M');
if vTailRedo = i.taill then
vCommt := '-- ';
else
vCommt := null;
end if;
vLigne := vCommt'Alter database drop logfile group 'i.group#';'chr(10)
vCommt'Alter database 'chr(10)
vCommt' add logfile thread 'i.thread#
' group 'i.group#' (';
For J in cur_member(i.group#)
-- Extraire les membres du groupe
Loop
if j.rownum > 1 then
vLigne := vLigne', ';
end if;
-- Vérifier si ASM est utilisé
if substr(j.member,1,1) = '+' then
vMember:= substr(j.member,1,(instr(j.member,'/',1)-1));
else
vMember := j.member;
end if;
vLigne := vLigne''''vMember'''';
End loop;
vLigne := vLigne') size 'vTailRedo'M reuse;';
dbms_output.put_line(vLigne);
dbms_output.put_line(chr(10));
vLigne := null;
End loop;
dbms_output.put_line('-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
dbms_output.put_line('Commandes à effectuer pour changer le REDO'
' Log active/courant');
dbms_output.put_line('-- Pour forcer le switch de groupe de redo log :');
dbms_output.put_line('ALTER SYSTEM SWITCH LOGFILE;');
dbms_output.put_line('-- Archiver le redo courant et activer le suivant');
dbms_output.put_line('ALTER SYSTEM CHECKPOINT;');
End;
/

Tablespace de type "bigfile"

Depuis Oracle 10g, il existe deux types de tablespace qui peuvent cohabiter dans une même base de données:

• bigfile
• smallfile

Un tablespace « smallfile » correspond au tablespace traditionnel, celui dont nous sommes habitués d’utiliser. Ce dernier peut contenir un maximum de 1022 fichiers de données.

Un tablespace « bigfile » peut contenir un seul fichier de données (datafile ou tempfile) qui peut être de taille imposante. Dépendamment de la taille du bloc Oracle, le fichier de données d’un tablespace « bigfile » peut atteindre la taille de 128 téraoctets.

Les tablespaces « Bigfile » sont seulement supportés avec la gestion locale des tablespaces (locally managed) et avec ASSM (automatic segment-space management). Ces options n’ont pas à être précisées car ce sont les défauts lors de la création d’un tablespace de type « bigfile » :

CREATE BIGFILE TABLESPACE TS DATAFILE '/u01/oradata/ts01.dbf' SIZE 2T;

Lorsqu’on augmente la taille d’un fichier, il n’est pas nécessaire d’utiliser l’option « datafile », l’utilisation du nom du tablespace pour altérer la taille du fichier de données est suffisant car le tablespace « bigfile » contient seulement un fichier de données.

Avec les commandes « CREATE DATABASE » ou « ALTER DATABASE », le type de tablespace par défaut peut être changé pour « BIGFILE ».

Le tablespace Undo géré localement et le tablespace temporaire peuvent être des « bigfile », même si leurs segments sont gérés manuellement.

Les tablespaces SYSTEM et SYSAUX ne peuvent pas être créés en bigfile.

Avec les tablespaces « bigfile », il est conseillé d’utiliser ASM (automatic storage management), ou tous autres gestionnaires de volume logique qui supporte l’extension dynamique, le striping et RAID.

Avec les tablespaces « smallfile », l’administrateur de bases de données avait la latitude d’augmenter un fichier de base de données ou d’ajouter un nouveau fichier de données pour accommoder l’évolution des données des applications. Tandis qu’un tablespace « bigfile », qui ne peut pas contenir plusieurs fichiers de données, l’administrateur de base de données doit s’assurer et prévoir suffisamment d’espace pour répondre à la capacité future.

L’utilisation des tablespaces de type « bigfile » n’a rien de négatif cependant, gardez en tête que le temps de recouvrement d’un fichier de données de plusieurs téraoctets risque d’être très long.

Tables externes

Les tables externes peuvent lire des fichiers plats comme si elles étaient des tables ordinaires. Par conséquent, il est pratique d'employer les tables externes pour charger des fichiers plats dans la base de données. Les exemples suivants montrent comment importer un fichier avec une table externe. Exemple #1 Le fichier se compose de 4 colonnes dont les champs sont sépérés par des virgules: 1;Avalanche;Colorado;COL 2;Senators;Ottawa;OTT 4;Bruins;Boston;BOS 5;Canadiens;Montreal;MTL 6;Sabres;Buffalo;BUF

Pour accéder au fichier. un répertoire (Directory) doit être créé dans la base de données :

create or replace directory ext_dir as 'C:\Temp'; -- Répertoire où le fichier sera déposé

Pour avoir le droit d'utiliser le répertoire, l'utilisateur Oracle doit obtenir des privilèges :

grant read, write on directory ext_dir to eric; -- Nom de l'utilisateur Oracle

Maintenant, il faut créer une table pour recevoir les données du fichier. Il faut définir les colonnes suffisament grandes pour qu'elles puissent contenir les données :

create table Equipe_Hockey(no_equi number(2),
nom varchar2(30),
ville varchar2(30),
cd_equi varchar2(3))
organization external
(type oracle_loader
default directory ext_dir
access parameters
(records delimited by newline
fields terminated by ';'
missing field values are null
)
location ('equipe.txt')
)
reject limit unlimited;

Finalement, effectuer un SELECT sur la table :

select * from Equipe_Hockey;

Un fichier de trace est généré dans le même répertoire du fichier. Ce fichier Log vous renseigne sur l'accès au fichier. Si le traitement rencontre un erreur, elle sera inscrite dans ce fichier.

Exemple #2

Pour les DBAs, voici une utilisation qui peut être très utile. Cette procédure lit les fichiers d'alerte générés par Oracle. Vous devez avoir le droit "create any directory privilege" pour l'utiliser (n.b. je ne l'ai pas essayé... ) :

Create or Replace procedure external_alert_log as
path_bdump varchar2(4000);
name_alert varchar2(100);
Begin

select
value into path_bdump
from
sys.v_$parameter
where
name = 'background_dump_dest';

select
'alert_' value '.log' into name_alert
from
sys.v_$parameter
where
name = 'db_name';

execute immediate 'create or replace directory background_dump_dest_dir as '''
path_bdump '''';

execute immediate
'create table alert_log_external '
' (line varchar2(4000) ) '
' organization external '
' (type oracle_loader '
' default directory background_dump_dest_dir '
' access parameters ( '
' records delimited by newline '
' nobadfile '
' nologfile '
' nodiscardfile '
' fields terminated by ''#$~=ui$X'''
' missing field values are null '
' (line) '
' ) '
' location (''' name_alert ''') )'
' reject limit unlimited ';
End;
/

Pour exécuter la procédure :

begin
external_alert_log;
end;
/

Pour consulter le contenu :

select * from alert_log_external;

Segment d'annulation corrompu

Si un jour vous êtes pris avec des segments d'annulation système corrompus dans un tablespace, pensez à utiliser le paramètre caché "_corrupted_rollback_segments". Il vous permettra de détruire le tablespace qui contient ces segments corrompus ainsi que les segments corrompus.

Ce paramètre doit être inscrit dans le fichier de paramètres PFILE (initSID.ora) et il doit être affecté des noms des rollback segments corrompus.

Exemple : _corrupted_rollback_segments = _syssm24$

Il suffit de démarrer la base de données avec le fichier de paramètres contenant ce paramètre et, de procéder au ménage. Par la suite, vous pourrez redémarrer la base de données avec le SPFILE.

Il est fortement recommandé d’utiliser ce paramètre en dernier recours. Si vous l’utiliser, veuillez prendre une copie de sauvegarde de vos données car vous avez probablement une corruption de données à d’autres endroits et vous risquez de faire face à d’autres problèmes.

Comportement du DEFAULT VALUE sur une colonne de table

Voici une petite particularité banale mais que plusieurs se font prendre au piège.

Supposons la table suivante :

CREATE TABLE test
(c1 VARCHAR2(10),
c2 NUMBER,
c3 DATE DEFAULT SYSDATE);

Saviez-vous qu’en exécutant un INSERT comme ci-dessous, la colonne C3 ne sera pas assignée à la valeur par défaut (SYSDATE):

INSERT INTO test(c1,c2,c3) VALUES ('b',2,NULL);

C'est normal car la colonne C3 est précisée ainsi que la valeur "NULL".

La même chose se produit lorsque la commande INSERT SELECT est utilisée si la valeur de la colonne correspondant à « C3 » est nulle dans la table « test2 »:

INSERT INTO test SELECT * FROM test2

Si l'on modifie la colonne pour qu'elle soit obligatoire et que nous exécutons une insertion comme celle précédemment, nous obtiendrons un erreur car nous forcons la valeur "NULL" :

ALTER TABLE test modify c3 NOT NULL;

INSERT INTO test(c1,c2,c3) VALUES ('c',3,NULL)
*ERROR at line 1:ORA-01400: cannot insert NULL into ("ERIC"."TEST"."C3")

Tandis que si nous enlevons la colonne C3 et la valeur "NULL" de la commande INSERT, l'insertion s'effectuera avec succès et la colonne sera affectée de la valeur par défaut :

INSERT INTO test(c1,c2) VALUES ('c',3);

Et ce, même si la colonne n'est pas obligatoire :

ALTER TABLE test modify c3 NULL;
INSERT INTO test(c1,c2) VALUES ('d',4);

Collecte de statistiques système

La collecte des statistiques système est malheureusement très souvent oubliée par les administrateurs et pourtant, elle ne le devrait pas car l'optimiseur effectue un calcul des coûts plus adéquat lorsque celles-ci sont disponibles.

Les statistiques Système procure à l'optimiseur des informations près de la réalité sur la façon dont votre système se comporte réellement. Ces informations permettent à l'optimiseur de produire une meilleure déduction entre les temps estimés et réelles pour l'exécution d'une requête.

Les Statistiques Système contiennent les détails qui permettent à l'optimiseur de comparer les coûts entre les entrées / sorties (I/O) et ceux liés aux processeurs (CPU).

Lorsque vous commencer à utiliser les statistiques système, l'optimiseur devient plus sensible au moment de choisir entre une lecture séquentielle (table scan) et l'utilisation d'index, car le coût d'une lecture multiblocs pour un accès séquentiel comportera des informations sur le temps d'exécution. Par exemple, Oracle peut décider qu'une lecture séquentielle d'une table serait plus rapide en se basant uniquement sur la vitesse de lecture de blocs (simple et multi).

Différentes façons de collecte peuvent être effectuées. Personnellement, j'aime bien avoir le contrôle alors, j'opte pour la méthode manuelle.

-- Établir une connexion avec SYS
connect / as sysdba

-- Créer la table de collecte de statistiques
EXEC DBMS_STATS.DELETE_SYSTEM_STATS('stats','stats','SYS');
EXEC DBMS_STATS.CREATE_STAT_TABLE ('SYS','stats','SYSAUX');

-- Démarrer la collecte en mode manuelle
EXEC DBMS_STATS.GATHER_SYSTEM_STATS -
(gathering_mode=>'START', -
stattab => 'stats', -
statid => 'stats', statown => 'SYS');

-- Vérifier si la collecte est activée
column statid format a7
column c1 format a13
column c2 format a16
column c3 format a16
select STATID, C1, C2, C3,to_char(sysdate,'MM-DD-YYYY HH24:MI:SS')
from stats;

-- Arrêt de la collecte
-- ATTENTION: Avant d'arrêter, assurez-vous d'avoir laisser fonctionner la collecte
-- pendant une période significative
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS -
(gathering_mode=>'STOP', -
stattab => 'stats', -
statid => 'stats', -
statown => 'SYS');

-- Prendre une copie des statistiques actuelles
EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','stats_bkp','SYSAUX');
EXEC DBMS_STATS.EXPORT_SYSTEM_STATS -
(stattab => 'stats_bkp', -
statid => 'stats_bkp', -
statown => 'SYS');
select * from sys.stats_bkp;

-- Activer les statistiques
EXEC DBMS_STATS.IMPORT_SYSTEM_STATS -
(stattab => 'stats', -
statid => 'stats', -
statown => 'SYS');

-- Afficher les nouvelles statistiques
select * from sys.aux_stats$;

Et, voilà, c'est assez simple n'est-ce pas !

Je vous conseille fortement d'utiliser les statistiques système. La mise en fonction de ces statistiques doivent être effectuée avec précaution. Pour minimiser les impacts et les surprises, effectuer des essais de performance sur un environnement dédié à des tests et comparer vos résultats.