Advantages and disadvantages of oracle log operation mode of archive mode and non archive mode

  • 2021-06-28 14:26:31
  • OfStack

The author talks about his understanding of these two operation modes today, and gives some feasible suggestions to improve the security of Oracle database with you.

1. Advantages and disadvantages of non-filing mode.

Non-archive mode is a log operation mode that does not preserve the redo history and can only be used to protect routine failures from media corruption.If the database is in log mode, the new log will directly overwrite the contents of the original log file and will not retain the data in the original log file when switching logs.
That may sound difficult to understand.I can tell a lot by giving a simple example.If there are now four log groups in the Oracle database, the log sequence numbers are 11, 12, 13, 14, respectively.When the database transaction changes fill the first log group file (serial number 11), the Oracle database automatically switches to the second log group file (serial number 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 sequence number is different from the first log file group, the log group is still the same.At this time, the contents of the first set of log files (serial number 11) are not archived because the database's mode of operation was chosen as non-archiving.The contents of the new log file will directly overwrite the contents of the first log group file.If the first log group file (serial number 15) is full and you switch to the second log group, the new content will directly overwrite the original data in log file 12 without archiving the second log group.

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

1 When the checkpoint is completed, the background process can overwrite the contents of the original redo log.That is, later log file contents can overwrite the contents of the original log file without archiving the contents of the previous log file during log switching.In this way, when data file corruption occurs, the database administrator will only be able to recover to the full partition of the past.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 intermediate log file are overwritten.Therefore, at this point the database administrator can only recover to the point where the full backup was made, not to the point where the database file was damaged (i.e., serial number 28).If a data file corruption occurs when the serial number is 18, the database can be restored (data with a restore point of 16) and then redo log files (serial numbers 17, 18) can be used to restore the data to the data at the time of the failure.Therefore, although you do not need to archive the redo log files, you can save disk space.However, it brings troubles to subsequent database recovery and reduces the security of the database.To do this, database administrators need to make choices based on their own enterprise.

2. When performing database backup, all data files and control files must be backed up.According to the author mentioned above, since the redo log will be overwritten later, database administrators can not basically restore the database data by redoing the log file, or, in other words, cannot restore all the data by redoing the log file.To do this, all data files and control files must be backed up when a database backup is performed.At the same time, you must use commands such as SHUTDOWNNORMAL to close the database.
*
2. Archive log mode.

The archived log mode corresponds to the non-archived log mode, which refers to the log operation mode that preserves the history of redo logs.This logging mode can be used not only to protect routines from failure, but also to protect media from damage.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 switches logs.Archive logs are backups of inactive redo logs.

If there are now four log groups in the Oracle database, the log sequence numbers are 11, 12, 13, 14, respectively.When the database transaction changes fill the first log group file (serial number 11), the background process switches to the second log group (serial number 12).Before this switch, however, there is a process in the database that copies the contents of the files in the first log group to the archive log.And so on.This is the most essential difference between archived and non-archived log modes.However, this difference has greatly changed database security.

If a data file error or a server hard disk corruption occurs when the log sequence number is 28, the log file records all data changes since the database backup.Moreover, if these log files and database backup files are stored in other media, the database administrator can use these data to recover the database to the data in case of media damage (i.e., log file serial number 28).From the point of view of protecting database data, this is a close to ideal state.

If you compare the non-archiving mode with the archiving mode, you can see that the archiving mode has the following characteristics.
1 When media damage occurs, such as hard disk damage or accidental deletion of data files, or routine failures occur, such as a sudden power outage on the server, the database administrator can rely on archive log files to prevent data loss.Instead of archiving, it tends to cope only with routine failures.Therefore, its application range is much larger than non-archiving mode.

2 is the limit for data backup.As mentioned above, if the database is in non-archive mode, when backing up the database, you must first shut down the database with commands such as SHUTDOWNNORMAL.When 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.In addition to the fundamental differences between database backups, there are also big differences in database recovery.If the database is in archived log mode, not only can a full recovery be performed, but the database can also be restored to a specific point with the help of archived log files.Thus, when the database fails unexpectedly, the maximum protection of data security.
However, if archive mode is used, a certain amount of disk space must be sacrificed.

3. How to choose the appropriate log operation mode?

Non-archive mode only works for data recovery when routines fail and cannot be used to protect media damage.That is, when the data files of the database are accidentally damaged, there is no solution for non-archive mode.Archiving mode can be used not only to protect routine failures, but also to maximize the recovery of the database's original data when the media fails.At this time, the database administrator can use database backup files, archive log files, redo log files, and so on to restore the data in the database to the point at which the failure occurred.

Since the archive and non-archive modes have their own characteristics, when is the best time to use the archive log mode and when should the non-archive mode be used?The answer to this question is to say it's fair and reasonable, but your mother-in-law says it's right.I'm afraid there isn't a definite answer to the debate for years.In this regard, the author puts forward his own views.This is not the standard answer, but for your reference.

First, it depends on how frequently data changes in the database.When there is less data change in the database, it is best to use a non-archiving mode.Conversely, if the data in the database changes frequently, such as some business operating systems, it is best to use the archive mode.

Secondly, it depends on the business's attitude towards data loss.If an enterprise has high data security requirements, such as banks, which do not allow any data to be lost, it is best to use archive logging mode.When a database fails unexpectedly, it can help the database administrator recover the data to the maximum extent possible.Similarly, when an enterprise can allow partial corruption of data, a non-archiving mode can be used to save additional overhead and disk space for log file backups when switching log groups.

Also, check to see if the database needs to run 24 hours a day.Because in non-archive mode, you must use commands such as SHUTDOWNNORMAL to close the database before you can back it up.This does not meet the requirements for the database to run 24 hours a day.In archive mode, even if the database is in OPEN state, it can be backed up without affecting the normal operation of the database.For this reason, if the database needs to run 24 hours a day, it is best to use the archive mode.Although the database has to pay some extra cost for this, I think it is also worth it.After all, hardware investment is valuable, and data is priceless.

The database administrator should choose the appropriate log operation mode according to the actual situation of the enterprise.This really makes the redo and archive logs the umbrella for the Oracle database.

Related articles: