cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-04031 after upgrade to oracle 12c

Former Member
0 Kudos

Hi experts,

After I upgraded my system Oracle 11g to Oracle 12c I have the error ORA-04031 every week,

Errors in file I:\ORACLE\PRO\SAPTRACE\diag\rdbms\pro\pro\trace\pro_arc2_8028.trc  (incident=530484):

ORA-04031: unable to allocate 352 bytes of shared memory ("shared pool","unknown object","sga heap(3,0)","krsdicle")

Errors in file I:\ORACLE\PRO\SAPTRACE\diag\rdbms\pro\pro\trace\pro_arc0_14736.trc  (incident=514716):

ORA-04031: unable to allocate  bytes of shared memory ("","","","")

I have to stop the database and started again... I increase the parameter shared_pool_size but only work one-two days more...

What could be the problem? I have a server with 64GB and the parameters are:

_enable_shared_pool_durations        boolean     FALSE

shared_pool_reserved_size            big integer 405600K

shared_pool_size                     big integer 5G

sga_max_size                         big integer 32G

sga_target                           big integer 0

memory_target                        big integer 0

pga_aggregate_limit                  big integer 12G

pga_aggregate_target                 big integer 6G

Any idea?

Thanks in advance,

Regards,

Accepted Solutions (1)

Accepted Solutions (1)

JamesZ
Advisor
Advisor
0 Kudos

Hi Victor,

Have you performed parameter check according to note 1171650 and

1888485 ?

If there is no reason to violate recommended parameter settings, we have to strictly follow up the recommendation.

Best regards,
James

Former Member
0 Kudos

Yes, I have all parameters correctly... one by one

JamesZ
Advisor
Advisor
0 Kudos

can you attach the parameter check result? Also can you attach the alert log?

please check if any backup is performed just before ORA-04031 in the alert log

Former Member
0 Kudos

Hi James,

Of course, I attach a small part of alert_log and the parameter check.

The day of the error was on Sunday, and there is not a backup.

Thanks in advance,

Regards,

JamesZ
Advisor
Advisor
0 Kudos

Hi Victor,


Below parameters need to be considered to remove as mentioned in the parameter check result:

_enable_shared_pool_durations       

_optimizer_batch_table_access_by_rowid

_optimizer_reduce_groupby_key                

sga_max_size                        

shared_pool_reserved_size           

As mentioned in the parameter check result, if there is no reason to keep above parameters, you need to remove.

Best regards,
James

Former Member
0 Kudos

Hi,

I deleted but the shared pool is decreasing every minute...is it normal?

It seems not releases the memory.

JamesZ
Advisor
Advisor
0 Kudos

Hi Victor,

What is the latest parameter check result?


Generally as long as the parameters are followed by SAP recommendation, then generally oracle will take care of the memory resource by himself. Is there any error? or any performance issue?

Best regards,
James

Former Member
0 Kudos

I attach you.

It seems work fine, but... before the changes too, until the shared_pool_memory increase and increase the limit and appear the error.

JamesZ
Advisor
Advisor
0 Kudos

Hi Victor,

Can you monitor the system and check if ORA-04031 occurs after keeping the parameters as SAP recommends?

Best regards,
James

fschoen
Explorer
0 Kudos

Hi Victor

BTW: It's always good to download the latest parameter check script for 12.1.0.2.

1888485 - Database Parameter for 12.1.0.2

If I interpret the result file you are using a version from May.

regards

fabian

Former Member
0 Kudos

I continue with the same problem...

JamesZ
Advisor
Advisor
0 Kudos

Hi Victor,

Have you applied bundle patch?

This may be caused by bugs on memory handling.

Best regards,
James

Former Member
0 Kudos

I installed the bundlepatch 6... and now, I am checking there ir bundlepatch 10

Could be a bug?

JamesZ
Advisor
Advisor
0 Kudos

Hi Victor,

Maybe it is a bug. If it is not difficult for you to apply patch for the system, you can try to apply the latest bundle patch.


Also if the problem does till occur after applying the latest bundle patch, I think you can raise a ticket to SAP or oracle to get the root cause of this issue. Do you have the account?

Because usually SAP customer should strictly follow the note 1171650 and 1888485 for the parameters. If the root cause is not addressed, I am afraid the situation may get worse and worse by adjusting parameters.

Best regards,
James

Former Member
0 Kudos

Hi,

I applied the last bundle, but I continue with the same problem.

Tha shared pool size decrease and decrease and, finally appear the same error.

Any idea?

Thanks in advance,

regards,

Answers (4)

Answers (4)

stefan_koehler
Active Contributor
0 Kudos

Hi guys,

just a crazy idea.

Why not just analyzing the heap dump and finding the root cause? Anything else is just like looking into a crystal ball. You can use the heap dump analyzer by Tanel Poder to make the reading much easier.

FYI - Oracle has changed the shared pool structure between 11g and 12c: ORACLE 12C : Shared Pool and durations enhancement ... and in your case the issue is in sub-pool 3 duration 0 ("sga heap(3,0)").

Regards

Stefan

Brindavan_M
Contributor
0 Kudos

Hi Victor,

According the alert logs shows that the server checking for authentication parameter to reach it might be increase the share pool size and even if you increase or decrease the size of shared pool size.

"Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter.

Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter.

Sun Jan 24 06:14:29 2016

Errors in file I:\ORACLE\PRO\SAPTRACE\diag\rdbms\pro\pro\trace\pro_arc2_8028.trc:

ORA-04031: unable to allocate 352 bytes of shared memory ("shared pool","unknown object","sga heap(3,0)","krsdicle")"

Solution: 1

In oracle 12.1, the default value for the SQLNET.ALLOWED_LOGON_VERSION parameter has been updated to 11. This means that database clients using pre-11g JDBC thin drivers cannot authenticate to 12.1 database servers unless theSQLNET.ALLOWED_LOGON_VERSION parameter is set to the old default of 8.

Workaround: Set SQLNET.ALLOWED_LOGON_VERSION=8 in the oracle/network/admin/sqlnet.ora file.

restart the database


Solution: 2


View the database statics

  SQL> show parameter sga

SQL> select pool,sum(bytes)/1024/1024 MB from v$sgastat group by pool;

Try to flush the shared pool:

SQL> alter system flush shared_pool;

SQL> alter system flush buffer_cache;


Restart the database


I hope this solution should work.



Thanks

Brindavan M





fschoen
Explorer
0 Kudos

Dear Brindavan


Only a comment on the sqlnet.ora parameter 'SQLNET.ALLOWED_LOGON_VERSION' mentioned by you.



If the alert.log is already mentioning, that a deprecated parameter is used, then the new parameter should be set in the file

SQLNET.ALLOWED_LOGON_VERSION_SERVER for database server ( Parameters for the sqlnet.ora File ).

regards

fabian

Former Member
0 Kudos

I have the option in the file sqlnet.ora:

SQLNET.ALLOWED_LOGON_VERSION = 8

but, the file that appear in C:\oracle\oraclehome01\PRO\12102\NETWORK\ADMIN  could be the problem that the file that there is in E:\usr\sap\PRO\SYS\profile\oracle do not have the before parameter?

Thanks in advance,

Regards,

Brindavan_M
Contributor
0 Kudos

yes... it might be also check the latest value need to be set. As per the link Parameters for the sqlnet.ora File value set to 12. Try the both scenario even if not works then flush the share pool and buffer pool as mentioned earlier comments.

Thanks,

Brindavan M

Former Member
0 Kudos

I continue with the same problem... not appear the deprecated, but the shared pool is decreasing and at the end gives the error

fschoen
Explorer
0 Kudos

Dear Victor

I didn't follow up the complete thread. But do you or someone of the company have an account on the Oracle Support website?

If yes probably you can give a try and using the troubleshooting tool "ORA-4031 Troubleshooting Tool (Doc ID 1521925.1)"

regards

fabian

Former Member
0 Kudos

Sorry, we have not an account on the Oracle...

former_member182657
Active Contributor
0 Kudos

Hi Victor,

Would you please check for SAP Note   690241 - The Shared SQL Area in the Shared Pool and large SQL Stmnts & work with following from the mentioned note


Solution

To benefit from the improved scalability of having multiple subpools please set your SHARED_POOL_SIZE init.ora parameter for Oracle by using the following formula:

SPS          =  minimum SHARED_POOL_SIZE needed for Oracle non RAC

SPS_RAC      =  minimum SHARED_POOL_SIZE needed for Oracle RAC

cpus        =   CPU_COUNT

SGA_MAX_SIZE =   Parameter SGA_MAX_SIZE in GB

The existing values of SGA_MAX_SIZE and cpus can be queried from v$parameter:

select name, value from v$parameter where name in ('sga_max_size', 'cpu_count');

SPS =  (cpus / 4 * 500 MB) + (SGA_MAX_SIZE * 5 MB) + 300 MB

SPS_RAC = SPS + (SGA_MAX_SIZE * 50 MB)

Examples:

CPUs SPS for 20GB SGA_MAX    SPS for 200GB SGA_MAX
NON RAC   RAC NON RAC RAC
   4      900M    1900M         1800M      11.8G
   8      1400M    2400M         2300M      12.3G
  12      1900M    2900M         2800M      12.8G
  32      4400M    5400M         5300M      15.3G
  64      8400M    9400M         9300M      19.3G

128      16.4G    17.4G         17.3G      27.3G

Regards,

Gaurav

Former Member
0 Kudos

It was working fine before the upgrade... I do not think there is a problem with he parameter.

former_member185239
Active Contributor
0 Kudos

Dear Victor,

if possible set the below parameter _disable_objstat_del_broadcast=false

By setting this parameter value to false, database will not keep the statistics for dropped objects/segments in shared pool.

Also follow the sapnote 1635622 - ORA-04031 despite sufficiently sized shared pool

With Regards

Ashutosh Chaturvedi

Former Member
0 Kudos

Hi,

I try to applied but appear the error:

alter system set "_DISABLE_OBJSTAT_DEL_BROADCAST"=FALSE scope=memory

                 *

ERROR at line 1:

ORA-02065: illegal option for ALTER SYSTEM

I try with scope=both, spfile and memory...

Is it work for oracle 12c?

Thanks in advance,

regards,

former_member185239
Active Contributor
0 Kudos

Dear Victor,

Can you paste the value of below command

show parameter _DISABLE_OBJSTAT_DEL_BROADCAST;

if you see the parameter , then you can change the parameter in this way. Before creating a pfile you need to take a backup of previous init<SID>.ora

- create pfile from spfile.

- Enter the parameter manually in pfile.

- create spfile from pfile.

Restart the oracle.

With Regards

Ashutosh Chaturvedi

Former Member
0 Kudos

Hi,

The command return nothing:

SQL> show parameter _DISABLE_OBJSTAT_DEL_BROADCAST;

SQL>