I couldn't find any clause to the REPORT
or LIST
commands which would allow you to specify the incarnation of interest.
You could however correlate your latest incarnation reset time with backup completion time to identify the backups completed before your current incarnation began.
First, let's set the environment variables so that RMAN output also includes time.
$ NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
$ NLS_TIMESTAMP_FORMAT='DD.MM.YYYY HH24:MI:SS'
$ export NLS_DATE_FORMAT
$ export NLS_TIMESTAMP_FORMAT
Now let's examine which incarnations we have in RMAN repository.
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 OCP 2737702586 PARENT 1 25.08.2013 05:18:32
2 2 OCP 2737702586 PARENT 635002 05.03.2014 10:49:33
3 3 OCP 2737702586 PARENT 750347 13.03.2014 18:08:15
4 4 OCP 2737702586 PARENT 750727 18.03.2014 11:37:57
5 5 OCP 2737702586 CURRENT 1137478 05.08.2014 08:19:38
It's shown in the output that our current incarnation's reset time is 5 August 2014 8:19 AM (05.08.2014 08:19:38
).
I have some backups completed before and after the current incarnation began.
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28 B F A DISK 05.08.2014 08:21:56 1 1 YES TAG20140805T082051
29 B F A DISK 05.08.2014 08:12:43 1 1 YES TAG20140805T081243
30 B F A DISK 05.08.2014 08:10:36 1 1 YES TAG20140805T081036
31 B A A DISK 05.08.2014 08:21:59 1 1 YES TAG20140805T082159
32 B F A DISK 05.08.2014 08:13:48 1 1 YES TAG20140805T081243
33 B A A DISK 05.08.2014 08:20:49 1 1 YES TAG20140805T082048
34 B F A DISK 05.08.2014 08:20:51 1 1 YES TAG20140805T082051
35 B F A DISK 05.08.2014 08:11:51 1 1 YES TAG20140805T081036
36 B A A DISK 05.08.2014 08:20:48 1 1 YES TAG20140805T082048
Now we can list backups which were completed before the current incarnation began.
RMAN> list backup summary
2> completed before
3> "to_date('05.08.2014 08:19:38', 'dd.mm.yyyy hh24:mi:ss')";
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
29 B F A DISK 05.08.2014 08:12:43 1 1 YES TAG20140805T081243
30 B F A DISK 05.08.2014 08:10:36 1 1 YES TAG20140805T081036
32 B F A DISK 05.08.2014 08:13:48 1 1 YES TAG20140805T081243
35 B F A DISK 05.08.2014 08:11:51 1 1 YES TAG20140805T081036
As you can see, only backups with completion time before the current incarnation's reset time are shown in the output.
After you narrowed down the backups to the ones whose completion time is before your current incarnation's reset time using COMPLETED
subclause of the LIST
command, you can specify the same condition to the DELETE
command.
RMAN> delete backup
2> completed before
3> "to_date('05.08.2014 08:19:38', 'dd.mm.yyyy hh24:mi:ss')";
There are also AFTER
and BETWEEN
specifiers to the COMPLETED
subclause. See the section Listing Selected Backups and Copies in Database Backup and Recovery User's Guide. An example of BETWEEN
specifier usage:
RMAN> list copy of datafile 2 completed between '10-DEC-2002' and '17-DEC-2002';
UPDATE
You can switch between incarnations using RESET DATABASE TO INCARNATION n
command (check the scenarios of usage), but you'll always end up with the same output from the LIST
and REPORT
commands with respect to backup sets, no matter which incarnation you switch to.
You can search Database Backup and Recovery Reference for the word "incarnation", to see when you can specify the incarnation and to which RMAN commands.
First you need to ensure that that the backup is in your library (surely you know better ways for find it, I put my query):
select
D.DB_NAME, B.DB_ID, BASET.BCK_TYPE,
decode(BASET.CONTROLFILE_INCLUDED,'BACKUP','YES','NO') as CONTROLFILE_INCLUDED,
BASET.INCR_LEVEL, bapiece.tag, bapiece.handle, bapiece.start_time,
bapiece.completion_time,
round(bapiece.bytes/1024/1024,2) as Mb
from rman.bp bapiece,
rman.dbinc d,
rman.bs baset,
rman.db b
where BAPIECE.DB_KEY=D.DB_KEY
and D.DB_NAME='SFPROD'
and BASET.DB_KEY=D.DB_KEY
and BASET.BS_KEY = BAPIECE.BS_KEY
and bapiece.start_time>sysdate-1
and B.DB_KEY=D.DB_KEY
--- and bapiece.handle like '%.dbf%'
order by tag desc, handle desc ;
And... I hate to use "set until time" to restore/duplicate database. It is more human friendly but you can be surprised with an rman error at the end of the restore. Try to find the last commit (SCN) for duplicate process:
SQL> select max(next_change#)
from v$archived_log
where archived = 'YES'
group by thread#;
or with rman:
RMAN> list backup of archivelog all;
--Next SCN
And then:
run { set until scn ... ; allocate auxiliary channel t1 type 'sbt_tape'; duplicate target database to DEVDB; }
Best Answer
No, it does not remove backups. They are just missing from the controlfile, and you can catalog them. When the T database was refreshed yesterday from P, a new incarnation of T started. But your UNTIL TIME clause refers to a point of time before that happened, when the previous incarnation was active. After restoring the controlfile on C, mount the database, then you can use the following command to view the incarnations:
RMAN> list incarnation;
You will also see the times when they started. Revert to the previous incarnation, e.g:
RMAN> reset database to incarnation 1;
Catalog the backups:
RMAN> catalog start with '/path/to/the/backups';
Then continue with restore and recover as you normally would.