background

Colleagues described that the database orcl instance will be unable to connect in a period of time. It can be used after one restart. It will be unavailable again in about 30 minutes to an hour.

Problem finding and troubleshooting ideas

For oracle problems, first check the alert.log log

1. View the alert.log log 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")

1. Looking at the orcl_.trc trace file, you can see the SGA: allocation forcing component growth wait event. It can be confirmed that the SGA cannot grow, that is, the SGA is supported. Increase the SGA to see how it works

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

1. Make an awr Report

Enter the Oracle installation directory. Because this is a window system, enter it directly by cd. The Oracle Home directory here is
D:\oracle\product\10.2.0\db_1
Execute the following command

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

Then continue to the next step. The following error appears, indicating that 32 bytes of shared memory cannot be allocated

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.


//Enter the value of num ﹣ days: 1
select    'Listing '
*
//Error on line 1:
ORA-04031: Unable to allocate 32 bytes of shared memory ("shared pool","select    'Listing '
...","sql area","kglhin: temp")


     , dba_hist_database_instance di
       *
//Error on line 8:
ORA-00604: recursion SQL Error at level 1
ORA-04031: Unable to allocate 32 bytes of shared memory ("shared pool","select
obj#,type#,ctime,mtim...","sql area","kglhin: temp")


ERROR:
ORA-00604: recursion SQL Error at level 1
ORA-04031: Unable to allocate 32 bytes of shared memory ("shared pool","select /*+ index(idl_ub2$
i_...","sql area","kglhin: temp")

Set SGA and restart the database. After increasing the memory of SGA to 5G, it will be found that the database does not restart temporarily, but the specific reason has to be found

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