Export --> ORA-04031:

[caesardutta]

Dear Sirs:

For the past few days in our production environment we are getting the error "ORA-04031: unable to allocate 4080 bytes of shared memory " during FULL
export of oracle database. Now before I go along let me summarise the system.

OS --> RHEL for Itanium 4.0 EE
Oracle DB--> 10G R2 10.2.0.3
RAC --> 2 Node
Export runs from node 2 at evening when there is virtually no load
SGA_MAX --> 3GB, SGA_TARGET--> 1.5GB
SHARED_MEMORY --> 480M
DB_BUFFER_CACHE --> 800M

The database is not custom database, rather used by Oracle Internet Directory (OID)

The export command is run as

Code: Select all

exp userid=" '/ as sysdba' " file=/archive/backup/ log=/archive/logs/ full=Y buffer=1000000000 statistics=NONE


We have done a restart of the system and even "alter system flush shared_pool".

Given below is the SHARED MEMORY ADVICE run continuously when export is running

Code: Select all

ESTIMATE_SIZE     FACTOR TIME_SAVED  LOAD_TIME
------------- ---------- ---------- ----------
          304      .6129    9468845     380976
          368      .7419    9498044     351777
          432       .871    9756506      93315
          496          1    9812428      37393
          560      1.129    9880150          1
          624     1.2581    9956505          1
          688     1.3871    9969391          1
          752     1.5161    9969398          1
          816     1.6452    9969398          1
          880     1.7742    9969398          1
          944     1.9032    9969398          1

ESTIMATE_SIZE     FACTOR TIME_SAVED  LOAD_TIME
------------- ---------- ---------- ----------
         1008     2.0323    9969398          1


Given below is SGA Advice, run continuoulsy in another window

Code: Select all

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       768              .5      1806327              1.0257             5242526
      1152             .75      1763533              1.0014             3956299
      1536               1      1761068                   1             3243134
      1920            1.25      1759131               .9989             2673964
      2304             1.5      1757722               .9981             2306841
      2688            1.75      1757722               .9981             2306841
      3072               2      1757722               .9981             2306841

7 rows selected.


SGA Paremeters

Code: Select all

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3G
sga_target                           big integer 1536M


Shared Memory Parameters

Code: Select all

SQL> show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 26004684
shared_pool_size                     big integer 480M
shared_server_sessions               integer
shared_servers                       integer     1
SQL>


Excerpt of export problem

Code: Select all

. . exporting table                   STATS$THREAD          0 rows exported
. . exporting table      STATS$TIME_MODEL_STATNAME          0 rows exported
. . exporting table                 STATS$UNDOSTAT          0 rows exported
. . exporting table                 STATS$WAITSTAT          0 rows exported
. exporting synonyms
. exporting views
EXP-00056: ORACLE error 4031 encountered
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","SELECT /*+rule*/ SYS_XMLGEN(...","sga heap(1,0)","library cache")
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583


I would also like to share another error from the alert log.

Code: Select all

Tue Jan 29 08:32:16 2013
Errors in file /oracle/ora10g/product/1020/admin/infradb/bdump/infradb2_m000_10501.trc:
ORA-00600: internal error code, arguments: [2024], [214161682], [214216401], [8192], [32], [2], [2601611791], [1913593858]


For ORA-00600: the tracefile shows memory dumps which I cannot understand. I have already raised an SR, but I want your opinion. I do not want to play with init file / spfile memory settings. Why? Because, this database is just used for authentication of ENterprise users and contain LDAP values and the error occurs viirtually when no load. Moreover, from advsiors i do not see any problem. May be I am missing on some fundamental concept.

Thanks,

Caesar

[Tim...]

Hi.

Normally the shared memory areas give you an idea of the memory pool the allocation is failing for. That may help in the identification of the issue. I notice generally that you have a very small amount of memory allocated to this database. I can't remember the last time I ran a production database with a shared pool less than 1G and the bugger cache significantly bigger. when you run with very small amounts of memory you often find the automatic shared memory management having to constantly resize the pools to make room for stuff, which leads to issues.

The obvious answer is wait for Oracle Support to respond, because ORA-00600 are unexpected internal errors, so it is impossible to predict what the issue is with just the error number alone. You might want to use the 600/7445 lookup facility in MOS. If this is known issue, there may already be a workaround listed.

Cheers

Tim...

[caesardutta]

Dear Tim:

Thanks a lot for your kind response. Oracle support has asked me to install RDA (Remote Diagonostic Agent) to get report about all internal configurations etc. Now this being a "month end" I do not want to go for installation and introducing extra load - even it be a "delta".

So lets wait till 4th Feb.

On a different note, yesterday midnight we did a re-start of the boxes and found that import ran successfully.

This is a very low transaction database. The total server capacity is 20GB of RAM. So given that SGA_MAX is 3GB, and SGA_TARGET is 1.5GB, I would be
very grateful if you could kindly provide me some figures for memory parameters (just based on your experience) so that I can have a look into them.

I also share the same thought, that due to low memory settings, Oracle is doing frequent paging which might lead to fragmentation and on the fly the objects (system ones) are not found and hence such memory dumps taking place.

Warm regards,

Caesar

[Tim...]

Hi.

RDA is just some scripts that gather information from the filesystem, host and database. I doubt you could sensibly measure the load it causes, so don't let that stop you, but by all means wait if you have any doubt.

As for memory, that really depends on a number of things, so it is difficult to give you a figure. Even on systems useing bind variables etc, I rarely see systems with a shared pool lower than 1G. There is so much Oracle code that has to be in the shared pool that less than this causes lots of aging of Oracle built-in code. Typically, I would use the advisors for PGA and SGA as a starting block, then increase or decrease as necessary based on the monitoring.

Cheers

Tim...