Consultas utiles con Rman

Posted on por

Hoy vamos a ver una entrada especial para dummies.
Vamos a utilizar esta entrada para ir recopliando algunas de las consultas utiles que podemos necesitar relaccionadas con rman .
Me gustaria decir que estas consultas son mias, pero varias de ellas han sido recopiladas a lo largo del tiempo , bien ca trabajo propio, como de casos con Oracle como de algunas webs que me han ayudado, con lo que, si alguien encuentra alguna y recooce a su propietario,e stare encantado de enlazarlo en caso que lo requiera

Informe de los ultimos backups de rman 

set pagesize 200
set linesize 200
col COMMAND_ID format a20;
col status  format a20;
col Gb format 99999999;
 col OBJECT_TYPE format a20;

Select COMMAND_ID,
status,
round(output_bytes /1024/1024,2)Gb ,
start_time,end_time ,
OBJECT_TYPE,round((end_time-start_time )*24,1) Duracion
 from v$rman_status 
where 
OBJECT_TYPE in ('DB FULL','DB INCR') 
and output_bytes  > 0 
order by start_time desc ;

Ver los jobs de RMAN que ha habido 

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

Operaciones acrivas de Rman 

set linesize 200;
set pagesize 9000;
 column STATUS FORMAT A15;
 column APPLIED FORMAT A4;
COLUMN NAME format a20;
column DB_UNIQUE_NAME format a20;
COLUMN sequence# CLEAR;
column OPEN_MODE format a10;
column name format a100;
col OPNAME for a40;
set linesize 220 pagesize 10000
col OPNAME for a40
select OPNAME,SOFAR/TOTALWORK*100 PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES,
trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA
from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN%';


 select OPNAME,SOFAR/TOTALWORK*100 PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES,
  trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA
  from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN: incremental datafile%';

En fin, como dije en un principio, simplemente una recopilacion de consultas utiles

Deja una respuesta