Oracle log operating mode (advantages and disadvantages of archive and non-archive mode) _oracle

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

the advantages and disadvantages of the non-archival mode.

Non-archive mode is the log operation mode that does not retain redo history and can only be used to protect the routine from failure and not to protect media corruption. If the database is in the log operation mode, when the log switch, the new log will directly overwrite the contents of the original log file, will not retain the original log file data.
That may sound more difficult to understand. The author gives a simple example, it will be clear many. If there are now four log groups in the Oracle database, the log sequence number is 11, 12, 13, and 14 respectively. When a database transaction changes to fill the first Log group file (the serial number is 11), the Oracle database automatically switches to the second log group file (serial number 12). by analogy. When the third log group file (serial number 13) is full, it switches to the fourth log group (the serial number is 14). When the fourth log group (serial number 14) is full, it switches to the first log group (the serial number is 15). Here, the serial number is different from the first log file group, but the log group is still the same. At this point, the contents of the first set of log files (serial number 11) are not archived because the operation mode of the database is selected as not in archive mode. The contents of the new log file will overwrite the contents of the first log group file directly. If the first log group file (serial number 15) is full, and the new content is switched to the second log filegroup, it overwrites the original data in log file 12 without archiving the second log filegroup.

Through the above analysis, we can sum up some features of the non-archival operation log mode.

First, after the checkpoint is completed, the background process can overwrite the contents of the original redo log. That is, when the log is switched, later log file content can overwrite the contents of the original log file when the contents of the previous log file are not archived. In this case, when the data file is corrupted, the database administrator can only revert to the point where the past was completely divided. If the database has four log groups. If the database administrator makes a full backup when the log group serial number is 16. The data file was corrupted when the log group serial number was 28. At this point, the contents of the log file in the middle are overwritten. So, at this point, the database administrator is only able to restore to the point of full backup, not to recover the data of the point when the database file is corrupted (that is, the serial number is 28). If there is an accident of data file corruption when the serial number is 18, you can restore the database (the restore point is 16 o'clock data), and then use the Redo log file (serial number 17, 18), that is, you can restore the data to the time of the failure. Therefore, although the Redo log files are not archived, save disk space. However, the subsequent database recovery caused by the trouble, reduce the security of the database. To this end, how to choose, or need a database administrator according to their own business situation, make a choice.

Second, when performing a database backup, you must back up all the data files and control files. According to the above author, because the redo log will be covered later, so, basically, the database administrator can not redo log files to restore the database data, or, by redo log files can not recover all the data. To do this, you must back up all of your data files and control files when you perform a database backup. You must also close the database using commands such as Shutdownnormal.
　　
Second, the archived log mode.

The archive log mode corresponds to the non-archived log mode, which is the log operation mode that retains the history of redo logs. This log operation mode can be used not only to protect the routine from failing, but also to protect against media corruption. If the database administrator sets the log to archive log mode, the background process automatically copies the contents of the redo log to the archive log when the background process is in log switching. Archive logs are backups of inactive redo logs.

If there are now four log groups in the Oracle database, the log sequence number is 11, 12, 13, and 14 respectively. When a database transaction change fills the first log group file (the serial number is 11), the background process switches to the second log group (the serial number is 12). Before this switch, however, the database has a process that is responsible for copying the contents of the files in the first log group into the archive log. by analogy. This is the most essential difference between the archive log mode and the non-archived log pattern. But this difference has brought a great change to the security of the database.

If the log serial number is 28 o'clock with a data file error or an incident where the server hard disk is damaged, because all of the data changes since the database backup are logged. And these log files are stored in other media with the database backup files, so the database administrator can restore the database to the data when the media is damaged (that is, the log file sequence number is 28). From the point of view of protecting database data, this is a very close to the ideal state.

If you compare the non-archive mode with the archive mode, you can find that the archiving model has the following characteristics.
The first is that when media corruption occurs (such as a hard drive or accidentally deletes a data file) or a routine fails, such as a sudden power outage on the server, the database administrator can prevent the loss of data by using an archived log file. Rather than archiving mode, it is often only possible to deal with routine failures. Therefore, its scope of application is much larger than the non-archive mode.

The second is the restriction condition of data backup. As mentioned above, if the database is in a non-archive mode, then the database is backed up with shutdownnormal commands. While in archive mode, the database administrator can still back up the database when the database is open, without affecting the normal use of the database. In addition to the fundamental differences between database backups, there are significant differences in database recovery. If the database is in the archive log mode, not only can full recovery be performed, but the database can also be restored to a specific point with the help of an archive log file. This will maximize the security of the data when there is an unexpected failure in the database.
However, if you take the archive mode, you must sacrifice a certain amount of disk space.

third, how to choose the appropriate log operation mode?

Non-archive mode only applies when a routine fails to recover data and cannot be used to protect media corruption. That is, when the data file for the database is accidentally corrupted, the non-archive mode has no response. The archive mode can be used not only to protect the routine from failure, but also to restore the original data of the database to the maximum extent when the media fails. At this point, the database administrator can use the database backup files, archive log files, redo log files and so on to restore the data in the database to the point where the failure occurred.

Since the archive operation mode and the non-archive mode of operation each have their own characteristics, then when the archive log mode is good, when should be used in a non-archive mode? The answer to this question is that the public is justified and the woman is right. I'm afraid there is not a fixed answer to the argument for a few years. In this respect, I would like to mention their own opinions. This is not a standard answer, just for everyone's reference.

The first thing to look at is how frequently data changes in the database. When the data in the database changes less, it is best to use a non-archive mode. Conversely, if the data in the database changes more frequently, such as some business operating systems, it is best to adopt an archiving model.

Second, to see the enterprise's attitude to data loss. If an enterprise has a high requirement for data security, such as a bank, it is not allowed to lose any data, it is best to use the archive log mode. When a database fails unexpectedly, it can help the database administrator to recover data to the maximum extent possible. Similarly, when an enterprise can allow partial corruption of data, it can take a non-archive mode to conserve the extra overhead and disk space for log file backups when switching log groups.

Again, check to see if the database needs to be run 24x7. Because in non-archive mode, you must close the database with commands such as shutdownnormal before you can back up the database. This is not consistent with the requirement that the database is running 24x7. In archive mode, even if the database is open, it can be backed up without affecting the normal operation of the database. For this reason, it is best to use an archiving model if the database needs to run 24x7. Although the database has to pay some extra cost, I think it is worthwhile. After all, the hardware investment has the price, the data is priceless.

The database administrator should choose the appropriate log operation mode according to the actual situation of the enterprise. This allows the redo logs and archive logs to truly become an umbrella for Oracle databases.