ORA-00845,ORA-04031
环境说明：
DB:Oracle 19.3.0.0.0
OS:Red Hat Enterprise Linux Server release 7.5 (Maipo)
问题：
将主机内存由3g降低到2g后，启动数据库报错如下：
ORA-00845: MEMORY_TARGET not supported on this system
调整memory_max_target值，使其小于/dev/shm值，启动pdb数据库报错如下：
ORA-04031: unable to allocate 1048848 bytes of shared memory
("shared pool","unknown object","PDB Dynamic He","alls-ktimcem")
问题原因：
在oracle database 11g中新增的内存自动管理AMM的参数MEMORY_TARGET，它能自动调整SGA和PGA，
这个特性需要用到/dev/shm共享文件系统，而且要求/dev/shm必须大于MEMORY_TARGET，
如果/dev/shm比MEMORY_TARGET小就会报错ORA-00845。
当主机内存调小后，系统/dev/shm也会自动变小，
但memory_max_target参数值在安装完数据库后不会自动变化，
导致主机内存缩小后memory_max_target参数值大于了/dev/shm，触发了ORA-00845问题。
解决方案：
一 调小memory_max_target和MEMORY_TARGET值，使其小于/dev/shm。
可以启动CDB$ROOT数据库了，但是在启动pdb时会报错ORA-04031，shared pool分配内存不足了。
二 适当调大/dev/shm，同时在适当调大memory_max_target和MEMORY_TARGET值。
过程如下：
调小主机内存
[oracle@cjcos01 ~]$ free -m
total used free shared buff/cache available
Mem: 1741 340 1072 9 328 1247
Swap: 3071 0 3071
启动数据库
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
错误描述
[oracle@cjcos01 dbs]$ oerr ora 0845
00845, 00000, "MEMORY_TARGET not supported on this system"
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.
查看当前/dev/shm大小
[root@cjcos01 ~]# df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
tmpfs 871M 0 871M 0% /dev/shm
调小memory_max_target和memory_target值
SQL> create pfile from spfile;
[oracle@cjcos01 ~]$ cd $ORACLE_HOME/dbs
[oracle@cjcos01 dbs]$ cp initcjcdb.ora initcjcdb.ora.bak
[oracle@cjcos01 dbs]$ catinitcjcdb.ora
*.memory_max_target=1287436800
*.memory_target=1287436800
[oracle@cjcos01 dbs]$ vim initcjcdb.ora
*.memory_max_target=887436800
*.memory_target=887436800
启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 788526632 bytes
Fixed Size 9139752 bytes
Variable Size 419430400 bytes
Database Buffers 41943040 bytes
Redo Buffers 3440640 bytes
In-Memory Area 314572800 bytes
Database mounted.
Database opened.
启动pdb
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 CJCPDB MOUNTED
SQL> alter session set container=cjcpdb;
Session altered.
报错ORA-04031
SQL> startup
ORA-04031: unable to allocate 1048848 bytes of shared memory ("shared pool","unknown object","PDB Dynamic He","alls-ktimcem")
错误描述
[oracle@cjcos01 dbs]$ oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory ("%s","%s","%s","%s")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.查看错误日志
[oracle@cjcos01 dbs]$ cd /u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace/
[oracle@cjcos01 trace]$ vim alert_cjcdb.log
......
2020-04-05T19:45:00.505509+08:00
Errors in file /u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace/cjcdb_ora_3286.trc (incident=89073) (PDBNAME=CJCPDB):
ORA-04031: unable to allocate 1048848 bytes of shared memory ("shared pool","unknown object","PDB Dynamic He","alls-ktimcem")
CJCPDB(3):Incident details in: /u01/app/oracle/diag/rdbms/cjcdb/cjcdb/incident/incdir_89073/cjcdb_ora_3286_i89073.trc显示waited for 'SGA: allocation forcing component growth'
[oracle@cjcos01 trace]$ vim /u01/app/oracle/diag/rdbms/cjcdb/cjcdb/incident/incdir_89073/cjcdb_ora_3286_i89073.trc
.....
Session Wait History:
elapsed time of 1.282890 sec since last wait
0: waited for 'SGA: allocation forcing component growth'
=0x0, =0x0, =0x0
wait_id=2568 seq_num=2620 snap_id=26
wait times: snap=0.000000 sec, exc=2.498875 sec, total=2.502222 sec
wait times: max=infinite
wait counts: calls=25 os=25
occurred after 0.000000 sec of elapsed time
1: waited for 'SGA: allocation forcing component growth'
=0x0, =0x0, =0x0
wait_id=2593 seq_num=2619 snap_id=1
wait times: snap=0.000062 sec, exc=0.000062 sec, total=0.000062 sec
wait times: max=infinite
wait counts: calls=1 os=1
occurred after 0.000000 sec of elapsed time
解决方案
1 调大/dev/shm
[root@cjcos01 ~]# df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
tmpfs 871M 0 871M 0% /dev/shm
[root@cjcos01 ~]# mount -o remount,size=1G /dev/shm
[root@cjcos01 ~]# df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
tmpfs 1.0G 0 1.0G 0% /dev/shm
2 调整/etc/fstab
[root@cjcos01 ~]# vim /etc/fstab
...
tmpfs /dev/shm tmpfs defaults,size=1g 0 0
3调大memory_max_target
[oracle@cjcos01 dbs]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@cjcos01 dbs]$ vim initcjcdb.ora
*.memory_max_target=1007436800
*.memory_target=1007436800
4 启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 1010823200 bytes
Fixed Size 9142304 bytes
Variable Size 641728512 bytes
Database Buffers 41943040 bytes
Redo Buffers 3436544 bytes
In-Memory Area 314572800 bytes
Database mounted.
Database opened.
5 启动pdb
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 CJCPDB MOUNTED
SQL> alter session set container=cjcpdb;
Session altered.
SQL> startup
Pluggable Database opened.
SQL> conn / as sysdba
Connected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 CJCPDB READ WRITE NO
作者：chenoracle
