Archivelog Space Needed Python Script

I wrote a script called to help size our Oracle archive log filesystems to support replication tools such as Fivetran, DMS, or GoldenGate which need a certain number of hours or days of archive log history at all times. In many cases we had backups that removed all the archive logs after they had been backed up once. So, that is essentially zero hours of history. If we only backed up once per day, it really peaked out at 24 hours of history, but the minimum was zero. Our replication products need 24 hours minimum in some cases. In other cases, we needed different numbers of hours. Also, the log backups and deletes run multiple times a day and on different schedules on some systems.

I based on a simplistic idea which I know is not perfect but so far it has been helpful. So, I thought I would share it here in case others can benefit. I would love any feedback, suggestions and criticism of the approach and implementation. The idea was to query V$ARCHIVED_LOG and see how full the filesystem would get if these same logs came in at the same times but with different retention times and different archive log filesystem sizes. I could try different settings and see the highest percentage that the filesystem hit.

I think this approach is imperfect because the past behavior recorded in V$ARCHIVED_LOG may not represent future behavior very well as things change. Also, the way I wrote it I assumed that the archive logs are laid down instantaneously. I.e. If the row in V$ARCHIVED_LOG has COMPLETION_TIME of 2/24/2024 16:11:15 then I assume that the filesystem gets (BLOCKS+1)*BLOCK_SIZE bytes fuller in that second. I also assume that the backups instantly remove all the logs which are beyond the retention.

I tested the script using the current settings for the archive log filesystem size and log backup run times and retention settings. I just compared the output percent full with reality. It was close but not exact. So, when I went to apply this for real, I padded the filesystem sizes so the expected percent full was less than 50%. So far so good in reality. I would like to build an emergency script that automatically clears out old logs if the filesystem gets full but so far, I have not. We do have alerting on archive log filesystem space getting too full.

If you run the script, you can see the arguments:

Arguments: oracle-username oracle-password tns-name configfile-name

Config file is text file with this format:

archivelog filesystem size in bytes
number of backups per day
one line per backup with 24-hour:minutes:seconds archivlog-retention-hours

for example:

02:15:00 168
06:15:00 168
10:45:00 168
14:15:00 168
18:15:00 168
22:15:00 168

The output is something like this:

2024-02-20 08:55:57 1.67%  
2024-02-20 09:10:02 1.68%  
2024-02-20 10:00:29 1.69%  
2024-02-20 11:00:20 1.7%  
2024-02-20 11:37:32 1.7%  
2024-02-20 12:01:17 1.68%  
2024-02-20 12:09:05 1.68%  
2024-02-20 12:43:53 1.69%  
2024-02-20 12:55:52 1.69%  
Max percent used archivelog filesystem: 46.15%
Date and time of max percent: 2023-12-24 11:52:17

When your archive log filesystem is too small the Max percent is over 100%.

It’s not perfect or fancy but it is available if someone finds it useful.


p.s. The script uses cx_Oracle so you will need to install that.

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

5 Responses to Archivelog Space Needed Python Script

  1. Голиков Сергей says:

    Why dont you use fast recovery area, and rman archivelog deletion policy something like “CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIME TO ‘SBT_TAPE’;”
    In that case oracle automaticaly removed from FRA old archive logs where recovery area becomes more than 80% ?

    Это же так просто)

    • Bobby says:

      Thanks for your comment. We have not used the fast recovery area in our standard deployments. We probably should. It sounds like a good idea. I guess since we need a certain number of hours or days of archive log history we would just have to figure out how big to make the FRA to keep that history present?

      Thanks again!

  2. Tim says:

    archive/undo management for log mining tools has been a pain for us as well. Using BRA with one primary destination ASM diskgroup and/or multiple diskgroups has pain points as well.

    For databases involved with log mining we eventually settled on [CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO ‘SBT_TAPE’;] for archivelog backup/delete jobs. Which are scheduled hourly across the board. Then published the keep archive logs on disk policy as up to 3 hours on best effort basis to set app admins expectations. We handle requests for archive log timeline restores back to disk as needed.

    This moved our pain point to archive log restore requests which is infrequent. IMO, establishing a retention policy did the most good. App admins have a known time limit to resolve replication failures before archive logs disappear.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.