Incomplete recovery script with sequence and time which generate set until statement for RMAN

Incomplete recovery script with sequence and time detail generate set until statement for RMAN

Check the RMAN backup is completed detail
No of rows tell how many backup is completed and last backup date and time.

col inputgb for 9999.99
col outputgb for 9999.99
col time for 9999.99
col start_Time for a21
col end_time for a21
col status for a10
select session_key,session_recid,session_stamp, to_char(start_time, 'MON/DD/YYYY HH24:MI:SS') Start_time,to_char(END_Time, 'MON/DD/YYYY HH24:MI:SS') END_Time, input_bytes/(1024*1024*1024) as "InputGB", output_bytes/(1024*1024*1024) as "outputGB", status,elapsed_seconds/60/60 as "time" from v$rman_backup_job_details order by 1;

Output:

SESSION_KEY SESSION_RECID SESSION_STAMP START_TIME           END_TIME              InputGB outputGB STATUS         time
----------- ------------- ------------- -------------------- -------------------- -------- -------- ---------- --------
          1             1    1043848916 JUN/23/2020 14:03:30 JUN/23/2020 14:05:34     8.41     5.90 COMPLETED       .03
          6             6    1043850228 JUN/23/2020 14:24:17 JUN/23/2020 14:26:16     8.41     5.90 COMPLETED       .03

Script to restore the archive from present backup in Oracle
From upper script you got two backups are preent with time and detail and from this script you restore archive from them.

select ' restore archivelog from logseq ' || Min_arch_backup.startNo || ' until logseq ' || Max_arch_backup.endNo || ' thread=' || Max_arch_backup.thread# || ';' "Restore Command"
from
(select thread#,session_key,session_recid,session_stamp,min(sequence#) startNo from v$backup_archivelog_details group by thread#,session_key,session_recid,session_stamp) Min_arch_backup,
(select thread#,session_key,session_recid,session_stamp, max(sequence#) endNo from v$backup_archivelog_details group by thread#,session_key,session_recid,session_stamp) Max_arch_backup
where Min_arch_backup.thread# = Max_arch_backup.thread# and Min_arch_backup.session_key = Max_arch_backup.session_key and
Min_arch_backup.session_recid = Max_arch_backup.session_recid and Min_arch_backup.session_stamp = Max_arch_backup.session_stamp;

Output:
Restore Command
------------------------------------------------------------------
restore archivelog from logseq 2407 until logseq 2715 thread=1;
restore archivelog from logseq 2413 until logseq 2721 thread=1;

Get the SET UNTIL Statement ffrom last Backup having Maximum Sequence of Archive Backup
In next query you will get the sequence and time if you know the specific time then use that query to find sequence or you can use set until time option in RMAN.

select 'set until sequence ' || seq# || ' thread ' || thread# || '; ' || chr(13)|| chr(10) || 'recover database ;' "Recover Command"
from (
select * from (
select thread#, sequence# + 1 seq#, next_change# from (
select * from v$backup_archivelog_details
where thread# || '_' || sequence# in
(select thread# || '_' || max(sequence#) from v$backup_archivelog_details group by thread#)
) order by next_change#
) where rownum = 1 ) ;

Output:
Recover Command
--------------------------------------------
set until sequence 2722 thread 1;
recover database ;

Get know the Sequence# and time you want to do incomplete recovery

--For last Sequence time detail
select session_key,session_recid,session_stamp,thread#, Sequence# "Last Archive Log Backup Sequence#",
to_char(next_time,'MON/DD/YYYY HH24:MI:SS') "Time" from v$backup_archivelog_details where thread# || '_' || sequence# in
(select thread# || '_' || max(sequence#) from v$backup_archivelog_details group by thread#);

--For last 10 Sequence time detail
select * from (
select session_key,session_recid,session_stamp,thread#, Sequence# "Last Archive Log Backup Sequence#",
to_char(next_time,'MON/DD/YYYY HH24:MI:SS') "Time" from v$backup_archivelog_details order by sequence# desc)
where rownum <= 10;

-- For all Sequence time detail
select session_key,session_recid,session_stamp,thread#, Sequence# "Last Archive Log Backup Sequence#",
to_char(next_time,'MON/DD/YYYY HH24:MI:SS') "Time" from v$backup_archivelog_details order by sequence# desc;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.