ITPub博客

首页 > 数据库 > Oracle > ORA-00845,ORA-04031

ORA-00845,ORA-04031

原创 Oracle 作者:chenoracle 时间:2020-04-06 11:49:53 0 删除 编辑

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",共同学习,共同成长!!!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2684625/,如需转载,请注明出处,否则将追究法律责任。

全部评论
Oracle 11g OCP、Oracle 11g OCM、OCMU 用户组成员,微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    537
  • 访问量
    981609