Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
- LOG_ARCHIVE_DEST_N
- 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.
1 | SQL> alter system set log_archive_dest_1 = 'location=/…… ' scope = both; |
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.
1 | SQL> alter system set log_archive_format='SINGLEDB_%t_%s_%r.arc' scope=both; |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 14 Current log sequence 16 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 14 Current log sequence 16 |
Enable Fast Recovery Area
The following operations are performed consequentively. If the order changes, you receive an error.
1 2 3 4 5 6 7 | SQL> alter system set db_recovery_file_dest_size=20000M; System altered. SQL> alter system set db_recovery_file_dest='+FRA'; System altered. |
Now, check the db_recovery parameter again;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 10000M SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 14 Current log sequence 16 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SQL> show parameter log_archive_dest_1 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string SQL> show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 10000M SQL> show parameter log_archive_format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf |
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;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> archive log list; (If FRA Used) Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 19 Current log sequence 21 SQL> archive log list; (If FRA Not Used) Database log mode No Archive Mode Automatic archival Disabled Archive destination /oracle/ora11g/dbs/arch Oldest online log sequence 7247 Current log sequence 7249 |
Check Archivelog Size
1 2 3 4 5 6 | SQL> show parameter db_recovery_file_dest; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 10G |
Check Archivelog Parameters
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | SQL> show parameter log NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_syslog_level string commit_logging string db_create_online_log_dest_1 string db_create_online_log_dest_2 string db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string enable_ddl_logging boolean FALSE log_archive_config string log_archive_dest string log_archive_dest_1 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string log_archive_dest_3 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_30 string log_archive_dest_31 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string log_archive_dest_9 string log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable log_archive_dest_state_18 string enable log_archive_dest_state_19 string enable log_archive_dest_state_2 string enable log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable log_archive_dest_state_3 string enable log_archive_dest_state_30 string enable log_archive_dest_state_31 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable log_archive_dest_state_9 string enable log_archive_duplex_dest string log_archive_format string RMANASM_%t_%s_%r.arc log_archive_local_first boolean TRUE log_archive_max_processes integer 4 log_archive_min_succeed_dest integer 1 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_start boolean FALSE log_archive_trace integer 0 log_buffer integer 6815744 log_checkpoint_interval integer 0 log_checkpoint_timeout integer 1800 log_checkpoints_to_alert boolean FALSE log_file_name_convert string |
Check Archivelog Format
1 2 3 4 5 | SQL> show parameter log_archive_format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string RMANASM_%t_%s_%r.arc |
How To Enable Archivelog Mode in Oracle?
Determine where the database archive log files will be stored(for FRA);
1 2 | SQL> alter system set db_recovery_file_dest_size=10G scope=both; SQL> alter system set db_recovery_file_dest='+FRA' scope=both; |
Determine where the database archive log files will be stored(File System Location);
1 | SQL> alter system set log_archive_dest_1 = 'location=/…… ' scope = both; |
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;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | SQL> alter system set log_archive_format='singledb_%t_%s_%r.arc' scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 3323752448 bytes Fixed Size 2217912 bytes Variable Size 1811941448 bytes Database Buffers 1493172224 bytes Redo Buffers 16420864 bytes Database mounted. Database opened. SQL> show parameter log_archive_format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string singledb_%t_%s_%r.arc |
Check Archivelog File Location;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SQL> archive log list; (FRA alanı kullanılmışsa) Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 19 Current log sequence 21 SQL> archive log list; (FRA alanı kullanılmamışsa) Database log mode No Archive Mode Automatic archival Disabled Archive destination /oracle/ora11g/dbs/arch Oldest online log sequence 7247 Current log sequence 7249 SQL> show parameter db_recovery_file_dest; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 10G |
Enable Archivelog Mode
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2213896 bytes Variable Size 1006635000 bytes Database Buffers 637534208 bytes Redo Buffers 7135232 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG SQL> |
Disabling Archivelog Mode
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2213896 bytes Variable Size 1006635000 bytes Database Buffers 637534208 bytes Redo Buffers 7135232 bytes Database mounted. SQL> alter database noarchivelog; Database altered. SQL> alter database open; Database altered. SQL> select log_mode from v$database; LOG_MODE ------------ NOARCHIVELOG SQL> |
