Naming and behaviour of memory parameters are confusing

[andbos]

Hi,

I think the naming and behaviour of memory parameters are confusing.

The comment in the YAML example (https://github.com/oracle/oracle-database-operator/blob/main/config/samples/sidb/singleinstancedatabase.yaml) says:

## Specify both sgaSize and pgaSize (in MB) or dont specify both

However, the initParams are not called sga/pgaSize but sgaTarget/pgaAggregateTarget. Example configuration from last tests:

  initParams:
    cpuCount: 0
    processes: 0
    sgaTarget: 4096
    pgaAggregateTarget: 4096

With above YAML configuration I got the following parameters settings after the instance had been started:

pga_aggregate_limit                  big integer 8G
pga_aggregate_target                 big integer 4G
sga_max_size                         big integer 4G
sga_min_size                         big integer 0
sga_target                           big integer 0

Why is sga_target set to 0 and sga_max_size set to initParams.sgaTarget? This seems to happen only at initial installation because if I edit initParams.sgaTarget in the YAML file and apply it then:

        {"updateInitParameters": {"name":"sinchdb11","namespace":"oracle-database"}}
2024-05-24T11:20:28Z    DEBUG   events  Unable to change the init-param as specified. Error log:
alter system set sga_target=5120M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size

If I try to change sga_max_size via SQLplus I get ORA-02095 error:

SQL> alter system set sga_max_size=5120M scope=both;
alter system set sga_max_size=5120M scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Best regards,
Andreas

[IshaanDesai45]

@andbos and you check init params for both the CDB and PDB . I think this is expected we might need to change the names of the initParams in the yaml if its causing a confusion

[andbos]

Hi,

There is a slight difference between CDB and PDB. With the following settings in the YAML at installation:

initParams:
  cpuCount: 0
  processes: 0
  sgaTarget: 5120
  pgaAggregateTarget: 4096

The database will have the following configuration:

CDB

pga_aggregate_limit                  big integer 8G
pga_aggregate_target                 big integer 4G
sga_max_size                         big integer 5G
sga_min_size                         big integer 0
sga_target                           big integer 5G

PDB

pga_aggregate_limit                  big integer 8G
pga_aggregate_target                 big integer 4G
sga_max_size                         big integer 5G
sga_min_size                         big integer 0
sga_target                           big integer 0

And it is possible to increase sga_max_size of the CDB:

SQL> alter system set sga_max_size=6144M scope=spfile;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
SQL>  show parameter sga_max_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 6G
SQL>

If I login to the PDB then I can increase sga_target manually.

SQL> alter system set sga_target=2048M scope=both;
System altered.
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 2G

So if you exclude the comment ## Specify both sgaSize and pgaSize (in MB) or dont specify both then the configuration makes sense, it is just not made very clear that sga_target is only set for CDB and that sga_target = sga_max_size. Also, it is possible to change sga_target by editing the YAML and apply it but only for the CDB.

Best regards,
Andreas

[IshaanDesai45]

@andbos the above PR have to updated comments aimed to eliminate any confusion. kindly check

[andbos]

Hi,

Thanks. Just one typo: "pagAggregateTarget".

Best regards,
Andreas

[IshaanDesai45]

Hi,

Thanks. Just one typo: "pagAggregateTarget".

Best regards, Andreas

corrected the typo and closing the issue