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):
- If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
- If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
- 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.
- 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).
- 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_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