{"id":1036,"date":"2011-04-18T11:34:12","date_gmt":"2011-04-18T09:34:12","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=1036"},"modified":"2011-04-18T14:05:06","modified_gmt":"2011-04-18T12:05:06","slug":"is-11-2-0-2-ready-for-production-judge-yourself","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2011\/04\/18\/is-11-2-0-2-ready-for-production-judge-yourself\/","title":{"rendered":"Is 11.2.0.2 ready for production? Judge yourself &#8230;"},"content":{"rendered":"<p>Murphy said: if problems arise, they all come at the same time. After several weeks of stability, this week brought hell of a lot of Oracle problems with 11.2.0.2 and RAC. To list the worst:<\/p>\n<ul>\n<li><strong>Downtime because of ASM process limit reached. <\/strong>Normally, the formula to calculate depends on the number of concurrent datafile extensions. In our case 100 ASM processes were not enough. Increased to 200 and found\u00a0 Note 1287496.1 which describes the issue. Merge Patch for bug is available for 11.2.0.2 GI Bundle 1 but not yet for GI Bundle 2. Ups.<\/li>\n<li><strong>CPU Starvation because of Adaptive Cursor Sharing:<\/strong> One server process was taking up 100% of cpu time for the last several hours. It turned out to be an OEM monitoring query having several thousands of child cursors.<br \/>\n<blockquote>\n<pre>PID \tUSER \tPR NI VIRT \tRES SHR S %CPU %MEM TIME+ COMMAND\r\n8667 \tora11 25 0 \t8423m 33m 28m R 99.7 \t0.1 358:27.78 ora_pz98_MDDB<\/pre>\n<\/blockquote>\n<p>I then checked different databases and found that almost all of our 11.2.0.2 databases have several hundreds or even thousands of child cursors.<\/p>\n<pre lang=\"SQL\">select sql_id, is_shareable, min(child_number), max(child_number), count(*)\r\nfrom gv$sql\r\ngroup by sql_id, is_shareable having count(*) &gt;100 order by 6 desc\r\n\r\nSQL_ID        IS_SHAREABLE MIN(CHILD_NUMBER) MAX(CHILD_NUMBER)  COUNT(*)\r\n------------- ------------ ----------------- ------------------ ---------\r\nc7kt3njhnmtkm Y            0                 5097               1397\r\nc7kt3njhnmtkm N            3                 3544               1836\r\n1vnhgmpc17vv0 Y            0                 3022               2697\r\n1vnhgmpc17vv0 N            6                 2185               444\r\n93qh89pxuxzuw Y            0                 1949               1522\r\n93qh89pxuxzuw N            2                 1625               428\r\n5fk0v8km2f811 Y            0                 1281               1763\r\n4f3ufvfcgfqsg Y            0                 792                794\r\ncjbwk0ww7j5rv Y            0                 627                1251\r\ndyqdzgxcv4d3t Y            0                 626                1252\r\n5fk0v8km2f811 N            3                 543                260\r\nf0jxh8d6b5af2 Y            0                 494                564\r\nf0jxh8d6b5af2 N            0                 290                130\r\ndbvkky621gqtr Y            0                 266                267\r\n32rqmpqpfv0gm Y            0                 255                257\r\ng9uwxs7pr8tjm Y            0                 254                257\r\n40k6jjt90n4fa Y            3                 199                129<\/pre>\n<p>I suspect this to be Bug 10182051 Extended cursor sharing generates many shareable child cursors  and there is a workaround:<\/p>\n<blockquote><p>alter system set &#8220;_optimizer_extended_cursor_sharing_rel&#8221;=none;<\/p><\/blockquote>\n<\/li>\n<\/ul>\n<ul>\n<li><strong>Limited database availability because of failing queries on gv$ tables:<\/strong><br \/>\n<blockquote>\n<pre>SQL&gt; select count(*) from gv$session\r\n2 ;\r\nselect count(*) from  gv$session\r\n*\r\nERROR at line 1:\r\nORA-12850: Could not allocate slaves on  all specified instances: 2 needed, 1\r\nallocated\r\nORA-12801: error signaled  in parallel query server P001, instance 3599<\/pre>\n<\/blockquote>\n<p>Currently no other known workaround than bouncing all the RAC instances.<\/li>\n<li><strong>DataGuard ASYNC Redo Transport not reliable:<\/strong> We have a RAC primary \/ single instance physical standby setup and use async redo transport. During times of heavy ETL on the primary, the standby databases stops at recovery of one archivelog with &#8220;(in transit)&#8221;. Primary is showing this error in alert log:\n<pre lang=\"SQL\">\r\nARC7: Creating remote archive destination LOG_ARCHIVE_DEST_2: 'HAMDB' (thread 2 sequence 4044) (MUCDB2)\r\nARC7: Archive log rejected (thread 2 sequence 4044) at host 'HAMDB'\r\nFAL[server, ARC7]: FAL archive failed, see trace file.\r\nARCH: FAL archive failed. Archiver continuing\r\nORACLE Instance MUCDB2 - Archival Error. Archiver continuing.\r\n<\/pre>\n<p>On standby side, it says:<br \/>\nMedia Recovery Waiting for thread 2 sequence 4044 (in transit)<\/p>\n<p>The standby database never recovers from this problem, except when standby database is bounced. The problem appears with and without broker configuration. Currently there is no known workaround.<\/p>\n<\/li>\n<\/ul>\n<p>Maybe some of this issues will be addressed in upcoming PSU April, which will be released this week. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>Murphy said: if problems arise, they all come at the same time. After several weeks of stability, this week brought hell of a lot of Oracle problems with 11.2.0.2 and RAC. To list the worst: Downtime because of ASM process limit reached. Normally, the formula to calculate depends on the number of concurrent datafile extensions. [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[57,47,16,19,5],"tags":[],"class_list":["post-1036","post","type-post","status-publish","format-standard","hentry","category-11gr2","category-bugs","category-data-guard","category-metalink","category-oracle-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1036","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=1036"}],"version-history":[{"count":23,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1036\/revisions"}],"predecessor-version":[{"id":1059,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1036\/revisions\/1059"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=1036"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=1036"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=1036"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}