Pages

Tuesday, December 1, 2020

Increase Oracle memory; Increase /dev/shm; ORA-00838; ORA-00845

====================
General
====================
Increase Oracle memory
Increase /dev/shm 


====================
Try to increase Oracle memory
====================
Start Point:


SQL> 

COL name FOR A30
COL value for A30
SELECT name, value FROM V$PARAMETER WHERE name like 'memory%';
NAME                           VALUE
------------------------------ ------------------------------
memory_target                  1073741824
memory_max_target              1073741824


Oracle has 1 Gb memory,
Linux server has 40Gb memory
SQL> !free -m
     total       used       free     shared    buffers  cached
Mem: 38154       1628      36526        672         31    1040
-/+ buffers/cache:        556      37598
Swap:         9215          0       9215
Let increase MEMORY_MAX_TARGET to 30Gb: 
ALTER SYSTEM SET MEMORY_MAX_TARGET=30000M scope=spfile;

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;

ORA-01078: failure in processing system parameters

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 1408M


SQL> ALTER SYSTEM SET MEMORY_TARGET=3000M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=3000M scope=spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 866 Serial number: 23

SQL> create pfile='/software/oracle/admin/igt/pfile/pfile_20201201_initigt.ora' from spfile='/software/oracle/admin/igt/pfile/spfileigt.ora';

File created.

Edit manually file pfile.
set MEMORY_TARGET to be 2000M
*.memory_target=2147483648


SQL> startup pfile='/software/oracle/admin/igt/pfile/pfile_20201201_initigt.ora'

ORA-00845: MEMORY_TARGET not supported on this system


Check Alert Log for more details:

Tue Dec 01 09:35:05 2020
Cannot set memory_target (1073741824) < min sga_target (1476395008) + pga_aggregate_target (0)
Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature. 
This feature requires the /dev/shm file system to be mounted for at least 31474057216 bytes. 
/dev/shm is either not mounted or is mounted with available space less than this size. 
Please fix this so that MEMORY_TARGET can work as expected. 
Current available is 4294967296 and used is 0 bytes. 
Ensure that the mount point is /dev/shm for this directory.
memory_target needs larger /dev/shm


Checking the Linux server

SQL> !df -h /dev/shm
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           4.0G     0  4.0G   0% /dev/shm

/dev/shm cannot support memory more than 4G memory
Need to change max_memory_target and memory_target to 2Gb for now

*.memory_max_target=2000M
*.memory_target=2000M

SQL> startup pfile='/software/oracle/admin/igt/pfile/pfile_20201201_initigt.ora'

ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size                  2254824 bytes
Variable Size            1577060376 bytes
Database Buffers          503316480 bytes
Redo Buffers                5148672 bytes
Database mounted.
Database opened.

Re-Create spfile from pfile

SQL> create spfile='/software/oracle/admin/igt/pfile/spfileigt.ora' from pfile='/software/oracle/admin/igt/pfile/pfile_20201201_initigt.ora';

File created.

Increase /dev/shm from current 4Gb to 30Gb, out of server total 40Gb

oracle@avilab-1-aps-01:~>% df -hP
Filesystem                     Size  Used Avail Use% Mounted on
tmpfs                          4.0G  331M  3.7G   9% /dev/shm

Step 1: 
vi /etc/fstab

Step 2: 

Change size for /dev/shm to 30G
change from :

tmpfs                   /dev/shm                tmpfs   size=4g,nodev,nosuid,noexec             0 0

to: 

tmpfs                   /dev/shm                tmpfs   size=30g,nodev,nosuid,noexec             0 0


stop oracle!!

Step 3: 
Remount 
/dev/shm filesystem

mount -o remount /dev/shm

Step 4: 
Check status:

df -hP | grep shm

Filesystem                     Size  Used Avail Use% Mounted on
tmpfs                           30G  700M   30G   3% /dev/shm

start oracle

Back to Oracle:
--8192M
ALTER SYSTEM SET MEMORY_MAX_TARGET=8192M scope=spfile;
ALTER SYSTEM SET MEMORY_TARGET=8192M scope=spfile;


SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET=4000M scope=spfile;
System altered.
SQL> ALTER SYSTEM SET MEMORY_TARGET=4000M scope=spfile;
System altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area 3.1334E+10 bytes

Fixed Size                  2268632 bytes

Variable Size            3.0803E+10 bytes

Database Buffers          469762048 bytes

Redo Buffers               58535936 bytes

Database mounted.

Database opened.


>% df -hP
Filesystem                     Size  Used Avail Use% Mounted on
tmpfs                           30G  1.2G   29G   4% /dev/shm


No comments:

Post a Comment