Data Structure of Oracle and DB2 used for backup

Data structure used for backup and recovery
Both Oracle and DB2 UDB have a group of components that form backup and recovery mechanisms. The architecture diagram shown in figures 1 and 2 gives an overview of the main components that provide Oracle and DB2 backup and recovery.

Figure 1. Oracle Architecture

Oracle Memory Structure

• Buffer cache-memory allocated to store data blocks read from physical data files.
• Shared Pool-memory allocated to store parsed SQL statements and data dictionary information of PL/SQL processes.
• Log buffer-memory allocated to store the images before and after the changed data. The data items recorded in it are continuous.
• Large pool-memory allocated to RMAN for backup and recovery.

Oracle background process

• Database reader DBWr) -- asynchronously writes dirty data in the buffer to physical data files.
• Log Reader LGWr) -- writes a project from the log buffer to redo log files.
• Checkpoint CKPt) -- synchronize the data file header and control file header with the current redo log and check points.
• Archive ARCH) -- automatically redo log copying. If the archive is not enabled, You need to manually archive the redo logs.

Oracle Database Structure

• Data Files: physical files that store data.
• Control File: a file that contains the physical structure and status of the database, for example, the name of the data file and log file containing the absolute path, the file size, the block size, and the online or offline status of the data file. It also contains the log file name and path, file size, and block size.
• Redo log-a file that contains the pre-and post-image of the changed data. Redo logs are required for recovery.
• Parameter file: The file that stores the parameters started by the instance. You can start an instance in multiple ways. Oracle first searches for whether spfile_SID.ora exists. If the file does not exist, Oracle searches for the Spfile. ora parameter file. If neither spfile_SID.ora nor spfile. ora exists, Oracle uses the init_SID.ora parameter file.
• Archive logs-physical copies of online redo logs. Archiving logs is required during online recovery.

Next we will see the DB2 UDB architecture and structure.

Figure 2. DB2 UDB Architecture

DB2 UDB Memory Structure

• Package cache-memory allocated to store static and dynamic SQL statements.
• Buffer Pool-memory allocated for data storage before data is refreshed to the disk.
• Log buffer-memory used to store the changes before refreshing all the changes to the database to the disk.

Figure 3. DB2 UDB database structure

• Drive/directory-The drive or directory specified in the create database Command.
• DB2 Instance name -- Name of the DB2 instance owner.
• NODE0000 -- number of database partitions. 0 indicates a non-partitioned database.
• SQL00001 -- Database ID starting from 1.
• SQLOGDIR-default log directory of the database.
• SQLT0000.0 -- directory tablespace SYSCATSPACE.
• SQLT0001.0 -- temporary tablespace TEMPSPACE1.
• SQLT0002.0 -- User tablespace USERSPACE1.

Backup and recovery options
For Oracle and DB2 UDB databases, there are two backup and recovery modes: offline and online. You can use any Mode for full and Incomplete recovery.

Offline backup requires that all applications be disconnected from the database. Online backup allows transactions to continue during the backup process. There is an implicit relationship in the recovery of the selected backup mode, because the backup mode determines the recovery mode.

As the name suggests, full recovery can completely recover all committed transactions, but incomplete recovery will lose some data when the transaction is restored. Both Oracle and DB2 UDB allow you to recover to the current time without data loss or the time before the current time, but some data is lost.

In general, the goal of recovery is to use the selected recovery model to reach a certain compromise between business needs and operational needs. For example, if the database is not of the task-critical and 24X7 type, it may be acceptable to stop the database for a period of time and lose some data. For media errors, retyping data may also be an acceptable method. Recovery depends on available backups and available logs. Sometimes, you have no choice but to perform Incomplete recovery.

There are two types of DB2 log records, each of which supports specific recovery options. The two types of log records areLoopAndArchiveLog records. When you choose to use cyclic logging by default), the only option is to perform offline backup and version recovery. If you choose to use archive log records and perform online backup and Rollback Recovery, you can restore to the time point at which data loss is least, or to the end log.