On Wednesday, 22 February 2017 at 02:15:04 UTC+13,
wolfgan...@gmail.com wrote:
> Hi
> We have a database nearly 3 TByte big where most of the data (80%) is stored in tablespaces which hold "archival data". This archival data is just reorganized every at the beginning to hold data of the last year. The data is still used in joins or directly with a select in an transactions also handling the active data.
> Because the tables and indexes in this "archive" tablespaces is reorganized and read only and will never change again, I thought it would be good to backup this tablespaces only once in the year and for the other tablespaces I make a weekly online tablespace backup including logs and on the other days incremental online backup.
> I have simulated that for a system crash and for restoring some tablespaces into a different instance to get data back which could be destroyed of human errors etc..
> My question now is if I have also to save the archived logs regularly since the backups of the large archived tablespaces were performed?
[test detail snipped]
What a great question this was! Shame there were no answers. Still, 6 years and a week late, not so bad...
The answer is that if you only need to recover the archive tablespaces to end of backup (way back) then you should not need archive logs beyond that PIT - provided that those tablespaces are self-consistent (e.g. there are no tables in these tablespaces that use other tablespaces with a different PIT for indexes or partitions). So it would be sensible to test what happens if somebody created such an object at a later date (without checking, I believe this would block recovery). It might also be sensible to INCLUDE LOGS in those archive backups, to mitigate any risk that they are removed after a certain age.
Jeremy