We are explaining here the Types of backup in oracle database
Different Types of backup in oracle database
What is online backup:
- An online backup or also known as an open backup is a backup in which all read-write data files 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 a backup which is taken while the system is up and available for 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 a backup which is taken while the system is down and not available for user updates
What is a 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 data file backup is a backup of a single data file. 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 than manually creating a file with a specific format.
Important attributes for Backup strategy
We must follow these simple principles to have a 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 data files and store them in a safe place, making multiple copies if possible
Type of Backup methods
Oracle provides users with 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.
Hope like this post on Types of backup in oracle database
backup controlfile in RMAN : Check out this post on how to backup controlfile in RMAN, How to backup controlfile to trace, and How to enable auto backup of controlfile
check rman backup status: Check out How to check RMAN backup status in sql, what is the status of the backup and input_type of the backup
RMAN List backup : RMAN List backup commands are used to list the backup taken using RMAN,Date and Time and many other details are included
RMAN Backup commands :Check out the RMAN Backup commands in this post. This is going to be very helpful for the person who is involved in backup and recovery