Online vs offline backup in oracle database by oracle ace manish sharma

To lay down a robust Backup and Recovery strategy for our database we first need to setup a stable Fast Recovery Area (FRA) and after that comes the Archivelog Mode. To understand What is Archivelog Mode and Why we need it, we first need to know what are offline backup and online backup as well as complete and incomplete backup.

In this article we will focus on Online and Offline Backup, what are they? What are their Advantages and disadvantages.

Offline Backup

Offline backup, as the name suggests is a backup which is performed while the database instance is down. Offline backup is also known as cold backup.

Advantages of Offline Backup

  • No Special Configuration Needed

The biggest advantage of an offline backup is that – it does not require any special configuration. You can literally perform an offline backup of a database on its factory settings.

  • Offline Backup Is The Complete Backup

Since the backup is taken while the database is down thus it is fair to say that – It is a complete backup of the database. But you will be surprised to know that – even though it is a complete backup still it’s not recommended as best practice. Not even for those test database that you have configured for dry runs of your apps.

Disadvantages of Offline Backup

Even though the backup created while the database is down is Complete Backup of the database but it is still not recommended and it’s all because of its advantage. Yes, its biggest advantage is also, its disadvantage.

Since it does not need any special configuration thus it’s possible that your database is in its default mode of operation which is NOARCHIVELOG. In this case, any changes made after the full backup are not recoverable. In a database which is running in NOARCHIVELOG , the point-in-time recovery is also not possible. Thus it’s always recommended to put your database in ARCHIVELOG mode.

The second disadvantage is the down time of the database. Since in order to take a full offline backup we need to shut down the database. And while the database is down no one can access it. In a large production databases this downtime can be a huge issue.

Is Offline Backup Consistent?

So you must be wondering – Since we have performed the backup while the database instance is down thus it should be consistent?

An offline backup can be called consistent only when the datafiles, control files and redo logs are synced with each other when you shut down the database. This happens only when the database is shutdown in a proper manner.

But in case the database is shutdown in an incorrect manner with SHUTDOWN ABORT command then the Database redo logs, control files and data files are not synced with each other.

Thus calling an offline backup of the database consistent, when the operations were aborted abruptly, will be wrong.

Online Backup

Online backup is quite the opposite of the offline backup. Which means a backup which was taken when the database is up and running is called an Online Backup. Online backup is also known as Hot Backup.

Advantages of Online Backup

  • Require Archivelog Mode

To perform an online backup, our database requires some special configuration. That is – Online Backup can be performed only on those database which are running in ARCHIVELOG mode. You can call it a disadvantage, but the good thing is that it’s just a one-time thing.

  • Near Zero Down Time

The biggest advantage of online backup over offline backup is that – It’s down time is near zero.

Is Online Backup Consistent?

Unlike Offline backup, online backup is performed when the database instance is up and running. When the database is running (even in its normal state) it is always in inconsistent state. And, any backup performed in the inconsistent state of the database is always an inconsistent backup. Thus online backup is not consistent backup. It is always an inconsistent backup.

We will learn more about inconsistent and consistent backup in the next tutorial which will be coming on my YouTube channel. So make sure to subscribe.

That’s it for this tutorial. Thanks and have a great day.