Oracle Database case study-Oracle System runtime fault-too little memory causes shared memory allocation failure

1.1 symptom description

Failed to allocate shared memory in Oracle. The ALTER log displays the following error message.

Errors in file /oracle/db/diag/rdbms/ora01/ora01/trace/ora01_reco_233670.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select local_tran_id, global...","sga heap(1,0)","kglsim heap")

Sat Jan 15 09:53:11 2011

DDE: Problem Key 'ORA 4031' was completely flood controlled (0x6)

Further messages for this problem key will be suppressed for up to 10 minutes

Sat Jan 15 10:03:11 2011

DDE: Problem Key 'ORA 4031' was completely flood controlled (0x6)

Further messages for this problem key will be suppressed for up to 10 minutes

Sat Jan 15 10:13:11 2011

DDE: Problem Key 'ORA 4031' was completely flood controlled (0x4)

Further messages for this problem key will be suppressed for up to 10 minutes

Note:

The ALTER log file directory is/$ ORACLE_BASE/diag/rdbms/$ ORACLE_SID/trace/alert __< ORACLE_SID>. log ".

1.2 possible causes

The database memory value set in Oracle is too small, leading to memory allocation failure.

1.3 positioning ideas

Check the configuration of Oracle memory control parameters.

SQL> show parameter memory

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address             integer     0

memory_max_target                    big integer 1000M

memory_target                        big integer 1000M

shared_memory_address                integer     0

As shown above, the Oracle database memory configuration value is too small, only 1 GB.

1.4 procedure

1. ToOracleThe machine on which the user logs on to the database.
2. ToSYSDBAThe user connects to the database.

% Sqlplus/as sysdba

3. Modify the value of "memory_max_target.

Modify the settings as needed.

SQL> alter system set memory_max_target = 2147483648 scope = spfile;

As shown above, change the value of "memory_max_target" to 2 GB.

4. Modify the value of "memory_target.

SQL> alter system set memory_target = 1610612736 scope = spfile;

5. Restart the database.

SQL> shutdown immediate

SQL> startup

6. Check the database running status.

SQL> select status from v $ instance;

The system displays the following information:

STATUS

------------

OPEN

SQL> select open_mode from v $ database;

The database runs normally and the system displays the following information:

OPEN_MODE

----------

READ WRITE

7. Check the configuration of database memory control parameters.

SQL> show parameter memory

After successful modification, the system displays the following information:

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address integer 0

memory_max_target big integer 2048M

memory_target big integer 1536M

shared_memory_address integer 0