Formations Oracle sur Genève et Lausanne

Voici les prochaines dates de formations que je donne en Suisse Romande (en français):

  • Nouvelles fonctionnalités 12c les 24 et 25 novembre 2014 à Lausanne
  • Nouvelles fonctionnalités 12c les 19 et 20 janvier 2015 à Lausanne
  • Tuning Oracle les 26 et 27 janvier 2015 à Genève
  • Tuning Oracle les 29 et 30 janvier 2015 à Lausanne
  • Bases DBA Oracle les 2,3,4 et 5 février 2015 à Lausanne

Ce sont tous des workshops avec beaucoup de demo et exercices, en petit comité, car il faut souvent manipuler pour se souvenir, et on n’a généralement pas le temps de le faire immédiatement après la formation.

Oracle 12c – les nouvelles fonctionnalités · 2 jours (détails et inscription ici)
Nouvelles fonctionnalités d’administration, de performance et de sécurité. Et bien sûr multitenant (pluggable databases).

Oracle Performance Tuning workshop · 2 jours (détails et inscription ici)
2 jours de théorie et de pratique pour savoir comment configurer, indexer, monitorer… qu’on soit toute option (Enterprise Edition avec Diagnostic Pack et Tuning Pack) ou simplement en Standard Edition.

Workshop Oracle DBA – les essentiels · 4 jours (détails et inscription ici)
Les bases pour commencer sur Oracle. Un approche des concepts qui permettent de comprendre le reste, aussi bien les opérations de bases que les problèmes qui peuvent se poser.

Cette année, tous les workshops planifiés ont eu lieu (ils ont lieu même avec peu de participants, c’es tle but des workshops) et le feedback a été très positif.

Verrouillage inattendu après un rollback

Nouveau post sur mon blog à dbi services: Oracle: an unexpected lock behavior with rollback

En bref:

  • La session 1 pose un verrou Row-S
  • La session 2 pose un verrou Share + Row-X
  • La session 2 libère le verrou en faisant un rollback

a la suite de ceci, toute nouvelle session qui veut vérouiller la même table dans n’importe quel mode va se retrouver à demander temporairement un verrou Exclusif!

Technologies de réplications (physiques et logiques)

En passant

Pour ceux qui sont en Suisse, le SOUG Romandie présente les technologies de réplication le 6 novembre à Lausanne.
J’y présenterai Dbvisit replicate, une alternative bon marché à Golden Gate avec une demo pour montrer que la mise en place d’un POC n’est pas compliquée. Il y aura aussi une présentation de Data Guard 12c et de Golden Gate.
Le programme complet: agenda sur le site du SOUG.

SOUG

Cas de restore/recovery

Vous faites des backups, mais savez-vous les restaurer? Il est important de d’entraîner à récupérer une base après une panne quelconque.

Cette page est un index sur les cas de recovery que j’ai posté l’an dernier:

Création de la base 12c pour les cas de recovery
perte d’un datafile (non système)
perte d’un datafile SYSAUX
perte d’un datafile SYSTEM
perte d’un membre de redo log
perte de tous les redo log
perte d’un controlfile multiplexé
perte de tous les controlfiles
perte du spfile
perte de tous les fichiers (sauf FRA)
bloc corrompu

Vous voulez en savoir plus? Le workshop Oracle Backup & Recovery (3 jours)
de dbi services donne la théorie (RPO/RTO, tous les types de backups et recovery)
et vous aurez aussi l’occasion de pratiquer: scénarios de crash sur les bases du lab.

Dernière date de l’année: à Lausanne les 12,13,14 novembre.
Les supports sont en anglais mais le cours est en français. Renseignements/inscriptions:
http://www.dbi-services.com/index.php/products/dbi-insite-workshops/oracle-backup-and-recovery-workshop

dbi insite workshops

Mécanismes internes du multitenant: liens objects et métadonnées

ceci est la traduction de mon article sur le blog dbi-services

Attention: il ne s’agit ici que de jouer avec les mécanismes internes non documentés. A ne faire que sur une base de test prête à être jetée. Jouer avec ces mécanisme est le meilleur moyen de planter la base avec des ORA-600 et de corrompre le dictionnaire.

Sur une base multitenant (CDB) nous savons que chaque pluggable database est isolée afin d’être vue comme une seule base lorsqu’on est connecté à son service. Mais elles partagent des ressource communes puisque le but est de consolider plusieurs bases en une seule. Pas de problème pour la CPU, la mémoire, les journaux redo et undo: il ne sont gérés qu’au niveau CDB. Le multitenant n’a pas changé grand chose là dessus, à part qu’on stocke l’identificateur de conteneur dans différentes structures, par exemple dans les vecteurs de redo pour identifier les objets (object id peuvent être les mêmes entre plusieurs CDB). Au niveau des fichiers, pas de gros changements par rapport à ce qui avait été introduit avec les tablespaces transportables.

Mais ce qui a été un plus gros challenge pour les développeurs d’Oracle 12c, c’est surtout la manière d’implémenter le dictionnaire. Il a été séparé:

  • Tout ce qui est interne au fonctionnement d’Oracle, les métadonnées du dictionnaire, les packages dbms_…, tout ce qui est stocké pour toute l’instance: tables de références, repository AWR, etc. C’est le but: consolider, ne pas répéter plusieurs fois ce qui est identique du moment qu’on est sur la même version d’Oracle
  • Toutes les métadonnées qui concernent l’application: les tables et packages que vous avez créé. Celles-ci sont propres à chaque pluggable tablespace, et sont transportées, clonées avec les oprérations de plug/unplug

et finalement, comme le but principal d’Oracle étant de pouvoir faire dans une PDB tout ce qu’on peut faire sur base, il faut pouvoir foir au niveau PDB les informations stockées au niveau CDB.

Tout ça semble un peu magique par rapport à ce qu’on connait d’Oracle qui n’a jamais fait cette séparation. Par exemple depuis le début d’Oracle on a dans la table SYS.TAB$ les informations de nos tables, ainsi que les informations des tables du dictionnaire – incluant les métadonnées de la table SYS.TAB$ elle-même. La documentation ne va pas très loin sur la manière dont ça a été implémenté. Heureusement on a quelques scripts dans ?/rdbms/admin qui donnes des idées là dessus. ils utilisent une syntaxe un peu particulière, non documentée, et qui n’est disponible que lorsque les scripts internes mettent le paramètre non documenté « _ORACLE_SCRIPT »=true.

Maintenant, ce sont des trucs de geek qui arrivent. Rien à voir avec ce qu^’on fait en production. Nous allons créer nos propres liens objet et métadonnée. Ce qui est fait dans la suite est fait dans une session avec « _ORACLE_SCRIPT »=true. On va voir des nouveaux éléments de syntaxe: cdb$view(), sharing=metadata, sharing=object, common_data

Container data objects

D’abord nous allons voir comment au niveau CDB la racine CDB$ROOT peut voir les informations des autres conteneurs (PDB)

Je suis au niveau CDB:

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id
CON_ID
------------------------------
1

et je créé une table normale:

SQL> create table DEMO_REG_TABLE sharing=none as select 111 dummy from dual;
Table created.

SQL> select * from DEMO_REG_TABLE;
     DUMMY
----------
       111

Puis je fais exactement la même chose dans la PDB:

SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show con_id
CON_ID
------------------------------
3

SQL> create table DEMO_REG_TABLE sharing=none as select 999 dummy from dual;
Table created.

SQL> select * from DEMO_REG_TABLE;
     DUMMY
----------
       999

et de retour dans la racine, j’utilse la fonction de table CDB$VIEW pour voir ce que j’ai dans chacune de mes PDB:

SQL> select * from  cdb$view(DEMO_REG_TABLE) where con_id in (1,3);
     DUMMY     CON_ID
---------- ----------
       999          3
       111          1

C’est comme ça que les ‘container objects’ sont définis: ils utilisent la fonction CDB$VIEW pour exécuter une requête sur chaque PDB, consolider le résultat et rajouter l’identifiant de PDB: CON_ID pour montrer d’où viennent les lignes.

Vous voulez savoir comment c’est implémenté? Il semble que ce soit le mécanisme de parallel query qui soit utilisé. Voici la preuve:

SQL> select * from  cdb$view(DEMO_REG_TABLE);
select * from  cdb$view(DEMO_REG_TABLE)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P002
ORA-00942: table or view does not exist

Lorsque la table n’est pas trouvée (j’ai volontairement créé une table sur quelques conteneurs seulement) c’est un process parallel query qui renvoit l’erreur.

Metadata links

Maintenant, je vais créer une fonction PL/SQ dans la racine et dans une PDB, mais je n’ai pas envie de dupliquer le code dans les deux tablespaces SYSTEM. Je vais utiliser SHARING=METADATA pour créer un metadata link.

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1

SQL> create function DEMO_MDL_FUNCTION sharing=metadata
  2  return varchar2 as dummy varchar2(100); begin select max(dummy) into dummy from DEMO_REG_TABLE; return dummy; end;
  3  /
Function created.

SQL> select DEMO_MDL_FUNCTION from dual;
DEMO_MDL_FUNCTION
------------------------------
111
SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show con_id
CON_ID
------------------------------
3

SQL> create function DEMO_MDL_FUNCTION sharing=metadata
  2  return varchar2 as dummy varchar2(100); begin select max(dummy) into dummy from DEMO_REG_TABLE; return dummy; end;
  3  /
Function created.

SQL> select DEMO_MDL_FUNCTION from dual;
DEMO_MDL_FUNCTION
------------------------------
999

Je peux donc voir le code dans SYS.SOURCE$ de la racine:

SQL> alter session set container=cdb$root;
Session altered.

SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO%');

      OBJ#       LINE SOURCE
---------- ---------- ------------------------------
     95789          1 function DEMO_MDL_FUNCTION

Mais rien dans ma PDB:

 
SQL> alter session set container=pdb1;
Session altered.

SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO%');

no rows selected

Object links

Ceci était pour ne stocker les métadonnées qu’à un seul endroit, dans la racine, en ayant seulement un lien dessus dans les PDB. On peut aussi faire la même chose pour des données qu’on ne veut stocker que dans la racine, en utilisant SHARING=METADATA pour créer la table et SHARING=OBJECT pour créer une vue dessus

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id
CON_ID
------------------------------
1

SQL> create table DEMO_MDL_TABLE sharing=metadata as select 111 dummy from dual;
Table created.

SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show con_id

CON_ID
------------------------------
3

SQL> create table DEMO_MDL_TABLE sharing=metadata as select 999 dummy  from dual;
Table created.
 
SQL> alter session set container=cdb$root;
Session altered.

SQL> select * from  cdb$view(DEMO_MDL_TABLE) where con_id in (1,3);

     DUMMY     CON_ID
---------- ----------
       999          3
       111          1
SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id
CON_ID
------------------------------
1

SQL> create view DEMO_OBL_VIEW sharing=object as select * from DEMO_MDL_TABLE;
View created.

SQL> select * from DEMO_OBL_VIEW;
     DUMMY
----------
       111
 
SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show con_id
CON_ID
------------------------------
3

SQL> create view DEMO_OBL_VIEW sharing=object as select * from DEMO_MDL_TABLE;
View created.

SQL> select * from DEMO_OBL_VIEW;
     DUMMY
----------
       111

La vue montre les données stockées dans CDB$ROOT en suivant le ‘object link’ au lieu de lire la table du container courant.

On ne peut pas insérer directement dans un ‘object link’:

SQL> insert into DEMO_OBL_VIEW select 9999 dummy from dual;
insert into DEMO_OBL_VIEW select 9999 dummy from dual
            *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

et on a une idée de l’implémentation en regardant le plan d’exécution:

---------------------------------------------
| Id  | Operation        | Name             |
---------------------------------------------
|   0 | SELECT STATEMENT |                  |
|   1 |  FIXED TABLE FULL| X$OBLNK$aed0818c |
---------------------------------------------

C’est une table X$ qui va faire le lien

Common data views

finalement, comment une PDB peut voir les données de la racine? Nous avons vu au dessus que la fonction que j’ai créé ci-dessus est stockée dans le SYS.SOURCE$ de la racine seulement. Par contre on peut la voir à partir du DBA_SOURCE de la PDB

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id
CON_ID
------------------------------
1

SQL> create or replace view DEMO_INT_VIEW common_data (dummy,sharing) as select dummy,case when dummy='222' then 0 else 1 end from DEMO_MDL_TABLE;
View created.

SQL> select * from DEMO_INT_VIEW;

     DUMMY    SHARING
---------- ----------
       111          1
       222          0

J’ai créé une ‘common data view’ en rajoutant une colonne ‘sharing’ pour montrer d’où viennent les lignes.

SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show con_id
CON_ID
------------------------------
3

SQL> create or replace view DEMO_INT_VIEW common_data (dummy,sharing) as select dummy,case when dummy='222' then 0 else 1 end from DEMO_MDL_TABLE;
View created.

SQL> select * from DEMO_INT_VIEW;
     DUMMY    SHARING ORIGIN_CON_ID
---------- ---------- -------------
       999          1             3
       111          1             1
SQL> set autotrace on
SQL> select * from DEMO_INT_VIEW;

     DUMMY    SHARING ORIGIN_CON_ID
---------- ---------- -------------
       111          1             1
       999          1             3

Execution Plan
----------------------------------------------------------
Plan hash value: 3158883863

--------------------------------------------------------------------------------------------
|Id  | Operation               | Name            |Pstart|Pstop |   TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |                 |      |      |       |      |            |
|  1 |  PX COORDINATOR         |                 |      |      |       |      |            |
|  2 |   PX SEND QC (RANDOM)   | :TQ10000        |      |      | Q1,00 | P->S | QC (RAND)  |
|  3 |    PX PARTITION LIST ALL|                 |    1 |    2 | Q1,00 | PCWC |            |
|  4 |     FIXED TABLE FULL    | X$COMVW$e40eb386|      |      | Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------

On voit que les données de chaque conteneur est retourné comme une partition.

Oracle Certified Master

J’ai un peu délaissé ce blog. D’une part parce que je blogue plutôt en anglais sur le blog de dbi services (les derniers blogs: comment mesurer si on se rapproche des limites nofile et nproc).

Mais aussi parce que ces derniers mois je préparais l’examen OCM 11g dont je viens d’avoir les résultats: je suis maintenant Oracle Certified Master. Une préparation longue et difficile afin d’être au point sur tous les sujets. Un examen delicat vu la quantité de tâches à réaliser en peu de temps, qui ne laisse pas de place à des hésitations, et peu de temps pour rechercher dans la doc. Et heureusement une attente pas trop longue dans mon cas car j’ai eu les résultats 2 semaines après.

Ce challenge m’a été proposé par dbi services lorsque j’ai rejoint cette société. Une composante indispensable de cette certification vient des moyens mis en oeuvre pour le préparer (2 formations obligatoires, quelques dizaines de jours de préparation pour être prêt à tous les scénarios). L’investissement de mon employeur là dessus m’a permis de faire cette longue préparation sans stress (d’ailleurs, j’ai arrêté de fumer au moment où je suis arrivé chez dbi services, et n’ai même pas eu envie d’une clope ni avant ni après l’exam OCM).

Au fait, dbi services recrute toujours sur la Suisse: http://www.dbi-services.com/index.php/sur-nous/jobs-a-career/senior-consultant-oracle-hf

Même si ce blog est un peu au ralentit, je suis toujours sur le forum et les autres news sont sur twitter.

Comment fournir un plan d’exécution pertinent

La version détaillée est en anglais sur mon blog à dbi services.
Ici un résumé, laissez un commentaire pour plus de détail en français est nécessaire.

Vous avez une requête qui prend trop de temps et vous voulez de l’aide pour analyser le plan d’exécution ? Alors, vous devez fournir le plan avec toute les infos pertinentes, et correctement formatées. Pas de plan sans la partie prédicat. Pas d’estimation de cardinalité sans les valeurs réelles. Pas de copie d’écran: soit un plan venant de dbms_xplan.display_cursor, soit, si vous avez Tuning Pack, un plan SQL Monitor.

Sans Tuning Pack

Paramétrage de la session:

set pagesize 10000 linesize 300 trimspool on serveroutput off
alter session set statistics_level=all;

Puis l’exécution de la requête, en se mettant dans le bon schema et en assignant des valeurs aux bind variables:

-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

Et enfin, la sortie du plan en ficher texte:

spool plan.txt
select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +peeked_binds'));
spool off

Avec Tuning Pack

Paramétrage de la session:

set pagesize 0 linesize 10000 trimspool on serveroutput off long 1000000000 longc 1000000000 echo off feedback off

Puis l’exécution de la requête, avec le hint MONITOR:

-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT /*+ monitor */ * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

Et enfin, la sortie du plan en ficher html:

spool plan.htm
select dbms_sqltune.report_sql_monitor(report_level=>'all',type=>'active') from dual;
spool off

L’event 10704 pour tracer les verrous

En passant

La version détaillée est en anglais sur mon blog à dbi services.
Ici un résumé, laissez un commentaire ici pour plus de détail en français.

On peut voir les verrous posés avec V$LOCK mais certaines verrous ne durent pas longtemps.
Si on veut connaitre exactement les verrous posés par une commande SQL (pour vérifier si une opération est réellement online par exemple) on peut utiliser l’event 10704.

On l’active avec:

alter session set events='10704 trace name context forever, level 3';
Session altered.

et le désactive avec

alter session set events='10704 trace name context off';
Session altered.

Sur le dump généré, je récupère les lignes qui m’intéressent avec un grep -E sur:

ksqgtl [*]{3}|ksqrcl: [A-Z]|ksqcnv: [A-Z]

Il faut aussi avoir les object_id en hexadécimal:

select object_id , to_char(object_id,'0XXXXXXX') , object_name,object_type from all_objects

Exemples:

On a l’acquisition d’un verrou (ici en mode 4 qui est Share):

ksqgtl *** TM-00017EA8-00000000-00000000-00000000 mode=4

La conversion du verrou vers un autre mode:

ksqcnv: TM-00017EA8-00000000-00000000-00000000 mode=3

La libération du verrou:

ksqrcl: TM-00017EA8-00000000-00000000-00000000

C’est avec cet évent que j’étudie le comportement des verrous de version en version, par exemple ici

Générer une clé avec une sequence – sans contention

La version détaillée est en anglais sur mon blog à dbi services.
Ici un résumé, laissez un commentaire ici pour plus de détail en français.

Lorsqu’on génère une clé primaire à partir d’une sequence, le problème est que toutes les insertions concurrentes vont toucher au même bloc d’index. Puisque les valeurs venant de la sequence s’incrémentent elles vont toutes à la fin de l’index.

Il est souvent conseillé d’utiliser un index REVERSE pour disperser ces valeurs. Mais ce n’est pas bon non plus car elles seront trop dispersées. Les inserts vont toucher à tous les blocs de l’index et dès qu’il y aura un peu de volume ça ne logera plus en buffer cache.

L’autre conseil est alors de partitioner l’index par HASH. Effectivement, on distribue les inserts sur quelques blocs. Mais en RAC on va voir passer ces blocs d’un noeud à l’autre, et on aura à nouveau des contentions.

Ce qu’il faut, c’est distribuer les inserts non pas en fonction des valeurs générées, mais en fonction de la session. Le but est qu’une session touche au même bloc d’index pour ses inserts, mais qu’une autre session touche à un autre bloc, surtout si elle est sur un autre noeud du RAC. La solution est donc de préfixer le numéro qui vient de la sequence avec un ora_hash() ou un mod() du session_id. Et mettre devant le numéro d’instance si on est en RAC.
La clé sera un peu plus longue, mais c’est toujours mieux qu’un reverse index qui va faire grossir tout l’index. Les numéros ne seront plus consécutifs, mais de toute façon ça n’a jamais été le cas. Une sequence sert à fournir un nombre unique pour une clé générée, pas è numéroter dans l’ordre.

En 12c il y a une option de la sequence qui permet de faire le préfixage automatiquement en donnant simplement le nombre de partition. Mais ce n’est pas documenté… donc à ne pas utiliser. J’espère le voir sur une prochaine version.

La demo est ici.