ORA-00093: pga_aggregate_limit must be between 2048M and 100000G

Error:

SQL>  startup pfile=initmylab1.ora

ORA-00093: pga_aggregate_limit must be between 2048M and 100000G

ORA-01078: failure in processing system parameters

Above error occurs while setting PGA_AGGREGATE_LIMIT to value less than 2GB in oracle 12c onwards version database instances ..


Memory Utilization status at the time of error :

[oracle@localhost trace]$ free -g

              total        used        free      shared  buff/cache   available
Mem:              1           0           0           0           1           0
Swap:            21           0          20

[oracle@localhost trace]$ free -m
              total        used        free      shared  buff/cache   available
Mem:           1727         563          62         877        1100         144
Swap:         22091         601       21489

Cause:

PGA memory setting theory says (From 12c’s onwards):

  1. If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
  2. If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
  3. If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
  4. In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB (when AMM enabled) and at least 3MB times the PROCESSES parameter (and at least 5MB times the PROCESSES parameter for an Oracle RAC instance).
  5. For a PDB, the default value is the same as the CDB’s default value.
So in this case Host memory is too less to accommodate PGA_AGGREGATE_LIMIT value of 2GB which need to be reduced along with avoiding error ORA-00093.

We will consider 3rd & 4th theory to fix the issue ..

Solution:

To fix the error we need to remove the memory_target parameters which will disable Automatic Memory Management (AMM) feature which dynamicaly allocates the PGA & SGA.


Problematic parameter setting of spfile leading to ORA-00093 -->


*.memory_max_target=800m
*.memory_target=750m

*.pga_aggregate_limit=2G
*.pga_aggregate_target=100m
*.processes=100
*.sga_max_size=0
*.sga_target=0


Correct the parameter setting in pfile to fix  ORA-00093 -->


*.sga_max_size=600m
*.sga_target=550m
*.pga_aggregate_limit=0
*.pga_aggregate_target=100m
*.processes=100


Follow the steps to correct parameter setting -->


SQL> startup pfile=initmylab1.ora

ORACLE instance started.
Total System Global Area  629144952 bytes
Fixed Size                  9137528 bytes
Variable Size             218103808 bytes
Database Buffers          398458880 bytes
Redo Buffers                3444736 bytes
Database mounted.
Database opened.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB_MYLAB                      MOUNTED

SQL> alter pluggable database PDB_MYLAB open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB_MYLAB                      READ WRITE NO

--> Save state to open PDB in read write mode on next restart 

SQL> alter pluggable database PDB_MYLAB save state;

Pluggable database altered.

SQL> create spfile from pfile='initmylab1.ora';

File created.

--> Edit the parameter file with correct parameters given above 

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 600M
sga_min_size                         big integer 0
sga_target                           big integer 552M
unified_audit_sga_queue_size         integer     1048576

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 0
pga_aggregate_target                 big integer 100M
 
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup
ORACLE instance started.


--> Alert log snippet
 
Using parameter settings in server-side spfile +DATA/spfilemylab.ora
System parameters with non-default values:
  processes                = 100
  sga_max_size             = 600M
  pga_aggregate_limit      = 0
  nls_language             = "AMERICAN"
  nls_territory            = "AMERICA"
  sga_target               = 552M
  control_files            = "+DATA/MYLAB/CONTROLFILE/current.261.1090291159"
  db_block_size            = 8192
  compatible               = "19.0.0"
  db_create_file_dest      = "+DATA"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=mylabXDB)"
  local_listener           = "LISTENER_MYLAB"
  audit_file_dest          = "/u01/app/oracle/admin/mylab/adump"
  audit_trail              = "DB"
  db_name                  = "mylab"
  open_cursors             = 100
  pga_aggregate_target     = 100M
  diagnostic_dest          = "/u01/app/oracle"
  enable_pluggable_database= TRUE
 

SQL> startup
ORACLE instance started.
Total System Global Area  629144952 bytes
Fixed Size                  9137528 bytes
Variable Size             218103808 bytes
Database Buffers          398458880 bytes
Redo Buffers                3444736 bytes
Database mounted.
Database opened.



--> Alert log snippet

QPI: qopiprep.bat file present
2021-12-19T03:57:54.948532-05:00
PDB_MYLAB(3):Undo initialization recovery: err:0 start: 2615926 end: 2615991 diff: 65 ms (0.1 seconds)
2021-12-19T03:57:57.142196-05:00
PDB_MYLAB(3):[6892] Successfully onlined Undo Tablespace 2.
PDB_MYLAB(3):Undo initialization online undo segments: err:0 start: 2615991 end: 2618185 diff: 2194 ms (2.2 seconds)
PDB_MYLAB(3):Undo initialization finished serial:0 start:2615926 end:2618245 diff:2319 ms (2.3 seconds)
PDB_MYLAB(3):Database Characterset for PDB_MYLAB is AL32UTF8
2021-12-19T03:58:11.818276-05:00
PDB_MYLAB(3):Opening pdb with no Resource Manager plan active
2021-12-19T03:58:14.339240-05:00
PDB_MYLAB(3):joxcsys_required_dirobj_exists: directory object exists with required path /u01/app/oracle/19c/javavm/admin/, pid 6892 cid 3
2021-12-19T03:58:15.398578-05:00
Pluggable database PDB_MYLAB opened read write


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB_MYLAB                      READ WRITE NO

Memory Utilization status after fixing the error ORA-00093 :

[oracle@localhost dbs]$ free -h
               total        used        free      shared  buff/cache   available
Mem:           1.7G        580M        195M        534M        951M        470M
Swap:           21G        905M         20G

[oracle@localhost dbs]$ free -m
              total        used        free      shared  buff/cache   available
Mem:           1727         580         190         534         956         470
Swap:         22091         905       21186



No comments:

Post a Comment