Lesson 5	Automatic archive log options
Objective	Set up automatic archiving of the redo log files in Oracle 23ai

Automatic archiving options

Manually archiving redo is primarily a validation or troubleshooting task. In Oracle 23ai, the normal operational baseline is ARCHIVELOG mode with automatic archiving enabled so that filled online redo logs are copied to archived redo logs without DBA intervention.

Automatic archiving is essential for recoverability: it enables point-in-time recovery and supports online backups (for example, RMAN backups taken while the database remains open). In NOARCHIVELOG mode, redo logs are overwritten after log switches, which limits recovery to the last offline backup.

How automatic archiving works in Oracle 23ai

When the database is in ARCHIVELOG mode, Oracle uses background archiver processes (ARCn) to copy redo from full online redo log groups to one or more archive destinations. The archivers run automatically as long as archiving is enabled and at least one valid destination is available.

Redo source: online redo log groups (multiplexed members).
Archive output: archived redo logs written to configured destinations (filesystem, ASM, or the Fast Recovery Area).
Control: archive destinations are configured with initialization parameters; monitoring is done via dynamic performance views.

Step-by-step: enable automatic archiving (SQL*Plus)

Step 1: Connect as SYSDBA

sqlplus / as sysdba

Step 2: Check the current log mode

archive log list;

select log_mode from v$database;

Step 3: If needed, enable ARCHIVELOG mode (requires downtime)

Changing from NOARCHIVELOG to ARCHIVELOG requires restarting the database into MOUNT state.

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

archive log list;

Step 4: Configure archive destinations (recommended: Fast Recovery Area)

Oracle 23ai installations commonly use the Fast Recovery Area (FRA) so Oracle can manage archived logs and other recovery files centrally. If you use FRA, set DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE.

show parameter db_recovery_file_dest;
show parameter db_recovery_file_dest_size;

-- Example (adjust paths/sizes for your environment)
alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;
alter system set db_recovery_file_dest_size=50G scope=both;

If you prefer explicit archive destinations instead of (or in addition to) FRA, configure LOG_ARCHIVE_DEST_n. Use valid destinations and consider multiple destinations for resilience.

show parameter log_archive_dest_1;
show parameter log_archive_dest_2;

-- Example filesystem destination (adjust path)
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch' scope=both;

Step 5: Validate destination status and errors

select dest_id, status, destination, error
from   v$archive_dest
order  by dest_id;

Step 6: Force a log switch and confirm logs are archiving

This validates the end-to-end configuration: redo is generated, a log switch occurs, and ARCn produces an archived redo log.

alter system switch logfile;
alter system archive log current;

select thread#, sequence#, first_time, archived, name
from   v$archived_log
order  by first_time desc
fetch first 10 rows only;

Disabling automatic archiving (use caution)

In modern Oracle releases, you do not “stop ARCH” using legacy Server Manager commands. If you disable archiving while still in ARCHIVELOG mode, the database continues to require archived redo for recoverability, and redo can accumulate until destinations fill, causing operational failures.

If a database truly should not archive redo (typically only for non-production or special-purpose environments), the correct approach is to switch the database back to NOARCHIVELOG mode during a maintenance window:

shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;

archive log list;

Operational note: production databases should generally remain in ARCHIVELOG mode with automatic archiving enabled, supported by monitoring, capacity management, and a backup strategy (commonly RMAN).

Monitoring and best practices

Monitor space: ensure archive destinations (or FRA) have capacity and alerts configured.
Multiple destinations: consider more than one archive destination for resilience (where appropriate).
Backups: pair archiving with a consistent backup strategy (commonly RMAN) to meet recovery objectives.
Avoid legacy parameters: do not use LOG_ARCHIVE_START or init.ora-only guidance for modern environments.