Hey all,
In 11.2.0.3, I want a list of all RMAN files created from the last full
backup plus the autobackup immediately following it. Here's the SQL I
came up with:
WITH tt AS
(
SELECT *
FROM
(
SELECT
tag,
next_key,
RANK() OVER (ORDER BY myrank NULLS LAST) newrank
FROM
(
SELECT
tag,
CASE WHEN tag LIKE :fullbacktag THEN RANK() OVER (ORDER BY
completion_time DESC) ELSE NULL END myrank,
RANK() OVER (PARTITION BY tag ORDER BY completion_time DESC) partrank,
LEAD(TAG) OVER (ORDER BY COMPLETION_TIME) NEXT_KEY
FROM v$backup_files
WHERE
tag LIKE :fullbacktag OR tag LIKE :autobacktag
)
WHERE partrank = 1
)
WHERE newrank = 1
)
SELECT vbf.fname
FROM v$backup_files vbf,tt
WHERE vbf.tag = tt.tag OR vbf.tag = tt.next_key;
The binds :fullbacktag and :autobacktag are the patterns for the RMAN
tags for the full backups and autobackups, respectively.
Yes, this works decently enough to stop me working on it, but as I tend
to plagiarize my old code, it seems like the SQL inside with WITH clause
could be subjectively "better" for my future code stealing.
Thoughts, anyone?
Rich
