What is Archiving in oracle database.

By Manish Sharma

Archiving in Oracle Database explained.

What is archiving in oracle database?

The configuration of a database at the time of its creation is necessary in order to easily recover your data in case of any occurrence of system failure. Online redo log files are the ones that contain the complete record of any changes that were made to the primary data files. These online redo log files are stored in online redo log groups. It is important to have at least two online redo log groups for your database. Once an online redo log files belonging to a group have been filled, the log writer process (LGWR) switches to a new group for continuing the writing process of redo log records. The inactive group of online redo log files can be automatically saved by the Oracle database at one or multiple offline destinations. These destinations are collectively addressed as archived redo log or the archive log. This process of converting online redo log files into archived log is called Archiving.

“The process of converting online redo log files into archived log is called Archiving.”

The process of archiving can only be performed when the database is running in the ARCHIVELOG mode. It is important to note that unless the group of online redo log files is archived, they cannot be reused by the log writer (LGWR). In case the LGWR switches to a new group and leaves the previous one inactive for some time then that inactive group becomes readily available for immediate reuse by the LGWR process provided the database is running in NOARCHIVELOG mode.

A database instance failure could be avoided in NOARCHIVELOG mode however this mode does not protects the database from media failure. The most recent changes made to the database that is stored in the online redo log files are only ones available for instance recovery. Therefore in order to restore the database operating in NOARCHIVELOG mode, you can only use the entire database backups that were taken while it was closed. Therefore it is essential that you frequently back-up the entire database when operating it in NOARCHIVELOG mode.

Advantages of Archiving.

The advantages of archiving online redo log files are:

  1. A database backup, with online and archived redo log files, ensures that you can recover all committed transactions in case of the failure of operating system or hardware.
  2. If you have a copy of the archived log files that were written while the database was being backed up then you can easily recover the database using that backup which was taken while the database was open and being used.
  3. You can perform online tablespace backups, and use these backups to restore a tablespace following media failure.
  4. You can keep a standby database current with its original database by continuously applying the original archived redo log files to the standby database.

The destination to which you want to archive the online redo log files must be decided beforehand. It is recommended by Oracle that archive log should be stored in such areas that are capable of fast recovery in order to simplify the operations of backup and recovery. There is a difference between fast recovery area and database area. The former is where the Oracle database stores and manages files related to backup and recovery whereas the latter is the location of the current database files that include – data files, control files and online redo log files.

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn