What is Archivelog Mode in Oracle and How Do I Enable and Disable Archivelog Mode

What is Archivelog Mode in Oracle?

The following operations cannot be performed until archive log mode is enabled.

  • RMAN Online Backup
  • Flashback Database
  • User Managed Hot Backup

ARCHIVE LOG MODE must be enabled to perform the above operations.

There are some situations in the database that must be considered before enabling archivelog mode. These are as follows;

  • The size of the disk on which our Archive Logs will be stored should be determined and disk space allocated accordingly. This is an important issue since archive log files take up space on the disk.
  • How long we will store our archive logs is another important issue.
  • It is necessary to decide whether the archive log files will be stored on a single directory or on multiple disks.
  • We need to determine the format of our archive logs. Thus, it will be easier to differentiate from other database files.

There must be some configurations on the database before enabling archive mode. Like determining where to save archive log files.

  1. LOG_ARCHIVE_DEST_N
  2. FRA Usage

1) Locating Archive Log Files(LOG_ARCHIVE_DEST_N)

We can set the location of archive log files with LOG_ARCHIVE_DEST_N parameter.

We also need to set the format of the archive logs. By default, Archive Logs are saved with a .dbf extension, but this makes it difficult to differentiate archive logs from data files. Therefore, we can define the desired format with LOG_ARCHIVE_FORMAT parameter.

The meaning of the characters used to determine the format of the archive log files;

%s  ->  log sequence number

%S  ->  log sequence number, zero filled

%t  ->  thread number

%T ->  thread number, zero filled

%a  ->  activation ID

%d  ->  Database ID

%r  ->  Resetlod ID

Oracle default format; %t_%s_%r.dbf

2) FRA(Fast Recovery Area) Usage

What is Fast Recovery Area?

FRA is an area where the following files are stored;

  • Archive log files,
  • RMAN backup files,
  • Flashback log files,
  • Online Redo Log files,
  • Control File

Use of the FRA area can be enabled by setting two parameters in the database. These parameters are; DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE.

DB_RECOVERY_FILE_DEST determines the directory to use as the FRA area.

DB_RECOVERY_FILE_DEST_SIZE determines the maximum space for files in the FRA area.

Before Enabling Fast Recovery Area

Enable Fast Recovery Area

The following operations are performed consequentively. If the order changes, you receive an error.

Now, check the db_recovery parameter again;

DB_RECOVERY_FILE_DEST  vs LOG_ARCHIVE_DEST

If the parameter LOG_ARCHIVE_DEST_N is set together with the FRA area in the PARAMETER file in the database, the archive log files are saved to the directory set in the LOG_ARCHIVE_DEST_N parameter, not the FRA area. These situations can be queried as follows.

When the first file is created in the FRA area, Oracle creates a set of logical directories on the file location specified by the db_recovery_file_dest parameter. Its structure is as follows;
db_recovery_file_dest/Oracle_SID/archivelog/[YYYY_MM_DD]

According to the above directory structure, a new directory is created every day and archive files are saved to these locations.

Oracle recommends the use of FRA space for RMAN backup files and Archive Log Files. FRA area management is performed by Oracle. Backup files and archive files are stored on the same disk.

When a very high level of REDO occurs, if there is no space in the FRA area; If a new REDO Log Switch operation occurs, the database will become unresponsive as there will be no more disk space.

Therefore, FRA is not a preferred area of ​​use in production databases. Instead, it is necessary to specify a space by setting LOG_ARCHIVE_DEST_N and keep it under control with SCRIPT.

Find Archivelog Location

You can check the Archivelog file location as follows;

Check Archivelog Size

Check Archivelog Parameters

Check Archivelog Format

How To Enable Archivelog Mode in Oracle?

Determine where the database archive log files will be stored(for FRA);

Determine where the database archive log files will be stored(File System Location);

If you set both of above parameters, the archive log files are saved to the directory set in the LOG_ARCHIVE_DEST_N parameter, not the FRA area. You can find the detail informartion above.

Set Archive Log Files Format;

Check Archivelog File Location;

Enable Archivelog Mode

Disabling Archivelog Mode

Onur ARDAHANLI
Author: Onur ARDAHANLI

Leave a Reply

Your email address will not be published. Required fields are marked *