Bonjour ami(e)s DBA
Mon alerte_log me remonte ce code d’erreur ORA-01555 sur une base de production d’un client:
on peut aussi avoir ce message lors d’un export datapump
Analyse :
En consultant le fichier je remarque plusieurs requêtes récurrentes select * from tab(‘val’,’val2′) , ces requêtes ont un temps d’exécution supérieur à la valeur actuel du undo_retention qui est de 7200
Explication :
En effet, à partir de Oracle9i, les rollback segments sont rebaptisés « journaux d’annulations ».
Traditionnellement les informations d’annulation des transaction sont stockés dans des rollback segments jusqu’à ce qu’une instruction COMMIT ou ROLLBACK soit exécutée, et à partir de ce moment, ces segments sont disponibles pour être réutilisés.
En plus, avec la gestion automatique des annulations (apparue en 10g), le DBA peut spécifier une durée de conservation supplémentaire des informations d’annulation après le COMMIT, afin d’éviter les erreurs de type « snapshot too old » sur les longues instructions.
Ceci est fait en définissant le paramètre UNDO_RETENTION. Sa valeur par défaut étant de 900 secondes (5 minutes), vous pouvez le modifier afin de garantir qu’Oracle conserve les journaux d’annulations pour des périodes plus longues. Vous devrez peut-être aussi garantir que cette durée de conservation soit respectée, et ceci en activant l’option « GARANTI » du tablespace UNDO
Question quelle est la valeur juste pour le parametre undo_retention ?
Pour moi la valeur correct doit correspondre à la valeur max sur un mois
voici la requête
SQL> select max(MAXQUERYLEN)
from v$undostat
where begin_time between to_date(’11/01/2016 22:30:00′,’MM/DD/YYYY HH24:MI:SS’)
and to_date(’11/29/2016 09:30:00′,’MM/DD/YYYY HH24:MI:SS’)
order by begin_time;
MAX(MAXQUERYLEN)
—————-
8510
Toutefois, il convient d’ajuster les paramètres importants suivants :
1 – La taille du tablespace UNDO
2 – Le paramètres d’initialisation UNDO_RETENTION
voici la formule qui permet d’avoir une taille optimal
optmal undo retention = actual undo size /(db_block_size * undo_block_per_sec)
Dans mon cas le tablespace est en autoextend yes avec une valeur à unlimited ( préco de l’éditeur du logiciel , suis pas tres d’accord mais bon …)
calcule de la taille du tablespace undo optimal qui supportera l’activité de la base :
SQL> SELECT d.undo_size/(1024*1024) « ACTUAL UNDO SIZE [MByte] »,
2 SUBSTR(e.value,1,25) « UNDO RETENTION [Sec] »,
3 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
4 g.undo_block_per_sec) / (1024*1024)
5 « NEEDED UNDO SIZE [MByte] »
6 FROM (
7 SELECT SUM(a.bytes) undo_size
8 FROM v$datafile a,
9 v$tablespace b,
10 dba_tablespaces c
11 WHERE c.contents = ‘UNDO’
12 AND c.status = ‘ONLINE’
13 AND b.name = c.tablespace_name
14 AND a.ts# = b.ts#
15 ) d,
16 v$parameter e,
17 v$parameter f,
18 (
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’
ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
———————— ————————- ————————
10592 7200 2012.39063
La taille du undo calculer (2012.4) est inférieur à la taille actuelle rien à faire .
la requête suivant permet de calculer la durée optimal pour le undo retention
SQL> SELECT d.undo_size/(1024*1024) « ACTUAL UNDO SIZE [MByte] »,
2 SUBSTR(e.value,1,25) « UNDO RETENTION [Sec] »,
3 ROUND((d.undo_size / (to_number(f.value) *
4 g.undo_block_per_sec))) « OPTIMAL UNDO RETENTION [Sec] »
5 FROM (
6 SELECT SUM(a.bytes) undo_size
7 FROM v$datafile a,
8 v$tablespace b,
9 dba_tablespaces c
10 WHERE c.contents = ‘UNDO’
11 AND c.status = ‘ONLINE’
12 AND b.name = c.tablespace_name
13 AND a.ts# = b.ts#
14 ) d,
15 v$parameter e,
16 v$parameter f,
17 (
18 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
19 undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’ 20 21 22 23 ;
ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
———————— ————————- —————————-
10592 7200 37896
La durée est de 37896 seconde soit 10h
Solution :
alter system set undo_retention = 37896
oraclementvotre