Showing posts with label rman. Show all posts

Wednesday, January 10, 2024

Query to check rman backup details in oracle

Query to check rman backup details in oracle:

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 999,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 in ('I0', '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;

>> on the prompt enter the # of days you want the backup for.

This is controlled by below setting in rman:

Configure RMAN output to keep for 'n' days;

where n is the number of days the information is retained.

This is a quick query sharing blog.

Thanks

Saturday, September 30, 2023

Procedure to restore selected list of archivelogs in oracle

Procedure to restore selected list of archivelogs

Steps overview:

0. Verify the control_file_record_keeptime parameter

1. Find out the list of archivelogs really available

2. Now define the goal of which archives we needed to restore

3. Verify if we have backup for the archvielog

4. Kick off the restore from rman

5. Verify if the archivelogs are now showing in v$archived_log

Execution:

0. Verify the control_file_record_keep_time parameter

show parameter control_file_record_keep_time;

Output:

SQL> show parameter control_file_record_keep_time;

NAME TYPE VALUE

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

control_file_record_keep_time integer 60

1. Find out the list of archivelogs really available

export NLS_DATE_FORMAT='DD/MON/YYYY HH24:MI:SS';

rman> crosscheck archivelog all;

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

set lines 200

set pages 3000

set colsep ,

select sequence#,status,completion_time,first_change#,next_change# from v$archived_log order by 1;

Output:

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=73 device type=DISK