Oracle 04031问题解决

背景

同事描述，说数据库orcl 实例用一段时间就会无法连接。重启一次后就可以用，大概30分钟到一个小时会再次无法使用。

问题发现及排查思路

oracle 问题首先查看alert.log 日志

1. 查看alert.log 日志，

Sat Sep 29 10:45:37 2018
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_q000_6756.trc:
ORA-22303: type "SYS"."AQ$_HISTORY" not found
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","unknown object","sga heap(3,0)","kglsim heap")

2. 查看 orcl_6756.trc 跟踪文件，可以看到SGA: allocation forcing component growth等待事件，可以确认的是由于SGA无法增长导致，也就是SGA被撑爆了,那么增大SGA来看看情况如何

SO: 000007FF811C3948, type: 4, owner: 000007FF81008B20, flag: INIT/-/-/0x00
  (session) sid: 224 trans: 0000000000000000, creator: 000007FF81008B20, flag: (51) USR/- BSY/-/-/-/-/-
            DID: 0001-0035-0000EC0D, short-term DID: 0000-0000-00000000
            txn branch: 0000000000000000
            oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000000000000, user: 0/SYS
  last wait for 'SGA: allocation forcing component growth' blocking sess=0x0000000000000000 seq=2 wait_time=12222 seconds since wait started=0
          =0, =0, =0
  Dumping Session Wait History
   for 'SGA: allocation forcing component growth' count=1 wait_time=12222
          =0, =0, =0

3. 制作awr 报告

进入Oracle安装目录，由于这里是window系统，直接cd 进入，这里的Oracle Home目录是
D:\oracle\product\10.2.0\db_1
执行以下命令

d:
cd D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN
sqlplus / as sysdba
@awrrpt

然后一直下一步，出现如下错误，提示无法分配32字节共享内存

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


输入 num_days 的值:  1
select    'Listing '
*
第 1 行出现错误:
ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select    'Listing '
...","sql area","kglhin: temp")


     , dba_hist_database_instance di
       *
第 8 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select
obj#,type#,ctime,mtim...","sql area","kglhin: temp")


ERROR:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select /*+ index(idl_ub2$
i_...","sql area","kglhin: temp")

设置SGA，并重启数据库，通过增大SGA内存为5G后，会发现数据库暂时确实没有重启，但是具体原因还得查找

alter system set sga_target=5G scope=spfile sid='orcl';
shutdown immediate;
desc v$database;