Managing Alert Log Size in Oracle AI Database 26ai

Starting with Oracle AI Database 26ai (Release Update 23.9), it is possible to control the maximum size of the alert log using the ALERT_LOG_MAX_SIZE parameter. The default value for this parameter is 1000 MB, which means the XML alert log can grow up to a maximum of 20 segments, each 50 MB in size.

In my test, I configured the alert log maximum size to 100 MB:

SQL> ALTER SYSTEM SET alert_log_max_size = 100M;
System altered.

***This parameter is modifiable using ALTER SYSTEM, but it is not modifiable at the PDB level.

To increase the size of the alert log and force file generation, I executed the following PL/SQL block to continuously write messages into the alert log:

begin
  for i in 1 .. 1000000 loop
    begin
      sys.dbms_system.ksdwrt(2, 'Vahid Yousefzadeh');
    end;
  end loop;
end;
/

After running this block, there are several XML files in the alert directory and each file size is approximately 50MB.

[root@OEL9-DB2 alert]# ls -lh
-rw-r----- 1 oracle oinstall 51M Feb 23 11:57 log_20.xml
-rw-r----- 1 oracle oinstall 51M Feb 23 11:57 log_21.xml
-rw-r----- 1 oracle oinstall 16M Feb 23 11:57 log.xml

In addition, I checked the trace directory and observed multiple alert text log files:

[root@OEL9-DB2 trace]# ls -lh alert*.log
-rw-r----- 1 oracle oinstall 4.1M Feb 23 11:57 alert_db26ai_20.log
-rw-r----- 1 oracle oinstall 4.1M Feb 23 11:57 alert_db26ai_21.log
-rw-r----- 1 oracle oinstall 1.3M Feb 23 11:57 alert_db26ai.log

This behavior indicates that the size and number of alert text log files are driven by the XML log files.

Controlling XML Segment Size

There is also a hidden parameter called: _alert_segment_size

This parameter allows us to control the size of each XML alert log segment.

SQL> ALTER SYSTEM SET "_alert_segment_size" = 65;
System altered.

SQL> ALTER SYSTEM SET alert_log_max_size = 100M;
System altered.

After generating alert activity again, the results were:

Alert (XML) directory:

[root@OEL9-DB2 alert]# ll
-rw-r----- 1 oracle oinstall 68157489 Feb 23 12:05 log_25.xml
-rw-r----- 1 oracle oinstall 68157489 Feb 23 12:05 log_26.xml
-rw-r----- 1 oracle oinstall  2656020 Feb 23 12:05 log.xml

Trace directory:

[root@OEL9-DB2 trace]# ls -lh alert*.log
-rw-r----- 1 oracle oinstall 5.3M Feb 23 12:05 alert_db26ai_25.log
-rw-r----- 1 oracle oinstall 5.3M Feb 23 12:05 alert_db26ai_26.log
-rw-r----- 1 oracle oinstall 211K Feb 23 12:05 alert_db26ai.log

As shown above, each XML segment is now approximately 66 MB, which reflects the new _alert_segment_size value.

Disabling the Alert Log Size Limit

If we want to return to unlimited alert log growth, we can set the parameter to 0:

SQL> ALTER SYSTEM SET alert_log_max_size = 0;
System altered.