RMAN - aid for identifying archived logs and controlfiles from database dictionary views

-- ------------------------------------
--
-- Find archivelog details details
--
-- The "SET_COUNT" is used in the disk file name e.g.31538 = ora_MY_DB12_ARC_piopvbq5_1_1_832548677_31538_1
--  The SEQUENCE# is used to restore archivelog e.g. RESTORE ARCHIVELOG from logseq 16414 until logseq 16434 thread=2;
--
--
-- ------------------------------------

break on thread# skip 1 on Date on SEQUENCE#
col "Date" format a25

select
-- 'RESTORE ARCHIVELOG from logseq '||min(a.SEQUENCE#)||' until logseq '||max(a.SEQUENCE#)||' thread='||a.thread#||';' ARCH_LOG_FILE
a.SEQUENCE#
-- , a.thread#
, to_char(a.first_time, 'DD-MON-YYYY HH24:MI') "Date"
, c.SET_COUNT, a.thread#
from v$log_history a, v$archived_log b,  V$BACKUP_REDOLOG c
where a.sequence# = b.sequence#
and b.sequence# = c.sequence#
and a.NEXT_CHANGE# = b.NEXT_CHANGE#
and b.NEXT_CHANGE# = c.NEXT_CHANGE#
and a.thread#=b.thread#
and a.first_time between
to_date('23-NOV-2013 08:00:00','DD-MON-RRRR HH24:MI:SS') and to_date('23-NOV-2013 14:00:00','DD-MON-RRRR HH24:MI:SS')
order by a.thread#, 1
/

-- ------------------------------------
--
-- Find archivelog details details
--
-- RMAN syntax generater for restoring archivelog files
--
-- ------------------------------------
col ARCH_LOG_FILE format a120

select
'rman<<END'||chr(10)||
'connect target /'||chr(10)||
'connect catalog MY_DB1/password@CATDB'||chr(10)||
'run'||chr(10)||
'{'||chr(10)||
' allocate channel ch00 type ''sbt_tape'';'||chr(10)||
' SEND ''NB_ORA_SERV=bkup-mstr_srvr.svr.emea.sdunnings.net,NB_ORA_CLIENT=my-client.svr.emea.sdunnings.net'';'||chr(10)||
' SET ARCHIVELOG DESTINATION TO ''/share/dbbkup/oracle/MY_DB1/rman/.SED'';' ARCH_LOG_FILE
from dual
union all
select
'RESTORE ARCHIVELOG from logseq '||min(a.SEQUENCE#)||' until logseq '||max(a.SEQUENCE#)||' thread='||a.thread#||';' ARCH_LOG_FILE
from v$log_history a, v$archived_log b,  V$BACKUP_REDOLOG c
where a.sequence# = b.sequence#
and b.sequence# = c.sequence#
and a.NEXT_CHANGE# = b.NEXT_CHANGE#
and b.NEXT_CHANGE# = c.NEXT_CHANGE#
and c.SET_COUNT in
(select
distinct c.SET_COUNT
from v$log_history a, v$archived_log b,  V$BACKUP_REDOLOG c
where a.sequence# = b.sequence#
and b.sequence# = c.sequence#
and a.NEXT_CHANGE# = b.NEXT_CHANGE#
and b.NEXT_CHANGE# = c.NEXT_CHANGE#
and a.thread#=b.thread#
and a.first_time between
to_date('23-NOV-2013 08:00:00','DD-MON-RRRR HH24:MI:SS') and to_date('23-NOV-2013 14:00:00','DD-MON-RRRR HH24:MI:SS')
)
group by a.thread#
union all
select
'}'||chr(10)||
'END' ARCH_LOG_FILE
from dual
-- order by a.THREAD#
/


-- ------------------------------------
--
-- Find controlfile details
--
--
--
--
--
-- ------------------------------------

col START_TIME format a25
col COMPLETION_TIME format a25
col HANDLE format a25
col DEVICE_TYPE format a8
col controlfile_included format a7 heading 'Control|File|Inc'
col INCREMENTAL_LEVEL format 999 heading 'Inc|Level' wrap

select
  -- d.bs_key,
  d.backup_type, d.controlfile_included , d.incremental_level, d.pieces,
  to_char(d.start_time, 'DD-MON-RRRR HH24:MI:SS') start_time,
  -- to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time
  -- d.elapsed_seconds
  d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes,
  -- , s.input_file_scan_only,
  z.HANDLE,
  z.TAG,
  q.INPUT_TYPE
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
  join V$BACKUP_PIECE z on z.set_stamp = d.set_stamp and z.set_count = d.set_count
  join v$rman_backup_job_details q on q.SESSION_STAMP=d.SESSION_STAMP
where d.start_time
between
to_date('23-NOV-2013 08:00:00','DD-MON-RRRR HH24:MI:SS') and to_date('23-NOV-2013 14:00:00','DD-MON-RRRR HH24:MI:SS')
and d.controlfile_included != 'NO'
and z.HANDLE is not null
order by d.start_time
/

col FILESIZE_DISPLAY format a10

select BTYPE
, ID2 "BACKUPSET SET_COUNT"
,to_char(CHECKPOINT_TIME,'DD-MON-RRRR HH24:MI:SS') "Date"
, FILESIZE_DISPLAY
from V$BACKUP_CONTROLFILE_DETAILS
where CHECKPOINT_TIME
between
to_date('23-NOV-2013 08:00:00','DD-MON-RRRR HH24:MI:SS') and to_date('23-NOV-2013 14:00:00','DD-MON-RRRR HH24:MI:SS')
/

-- ------------------------------------
--
-- Find controlfile details details
--
-- RMAN syntax generater for restoring controlfile files
--
-- ------------------------------------

select
'rman<<END'||chr(10)||
'connect target /'||chr(10)||
'connect catalog MY_DB1/password@CATDB'||chr(10)||
'run'||chr(10)||
'{'||chr(10)||
' allocate channel ch00 type ''sbt_tape'';'||chr(10)||
' SEND ''NB_ORA_SERV=bkup-mstr_srvr.svr.emea.sdunnings.net,NB_ORA_CLIENT=my-client.svr.emea.sdunnings.net'';' CNTRLFILE
from dual
union all
select
-- z.HANDLE CNTRLFILE
'RESTORE CONTOLFILE to ''/share/dbbkup/oracle/MY_DB1/rman/.SED/control.tmp'' from '''||z.HANDLE||''' ;' CNTRLFILE
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
  join V$BACKUP_PIECE z on z.set_stamp = d.set_stamp and z.set_count = d.set_count
  join v$rman_backup_job_details q on q.SESSION_STAMP=d.SESSION_STAMP
where d.start_time
between
to_date('23-NOV-2013 08:00:00','DD-MON-RRRR HH24:MI:SS') and to_date('23-NOV-2013 14:00:00','DD-MON-RRRR HH24:MI:SS')
and d.controlfile_included != 'NO'
and z.HANDLE is not null
union all
select
'}'||chr(10)||
'END' CNTRLFILE
from dual
-- order by a.THREAD#
/