If you are ready to take control of your Oscar server this is the full server and database migration process.
You will have to get a full backup from your current OSP. They usually charge for this process and you should probably do this process twice. The 1st time is to test the conversion and the 2nd time is the final transfer.
This post is a work-in-progress. Once completed, the instructions will be added to the online manual.
The OSP will make 2 backup files and put them online, on a secure server. The database backup is usually small. Just a few Gb. THe document backup can be considerably larger, depending on the number of documents you have. Over 200 Gb is not uncommon.
You should also have a working installation of the version you are migrating into, preferable with Demo data.
NOTE: This is NOT the procedure to use if you are merging the OSP installation into an existing production database.
The migration has the following steps:
- 1) Contact your OSP and request an initial backup. This should be done in advance of your final migration to allow time for the OSP to make the backup and for you to test the process before the final transfer.
- 2) Install and setup the version you want to migrate into. Include the demo data. The Installation instructions can be found at https://oscargalaxy.org/knowledge-base/oscar-19-installation/
Login and make sure it works. - 3) Backup the database (oscar_15) including the Demo data and anything else you may have added.
- 4) Restore the backup into another database (eg. oscar_orig)
- 5) Decrypt and uncompress the backups you received from your OSP. This can take a long time.
- 6) Restore the OSP backup database over the existing oscar_15 database.
- 7) Move the documents into the right folder and check the permissions.
- 8) Compare the schemas of the restored OSP database (oscar_15) and the original database (oscar_orig). This is the most time-consuming step. There will be many small differences that have to be reconciled.
As you update the database make backups!
Every line that you enter on the server should be copied from a document that will be used to re-run the final transfer.
You can always revert back to a functioning installation by dropping the oscar_15 database and restoring oscar_orig into oscar_15 OR changing the database name in the oscar.properties file.
Other considerations:
You cannot do billing from 2 installations at the same time.
You need to setup and test backups, faxing and any other services previously done by your OSP.
TIPS:
Run the conversion using the ‘screen’ program. This will ensure that even if you get disconnected, your commands will continue running.
5) If the files are taken from the normal Oscar backups, they may be encrypted. You will need the database password to decrypt these files.
6) Restoring the OSP database may fail due to differences with the mySQL/mariadb configuration.
Tip: The database backup file may be large but surprisingly easy to edit. I use nano -l
charset differences: If the OSP still uses latin1, you will need to change CHARSET=latin1 to CHARSET=utf8mb3
sudo sed -i 's/CHARSET=latin1/CHARSET=utf8mb3/g' ~/database_backup.sql
MyISAM vs InnoDB: Some of the older form tables are too big for the default InnoDB config. These are: formAdf, formIntakeHx, formONAR, formONAREnhancedRecord, formONAREnhancedRecordExt1, formONAREnhancedRecordExt2. You can just edit the .sql file changing the InnoDB to MyISAM and restart the restore.
Many of these older form tables may have no data. An alternative workaround is to simply cut out the file creation lines and re-create the tables from the original backup you made in set 4). I did this for formONAREnhanced, formRourke2020. If these forms have data, this is a very bad idea!
sed -i 18,7269d ~/ will remove all lines from 18 to 7269.
8) The following tables are critical. If you do not add the missing fields, Oscar will not start ;-(
I took extra time to add the missing fields to oscar_15 in the same order as defined in oscar_orig. For reasons…
The DROP columns is optional. Oscar will not crash if the table contains extra columns that exist in OscarPro. Probably a good idea, none the less.
I used the perl mysqldiff utility to list all differences between oscar_orig and oscar_15. https://metacpan.org/dist/MySQL-Diff/view/bin/mysqldiff
The command to run is
mysqldiff -o db:oscar_15 db:oscar_orig --user1=root --password1=dbpassword --user2=root --password2=dbpassword
Adding missing fields to oscar_15 is critical because otherwise Tomcat will crash with ‘Database connection error: org.hibernate.exception.SQLGrammarException: could not execute query.’
You will need to read the tomcat log carefully to find the missing table.field using journalctl -u tomcat9.service –since “2025-09-11 11:01:00″.
Hopefully, you will spot the Unknown column ‘security0_.lastUpdateDate’ in ‘SELECT’
I converted them in the following order:
security ADD passwordUpdateDate, pinUpdateDate, lastUpdateUser, lastUpdateDate, totp_enabled, totp_secret, totp_algorithm, totp_digits, totp_period
If you don’t have the old login info, you will have to copy a valid password hash from oscar_orig.security.password.
Note: The password encryption in OscarPro uses a longer and more secure key.
demographic ADD family_doctor, middleNames, residentialAddress, residentialCity, residentialProvince, residentialPostal, roster_enrolled_to, family_physician
UPDATE oscar_15.demographic SET middlenames = ” WHERE middlenames is NULL ;
UPDATE oscar_15.demographic SET family_doctor = “
UPDATE oscar_15.demographic SET family_doctor = “
demographicArchive ADD family_doctor, roster_enrolled_to, middleNames, residentialAddress, residentialCity, residentialProvince, residentialPostal, pref_name, family_physician
messagelisttbl ADD destinationFacilityId, sourceFacilityId
UPDATE oscar_15.messagelisttbl SET destinationFacilityId = 0 WHERE destinationFacilityId is NULL ;
UPDATE oscar_15.messagelisttbl SET sourceFacilityId = 0 WHERE sourceFacilityId is NULL ;
At this point, you should be able to login and see the appointments & demographic info.
allergies ADD nonDrug
view ADD providerNo
document ADD receivedDate
drugs ADD short_term, protocol, priorRxProtocol, pharmacyId
Smallprint: This process was used to convert OscarPro(tm) to the Open Source .deb Installation as documented on oscargalaxy.org. Other Oscar forks may require a different process.