From time to time, we receive calls from OASIS users asking if it would “ok” to delete log files for the OASIS database. In some cases, the issue is critical as the server is running low on disk space. This document describes the best way to manage this case.
Running Low on Disk Space
The features in OASIS have proven to be extremely valuable to many OASIS users, especially submittals and the attachments features. Unfortunately, these features use a lot of disk space over time. As of this writing, the largest OASIS database is over 1.4 TB in size. This sounds like a lot of disk space. Just a few years ago, the average desktop computer might have come with a 500 GB hard drive which was more than enough space for several years of data. As time marches forward, hard drive density has increased and some hard drives can now store 8000 GB (8 TB) or more on one drive, for example. But backing up and managing a large database and set of log files still takes time, and in some environments can become a major headache when considering backing up the OASIS database in addition to things like Exchange, user profiles, and whole virtual machines. Available hard drive space becomes scarce very quickly.
Before running out and upgrading a server, consider the purchase of a low cost USB hard drive for additional log file space.
The Sybase Database
OASIS uses a product called SAP Sybase SQL Anywhere. (In the past, this product was called Adaptive Server Anywhere, or ASA.) SQL Anywhere is known as a relational database and implements an internal recovery technology. The advantage is that for most system failures, the database automatically recovers. The down side is that the recovery process uses disk space for log files. So what are the files utilized by the OASIS database?
- oasis.db - this is the OASIS database. All the Quotes, Orders, Attachments, Everything!
- oasis.log - this is the active transaction log (recovery file).
- YYMMDDAA.LOG - these are inactive recovery files from the daily backup process or from a manual log truncation.
How does the system work? In short, when you open view an order in OASIS, the data is pulled from oasis.db. When you save an order (or other transaction) in OASIS, the data is written to both oasis.db and oasis.log. This “dual write” is important when there is a system failure (e.g. the server loses power) and the data in one of the files (most likely oasis.db) is corrupted, the oasis.log file is automatically “consulted” by the database to correct the error. This self-recovery feature can save a database from corruption in the event of a power failure.
The down side is that the size of the oasis.log file will grow forever and it should be truncated (or rotated) periodically. THIS DOES NOT MEAN DELETE. Deleting the oasis.log file itself will likely cause your database not to start. However, if you log into OASIS, select “Configuration”, then "Software" and click on “Backup transaction log”, the database will take the following actions:
- The oasis.log file is renamed using the date and some unique numbers (e.g. 151029AA.LOG)
- A new (empty) oasis.log file is created
- The inactive recovery log file names are always a date stamp: Year, month, day, then the log sequence. It starts with AA (single daily log file). If the log is truncated first by the daily backup process, then manually through the process above (for example), you would have an AA log and an AB log, then AC, AD, etc. if there are multiple log rotations throughout the day. (This is not typical of most environments.)
- If you are using the automated backup, OASIS will automatically truncate the log daily and the above steps are unnecessary.
- The truncated transaction logs have internal offsets at the beginning and end of the files. For successful recovery purposes, all sequential log files must be present.
What CAN I Delete?!
The official answer from Ingen Software is: NOTHING! That may seem a little harsh, but before giving a better answer to the question, you must understand that we have helped several groups recover data from mechanical hard drive failures due to power outages, server faults, virus infections, and we have even seen a “bullet proof” RAID array crash. But we have never lost an entire database when a full set of log files was available, and we DO NOT want you to be the first.
- With all the log files created for the OASIS database, it is possible to recover from a hardware failure and rebuild the database from Day 1 up to the point of failure with zero data loss.
- And we have done this several times already (and that should scare you).
What Can I Really Delete?
There is one important factor to know before answering that question. Are you using the automated daily backup that is included with the OASIS software? (If not, PLEASE consider setting them up.)
If you are using daily backups
You have two folders to consider: The live database location (C:\OASIS by default) and the backup location (C:\oasis_backup by default).
There will be an AA log file for every day in the live database location, and there will be a duplicate of those AA log files in the backup location. You only need one set of them. All AA log files from one of the two locations could be copied to an external hard drive and the originals in both locations could be deleted.
NOTE: We recommend only deleting one copy and keeping one set of logs on the server. In the event of a database failure, it would take time to copy those logs back to the server from the external hard drive which means more down time.
If you are not using daily backups
- PLEASE consider setting them up
- Purchase an external USB hard drive. At the time this article was written, a 1 TB USB 3.0 external hard drive (1000 GB) could be found for around $60 USD
- Use the method above to backup the transaction log
- Stop the OASIS database
- Copy ALL files in the OASIS database directory to the USB drive and verify that the data did indeed copy.
- Remove all "AA log" files from the OASIS database directory (formatted as YYMMDDAA with the extension .log)
- DO NOT DELETE oasis.db or oasis.log files
- Start the database and verify transactions from the previous day (ensures the data is still on the server).
- Place the USB drive in a bank safe deposit box or other secure location
If this sounds like a lot, then consider this:
- What if all the data you have in OASIS right now were to simply disappear? Would your business be negatively impacted?
- We have had customers whose offices were burglarized and their computers and server (along with all their backups) were stolen. They unfortunately had to start over with a blank database.
What is truly required for a successful recovery?
The reality in a recovery scenario is that we only need a known good, validated copy of the oasis.db file and every AA log file since that copy was made through the current date. The database will contain everything from the past AA log files, so as long as we have a known good database from a certain date, the AA logs from that date and before become redundant. A recommended policy would be to validate a copy of the backup monthly or quarterly, delete old AA logs from the validated database's last modified date and before.
- Have a daily backup set up and running
- Get a copy of the oasis.db file from the oasis_backup folder and save it to an external hard drive or other separate location.
- Copy this copy to another server or workstation with the server version of OASIS installed.
- Run a validation on the "validation copy" of the database. (Video Walkthrough)
- If this copy validates successfully, preserve the original copy on the external hard drive or the separate location and that becomes your known good copy to work from.
- The copy that was validated is no longer a viable backup itself because it has been started. (The offsets in the log files wouldn't match up.) The validation copy can be deleted.
- All AA logs from the day prior to the the database date and before could be deleted at that point. (ie, if the database shows a date of 12/1/16, you can delete log files from 11/28 and before.)
- In the event of a database failure, we would attempt to recover from the copy in the oasis_backup folder. If this fails, we would go to the known good copy and roll it forward using AA logs.
What Are the Minimum OASIS Disk Space Requirements?
The disk space requirements vary greatly between OASIS users. New users should assume at least 10GB of disk usage the first year. (Likely more if going “green” by using OASIS to transition to a paperless office.)
When “cleaning up” a server using the process above, the smallest amount of disk space required will ever be is 1.5 times the size of the oasis.db file. The extra .5 estimate is for:
- The required oasis.log file
- Temporary files created by the database
Do realize, this is the bare minimum.