Enable Archive Mode and Archive Log Destination Change During Database Upgrade

During the Oracle Database upgrade for the Oracle EBS 12.2.x environment, the archive log destination was relocated to a new filesystem to ensure adequate storage capacity for the increased archive log generation that occurs during upgrade activities. This preventive measure helps avoid archive destination space issues, which could interrupt the upgrade process and potentially cause database downtime.

During an Oracle Database upgrade (especially for Oracle EBS 12.2.x upgrades), it is recommended to:

1. Enable ARCHIVELOG mode (if not already enabled).
2. Configure a dedicated archive log destination with sufficient free space.
3. Prevent upgrade failures caused by archive destination space exhaustion.
4. Facilitate database recovery in case of upgrade rollback requirements.

Pre-Upgrade Validation

• Verified current archive log configuration.
• Checked available filesystem space for existing archive destinations.
• Confirmed archive log generation rate during upgrade windows.
• Created and validated the new archive log directory with appropriate ownership and permissions.

Why This Step Is Required

Database upgrade operations generate a large volume of redo logs due to:

• Data dictionary upgrades
• Invalid object recompilations
• AD/TXK patches
• Statistics gathering
• Online patching synchronization activities

Implementation

Create New Archive Log Directory

[root@proderpdb ~]# mkdir -p /oradata/prod/db/data/archive
[root@proderpdb ~]# chown oracle:dba /oradata/prod/db/data/archive
[root@proderpdb ~]# chmod 775 /oradata/prod/db/data/archive

Reviewed the existing archive log destination:

[oracle@proderpdb ~]$ . PRODCDB_proderpdb.env

[oracle@proderpdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 18 13:53:15 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
SQL>

SQL> show parameter recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 20G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oradata/prod/app/oracle/product/19.3.0/dbs/arch
Oldest online log sequence     9
Current log sequence           11

SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down.
Database closed.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size                 18662888 bytes
Variable Size            1946157056 bytes
Database Buffers         1.4126E+10 bytes
Redo Buffers               14888960 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;
Database altered.

SQL> alter system set log_archive_dest_1= 'LOCATION=/oradata/prod/db/data/archive' scope=both;
System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/prod/db/data/archive
Oldest online log sequence     9
Next log sequence to archive   11
Current log sequence           11

SQL> alter database open;

Database altered.

SQL> select destination,STATUS from v$archive_dest where status='VALID';

DESTINATION
--------------------------------------------------------------------------------
STATUS
---------
/oradata/prod/db/data/archive
VALID

SQL> alter system switch logfile;
System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/prod/db/data/archive
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12
SQL>