Werner Still

setting up insurance - create a backup

Blog Post created by Werner Still on Dec 22, 2015

Easiest way of doing a backup is a consistent Oracle Backup. Shutdown the Database, take a backup, startup Database. Restore directly the database files you need and recover or restore all and no recovery will be necessary.

The bad point is that the Database is not available during the time of the backup. So this scenario will be only possible for non critical database.

 

Most common is the online backup. This is from the Oracle side an inconsistent backup, as only the datafiles are not enough to have all transactions consistent. A recovery after the end online backup is necessary to have a consistent backup. This requires to have at least the archive logs available that had been produced during the time the database was in online backup. Pro is that the database is always available and transactions can be utilized with the database. During the online backup there is a lot more work to do for the database and after the online backup there is work to transfer the transactional data into the datafiles. This will impact the performance of the database during and after the online backup. The longer the database will be in online backup the more impact is on this performance item.

The restore is dependent on the transfer type but the recovery time is heavily dependent on the time the database was in online backup mode. The transfer method needs to be optimized for this.

 

A very recent supported type of backup is the crash consistent backup. This backup is only possible with storage based solutions. The backup takes only a very short time and there is virtually no impact to the database. The restore is dependent on the type of snapshot and recovery is heavily necessary as there are potentially a lot of transactions not in the datafiles.

 

RMAN uses for an online backup a special way to have a copy on write type of snapshot for the backup. With this type it is not necessary to have an online backup but the impacts are pretty much the same.

 

The time the database will be not available or impacted depends on the type of data transfer.

A full backup to another disk or via Network to another Server takes a long time. Restore will be quick with this as only one transfer per database file will be necessary.

Incremental backup will be in most cases faster than a full backup as only the changed items compared to the full backup need to be transferred. If an intelligent detection of the changed items is added this will be improved even more. But there is no free lunch, restore will take longer as the full backup needs to be restored and then the increments need to be applied to the full backup. This restore process takes a long time.

Storage based snapshots are very fast, in the range below one minute even for databases in the 100TB size. This makes this method very powerful for very large and very busy databases. If it is to be considered a real backup depends on the type of snapshot. A local pointer based snapshot is no protection against a physical issue, just against a logical issue. A local full size snapshot protects against a local disk failure, not against a larger issue. Remote pointer based snapshots protect against a site disaster. The drawback is the further out the snapshot is the longer takes the restore. A local pointer based snapshot can be reverted within seconds, a remote snapshot takes a lot longer as the changed data has to be transferred across the replication line. There is an additional way to use snapshots, they can be made available on a server and used like normal files. This way only the real necessary data needs to be transferred.

 

 

Backup TypeBackup PropertiesGood for Database

consistent - cold backup

database down

no recovery necessary if all backup files are restored

non critical

outage possible

inconsistent - online backup

inconsistent - RMAN backup

database up

recovery necessary

critical

no outage possible

inconsistent - crash backup

database up

recovery necessary, support starts with 12c

storage based snapshot technology necessary

critical

no outage possible

 

Transfer TypeTransfer PropertiesGood for Database
full backup, network based

long time for backup

medium time for restore

large storage space

small to medium sized

medium restore requirements

incremental backup, network based

short time for backup

restore takes longer due to incremental restore

low storage space

small to medium sized

low restore requirements

snapshot backup, storage based

very short time for backup

restore dependent on target fast to medium

storage space based on snapshot method (differential or full)

medium to very large sized

fast to medium restore requirements

Outcomes