When Archiving is necessary in Oracle Database

January 29, 2019 | By

For enabling archivelog in Oracle database, we need to consider the primary goal of end users and business requirement.

The advantages of archiving generally make it necessary for almost any environment. However, in certain environments, it becomes more of a source of overhead than a facility. It’s always necessary to evaluate the amount of data loss that is “tolerable” to the end users. Prima facie, absolutely no data loss is tolerable to the end users. However, with some patience and persistence, you may find that some data loss may be acceptable, after all. For instance, if the end users are more interested in data for the last three years, loss of older data may be tolerable, until the time it is needed again. Even when no data loss is tolerable if the time required repopulating the database is less than the time required to recover it, then archiving becomes redundant.

For example in a data-mart, a cold backup if the database is not being operated in archivelog mode, a hot backup is not possible and a logical backup may be taken every night. The data-mart may be loaded via nightly batch processes following the backup. If there is a database crash any time during the day and data file is lost, the database may be restored using the previous night’s backup. Then the batch processes that populated it may be rerun to restore it to its pre-crash state. It this restoring and repopulating occurs faster than restoring and applying recovery, then archiving may not be necessary for such an environment and also not recommended because it is useless.

Also, another factor that aids the decision is the availability of disk-space for archiving. I have observed that, at some of my client sites, a smaller machine is dedicated for each data-mart. And these smaller machines do not always have access to the necessary disk space for archiving. If space is a major concern due to lack of funds and so on and repopulating the database occurs at the same pace or faster than recovery, then I am generally inclined to tilt toward not having archiving turned on. However, it has to be borne in mind that besides recovery up to the last transaction, you would be missing some other significant advantages by disabling archiving. The point here is, after evaluating availability requirements; you may still be able to configure a satisfactory environment which one that satisfies all SLA clauses without archiving being turned on. In the few hours of downtime that is available, you still may be able to perform backups, restores, and rebuilds, rather than backups, restores, and recovery.

If disk space is scarce and archiving is highly necessary, then arching to other offline media such as tape drive needs to be considered. As long as the administrators ensure that the tapes are changed frequently to prevent them from filling up and freezing the database, this should be a workable, albeit slower and even more administration-intensive, alternative. Also, some sites enabling archive log mode only so that online backups can be performed. In such cases, restoring the online backups, applying the archived redo-logs created during the backups, and rerunning certain batch jobs would also allow recovery up to the last committed transaction. In such situations, the archived logs created after the backups may be deleted off disk using cron jobs without even backing them up. As long as the archived logs created during the online backups are retained, recovery should not be the problem. Such an approach is useful during high disk space scarcity. In any case, ensure that you understand all availability requirements either directly from the end users or from the SLA, prior to disabling archivelog mode or deleting archived logs.

Tags: , ,

Category: oracle dba

Comments are closed.