génération des archivelogs

Pour avoir des informations sur la génération des archivelog :

select to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from v$log_history
group by to_char(first_time,'YYYY-MON-DD');



select count(recid), trunc(sum(blocks*block_size)/1024/1024) "Size Mo", trunc(first_time,'HH24') "Time by Hour"
from v$archived_log
where creator = 'ARCH'
group by trunc(first_time,'HH24')
order by trunc(first_time,'HH24') desc;

Suivre les activités du backup RMAN

Voici quelques requêtes bien utiles pour suivre les activités les activités d'un job de backup  RMAN :

Select   sid,   start_time,   totalwork  sofar,  (sofar/totalwork) * 100 pct_done
from   v$session_longops
where
   totalwork > sofar
AND    opname NOT LIKE '%aggregate%'
AND    opname like 'RMAN%';

Select    sid,    spid,    client_info,    event,    seconds_in_wait,    p1, p2, p3
 from    v$process p,    v$session s
 where    p.addr = s.paddr
 and    client_info like 'rman channel=%';

Pour suivre le status d’un job de backup avec RMAN :

REM RMAN Progress
alter session set nls_date_format='dd/mm/yy hh24:mi:ss'
/
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'
/

REM RMAN wiats
set lines 120
column sid format 9999
column spid format 99999
column client_info format a25
column event format a30
column secs format 9999
SELECT SID, SPID, CLIENT_INFO, event, seconds_in_wait secs, p1, p2, p3
  FROM V$PROCESS p, V$SESSION s
  WHERE p.ADDR = s.PADDR
  and CLIENT_INFO like 'rman channel=%'
/

This script by Osama Mustafa will monitor the progress of a running RMAN job:

Une autre requête pour suivre l’avancement d’un job de backup RMAN :

select    to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
   sofar,
   totalwork,
   elapsed_seconds/60 "ELAPSE (Min)",
   round(sofar/totalwork*100,2) "Complete%"
from    sys.v_$session_longops
where  compnam = 'dbms_backup_restore';

Requête ASM pour suivre l'opération du rebalance

 select INST_ID, GROUP_NUMBER, OPERATION, PASS, STATE, ACTUAL, EST_MINUTES, EST_RATE  from  gv$asm_operation where state = 'RUN'

actuellement : 

   INST_ID GROUP_NUMBER OPERATION       PASS                        STATE            ACTUAL EST_MINUTES   EST_RATE

---------- ------------ --------------- --------------------------- ------------ ---------- ----------- ----------

         2            1 REBAL           REBALANCE                   RUN                   4         288       5476

Normalement le ESt_MINUTES doit baisser, une fois le rebalance est fini il n'y aura aucune ligne 

Les sessions avec des LOCKs

Requête pour avoir des infos sur les sessions qui lock :


select blocking_session,blocked_session,script from
( select distinct
s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session,
s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session,
decode(s1.type,'USER','alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' ,null, s2.LAST_CALL_ET/60)
script ,
count(*) over (partition by s1.inst_id,s1.sid) blocked_cnt
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.block > 0 and l2.request > 0
and l1.id1 = l2.id1 and l1.id2 = l2.id2 )
order by blocked_cnt desc;


select LAST_CALL_ET/60 , blocking_session,blocked_session,script from
( select distinct s2.LAST_CALL_ET,
s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session,
s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session,
decode(s1.type,'USER','alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' ,null )
script ,
count(*) over (partition by s1.inst_id,s1.sid) blocked_cnt
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.block > 0 and l2.request > 0
and l1.id1 = l2.id1 and l1.id2 = l2.id2 )
order by blocked_cnt desc;