Backups & The OASIS Database – Ingen Software

This document identifies the advantages and disadvantages for various backup solutions concerning the OASIS database. We strongly recommend all offices using OASIS to review this document and ask one simple question: what impact would there be if the server failed right now? This document provides solutions based on how one might answer that question. Regardless of the backup solution, we strongly recommend testing the backup by periodically performing a database recovery from your backups. There is nothing more frustrating than finding that the backups were not working when the server crashes.

How to Size the Impact

Servers are machines with mechanical parts. Over time, those parts wear out. Servers are also connected to networks with internet access. With viruses and malware migrating across the Internet, there is always a possibility that something could happen resulting in hard drive failure or database corruption. What impact would there be if your OASIS server failed right now?

Sizing the impact is important, as the cost of backup solutions increases as the level of protection increases. Like all business solutions, there is a cost versus benefit analysis that must be performed. You may categorize your office below as a guide to selecting the backup solutions below.

A Paperless Office!

Yes, there are several “paperless offices” using OASIS now. Internally, OASIS is designed for paperless offices and is able to image and retain documents in many different formats streamlining large and small offices alike. The result is the ability to better support your customers. In a truly paperless office, the document only exists in digital format. If the server fails, the document is lost. Period. If your office is totally or partially paperless, seriously consider the solutions below:

Nightly backups to a removable drive or other media
Backups to a second server
Offsite backups weekly (removable drive or other media)
Live backup to a second server (optional)
OASIS Cloud Backup (optional service from Ingen)
Quarterly “Simulated database failure”

High Volume Office

Is your OASIS database over 50 GB (gigabytes) or larger? If so, then you likely have a high volume office and are likely using many of the OASIS features. Offices with databases this large will likely cause a large impact in the office if the server were to fail. Please consider the solutions below:

Nightly backups to tape or removable drive or other media
Backups to a USB connected disk
Offsite backups monthly (removable drive or other media)
OASIS DB Integrity Checks at least twice a year

Average Office

The average OASIS user has a 5 GB to 20 GB database. Loss of the database due to server (or other) failure would be difficult and would cause a significant impact. However, the cost of complicated backup solutions does not warrant complicated and expensive backup solutions. Even so, we recommend the following low-cost solutions to ensure that a failure is recoverable and impact to your business is minimized:

Nightly backups to a USB connected disk
Offsite backups quarterly
OASIS DB Integrity Checks at least once a year

Small Office

Small office solutions only apply to the smallest OASIS users. The impact to the office if the server (likely a user’s PC) fails would be more significant than most small office users would admit. Even worse, the probability of a failure of the server in a small office greater for a number of reasons. The following solutions are free or nearly free – we do recommend implementing the following:

Nightly backups to a USB connected disk
Weekly backups to a user’s PC
Quarterly Offsite backups using a removable drive
Yearly OASIS DB Integrity Checks

Backup Solutions

Creating a readable copy of the database

OASIS utilizes a relational database technology: SAP Sybase SQL Anywhere. The technology consists of what is known as a service (a program running in the background) and a client – the OASIS desktop interface (a thin client). The service launches the OASIS database when the server starts and shuts down the database when the server shuts down.

This presents an issue for backups as many backup solutions are not able to read an open file. For the ones that can, corruption of the database is likely. For this reason many of the solutions in this section rely on this step.

Creating a readable copy of the database may be accomplished in two different ways:

The database service may be programmed to automatically create a copy of the database on the local server intended for backup.
The database backup utility (dbbackup) is able to connect to the service and copy the database to the local server or a remote server.

Restoration of a Backup

Begin by identifying the last compete oasis.db file that can be recovered from tape. Then identify all the files ending in .log created after the oasis.db file was created. Proceed to the “Standard database recovery” section.

Backups to a USB Connected Drive or other Media
Backups to a Second Server
Backups to a User's PC
Offsite Backups
Live "Snapshot" Backups
OASIS Cloud Backup (optional service)

Simulated database failure

If a tree falls in the forest and nobody is around to hear it, does it make sound? If a backup is never restored, was it ever actually written to the backup media (tape or disc)?

Simulating a database failure is perhaps the only way to ensure the backup process is working. Simply put, your tape device (or USB hard drive) could just as easily be blank as contain a usable backup. This is a scary possibility.

To begin the process, follow the steps to create a backup. Then obtain another computer to use as a server (a spare workstation is enough for a simulation) and follow the restoration process on the workstation. The result should be a usable OASIS database. If not, it must be determined what is preventing the creation of a usable database. Otherwise, your backup process is meaningless.

OASIS DB Integrity Checks

Validating the OASIS database is a relatively simple process. The tools for this are included in the ASA Configuration utility within a server installation of OASIS.

For best results, make a copy of the backup you wish to validate so that the backup itself remains viable in case of untimely failure. Install OASIS as a server on the spare workstation or server. Launch the ASA Configuration utility, then go to the Tools menu and select Verify Database. Navigate to the copied backup and select the oasis.db file. The validation itself could take several hours depending on the size of the database.

Database Copy

Writing the Backup

The OASIS database copy method is a low cost way to create a database backup and writing the result to a DVD.

Begin by selecting “Configuration” in OASIS, then select “Software”.
Click on “Database Copy”:
Click “Target File”, then click on the “…” button. Most users write the backup to their desktop folder. Type the name of the file for OASIS to write, something like "backup.oasisz".
Press next and select all entries except “Manufacturer Catalogs and Images” and “Manufacturer Parts List Data”. This information is recoverable from other sources. If these are needed, please consider another backup solution.
Press next and select all entries:
The press “OK”. The process will freeze the OASIS screen and likely take a while to run. There is no progress indicator on this screen. We recommend running this at the end of the day, allowing the process to run overnight.

When the process completes, the screen will go away, returning you to the main OASIS window. Using a CD/DVD writing utility or USB flash drive, create a copy of the file.

Restoration

Restoration of the database requires one to first create a new and empty database.

Once you are able to login to the new database, select the “Configuration” folder then “Software”.
Click on “Database Copy”:
Click on “Source File” and then click on the “…” button. Select the backup.oasisz file from the CD/DVD or USB flash drive.
Press “Open” then “Next”:
Make no changes and select “Next”. Select all entries (Selecting “Manufacturer Catalogs and Images” and “Manufacturer Parts List Data” will have no effect as the data was not saved initially.)
Press next and select all entries:
The press “OK”. The process will freeze the OASIS screen and likely take a while to run.

When the process completes, the screen will go away, returning you to the main OASIS window. Log out and log back into OASIS and the system will be restored.

Live "Open File" Backup Solutions

In short, we do not recommend “open file” backup solutions. The only successful solutions of this type have a “plug in” for the database system that helps the backup solution write a complete copy of the database. Without the extra software (which can be very expensive) there is a strong probability that the oasis.db file will be incompletely written to the backup device. That issue is not solvable and the result will likely be a recovery from log files.

With the latest updates for the SQL Anywhere database engine, it can be made VSS aware if you are using a backup solution that uses Windows Volume Snapshot Service. This functionality is something we are looking to add to the server installation of OASIS in the future.

Standard Database Recovery

To begin the database recovery, locate the most current undamaged oasis.db file in a directory on the server. Include the matching oasis.log file (if you do not have one, contact Ingen Software – it may be possible to force an oasis.log file).

With a complete set of AA logs

If you have all AA log files from the date of the backup database to the point of simulated failure, including an oasis.log file, the process becomes very simple.

Bring up a command prompt and use the CD command to locate the directory with the database file. Start the database engine manually by typing one of the following (depending on database version and server installation:

c:\progra~1\oasis27\dbsrv9.exe -ar oasis.db
c:\progra~1\oasis271\asa11\bin64\dbsrv11.exe -ar oasis.db
c:\progra~1\oasis272\asa11\bin64\dbsrv11.exe -ar oasis.db
c:\progra~1\oasis273\asa11\bin64\dbsrv16.exe -ar oasis.db

The database server will use its internal offset and compare that to the starting and ending offsets of the log files, then sequentially apply all log files as needed. Once recovery is completed, the database will continue running normally. (It would be best to stop the database at this point, then restart it as as service.)

With an incomplete set of AA logs

If some logs are missing, the remaining logs must be applied one at a time. Data loss will happen because whole days of logs are missing.

c:\progra~1\oasis27\dbsrv9.exe -n oasis oasis.db
c:\progra~1\oasis271\asa11\bin64\dbsrv11.exe -n oasis oasis.db
c:\progra~1\oasis272\asa11\bin64\dbsrv11.exe -n oasis oasis.db
c:\progra~1\oasis273\asa16\bin64\dbsrv16.exe -n oasis oasis.db

After the database server starts, stop it by clicking on the SQL or lightning bolt icon in the lower right corner of the screen and selecting the “Shutdown” option. (This process ensures the database and log file match.)

Next, order the other log files in ascending date order. Each must be applied using the command below – just change the 141121AA.log with the name of the file from your system. Repeat for each log file that is available. An error will be produced if an attempt is made to apply log files in the wrong order or if users attempt to use the system before all log files are applied from the backups:

c:\progra~1\oasis27\dbsrv9.exe -a 141121AA.LOG oasis.db
c:\progra~1\oasis271\asa11\bin64\dbsrv11.exe -a 141121AA.LOG oasis.db
c:\progra~1\oasis272\asa11\bin64\dbsrv11.exe -a 141121AA.LOG oasis.db
c:\progra~1\oasis273\asa16\bin64\dbsrv16.exe -a 141121AA.LOG oasis.db

Once all log files have been applied, start the database and login to OASIS. Check to ensure all the required data is available.

If important data is missing – recheck to ensure all log files have been restored from backup (don’t forget the oasis.log that was available when the server crashed – it may be applied to the database as well). If errors are encountered or data is still missing, contact Ingen Software – there may be other options available.

If Recovery was Unsuccessful

A backup solution was selected and the result was not good enough. Information from the corrupted database is needed and a recovery is required. The good news is there MAY BE a solution, but it is not free. All the methods above result in a restore process that produces a usable database from the time of the last backup. The options described below require technicians to perform low-level recovery either on the drive or the database. Call Ingen Software for more information.

Log file (.log) recovery

The oasis.log (and similarly named .log files) contain a "play by play” copy of the database. Technicians are able to translate this information into instructions that may be submitted to your database thereby restoring the required transactions. This is a very time consuming process.

Database File (.db) recovery

The worst of the worst is to utilize very low-level utilities and help from the database vendor (Sybase) to restore transactions from the oasis.db file. Sometimes, this can be done by technicians at Ingen. Other times, the database must be sent to the vendor for recovery. The results cannot be predicted and there is a cost for attempting the operation.