Shulou(Shulou.com)06/01 Report--

Article reference: ORACLE 11g official document [Managing Memory]

After setting up the database cluster environment before, the maintenance staff on the site did not know when to change to manual memory management, and the parameter setting was unreasonable, which caused the customer plsql to execute an error, as shown below:

Check the database log and have the following hints:

Wed Mar 15 00:15:08 2017

Errors in file / app/oracle/diag/rdbms/resdb/resdb1/trace/resdb1_ora_12320832.trc (incident=441054):

ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool", "unknown object", "streams pool", "fixed allocation"

Callback ")

Incident details in: / app/oracle/diag/rdbms/resdb/resdb1/incident/incdir_441054/resdb1_ora_12320832_i441054.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Wed Mar 15 16:11:30 2017

Errors in file / app/oracle/diag/rdbms/resdb/resdb1/trace/resdb1_ora_8126806.trc (incident=440838):

ORA-04031: "shared pool", "SELECT TRIGGER_NAME, TRIGGER...", "SQLA", "tmp")

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file / app/oracle/diag/rdbms/resdb/resdb1/trace/resdb1_m000_10879616.trc (incident=442639):

ORA-04031: unable to allocate 536 bytes of shared memory ("shared pool", "select / * + rule * / bucket, e...", "SQL ^ bbcee4f7"

, "qerixs: rixalo")

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Wed Mar 15 16:11:31 2017

Dumping diagnostic data in directory= [CDMP _ 20170315161131], requested by (instance=1, osid=10879616 (M000)), summary= [inc

Ident=442638].

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Wed Mar 15 16:11:32 2017

Sweep [inc] [442639]: completed

Check the database memory parameter settings, not automatic memory management, set automatic memory management mode processing.

Problem handling process

Memory management: automatic memory management, manual memory management

Automatic memory management: automatic memory management of PGA and SGA is supported after oracle 11g. You only need to set the total memory size of the instance, and automatically allocate PGA and SGA size as needed.

Manual memory management: if you want to manage memory settings manually, this requires DBA to set it appropriately according to the needs of the environment.

Automatic memory management settings: just modify the initialization parameter setting MEMORY_TARGET and the optional parameter setting MEMORY_MAX_TARGET

Set up automatic memory management steps:

1. Log in to the database as a user with sysdba privileges

View the parameter settings for the current database SGA_TARGET and PGA_AGGREGATE_TARGET

SQL > show parameter sga_target

NAME TYPE VALUE

-

Sga_target big integer 0

SQL > show parameter pga_aggregate_target

NAME TYPE VALUE

-

Pga_aggregate_target big integer 160000M

Execute the following SQL query statement to confirm the maximum PGA allocated when the instance is started

SQL > select value from v$pgastat where name='maximum PGA allocated'

VALUE

-

4620300288

The parameter values are calculated according to the following formula:

Memory_target = sga_target + max (pga_aggregate_target, maximum PGA allocated)

two。 Initialization parameter MEMORY_MAX_TARGET

First check the MEMORY_MAX_TARGET parameters in the current database

SQL > show parameter MEMORY_MAX_TARGET

NAME TYPE VALUE

-

Memory_max_target big integer 0

SQL > ALTER SYSTEM SET MEMORY_MAX_TARGET = 160000M SCOPE = SPFILE SID='*'

ALTER SYSTEM SET MEMORY_TARGET = 160000M SCOPE = SPFILE SID='*'

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE SID='*'

3. Restart the database

4. Automatic memory management monitoring

The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

SQL > select * from v$memory_target_advice order by memory_size

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.