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.