[26ai] ALERT_LOG_MAX_SIZE

If you prefer to read in Spanish Spanish version.

Today, let's talk about a new parameter in 26ai in order to handle the size our alert log in a straightforward and efficient way. It's alert_log_max_size parameter.

The default value is 1000Mb:

SQL>
SQL> show parameter alert_log_max_size

NAME               TYPE        VALUE
------------------ ----------- -------
alert_log_max_size big integer 1000M

SQL>

How does Oracle handle it? Oracle splits the alert log in segments of 50Mb. In other words, we'll have 20 files of 50Mb each.

The parameter can only be modified at the CDB$Root, not at the PDB:

SQL> ALTER SYSTEM SET ALERT_LOG_MAX_SIZE = 150M; 

ALTER SYSTEM SET ALERT_LOG_MAX_SIZE = 150M 
* 
ERROR at line 1: ORA-65040: Operation is not allowed from within a pluggable database.

Let's write some information into the alert log in order to see how Oracle works:

[oracle@~]cat save_inf.sql
begin 
declare 
 i number := 0; 
 begin 
  loop i := i + 1; 
  sys.dbms_system.ksdwrt(2, 'string(''L'',?)= ' || dbms_random.string('L',trunc(dbms_random.value(10,21)))); 
 exit when i >= 10000000; 
end loop; 
end; 
end; 
/
[oracle@~] 
 watch -n 10 "sqlplus -s / as sysdba @save_inf.sql"
[oracle@l23ai alert]$ ls -lash *xml 
51M -rw-r----- 1 oracle asmdba 51M Apr 2 18:11 log_1.xml 
51M -rw-r----- 1 oracle asmdba 51M Apr 2 18:11 log_2.xml 
46M -rw-r----- 1 oracle asmdba 46M Apr 2 18:11 log.xml

As we can see, our alert log has been split in files of 50Mb each!.

A quick note about the new parameter, if we set it to zero value, then, the alert log can grown indefinitely.

But, can we modify the value of 50Mb? Of course. There is a hidden parameter, it's "_alert_segment_size" that allows us to change that value.

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

SQL> show parameter "_alert_segment_size"

NAME               TYPE        VALUE
------------------ ----------- -------
_alert_segment_size integer     30

[oracle@l23ai ~]$ 
 watch -n 10 "sqlplus -s / as sysdba @save_inf.sql"
[oracle@l23ai alert]$ ls -lash *xml 
51M -rw-r----- 1 oracle asmdba 51M Apr 2 18:11 log_1.xml 
51M -rw-r----- 1 oracle asmdba 51M Apr 2 18:11 log_2.xml 
46M -rw-r----- 1 oracle asmdba 46M Apr 2 22:10 log_3.xml 
31M -rw-r----- 1 oracle asmdba 31M Apr 3 09:11 log_4.xml 
31M -rw-r----- 1 oracle asmdba 31M Apr 3 09:11 log_5.xml 
4.9M -rw-r----- 1 oracle asmdba 4.9M Apr 3 09:11 log.xml

Looking forward to seeing you in the next article :)