on 01-26-2016 4:48 PM
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,
Hi Victor,
Have you performed parameter check according to note 1171650 and
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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,
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
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 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.