For enabling archivelog in the Oracle database, we need to consider the primary goal of end-users and business requirements.
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, the 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 to repopulate 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 at any time during the day and the 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 to 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 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 workable, albeit slower, and even more administration-intensive, alternative. Also, some sites enable 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.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.