Database users cannot log on to alert Log reporting ORA-04031 troubleshooting

Database users cannot log on to alert Log reporting ORA-04031 troubleshooting

Database users cannot log on to alert Log reporting ORA-04031 troubleshooting

Symptom:
1. SQL plus/as sysdba cannot access the database host.

2. the alert Log reports the following errors:
Errors in file/Oracle/admin/dbrac/bdump/dbrac2_q000_1329.trc:
ORA-22303: type "SYS". "AQ $ _ HISTORY" not found
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool", "select user #, type # from user...", "SQL area", "tmp ")

The reason is as follows:
1. You cannot log on because you cannot allocate memory in the share pool.
2. The reason why the share pool cannot allocate memory is that the database uses a large number of constant SQL statements (without binding variable SQL statements) for hard parsing.

In this case, SQL plus/as sysdba cannot access the database. What should I do?

Solution:
1. Based on the Principle of Rapid database recovery, first kill the pmon process and re-pull the database.

2. To reduce the probability of a fault, the method is as follows:
Add an automatic share pool plan for database flushing to the automatic plan and click it once every night. The impact of this operation on the business can be ignored. The SQL statement for flushing the share pool is as follows:
Alter system flush shared_pool;

3. To completely solve this problem, the application needs to modify all constant SQL statements (without binding variable SQL) to Bind Variable SQL statements.

-------------------------------------- Split line --------------------------------------

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

-------------------------------------- Split line --------------------------------------