As you might have read in my previous blogs I did some tests with Oracle 10g and 11g on Linu 6 Update 1 – and it works even though there is no official support from Oracle. Due to a presentation for the German Oracle User Group (DOAG) I did some tests with Automatic Memory Management and in this blog I like to share some experiences with you.
\n<\/p>\n
As you probably know there is one single parameter\u00a0 in Oracle 11g to control the memory usage for both SGA and well as PGA: memory_target<\/em>. So for my tests I tried to give both of my instances in the RAC environment 2 GB on memory instead of 1 GB.<\/p>\n Yes you know: it’s the new shared memory structure used with Oracle 11g (see chapter 2). Some more specific information can be found in the alert-file.<\/p>\n Unfortunately I forgot that ASM has an SGA as well so is using approx 200 MB on shared memory. I now have to choices to go ahead:<\/p>\n For the first point I was really in trouble – why? Because how can you change a server parameter without starting the instance? As the spfile<\/em> for the RAC database is stored inside ASM “CREATE pfile= … FROM spfile”<\/em> doesn’t work. Nor is it possible to directly edit the spfile<\/em>. If you have some hints how to read or change values for an spfile<\/em> inside ASM without starting the instance please let me know. Here’s my first recommendation: 1. Never make changes to the spfile without copying it to a readable and writable init.ora:<\/p>\n 2. Change the parameter for one instance at a time. Because this allows you to startup the remaining instance and revert the changes. So my only choice was to increase the shared memory.<\/p>\n To use memory_target<\/em> and memory_max_target<\/em> the operating system has to switch to a Posix-Style shared memory management instead of the old System-V style to manage both the SGA as well as the PGA with one single structure. To use 2 GB memory for my RAC instances I changed the parameter for tmpfs<\/em> in \/etc\/fstab<\/em> accordingly.<\/p>\n It doesn’t matter that I used 3 GB as this is only a upper limit for the shared memory. Okay so let’s reboot the server to use the new shared memory value…<\/p>\n After the reboot the size of the shared memory is reset to 2026656 KB – so approx. 2 GB again. What happened? As I’m running an unsupported environment I had no chance to find a solution at Oracle support – but I found some hints in the Linux forum. With Linux 6 there is one minor difference in the file \/etc\/rc.d\/rc.sysinit<\/em>. To allow the shared memory go grow accordingly to the entry in \/etc\/fstab<\/em> you need to remove the “-f<\/em>” for the mount command:<\/p>\n And now it works! After the reboot the correct value for shm is used and my instances were restarted again.<\/p>\n The parameter memory_max_target<\/em> is used to define the upper limit for the shared memory but not the real size. While starting the instance the value the Oracle kernel checks if that amount of memory is available but for the allocation memory_target<\/em> is used instead. So you can increase and decrease memory_target<\/em> as long as it fits into memory_max_target<\/em> without restarting the instance.<\/p>\n In the next step the various caches like shared pool or buffer cache can be resized automatically or manually. Let’s look into v$sgainfo<\/em> for some details:<\/p>\n So the “new” area is marked as “Free SGA memory<\/em>” – that’s what I expected. After some more tests I realized the neither the shared pool nor the buffer cache is increasing. So I’m going to increase the buffer cache manually.<\/p>\n Why? “Free SGA Memory Available<\/em>” in v$sgainfo<\/em> is misleading as it isn’t free memory but the PGA.<\/p>\n But how can I decrease the PGA? Let’s try this:<\/p>\n Nice command but without any positive impact as with automatic memory management (also with sga_target in Oracle 10g) the defined values are lower limits and not target values. So in this case the PGA cannot decrease below 200 MB but as the current value is approx. 1.3 GB it makes no difference. So from what I know the only way to increase the buffer cache is to increase the whole SGA first.<\/p>\n As you can see the additional 500 MB are allocated for the buffer cache – hurray.<\/p>\n In addition to the already given recommendation to make a backup before changing server parameters I would recommend to set a lower limit for the most important caches (PGA, buffer cache and shared pool). This eliminates negative impacts of poor written software (e.g. not using bind variables). In one of my recent projects the PGA had a size of 3 GB, shared pool was approx. 1.5 GB but only 500 MB were left for the buffer cache. So here is one example how to set the limits for the caches:<\/p>\n The total memory is 2 GB where the buffer cache has a minimum of 1 GB, the shared pool is minimum 500 MB and the PGA 200 MB. So approx. 300 MB can be “automatically” allocated and deallocated according to the application. Differences between memory_target<\/em> and memory_max_target<\/em> are only useful if you are running more databases on one single server. With the adjustment of memory_target<\/em> one instance can use more memory (e.g. for a batchrun) for a short period of time while other instances can use the same memory for the rest of the time. I would appreciate comments and additional hints – especially how to make changes to and spfile in ASM if you are not able to start the instance.<\/p>\n","protected":false},"excerpt":{"rendered":" As you might have read in my previous blogs I did some tests with Oracle 10g and 11g on Linu 6 Update 1 – and it works even though there is no official support from Oracle. Due to a presentation for the German Oracle User Group (DOAG) I did some tests with Automatic Memory Management and in this blog I like to share some experiences with you.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_crdt_document":"","_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[406],"tags":[91,83,84,85,88,86,90,22,41,87,89],"class_list":["post-117","post","type-post","status-publish","format-standard","hentry","category-oracle-en","tag-dev-shm","tag-amm","tag-asmm","tag-automatic-memory-management","tag-db_cache_size","tag-memory_target","tag-ora-00845","tag-oracle","tag-oracle-12c","tag-sga_target","tag-shared_pool_size"],"acf":{"occupation":"Write the Occupation of the Person","person_can-be-speaker":true,"person_is-team":true,"person_related-user-account":null,"person_ordering-number":50,"publication_speakers":null,"publication_content-language":["de"],"publication_files":null,"publication_event":"","publication_date":null},"spectra_custom_meta":{"_fgj2wp_old_id":["117"],"_yoast_wpseo_metadesc":["Automatic Memory Management"],"_yoast_wpseo_metakeywords":["AMM, ASMM, Automatic Memory Management, Memory_target, sga_target, db_cache_size, shared_pool_size, ORA-00845, \/dev\/shm"],"layout_show-author-box":["1"],"_layout_show-author-box":["field_5a64ee9cc6490"],"_edit_lock":["1518384155:1"],"_edit_last":["3"],"_vc_post_settings":["a:1:{s:10:\"vc_grid_id\";a:0:{}}"],"_yoast_wpseo_content_score":["90"],"layout_header":["light"],"_layout_header":["field_5a48111185e5f"],"layout_background":[""],"_layout_background":["field_5a481193c0995"],"layout_hide_services":["0"],"_layout_hide_services":["field_5a4811c5c0996"],"layout_show-categories-tags":["1"],"_layout_show-categories-tags":["field_5a64eee4c6491"],"blogpost_related-publications":[""],"_blogpost_related-publications":["field_5a480debdac62"],"blogpost_related-files":[""],"_blogpost_related-files":["field_5a480f377db29"],"_yoast_wpseo_primary_category":[""],"rank_math_primary_category":[""],"rank_math_description":["Automatic Memory Management"],"rank_math_news_sitemap_robots":["index"],"rank_math_robots":["a:1:{i:0;s:5:\"index\";}"],"astra_style_timestamp_css":["1773714843"],"rank_math_internal_links_processed":["1"],"wpil_sync_report3":["1"],"wpil_links_inbound_internal_count":["0"],"wpil_links_inbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_internal_count":["0"],"wpil_links_outbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_external_count":["0"],"wpil_links_outbound_external_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_sync_report2_time":["2024-08-27T11:47:31+00:00"],"copied_media_ids":["a:0:{}"],"referenced_media_ids":["a:0:{}"],"_uag_css_file_name":["uag-css-117.css"]},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Johannes Ahrends","author_link":"https:\/\/carajandb.com\/en\/author\/9aa6cdb2095bd409\/"},"uagb_comment_info":3,"uagb_excerpt":"As you might have read in my previous blogs I did some tests with Oracle 10g and 11g on Linu 6 Update 1 – and it works even though there is no official support from Oracle. Due to a presentation for the German Oracle User Group (DOAG) I did some tests with Automatic Memory Management…","_links":{"self":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/117","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/comments?post=117"}],"version-history":[{"count":0,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/117\/revisions"}],"wp:attachment":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/media?parent=117"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/categories?post=117"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/tags?post=117"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\r\nSQL> ALTER SYSTEM SET memory_max_target = 2G scope=spfile sid='*';\r\n\r\nSystem altered.\r\n\r\n% srvctl stop database -d ORASE\r\n% srvctl start database -d ORASE\r\nPRCR-1079 : Failed to start resource ora.racvm1.db CRS-5017: The resource action \"ora.racvm1.db start\" encountered the following error: ORA-00845: MEMORY_TARGET not supported on this system .\r\nFor details refer to \"(:CLSN00107:)\" in \"\/u01\/app\/grid\/11.2.0\/grid\/log\/obelix\/agent\/crsd\/oraagent_oracle\/oraagent_oracle.log\".\r\nSTARTUP WARNING: You are trying to use the MEMORY_TARGET feature.\r\nThis feature requires the \/dev\/shm file system to be mounted for at least 2097152000 bytes. \/dev\/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 1866305536 and used is 208990208 bytes. Ensure that the mount point is \/dev\/shm for this directory. \r\nmemory_target needs larger \/dev\/shm.\r\n<\/pre>\n
\r\nWARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the \/dev\/shm file system to be mounted for at least 2147483648 bytes. \r\n\/dev\/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. \r\nCurrent available is 1866305536 and used is 208990208 bytes. Ensure that the mount point is \/dev\/shm for this directory.\r\n<\/pre>\n
\n
SQL> CREATE pfile='\/tmp\/initRAC.ora' FROM spfile;<\/pre>\n
Posix-Style Shared Memory Management<\/h2>\n
shmfs \/dev\/shm tmpfs size=3g 0 0<\/pre>\n
\r\n# df -k Filesystem 1K-blocks Used Available Use% Mounted on\r\n\/dev\/mapper\/vg_..._root 26391624 16380736 8670268 66% \/\r\ntmpfs 2026656 1267368 759288 63% \/dev\/shm\r\n\/dev\/sda1 495844 50942 419302 11% \/boot\r\n<\/pre>\n
\r\nOld: mount -f \/dev\/shm >\/dev\/null 2>&1\r\nNew: mount \/dev\/shm >\/dev\/null 2>&1\r\n<\/pre>\n
3.\u00a0 Memory usage of various caches<\/h2>\n
SQL> ALTER SYSTEM SET MEMORY_TARGET=2000M sid='ORASE1';<\/pre>\n
\r\nSQL> select * from v$sgainfo;\r\n\r\nNAME BYTES RES\r\n-------------------------------- ---------- ---\r\nFixed SGA Size 2229944 No\r\nRedo Buffers 5173248 No\r\nBuffer Cache Size 352321536 Yes\r\nShared Pool Size 385875968 Yes\r\nLarge Pool Size 16777216 Yes\r\nJava Pool Size 16777216 Yes\r\nStreams Pool Size 0 Yes\r\nShared IO Pool Size 0 Yes\r\nGranule Size 16777216 No\r\nMaximum SGA Size 2087780352 No\r\nStartup overhead in Shared Pool 137554960 No\r\nFree SGA Memory Available 1308622848\r\n<\/pre>\n
\r\nSQL> ALTER SYSTEM SET db_cache_size=900M sid='ORASE1';\r\n ALTER SYSTEM SET db_cache_size=900M sid='ORASE1'\r\n*\r\nERROR at line 1: ORA-02097: parameter cannot be modified because\r\nspecified value is invalid ORA-00384: Insufficient memory to grow cache\r\n<\/pre>\n
\r\nSQL> SELECT * from v$pgastat\r\n WHERE name LIKE '%target parameter%';\r\nNAME VALUE UNIT\r\n-------------------------------- ---------- ------------\r\naggregate PGA target parameter 1308622848 bytes\r\n<\/pre>\n
SQL> alter system set pga_aggregate_target=200M;<\/pre>\n
\r\nSQL> ALTER SYSTEM SET sga_target=1500M sid='ORASE1';\r\n\r\nSystem altered.\r\n\r\nSQL> SELECT * FROM v$sgainfo;\r\n\r\nNAME BYTES RES\r\n-------------------------------- ---------- ---\r\nFixed SGA Size 2229944 No\r\nRedo Buffers 5173248 No\r\nBuffer Cache Size 1140850688 Yes\r\nShared Pool Size 385875968 Yes\r\nLarge Pool Size 16777216 Yes\r\nJava Pool Size 16777216 Yes\r\nStreams Pool Size 0 Yes\r\nShared IO Pool Size 0 Yes\r\nGranule Size 16777216 No\r\nMaximum SGA Size 2087780352 No\r\nStartup overhead in Shared Pool 137554960 No\r\nFree SGA Memory Available 520093696\r\n<\/pre>\n
4. Tips<\/h2>\n
\r\nmemory_max_target = 3G\r\nmemory_target = 2G\r\ndb_cache_size = 1G\r\nshared_pool_size = 500M\r\npga_aggregate_target = 200M\r\n<\/pre>\n