ORA-00845,ORA-04031

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]$ cat  initcjcdb.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 欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！