Enabling Archivelog mode is as a critical practice for ensuring data integrity, disaster recovery readiness, and seamless database operations. In this entry, we unravel the significance of Archivelog mode, delineate its indispensable features, provide a step-by-step guide on enabling it, and offer insights into modifying the archive log destination.
Why Enable Archivelog Mode?
Archivelog mode represents a pivotal configuration setting in Oracle databases, transforming the database’s logging mechanism to include archived redo logs alongside online redo logs. This paradigm shift confers several advantages, including:
- Point-in-Time Recovery: Archivelog mode facilitates point-in-time recovery (PITR), enabling administrators to restore the database to a specific timestamp, critical for mitigating data corruption or human errors.
- Continuous Backup: With Archivelog mode enabled, Oracle can generate a continuous stream of archived redo logs, allowing for consistent database backups without requiring downtime.
- Log-Based Replication: Archivelog mode lays the foundation for log-based replication solutions, facilitating real-time data synchronization between databases for high availability and disaster recovery purposes.
- Advanced Data Guard Features: Oracle Data Guard, a cornerstone of disaster recovery solutions, mandates Archivelog mode for implementing features such as standby databases and far sync instances, bolstering database resilience.
More on Redo log and Archive log
The redo log and the archive log are fundamental components of data protection and recovery mechanisms within an Oracle database environment. The redo log consists of a set of files that record all changes made to the database. These changes are recorded in a sequential order, capturing both committed and uncommitted transactions, ensuring data integrity and providing a means for database recovery in the event of system failure or crash.
On the other hand, the archive log consists of copies of the redo log files that have been archived to persistent storage. These archive log files are crucial for point-in-time recovery, allowing database administrators to restore the database to a specific state by applying archived redo logs up to a desired timestamp.
In essence, while the redo log captures real-time changes to the database, the archive log serves as a historical record that enables comprehensive data recovery and disaster resilience.
Enabling Archive Log Mode
[oracle@primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 19 10:56:34 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
-- Check that the archive log mode in off.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/19/db_1/dbs/arch
Oldest online log sequence 26
Current log sequence 28
-- Move the archive log destination to the second disk.
SQL> alter system set log_archive_dest='/u02/oracle/ORCL/archive/' scope=both;
System altered.
-- Shutdown the DDBB and start it up in mount mode to enable archive log mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3590322960 bytes
Fixed Size 9141008 bytes
Variable Size 1476395008 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7634944 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
-- Check the archive log mode is enabled.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/oracle/ORCL/archive/
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
-- Check the archive log mode is on.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
-- The view v$archive_dest shows us every defined destination and its status.
SQL> column destination format a40
SQL> select dest_id, status, destination from v$archive_dest where dest_id < 5;
DEST_ID STATUS DESTINATION
---------- --------- ----------------------------------------
1 VALID /u02/oracle/ORCL/archive/
2 INACTIVE
3 INACTIVE
4 INACTIVE
Testing how Archive Log works
SQL> select group#, thread#, sequence#, bytes/1024/1024, members, status from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 28 200 1 CURRENT
2 1 26 200 1 INACTIVE
3 1 27 200 1 INACTIVE
SQL> !ls -l /u02/oracle/ORCL/archive
total 0
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> select group#, thread#, sequence#, bytes/1024/1024, members, status from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 28 200 1 ACTIVE
2 1 29 200 1 CURRENT
3 1 27 200 1 INACTIVE
SQL> !ls -l /u02/oracle/ORCL/archive
total 42436
-rw-r----- 1 oracle oinstall 43453952 feb 19 11:32 1_28_1151886492.dbf
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> select group#, thread#, sequence#, bytes/1024/1024, members, status from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 28 200 1 ACTIVE
2 1 29 200 1 ACTIVE
3 1 30 200 1 CURRENT
SQL> !ls -l /u02/oracle/ORCL/archive
total 42516
-rw-r----- 1 oracle oinstall 43453952 feb 19 11:32 1_28_1151886492.dbf
-rw-r----- 1 oracle oinstall 79360 feb 19 11:34 1_29_1151886492.dbf
Instead of using ALTER SYSTEM SWITCH LOGFILE, we could have also employed ALTER SYSTEM ARCHIVE LOG CURRENT. Both commands force a log switch, albeit employing different mechanisms. While the first one is immediate and only switches the log file in the current instance when used in a RAC environment, the second one waits for the archiver process to complete its writing process of the online redo log to the archiver file and switches the log file in every instance of the RAC.
Backing up and Deleting Archive Log files
it’s important to note that archive log files are not automatically deleted. It’s the responsibility of the system administrator to manage and remove these files periodically to prevent the disk from filling up. Failure to do so can result in critical issues, as the database will crash if it runs out of disk space. Hence, regular maintenance of archive log files is crucial for ensuring the stability and uninterrupted operation of the database system.
The usual way to do this is through RMAN every time we perform the backup.
-- Backup the database and include the archivelogs. RMAN> backup database plus archivelog; Starting backup at 19-FEB-24 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=17 RECID=1 STAMP=1153909616 input archived log thread=1 sequence=18 RECID=2 STAMP=1153909708 input archived log thread=1 sequence=19 RECID=3 STAMP=1154084458 ..... input archived log thread=1 sequence=32 RECID=16 STAMP=1161370201 channel ORA_DISK_1: starting piece 1 at 19-FEB-24 channel ORA_DISK_1: finished piece 1 at 19-FEB-24 piece handle=/u01/app/oracle/product/19/db_1/dbs/0d2ji6ip_1_1 tag=TAG20240219T185001 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 19-FEB-24 Starting backup at 19-FEB-24 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf ....... Finished backup at 19-FEB-24 Starting backup at 19-FEB-24 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=33 RECID=17 STAMP=1161370335 channel ORA_DISK_1: starting piece 1 at 19-FEB-24 channel ORA_DISK_1: finished piece 1 at 19-FEB-24 piece handle=/u01/app/oracle/product/19/db_1/dbs/0h2ji6mv_1_1 tag=TAG20240219T185215 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 19-FEB-24 Starting Control File and SPFILE Autobackup at 19-FEB-24 piece handle=/u01/app/oracle/product/19/db_1/dbs/c-1679667226-20240219-00 comment=NONE Finished Control File and SPFILE Autobackup at 19-FEB-24 -- Configure the deletion policy of ArchiveLog in RMAN RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE DISK; -- Detect that the logs have gone missing and mark their records in the control file as 'expired' RMAN> CROSSCHECK ARCHIVELOG ALL; -- Remove the record of the archivelogs from the control file and adjust the FRA usage RMAN> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL; accordingly -- Delete Archive Log files older than seven days. RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7';
Disabling Archive Log Mode
[oracle@primary ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 19 10:48:54 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 3590322960 bytes Fixed Size 9141008 bytes Variable Size 1476395008 bytes Database Buffers 2097152000 bytes Redo Buffers 7634944 bytes Database mounted. SQL> alter database noarchivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/19/db_1/dbs/arch Oldest online log sequence 26 Current log sequence 28