We have (for some forgotten reason) on our main production db
* 20 fixed (f 262144) After Image extents.
* After Image Archiving set to -1 (ie archive when full).
This is a bad combination since extents are almost never full in our environment. Extents are therefore rarely archived.
Simply switching to an interval value (10 minutes or similar) is also not very good because of the large extents.
What's a good approach here?
- Switch to non-fixed extents?
- Switch to a fixed but smaller amount of files (200 files x 1/10th the size or similar)?
- Combine some of the above with a reasonable interval?
- Something else?
Note: we also run replication.
Linux, 64-bit, 11.6.3 (11.7 in a few weeks).
0) 262144 is not large
1) archiver archives only the transaction log data, not the free space
It's large for the purpose on archiving only "on demand", since this ondemand for our use case happens four times per 24 hours or so, but perhaps not large in other senses.
I'm pretty sure we tested setting an archiving interval and had extensive growth in file usage, I'll try it again just to make sure.
My approach at my clients' sites is to use variable AI extents and use the AI File Management Daemon to switch at an interval appropriate for their RPO. There may be some very large/busy databases where variable extents are not the best choice but my databases tend to be on the small size (10s to a few 100s of GB) so using variable is not problematic.
On the DR side, you can conserve space by compressing *older* AI files. It is a good idea to test your backups daily and keep the AI files since the last backup uncompressed, to minimize recovery time. Tip of the hat to Mike Furgal for that insight. If your network between DR and prod is slow, keep a copy of those recent files on prod as well as DR.
Obviously I must have done something strange the last time. After setting archiving to 10 minutes intervals I can see that files are not very large. Lots of them are 256K rather than 256M, perhaps I didn't see that K last time... Anyhow thanks!