An effective backup strategy will always be at the heart of data integrity and disaster recovery. In an ideal world, you may never have to call on it, but any backup plan should be reviewed and tested from time to time to ensure you’re not caught out by a series of unexpected events.
In the case of Oracle database backups, there are two most commonly used options, known as ‘hot’ or ‘cold’ backups – basically a ‘hot’ backup is when the database is online and a ‘cold’ backup is carried out when the Database is offline. I’ll explain a little more about each of these:
A hot backup is carried out whilst the database is operational. This has the advantage that you don’t need to arrange for downtime of the system and is particularly useful when access to the database is required outside of normal working hours. Hot backups are the answer if you need the database online 24 hours a day.
Hot backups work by backing up separate sections of the database one at a time. During an Oracle hot backup, the backup software or agent sets a ‘tablespace’ into a backup state (it does this by creating a checkpoint) and then copies it to your backup media. Once finished it then takes ‘tablespace’ out of the backup state. During a hot backup the database can still be used for normal operation.
It’s important to remember that Oracle needs to be in archivelog mode for hot backups to work. This means that the details of any transactions in progress at the time of the backup can be applied if the database needs to be recovered.
A cold backup works rather differently by taking copies of the database files, redo logs and the control files after the database has been taken offline. It takes an exact copy of the database as it stands at a particular point in time and you can use this data to fully restore the system to that point. Because the database is offline, no-one is using it and therefore there’s no chance of data changing during the backup.
The database can be in either archivelog or noarchivelog mode, as long as its offline. You may have to be patient though if your database is very large as this can take some time.
When deciding which method to use, you’ll need to assess which option is best suited to your authority’s particular circumstances, costs can vary between solutions. The key is to ensure you have a solid backup strategy – there’s no time like the present to ensure your plan is up to date.
Whatever method you decide, it is important that you set aside time each year to test that the backups can be restored. Backups of the production database can easily be restored to no-production databases.