Online Hot Backup comparison between Oracle and DB2

Oracle online backup

In Oracle, online backup requires database operations in ARCHIVELOG mode. In an online store, the database must be on 24x7. Therefore, it is impossible for users to back up the database offline without accessing the database. In this scenario, the database should be run in ARCHIVELOG mode. In this mode, the transaction will continue to run, and the backup processing will continue.

Unlike offline backup, online backup only requires backing up data files and control files. The offline database backup unit is the entire database, while the online backup unit is some or all tablespaces.

In Oracle, the idea of online backup is that when a user executes a transaction, all the changes made to the database are committed or not committed) stored in the redo log buffer, then the LGWr process writes them to the online redo log file. Redo logs are written cyclically. Therefore, before rewriting them, you must start the ARCH process to manually or automatically archive the redo logs. The LOG_ARCHIVE_DUPLEX_DEST parameter is used when all transactions are recorded and archived Through multiplexing in the future. If media recovery is required, you can use these archived redo logs for restoration.

Note that using online backup cannot guarantee that you do not lose data. Such steps as putting control files in different locations through multiplexing to make the database immune, using online redo logs and archiving redo logs are all necessary to avoid single point of failure.

In Oracle, to switch from the default offline backup to an online backup with automatic archiving, perform the following operations:

• In init. ora, enter the LOG_ARCHIVE_START, LOG_ARCHIVE_DEST, LOG_ARCHIVE_FORMAT, and LOG_ARCHIVE_DUPLEX_DEST and LOG_ARCHIVE_DEST_N parameters with appropriate values.
• Close and start Mount ).
• Operate databases in archivelog mode.
• Open the database.
• Verify the archived log list.
• Archive all logs.
• Back up all newly created logs.

Run the "Alter Tablespace ts_name Begin Backup" command to enable online Backup by placing any or all online tablespaces in online Backup mode. When this command is issued, all data files in online backup mode will be issued with the checkpoint SCN. In other words, all the dirty bits in the data buffer zone are refreshed to the data file. After the initial checkpoint SCN, no new checkpoint SCN will be issued. For data files that are not in online backup mode, the subsequent check points will add SCN to their file headers. When "Alter Tablespace ts_name End Backup" is issued, the checkpoint SCN is recorded again in the file header of the online Backup data file. The Begin Backup and End backup commands let Oracle know what operations to redo and where the rollback will terminate.

We recommend that youBegin backupAndEnd backupMinimum Time consumed between commands. In addition, we recommend that you perform as few user activities as possible during this period, because Oracle will record the entire block image when changing the block for the first time. For online backup data files, this will generate a large number of redo logs. In factBegin backupThen, we should immediately execute system replication, and thenEnd backupTo quickly end the operation. Backup is performed in strict order, which means that after the data files in table space 1 are backed up, the data files in table space 2 will be backed up.

For online backup, pay special attention to the SYSTEM tablespace and rollback segment tablespace. At the same time, make sure that the control file is backed up as long as the database mode is changed. The backup script should reflect the latest mode.

DB2 UDB online backup

The Restoration Mechanism of DB2 UDB execution time point is similar to that of Oracle. To understand the online backup and recovery of DB2 UDB, you need to understand the archived log records. There are three log definitions that we need to be familiar:

• Activity Log-this log contains transactions that have not been committed or rolled back, or transactions that have been committed but have not been refreshed to the disk.
• Online archiving log-this log contains the transaction information submitted and recorded to the hard disk, which is in the same directory as the activity log.
• Offline archiving logs-if you move online archiving logs from the active log directory to another directory or tape, it becomes an offline archiving log.

There are many log-related configuration parameters:

• LOGFILSIZ-the size of each log file. The default value is 250. The unit is 4 kb.
• LOGPRIMARY -- the default number of master log files is 3 ).
• LOGSECOND -- number of second-level log files that can be allocated when the primary log file is full. When this parameter is set to-1, version 8), an unlimited number of activity logs can be used.
• NEWLOGPATH -- used to change the storage location of log files. To make this parameter take effect, you need to reactivate the database.
• MIRRORLOGPATH -- the image path of the log file to avoid spof.
• OVERFLOWLOGPATH -- specify the directories in which logs can be searched during the rollback to allow the rollback operation to access logs in multiple directories.
• USEREXIT -- enable the user exit function to automatically archive logs.
• BLK_LOG_DSK_FUL -- When DB2 cannot create a new log file in the active Log Path, it will prevent the error that the generated disk is full.

To use online backup, you must open the archive log. By setting LOGRETAIN to ON, you can open archived log records.

Archive logging is a log record mechanism different from cyclic logging. Because cyclic logging overwrites submitted logs, archive logging archives submitted logs.

You cannot reuse logs that have been converted to archive logs in archive logs. To move online archived logs, You need to manually move them or use the user exit program user exit) to move them. Figure 1 illustrates the archiving logging mechanism:

Figure 1. Activity, online, and archive Logging

In the figure, logs (n + 1) are activity logs, and logs (n) and logs (n-I) are all online archived logs. These archived logs can be stored in the path where active logs are stored, or you can use the user Export program to specify the directory or media for storing them.

By default, activity and online archiving logs are stored in the SQLOGDIR directory. The NEWLOGPATH database configuration parameters determine where to store future activity archive logs. To enable the NEWLOGPATH parameter to take effect, you must stop the database to disable all active log files ). Then, reactivate the database will create a new log file in the new path, and the original archive log will remain in the original path.

When online backup is performed, all database transactions are recorded. After the online backup is completed, DB2 forcibly closes the current active log and archives it, as shown in Figure 2:

Figure 2. Active logs being disabled

To prevent single point of failure (spof) for offline archiving logs, such as media failure, log images should be used. MIRRORLOGPATH is a configuration parameter used to specify the image path. It allows DB2 to write the second copy of the same log file to different directories. You need to reactivate the database to make the logpath Configuration Parameter valid.

If a problem occurs when writing a copy to the Image Log Path, DB2 writes a message to the management notification log, indicating that an error has been encountered. DB2 will continue to write the log records to the working Log Path. You do not need to synchronize the Log Path. To determine which logs are active and which logs are archived, run the DB2 Command get db cfg to view the "first active Log File ". This command provides the currently active log files, so the archived logs will be those earlier than the current log.

In addition, each backup operation such as database, tablespace, or Incremental Backup will include a copy of the history file RHF. You can use the information provided in the history file to restore a part of the entire database or database to a certain time point. Each database creates a recovery history file, which is automatically updated in the following circumstances:

• Back up the database or tablespace.
• Recover the database or tablespace.
• Roll back the database or table space.
• Create a tablespace.
• Modify the tablespace.
• Tablespace.
• Rename a tablespace.
• Delete a tablespace.
• Load a table.
• Delete a table.
• Reorganize the table.