Oracle database backup: Type of backup and backup strategy

We are explaining here Oracle database backup: Type of backup and backup strategy


Different Type of Oracle Backups

What is online backup:

An online backup or also known as an open backup,is a backup in which all read-write datafiles and control files have not been checkpointed with respect to the same SCN.
For example, one read-write datafile header may contain an SCN of 500 while other read-write datafile headers contain an SCN of 105 or 100. Oracle cannot open the database until all of these header SCNs are consistent, that is, until all changes recorded in the online redo logs have been saved to the datafiles on disk

It is backup which is taken while the system is up and available to user updates

What is offline backup:

An Offline Backup: all datafiles and control files are consistent to the same point in time – consistent with respect to the same SCN,
for example. The only tablespaces in a consistent backup that are allowed to have older SCNs are read-only and offline-normal tablespaces,. The only way to perform this type of backup is to shut down the database cleanly and make the backup while the database is closed. A consistent whole database backup is the only valid backup option for databases running in NOARCHIVELOG mode.

it is backup which is taken while the system is down and not available to user updates

What is Whole database backup
The most common type of backup, a whole database backup contains the control file along with all database files that belong to a database. If operating in ARCHIVELOG mode, the DBA also has the option of backing up different parts of the database over a period of time, thereby constructing a whole database backup piece by piece.
What is Tablespace backup
A tablespace backup is a subset of the database. Tablespace backups are only valid if the database is operating in ARCHIVELOG mode. The only time a tablespace backup is valid for a database running in NOARCHIVELOG mode is when that tablespace is read-only or offline-normal

What is datafile backup

A datafile backup is a backup of a single datafile. Datafile backups, which are not as common as tablespace backups and are only valid if the database is run in ARCHIVELOG mode. The only time a datafile backup is valid for a database running in NOARCHIVELOG mode is if that datafile is the only file in a tablespace. For example, the backup is a tablespace backup, but the tablespace only contains one file and is read-only or offline-normal. .

What is control file backup
A control file backup is a backup of a database’s control file. If a database is open, the user can create a valid backup by issuing the following SQL statement: ALTER DATABASE BACKUP CONTROLFILE to ‘location’; or use Recovery Manager (RMAN).
What is archived log backups

Archived redo logs are the key to successful media recovery. Depending on the disk space available and the number of transactions executed on the database, you want to keep as many days of archive logs on disk and you want to back them up regularly to ensure a more complete recovery
Configuration file backups

Configuration files may consist of spfile or init.ora, password file, tnsnames.ora, and sqlnet.ora. Since these files do not change often, then they require a less frequent backup schedule. If you lost a configuration file it can be easily recreated manually. When restore time is a premium, it will be faster to restore a backup of the configuration file then manually creating a file with a specific format.
Important attributes for Backup strategy

We must follow these simple principles to have good backup strategy
1) Multiplex the online redo logs
2) Run the database in ARCHIVELOG mode and archive redo logs to multiple locations
3) Maintain multiple concurrent backups of the control file
4) Take frequent backups of physical datafiles and store them in a safe place, making multiple copies if possible
Type of Backup methods

Oracle provides users a choice of several basic methods for making backups. The methods include:
1) Recovery Manager (RMAN) – A component that establishes a connection with a server process and automates the movement of data for backup and recovery operations.
2) Oracle Enterprise Manager – A GUI interface that invokes Recovery Manager.
3) Oracle Data Pump or Export/Import – The utility makes logical backups by writing data from an Oracle database to operating system files in a proprietary format. This data can later be imported into a database.
4) User Managed – The database is backed up manually by executing commands specific to the user’s operating system.

Leave a Reply