Script to identify the restore/recover point for archive logs

I did some work on an interesting project to keep a standby database in sync with a production primary database manually. This is not a true standby database as the primary database does not communicate with this standby database. Due to certain reason, we could not configure Data Guard to allow data replication between these two database. So no way to do the redo log shipping like we do in Data Guard environment. What I mean manually is we take the archivelog backup from the previous day, restore and recover to this standby database. As this database is a VLDB, the volume of daily archive log files is in size of multi-terabyte. We use an Exadata X-4 full rack to host this standby database. Even with restoring using all db nodes and 200+ channels, it still take several hours for restore only. And similar timing in recovering these archive logs. Not mention the time copying file between two data centers. It takes a lot of efforts to keep up with production primary database and reduce the lag between these two databases.

The benefit doing this manually is the minimum impact in current production environment. The only overhead on production db is when copying files to Exadata. The impact is quite low. We scp rman backup pieces using all 8 db nodes to maximize the utilization of band width.

One major task during this restore and recover is to identify the correct restore and recover point from the daily rman backupset for the archive logs. Identify the right recover point, different people might have different opinions. Just like the image below, how many bars can you see, three or four?

three_or_four_bars

There are many blogs and articles discussing the way to identify the correct restore and recover points. The majority of people like to use v$archived_log view to get the recover point. In my scenario, it did not work well as I can get the correct recovery point only after I restore all the archive logfiles. What I want is after cataloging the rman backup piece, what are last applied archive log sequence for each thread, and what are my next recover point for the current rman backup pieces that are just cataloged.

Using both v$archived_log and v$backup_archivelog_details views,  I created a script that can help me answer all the questions I have.
1. The restore commands can be used for each thread
2. The last applied archive log sequence for each thread, also include the timestamp and next change SCN#
3. The last possible recover point for each thread for the cataloged rman backup pieces
4. The recover command

The script is listed as follows:

db_arc_seq_range.sql

col "Restore Command" for a100
col "Applied Logs" for a100
col "Catalog Logs" for a100
col "Recover Command" for a80
select ' restore archivelog from logseq ' || applied_arc.startNo || ' until logseq ' || catalog_arc.endNo || ' thread=' || catalog_arc.thread# || ';' "Restore Command"
from
--(select thread#,max(sequence#) + 1 startNo from gv$archived_log where applied='YES' group by thread#) applied_arc,
(select thread#,max(sequence#) startNo from gv$archived_log where applied='YES' group by thread#) applied_arc,
(select thread#, max(sequence#) endNo from v$backup_archivelog_details group by thread#) catalog_arc
where applied_arc.thread# = catalog_arc.thread#;

prompt '=========== Archive Log Info ============='
select distinct 'Thread ' || thread# || ': last applied archive log ' || sequence# || ' at ' || to_char(next_time, 'MON/DD/YYYY HH24:MI:SS') || ' next change# ' || next_change# "Applied Logs"
from v$archived_log
where thread# || '_' || sequence# in
(select thread# || '_' || max(sequence#) from v$archived_log where applied='YES' group by thread#)
--and applied='YES'
;
select 'Thread ' || thread# || ': last cataloged archive log ' || sequence# || ' at ' || to_char(next_time, 'MON/DD/YYYY HH24:MI:SS') || ' next change# ' || next_change# "Catalog Logs"
from v$backup_archivelog_details
where thread# || '_' || sequence# in
(select thread# || '_' || max(sequence#) from v$backup_archivelog_details group by thread#)
;

prompt '=========== recover point ================'
--select 'recover database until sequence ' || seq# || ' thread ' || thread# || ' delete archivelog maxsize 4000g; ' Content
select 'set until sequence ' || seq# || ' thread ' || thread# || '; ' || chr(13)|| chr(10) || 'recover database delete archivelog maxsize 4000g; ' "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
)
;

The following example shows the execution of the script.

SYS&gt; <strong>@db_arc_seq_range</strong>
Restore Command
----------------------------------------------------------------------------------------------------
restore archivelog from logseq 323498 until logseq 324015 thread=1;
restore archivelog from logseq 351250 until logseq 351828 thread=2;
restore archivelog from logseq 308766 until logseq 309396 thread=3;
restore archivelog from logseq 345805 until logseq 346271 thread=4;
restore archivelog from logseq 629650 until logseq 630749 thread=5;
restore archivelog from logseq 502202 until logseq 502899 thread=6;

6 rows selected.

'=========== Archive Log Info ============='

Applied Logs
----------------------------------------------------------------------------------------------------
Thread 1: last applied archive log 323498 at SEP/16/2014 22:41:27 next change# 10900757473229
Thread 2: last applied archive log 351250 at SEP/16/2014 22:41:28 next change# 10900757476463
Thread 3: last applied archive log 308766 at SEP/16/2014 22:44:30 next change# 10900759270706
Thread 4: last applied archive log 345805 at SEP/16/2014 22:43:42 next change# 10900758591989
Thread 5: last applied archive log 629650 at SEP/16/2014 22:43:39 next change# 10900758575645
Thread 6: last applied archive log 502202 at SEP/16/2014 22:42:06 next change# 10900757720611

6 rows selected.
Catalog Logs
----------------------------------------------------------------------------------------------------
Thread 1: last cataloged archive log 324015 at SEP/17/2014 23:12:31 next change# 10902104866903
Thread 2: last cataloged archive log 351828 at SEP/17/2014 23:12:31 next change# 10902104871653
Thread 3: last cataloged archive log 309396 at SEP/17/2014 23:12:29 next change# 10902104850854
Thread 4: last cataloged archive log 346271 at SEP/17/2014 23:12:30 next change# 10902104860405
Thread 5: last cataloged archive log 630749 at SEP/17/2014 23:12:30 next change# 10902104862135
Thread 6: last cataloged archive log 502899 at SEP/17/2014 23:12:32 next change# 10902104879394

6 rows selected.

'=========== recover point ================'

Recover Command
--------------------------------------------------------------------------------
set until sequence 309397 thread 3;
recover database delete archivelog maxsize 4000g;

It shows that we completed the restore and recover of Sep. 16’s archive logs and the recover point for Sep. 17’s archive logs is sequence 309397 thread 3.