Oracle log operation mode (benefits and disadvantages of archive mode and non-archive mode)

Today, I will talk about my understanding of these two operation modes and give some feasible suggestions to improve the security of Oracle databases.

I. Advantages and Disadvantages of non-archive mode.

The non-archive mode is a log operation mode that does not retain the redo history. It can only be used to protect the failure of the routine, but not the damage of the media. If the database adopts the log operation mode, during log switching, the new log will directly overwrite the content of the original log file without retaining the data in the original log file.
This may sound hard to understand. The author will give a simple example to understand a lot. For example, there are four log groups in the Oracle database. The log serial numbers are 11, 12, 13, and 14 respectively. When Database Transaction changes are filled with the first log group file (serial number is 11), the Oracle database automatically switches to the second log group file (serial number is 12. And so on. When the third log group file (serial number 13) is full, it switches to the fourth log group (serial number 14 ). When the fourth log group (serial number 14) is full, it switches to the first log group (serial number 15 ). Here, although the serial number is different from the first log file group, the log group is still the same. In this case, because the database operation mode is not archive, the content in the first log file (serial number 11) is not archived. The content of the new log file will directly overwrite the content in the first log group file. If the first log group file (serial number is 15) is full, When you switch to the second log file group, the new content will not be archived in the second log file group, directly overwrite the original data in log file 12.

Through the above analysis, we can summarize some features of the non-archive operation log mode.

First, after the checkpoint is completed, the background process can overwrite the original redo log Content. In other words, when switching logs, the subsequent log file content can overwrite the content of the original log file without archiving the previous log file content. In this case, when the data file is damaged, the database administrator can only restore to the full split point in the past. If the database has four log groups. If the log group serial number is 16, the database administrator performs a full backup. When the log group serial number is 28, the data file is damaged. In this case, the contents of the intermediate log file are overwritten. Therefore, at this time, the database administrator can only restore to the point of the full backup, but cannot restore the data of the point where the database file is damaged (that is, the data with the serial number of 28. If a data file is damaged when the serial number is 18, you can restore the database first (the data with the Restore point being 16 ), then, redo the log file (serial number 17 and 18) to restore the data to the fault. Therefore, you do not need to archive the redo log files to save disk space. However, it makes subsequent database recovery troublesome and reduces the security of the database. To this end, the database administrator needs to make a choice based on the situation of the enterprise.

Second, all data files and control files must be backed up during database backup. As described above, because the redo log will be overwritten later, the database administrator cannot recover the database data by redoing the log file, or, you cannot recover all data by redoing the log file. Therefore, all data files and control files must be backed up during database backup. In addition, you must use commands such as SHUTDOWNNORMAL to shut down the database.
　　
Ii. archiving log mode.

The archive log mode corresponds to the non-archive log mode. It refers to the log operation mode that retains the redo log history. This log operation mode can be used not only to protect the failure of the routine, but also to protect the situation where the media is damaged. If the database administrator sets the log archive mode, when the background process switches the log, the background process automatically copies the redo log content to the archive log. Archive logs are backups of inactive redo logs.

For example, there are four log groups in the Oracle database. The log serial numbers are 11, 12, 13, and 14 respectively. When Database Transaction changes are filled with the first log group file (serial number is 11), the background process switches to the second log group (serial number is 12 ). However, before this switchover, a database process is responsible for copying the file content in the first log group to the archive log. And so on. This is the essential difference between the archive log mode and the non-archive log mode. However, this difference has greatly changed the database security.

For example, when the log serial number is 28, a data file error occurs or the server hard disk is damaged, because the log file records all the data changes since the database backup. In addition, these log files and database backup files are stored in other media. Then, the database administrator can use these files to restore the database to medium damage (that is, the log file serial number is 28). From the perspective of protecting database data, this is a situation close to ideal.

If you compare the non-archive mode with the archive mode, you can find that the archive mode has the following features.
First, when the media is damaged (such as hard disk damage or accidental deletion of data files) or a routine fails (such as a sudden power failure on the server), the database administrator can archive the log files to prevent data loss. Instead, the archive mode can only cope with routine failures. Therefore, the application scope is much larger than that in non-archive mode.

Second, restrictions on data backup. As mentioned above, if the database is in non-archive mode, you must use commands such as SHUTDOWNNORMAL to shut down the database when backing up the database. When the database is in archive mode, the database administrator can still back up the database when the database is in Open state, without affecting the normal use of the database. Apart from the essential differences between database backup and backup, there are also great differences in database recovery. If the database adopts the archive log mode, it can not only perform full recovery, but also restore the database to a specific point with the help of archiving log files. This maximizes data security when a database encounters an unexpected fault.
However, if you adopt the archive mode, you must sacrifice a certain amount of disk space.

3. How to select an appropriate log operation mode?

The non-archive mode is only applicable to data recovery when the routine fails and cannot be used to protect media corruption. That is to say, when the data files in the database are accidentally damaged, the non-archive mode has no countermeasures. The archive mode can not only protect the failure of routine, but also restore the original data of the database to the maximum extent when the media fails. In this case, the database administrator can use the database backup files, archive log files, and redo log files to restore the data in the database to the point where the fault occurred.

Since the archive operation mode and non-archive operation mode have different characteristics, it is better to adopt the archive log mode and when should we adopt the non-archive mode? The answer to this question is to make a public statement and to make a sense. I'm afraid there is no fixed answer for several years. In this regard, I will give my own opinions. This is not a standard answer, but for your reference.

First, it depends on the frequency of data changes in the database. When there are few changes to the data in the database, it is best to adopt the non-archive mode. On the contrary, if the data in the database changes frequently, such as some business operating systems, it is best to adopt the archive mode.

Second, it depends on the company's attitude towards data loss. If enterprises have high requirements on data security, such as banks, and do not allow the loss of any data, it is best to adopt the archive log mode. When the database encounters an unexpected fault, it can help the database administrator restore data to the maximum extent. Similarly, when an enterprise allows partial damage to data, it can adopt non-archive mode to save additional overhead and disk space for log file backup when switching the log group.

Furthermore, check whether the database needs to run around the clock. In non-archive mode, you must use commands such as SHUTDOWNNORMAL to shut down the database before backing up the database. This is inconsistent with the database's round-the-clock operation requirements. In archive mode, you can back up the database even if it is in OPEN state, without affecting the normal operation of the database. Therefore, if the database needs to run around the clock, it is best to adopt the archive mode. Although the database has to pay some extra costs, I think it is worthwhile. After all, hardware investment is valuable and data is priceless.

The database administrator should select an appropriate log operation mode based on the actual situation of the enterprise. In this way, redo logs and archive logs become the umbrella of Oracle databases.