Hi Tom,
Could you please advise on what the appropriate timestamp is for a Point In Time recovery that won't depend on backups done on different days.
I understand you need to select a time where you have a backup of datafiles before that time, and archive logs up to that time.
During the backup, the command ,
BACKUP
FULL
FORMAT '%d_%T_%s_%p_FULL'
TAG 'FULL'
DATABASE
INCLUDE CURRENT CONTROLFILE
PLUS ARCHIVELOG FORMAT 'ARCH_%d_%s_%p_%T';
is used, so there's a backup of the archive logs generated during the backup.
What timestamp should be used in the restore & recovery such that it doesn't require archive logs from future backups ,or datafiles from previous backups.(Which may not be available).
I want the recovery to be able to use just the datafiles and archive logs taken during that particular backup.
Thanks.
If you run a LIST BACKUP command for that backup you just took, you'll see something like this:
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
65 37.50K DISK 00:00:00 12-MAY-20
BP Key: 65 Status: AVAILABLE Compressed: NO Tag: TAG20200512T165120
Piece Name: D:\ORACLE\RECOVERY\DB19\BACKUPSET\2020_05_12\O1_MF_ANNNN_TAG20200512T165120_HCNRW8J9_.BKP
List of Archived Logs in backup set 65
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 149 16328555150539 12-MAY-20 16328555150629 12-MAY-20
1 150 16328555150629 12-MAY-20 16328555152563 12-MAY-20
1 151 16328555152563 12-MAY-20 16328555152590 12-MAY-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
66 Full 18.23M DISK 00:00:01 12-MAY-20
BP Key: 66 Status: AVAILABLE Compressed: NO Tag: TAG20200512T165121
Piece Name: D:\ORACLE\RECOVERY\DB19\AUTOBACKUP\2020_05_12\O1_MF_S_1040230281_HCNRW9W1_.BKP
SPFILE Included: Modification time: 01-MAY-20
SPFILE db_unique_name: DB19
Control File Included: Ckp SCN: 16328555150654 Ckp time: 12-MAY-20
RMAN>
(Full disclosure - I've added some fictitious lines of archives to demonstrate the point)
Thus we have your full (but inconsistent) backup plus the archivelogs. The "Next SCN" is the SCN for the archive logs as per V$ARCHIVED_LOG, namely it is the first SCN in the NEXT archive log.
Hence, you can see that Next matches the Low for the next log
Thrd Seq Low SCN Low Time Next SCN Next Time
1 149 16328555150539 12-MAY-20 16328555150629 12-MAY-20
1 150 16328555150629 12-MAY-20 16328555152563 12-MAY-20
1 151 16328555152563 12-MAY-20 16328555152590 12-MAY-20
Hence you could use the UNTIL SCN clause, to nominate the last SCN that your list archivelog covers, ie, Next Scn - 1
Since you are tagging your backups, then there is also the "ARCHIVELOG TAG tag_name" option