Physical Backups  «Prev  Next»

Lesson 2 Introducing basic database backup concepts
ObjectiveIdentify the Available Classes of Database Backup.

Identify Available Classes of Database Backup using Oracle

In Oracle 19c, database backups can be broadly categorized into the following classes:
  1. Physical Backups: These are backups of the physical files that constitute the database. They include data files, control files, and archived redo logs. Physical backups can be further divided into:
    • Full Backups: This type of backup includes all the database files. A full backup can be used to restore the database to the point in time when the backup was taken.
    • Incremental Backups: These backups include only the changes made to the database since the last backup. Incremental backups can be cumulative (including all changes since the last full backup) or differential (including changes since the last incremental backup).
  2. Logical Backups: Logical backups contain logical data (such as tables, stored procedures, etc.) extracted from the database using Oracle Data Pump or the older Export/Import utilities. These backups are independent of the physical storage details and can be used for tasks like migrating data between databases that might have different storage structures.
  3. Online and Offline Backups:
    • Online Backups (also known as hot backups) are taken while the database is open and available for use. This requires careful handling to ensure data consistency, particularly for files that are being actively changed during the backup process.
    • Offline Backups (cold backups) are taken when the database is shut down, ensuring a consistent state without the need for additional recovery steps. This class is simpler but requires downtime, which might not be feasible for 24/7 operations.
  4. RMAN Backups: Oracle Recovery Manager (RMAN) is a powerful tool provided by Oracle for backup and recovery tasks. RMAN can manage both physical and logical backup strategies, offering features like incremental backups, backup compression, and encryption. RMAN backups can be more sophisticated and are highly recommended for their deep integration with Oracle databases and their advanced features.

Each of these backup types serves different purposes and has its own advantages and disadvantages. The choice of backup strategy depends on factors like the importance of the data, available downtime, storage resources, and the specific requirements of your database environment.

Physical versus Logical

There are two types of backup,
  1. physical and
  2. logical.
A physical backup operation results in a byte-for-byte copy of the Oracle database files. Physical backup is also called Operating System backup (O/S) because the DBA copies the files using the O/S, not Oracle. Logical backup is created using the EXP utility. This utility completely reorganizes the data by extracting it from the physical database file(s) and putting it in a single, "flat" operating system file. The logical backup process is not part of this course. The images below illustrate the concept for these two types of backup.
Physical consisting of INIT, Control, Online Redo Logs and Data Files
1) Physical backups consisting of INIT, Control, Online Redo Logs and Data Files

Llogical backups consisting of INIT, Control, Online Redo Logs and Data Files
2) Logical backups consisting of INIT, Control, Online Redo Logs and Data Files

  1. Physical Backup: A complete physical backup includes all the important parts of a database: control file(s), redo log files, archive log files, and datafiles. If the original data is lost, these file copies can be recovered because the backup contains all the necessary information. In the case of media failure[1], a physical database backup is the best possible way to successfully recover the data. Without a copy of the database files, not only must the database be re-created empty of user objects, the objects themselves must be re-created. Additionally, all user data must be re-entered. When you make a physical backup of a database, you actually take an operating system backup[2] of datafiles while the database is open or closed. An operating system (O/S) backup is made using an operating system command and can be written to disk or tape in any format that a specific operating system supports.
  2. Logical Backup: DBAs usually consider logical backup a supplement to their database backup strategy because the EXP utility can be very time-consuming; imagine doing 100 million inserts when the database is large. The EXP utility does not provide the same speed advantages that O/S backups offer at recovery time.

Logical Backup of Data in Oracle

Although it is considered the DBA's job to perform database backups, users should be strongly encouraged to make their own EXP backups. If a user does not have a personal EXP backup copy and accidentally deletes data, then discovers this days after the fact, it might become a DBA's nightmare to hunt for the correct backup file to recover the lost data.
Strategies available for backing-up an Oracle Database
The following methods are valid for backing-up an Oracle database:
  1. Export/Import: Exports are "logical" database backups in that they extract logical definitions and data from the database toa file.
  2. Cold or Off-line Backups: Shut the database down and backup up ALL data, log, and control files.
  3. Hot or On-line Backups: If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files.
  4. RMAN Backups: While the database is off-line or on-line, use the "rman" utility to backup the database.

It is advisable to use more than one of these methods to backup your database. For example, if you choose to do on-line database backups, also cover yourself by doing database exports. Also test ALL backup and recovery scenarios carefully. It is better to be safe than sorry. Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files, etc. If your database is in ARCHIVELOG mode, you also need to backup archived log files.

What is Mirroring?

Maintaining identical copies of data on one or more disks. Typically, mirroring is performed on duplicate hard disks at the operating system level, so that if a disk is unavailable, then the other disk can continue to service requests without interruptions. When mirroring files, Oracle Database writes once while the operating system writes to multiple disks. When multiplexing files, Oracle Database writes the same data to multiple files.

[1]Media failure: Media failure refers to the error that occurs when you try to write or read a file that is required to operate the database. It is also called disk failure because there is a physical problem reading or writing physical files on the disk.
[2]Operating system backup: An operating system (O/S) backup is made using an operating system command. Operating system backups can be written to disk or tape in any format that a specific operating system supports.
[3] Mirrored online redo logs: A mirrored online redo log is also called a multiplexed online redo log. It contains copies of online redo log files physically located on a separate disk. Any changes made to one member of the group are recorded in other members. The next lesson examines the available options for database backup.

SEMrush Software