Techniques de FLASHBACK d'ORACLE

1- Vue d’ensemble
Les techniques de flashback sont un ensemble de fonctionnalités proposées par Oracle qui permettent de voir l’état passé de données, ou de ramener une table ou la totalité de la base de données dans le passé.
Les fonctionnalités proposées sont les suivantes :

 - Flashback Query : permet de lire les données telles qu’elles étaient à un instant dans le passé (appary en version 9i).

- Flashback Version Query : permet de voir toutes les versions d’une ligne sur un certain intervelle de temps (apparu en version 10).

- Flashback Transaction Query) : permet de voir les modifications réalisées par une ou plusieurs transactions sur un certain intervalle de temps (apparue en version 10).

- Flashback Transaction : permet d’annuler les modifications d’une transaction, et de ses transactions dépendantes( apparue en version 11).

- Flashback Data Archive (Oracle Total Recall) : permet de conserver sur le long terme, toutes les modifications apportées à une table (apparue en version 11).

- Flashback Table : permet de ramener une table dans l état où elle était, juste avant sa suppression (apparue en version 10).

- Flashback Database : permet de ramener la totalité de la base de données dans l’état où elle était à un certain moment dans le passé (apparue en version 11).

Seule la fonctionnalité Flashback Query est disponible dans toutes les éditions de la base de données (et donc notamment en Standart Edition).
La fonctionnalité Flashback Data Archive, est une option de l’Entreprise Edition et nécessite donc, une licence supplémentaire.

Les autres fonctionnalités de Flashback nécessitent l’Entreprise Edition, mais sans option supplémentaire.
Les fonctionnalités de Flashback de requête (Flashback Query, Flashback Version Query et Flashback Transaction Query), et la fonctionnalité de Flashback Table, utilisent les informations d’annulation pour revenir en arrière. Le paramètre UNDO_RETENTION et le tablespace d’annulation doivent donc être correctement dimensionnés, si vous souhaitez pouvoir retourner loin dans le passé.

2- Niveau ligne
Flashback Query

Pour lire les données telles qu’elles étaient à un instant donné du passé, vous pouvez utiliser l’option AS OF sur une table présente dans la clause FROM d’une requête SELECT.

Syntaxe :
Nom_table AS OF { TIMESTAMP | SCN } expression

L’option TIMESTAMP permet de retourner à un instant donné du passé en indiquant une date et une heure ; dans ce cas, l’expression doit être de type TIMESTAMP. L’option  SCN permet de retourner à un instant donné du passé en indiquant un numéro SCN ; dans ce cas, l’expression doit être un nombre.


SQL> select   *   from   emp   where   empno = 7369;

     EMPNO ENAME      JOB              MGR    HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- --------------------------------
      7369 SMITH     CLERK                  7902    17/12/80               800                        20

SQL>  select to_char(sysdate, 'DD/MM/YYY HH24:MI:SS') "SYSDATE",
  2  dbms_flashback.get_system_change_number "SCN"
  3  FROM dual;

SYSDATE                  SCN
------------------ ----------
06/10/013 15:31:48    3446120



La fonction GET_SYSTEM_Change_NUMBER du package  DBMS_FLASHBACK retourne le numéro SCN courant. Il faut le privilège  EXECUTE sur le package pour l’utiliser.

Un peu plus tard :


SQL>  update emp set ename = 'ZAKARIA' where empno = '7369';
1 ligne mise à jour.
SQL> commit;
Validation effectuée.

SQL> select to_char(sysdate, 'DD/MM/YYY HH24:MI:SS') "SYSDATE",
  2  dbms_flashback.get_system_change_number "SCN"
  3  FROM dual;

SYSDATE                   SCN
------------------ ----------
06/10/013 15:38:41    3446364



Maintenant je vais retourner vers le passé avec la notion du temps :

SQL>  SELECT *  FROM EMP 
  2  AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' minute
  3  where empno = 7369;
  
  EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
--------- ---------- --------- ---------- -------- ---------- ---------- ----------
     7369 SMITH      CLERK               7902 17/12/80             800                              20



Ou bien avec la notion du SCN :


SQL> SELECT *  FROM EMP 
  2  AS OF SCN 3446120
  3  where empno = 7369;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH              CLERK           7902 17/12/80        800                                  20




NB : c'est un résumé de la partie FLASHBACK du livre "Administration ORACLE 11g" de OLIVIER HEURTEL.

Statistiques ORACLE, Package DBMS_STAT

La semaine dernière, et lors d’une mission de consulting chez un opérateur télécoms, on a confronté un problème de performance qui concerne les statistiques.

Bref, le problème est le suivant :

L’équipe de développement a  développé une application qui utilise une base de données Oracle 10g. Au niveau du serveur de recette l’application marche très bien, avec un temps de réponse raisonnable pour  la plus part des requêtes, mais une fois l’application est en production, oooops le temps de réponse est vraiment anormale (Exemple : une requête en recette : 15s, en prod : 3 minutes !!!!!!!!!!!!!).

NB : Avant mon intervention, ils ont bien vérifié que la configuration des 2 serveurs (Recette et Prod) ont les mêmes caractéristiques . Les 2 BD ont aussi la même volumétrie.

Avant de commencer d’inspecter les plans d’exécutions, il fallait voir les statistiques utilisées par Oracle. On sait parfaitement qu’Oracle se base sur les statistiques collectées pour déterminer le meilleur plan d’exécution. Si on arrive à avoir les mêmes statistiques que le serveur de recette, il nous suffit de rejouer son scénario et si le problème vient réellement d'un plan d'exécution différent, alors on va  pouvoir identifier la ou les requêtes en question.

Pour cela, on va sauvegarder les statistiques du serveur de recette :

1-      Créer une table pour sauvegarder les statistique : (USER à le schéma qui utilise l ’application, NOM_TABLE_STATS à Nom de la table des statistiques)

     exec DBMS_STATS.CREATE_STAT_TABLE (ownname=>'USER', stattab=>'NOM_TABLE_STATS');

2-      Alimenter la table NOM_TABLE_STATS par les statistiques du serveur de recette (A travers un Export du package DBMS_STATS)

    exec DBMS_STATS.EXPORT_SCHEMA('USER','NOM_TABLE_STATS');

3-      Au niveau du serveur PROD, importer les statistique déjà collectées :

-          Pour cela, il faut déjà supprimé les statistiques du PROD par la commande suivante :

exec DBMS_STATS.DELETE_SCHEMA_STATS('USER',FORCE=>TRUE);

-          Importer les statistiques :

 exec DBMS_STATS.IMPORT_SCHEMA_STATS('USER','NOM_TABLE_STATS');

Et voilà, maintenant on a les même statistiques au niveau des 2 serveurs, après les tests au niveau de la PROD, on a constaté que le temps de réponse est très raisonnable.

NB : Normalement c’est la première étape qu’on doit faire, il y a des cas où le problème persiste toujours, dans ce cas il faut revoir et analyser les plans d’exécutions pour voire s’il y a des full scan ou des mauvaises utilisation d’index ou bien autre chose.

Zakaria EL HAMDAOUI

Consultant Oracle

Blog Oracle de Zakaria EL HAMDAOUI: TUNING : REGLAGE DE LA SHARED POOL

Blog Oracle de Zakaria EL HAMDAOUI: TUNING : REGLAGE DE LA SHARED POOL: 1. REGLAGE DE LA SHARED POOL V$LIBRARYCACHE : contient des statistiques relatives à un type d’élément conservé dans le cache « library ». •...

Utiliser le mot de passe d’un utilisateur Oracle

Il arrive parfois que l’administrateur veut se connecter avec le compte d’un autre utilisateur sans que ce dernière soit au courant. Dans cet exemple, on va monter comment se connecter avec le mot de passe d’un autre utilisateur Oracle.

Tout d’abord, il faut avoir le privilège SYSDBA :

SQL*Plus: Release 10.2.0.1.0 -Production on Dim. Févr. 10 15:35:10 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connecté à une instance inactive.

SQL> 
ZIKO>startup
Instance ORACLE lancée.

Total System Global Area  163577856 bytes
Fixed Size                  1247852 bytes
Variable Size              83887508 bytes
Database Buffers           71303168 bytes
Redo Buffers                7139328 bytes
Base de données montée.
Base de données ouverte.
ZIKO>show user
USER est "SYS"
ZIKO>SELECT username,password from DBA_USERS WHERE username='SCOTT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
SCOTT                          80337D4D053124C7

OU BIEN : 

ZIKO>SELECT  SPARE4||';'||PASSWORD  FROM  SYS.USER$  WHERE USERNAME='SCOTT'

S:1ECBECB96D020B2E74FF9BB02FD1EBED108361E19E4DBB246A81A110A185;39A689CAF7B52A8D

On voit que les mots de passe au niveau de DBA_USERS sont cryptés, alors je note le mot de passe crypté de l’utilisateur SCOTT « 80337D4D053124C7 » .

Après je vais modifier le mot de passe de SCOTT pour que je peux utiliser sont compte :

ZIKO>ALTER USER SCOTT IDENTIFIED BY aaa;

Utilisateur modifié.

ZIKO>conn scott/aaa;
Connecté.
ZIKO>show user
USER est "SCOTT"
ZIKO>

Maintenant je suis connecter avec l'utilisateur SCOTT, une fois terminé, je dois lui rendre son ancien mot de passe pour qu'il ne soit pas au courant, pour cela je vais utilisé la chaîne crypté de son origine mot de passe « 80337D4D053124C7» :

ZIKO>ALTER USER SCOTT IDENTIFIED BY VALUES '80337D4D053124C7';

Utilisateur modifié.

ZIKO>show user
USER est "SYS"
ZIKO>conn scott/ziko
Connecté.
ZIKO>show user
USER est "SCOTT"
ZIKO>

Je voulais noter qu'il y a pas une possibilités pour voir les mots de passe en réel.

@+

TUNING : REGLAGE DE LA SHARED POOL

1. REGLAGE DE LA SHARED POOL

V$LIBRARYCACHE : contient des statistiques relatives à un type d’élément conservé dans le cache « library ».

• NAMESPACE : Les éléments qui reflètent l’activité du cache « library » relative aux instructions SQL et aux blocs PL/SQL : SQL AREA, TABLE/PROCEDURE, BODY et TRIGGER
• GETS : affiche le nombre total de demandes d’informations sur l’élément correspondant.
• PINS : pour chaque zone, PINS affiche le nombre d’exécutions d’instructions ou de procédures SQL.
• RELOADS : si l’application appelle l’exécution d’une instruction SQL et que, dans le cache « library », la zone SQL partagée contenant la représentation analysée de cette instruction a été libérée afin de faire de la place à une autre instruction ou en raison de l’invalidation des objets indiqués par l’instruction, le serveur Oracle recharge implicitement cette instruction et, par conséquent, la réanalyse. Le nombre de rechargements est calculé pour chaque espace de nom.
• INVALIDATIONS : lorsqu’un objet est modifié, il est possible qu’il existe un meilleur chemin d’exécution pour toutes les instructions utilisant cet objet. C’est la raison pour laquelle le serveur Oracle marque toutes les exécutions utilisant un objet modifié comme non valides

 LIBRARY CACHE HIT RATIO >90%:

SELECT SUM(PINS-ROLOADS)/SUM(PINS)*100 ‘’Library cahce HIT-Ratio’’
FROM V$LIBRARYCACHE;

 RELOADS TO PINS <1%


SELECT SUM(ROLOADS)/SUM(PINS)*100 ‘’Reload-Ratio’’
FROM V$LIBRARYCACHE;

 PINS TO RELOADS 


SELECT sum(pins), sum(reloads), sum(pins) * 100 / sum(pins+reloads) “Pin Hit%” FROM v$librarycache;

V$SQLAREA: statistiques détaillées sur tous les curseurs partagés et 1000 premiers caractères de l’instruction SQL.

V$SQL : cette vue répertorie les statistiques sur la zone SQL partagée et contient une ligne pour chaque enfant du texte SQL entré à l’origine. V$SQL est une vue similaire à V$SQLAREA, excepté le fait qu’elle ne comporte pas de clause GROUP BY qui peut rendre l’interrogation de la vue V$SQLAREA plus coûteuse.

V$SQLTEXT : texte SQL complet sans troncature, affiché sur plusieurs lignes.

V$DB_OBJECT_CACHE : objets de la base de données mis en mémoire cache, y compris les packages ; comprend également les tables et les synonymes lorsque ces objets sont référencés dans des instructions SQL.

V$SHARED_POOL_RESERVED :Cette vue facilite le réglage du pool et de l’espace réservés au sein de la zone de mémoire partagée.

• FREE_SPACE : est la quantité totale d’espace libre dans la liste d’espaces réservés.
• AVG_FREE_SIZE : est la taille moyenne de la mémoire libre dans la liste d’espaces réservés.
• MAX_FREE_SIZE : est la taille de la plus grande zone de mémoire libre dans la liste d’espaces réservés
• REQUEST_MISSES : est le nombre de fois où la liste d’espaces réservés ne possédait pas de mémoire libre pour satisfaire la demande et a commencé à retirer des objets de la iste LRU.
• REQUEST_FAILURES : est le nombre de fois où une demande n’a pas été satisfaite en raison de l’insuffisance de mémoire.
• LAST_FAILURE_SIZE : est la taille de la dernière demande non satisfaite


objectif :

REQUEST_FAILURES ou REQUEST_MISSES proche de 0 ou/et n’augmentent pas.
SHARED_POOL_RESERVED_SIZE~=10% de SHARED_POOL_SIZE
 DBMS_SHARED_POOL .ABORTED_REQUEST_THRESHOLD: permet de restreindre la quantité de mémoire partagée à vider avant de signaler une erreur ORA-4031, afin de limiter l’étendue d’un vidage éventuellement provoqué par la présence d’un objet volumineux.

V$ROWCACHE : information sur dictionary cache :

• PARAMETER : donne le nom (de categorie) du cache du dictionnaire de données faisant l’objet de l’état.
• GETS : affiche le nombre total de demandes d’informations sur l’élément correspondant (par exemple, pour la ligne contenant des statistiques sur les descriptions de fichier, cette colonne indique le nombre total de demandes de données de description de fichier).
• GETMISSES : affiche le nombre de demandes de données ayant échoué en mémoire cache.