Oracle : agrandir les redo logs et surveiller les changements de fichier
Procédure pour remplacer les groupes de redo logs par des groupes plus grands, avec requêtes de surveillance des log switches et du redo généré.
Procédure pour agrandir les groupes de redo logs Oracle : si les changements de fichier (log switches) surviennent plus de quelques fois par heure, il est conseillé d'augmenter la taille des redo logs. Les chemins et tailles ci-dessous sont des exemples à adapter.
1. Lister les groupes et leurs membres
set linesize 300
column GROUP# format 99999
column STATUS format a9
column TYPE format a7
column MEMBER format a100
column IS_RECOVERY_DEST_FILE format a100
select GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE from v$logfile;
column REDOLOG_FILE_NAME format a50
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ;
Exemple de résultat (3 groupes, 2 membres chacun) :
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
------ ------- --------- --- --------- --------------------------------------- -------
4 1 20338 NO CURRENT /u02/oradata/ORCL/redo04a.log 600
4 1 20338 NO CURRENT /u01/oradata/ORCL/redo04b.log 600
5 1 20336 YES INACTIVE /u02/oradata/ORCL/redo05a.log 600
5 1 20336 YES INACTIVE /u01/oradata/ORCL/redo05b.log 600
6 1 20337 YES INACTIVE /u02/oradata/ORCL/redo06a.log 600
6 1 20337 YES INACTIVE /u01/oradata/ORCL/redo06b.log 600
2. Comprendre les commandes de bascule
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM CHECKPOINT;
SWITCH LOGFILEchange simplement de groupe courant (le groupeCURRENTdevientACTIVE, un autre devientCURRENT) et ne garantit pas un archivage immédiat.ARCHIVE LOG CURRENTprovoque l'archivage immédiat du groupe courant.CHECKPOINTécrit dans les fichiers de données les transactions validées à partir des blocs du cache. Tous les groupesACTIVEdeviennentINACTIVE.
3. Remplacer un groupe
Un groupe ne peut être supprimé que s'il n'est ni CURRENT ni nécessaire à la récupération d'instance. Procéder groupe par groupe, en commençant par un groupe INACTIVE.
-- Le groupe à supprimer ne doit pas être CURRENT
select group#, status from v$log;
-- Si besoin, changer de groupe courant (parfois jusqu'à 3 fois)
alter system switch logfile;
select group#, status from v$log;
-- Supprimer le groupe
alter database drop logfile group 5;
Si la suppression échoue (par exemple ORA-01624: log 5 needed for crash recovery of instance ...), forcer un checkpoint puis recommencer :
alter system checkpoint;
-- ou, en RAC :
alter system checkpoint global;
alter database drop logfile group 5;
Supprimer ensuite les anciens fichiers au niveau du système d'exploitation, puis recréer le groupe avec la nouvelle taille :
rm /u02/oradata/ORCL/redo05a.log
rm /u01/oradata/ORCL/redo05b.log
alter database add logfile group 5
('/u02/oradata/ORCL/redo05a.log', '/u01/oradata/ORCL/redo05b.log') size 1024M;
Depuis SQL*Plus, les commandes système peuvent aussi être lancées avec ! (par exemple !sudo rm /u02/oradata/ORCL/redo05a.log).
Répéter l'opération pour chaque groupe. Le groupe CURRENT sera traité en dernier, après un alter system switch logfile; suivi d'un alter system checkpoint;. Si les groupes n'ont qu'un seul membre, la syntaxe est la même avec un seul fichier :
alter database add logfile group 1 ('/u02/oradata/ORCL/redo01.log') size 2G;
4. Vérifier le résultat
select group#, bytes/1024/1024 as size_mb, status from v$log;
Vérifier que chaque groupe affiche la nouvelle taille. Pour retirer un groupe inutile en dernier recours, alter database clear logfile group N; réinitialise son contenu.
Surveillance
Nombre de changements de redo par jour et par heure
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select
trunc(first_time) DAY,
trunc(first_time,'HH24') DATE_HOUR,
count(*) LOG_SWITCHES
from v$log_history
group by rollup( trunc(first_time) , trunc(first_time,'HH24') )
order by 1,2;
Cumul des changements par jour
WITH row_source AS
( select
trunc(first_time) DAY,
trunc(first_time,'HH24') DAY_HOUR,
count(*) LOG_SWITCHES_PER_HOUR
from v$log_history
group by trunc(first_time) , trunc(first_time,'HH24') )
select DAY, DAY_HOUR, LOG_SWITCHES_PER_HOUR,
sum(LOG_SWITCHES_PER_HOUR) OVER (PARTITION BY DAY ORDER BY DAY_HOUR) "TOTAL_SWITCHES_SOFAR_TODAY"
from row_source
order by 1,2;
Sessions générant le plus de redo
select
s.sid,
n.name,
s.value,
sn.username,
sn.program,
sn.type,
sn.module
from
v$sesstat s join v$statname n on n.statistic# = s.statistic#
join v$session sn on sn.sid = s.sid where name like '%redo entries%'
order by
value desc;
select s.inst_id, s.sid, serial#, program, module, username, value redo_size
from gv$session s, gv$sesstat ss, v$statname sn
where s.sid = ss.sid and ss.statistic# = sn.statistic# and sn.name = 'redo size'
and s.inst_id = ss.inst_id
order by redo_size desc;
Remarques
- Prévoir des groupes plus grands que la charge ne l'exige évite des changements de fichier trop fréquents ; les tailles (450 Mo, 1 Go, 2 Go) vues dans les exemples sont à choisir selon le volume de redo généré.
- Les redo logs étant multiplexés, garder le même nombre de membres par groupe après recréation.
- Le paramètre
LOG_CHECKPOINT_INTERVALpeut aussi influencer la fréquence des checkpoints. - Référence : Resize redo log members.