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