ADG--Ora-30927報錯處理

dbLjy2015發表於2017-01-11

一、環境

資料庫環境為DG一主三備（最大可用模式），作業系統均為CentOS release 6.5版本，資料庫版本11.2.0.3.15

二、描述

2016年12月26日下午，業務人員突然說系統不能用，一些網頁頻繁報錯，沒辦法將資料查出來，最後結合開發人將對應的sql提取出來進行分析，發現所有的sql均帶有with語法，且都是對standby databae進行的查詢。通過metalink查詢發現，這與一個bug有關。在ADG環境中使用with語句且系統自動生成臨時表時會出現

ORA-30927: Unable to complete execution due to failure in temporary table transformation報錯。（在standby database中會出現）

METALINK上的描述是：


On ADG, queries that use a cursor-duration temporary table may fail with ORA-30927 errors.

Such queries use Star with Temp Transformation and subquery factoring (WITH clause).

（Bug 14143632- ora-30927 on active data guard（文件ID 14143632.8））


此時線上的資料庫PSU已打到了11.2.0.3.15但是沒有包含這個bug的補丁。補丁號（14143632）

例如：執行如下程式碼，with內的表被使用了兩次，oracle會自動生成一個臨時表來存放with內的表。


  1. With a as (
  2. Select object _id aa,object_name na from dba_objects
  3. )
  4. Select * from a where aa in (select a.aa from a);


而對於with內的表只使用一次的是不會生成臨時表


  1. with a as (
  2. select object_id aa,object_name na from dba_objects
  3. )
  4. select * from a;


三、解決

在METALINK下載相應的補丁，上傳至standby database伺服器

通過opatch檢視為一個online的補丁。

且需要19769496這個補丁，在檢視以前安裝過的補丁發現已經有這個補丁。現在就可以直接線上打補丁。


1.    檢查可以線上處理

  1. [oracle@oracle-test 14143632]$ opatch query -all online
  2. Oracle 中間補丁程式安裝程式版本 11.2.0.3.6
  3. 版權所有 (c) 2013, Oracle Corporation。保留所有權利。
  4. Oracle Home : /home/app/oracle/product/11.2.0/dbhome_1
  5. Central Inventory : /home/app/oraInventory
  6. from : /home/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
  7. OPatch version : 11.2.0.3.6
  8. OUI version : 11.2.0.3.0
  9. Log file location : /home/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2016-12-27_15-36-57下午_1.log
  10. -------------------------------------------------------------------------------
  11. Patch created on 5 Jun 2015, 23:20:09 hrs PST8PDT
  12. Need to shutdown Oracle instances: false
  13. Patch is roll-backable: true
  14. Patch is a "Patchset Update": false
  15. Patch is a rolling patch: true
  16. Patch has sql related actions: false
  17. Patch is an online patch: true
  18. Patch is a portal patch: false
  19. Patch is an "auto-enabled" patch: false
  20. Patch is translatable: false
  21. List of platforms supported:
  22. 226: Linux x86-64
  23. List of prereq patches:
  24. 19769496
  25. List of overlay patches:
  26. 19769496
  27. List of bugs to be fixed:
  28. 14143632: QUERIES MAY FAIL WITH ORA-30927 ERRORS ON AN ADG STANDBY DATABASE
  29. This patch is a "singleton" patch.
  30. This patch belongs to the "db" product family
  31. This patch supports the patching model as "one-off"
  32. This patch supports the language "en"
  33. List of executables affected:
  34. ORACLE_HOME/bin/oracle
  35. ORACLE_HOME/bin/renamedg
  36. ORACLE_HOME/lib/libclntsh.so.11.1
  37. List of optional components:
  38. oracle.rdbms: 11.2.0.3.0
  39. List of optional actions:
  40. Patch the Database instances with Online Patch hpatch/bug14143632.pch
  41. Possible XML representation of the patch:
  48. 14143632
  52. -------------------------------------------------------------------------------
  53. OPatch succeeded.


2.    線上打補丁

檢查現有補丁
  1. [oracle@newfhldb1 OPatch]$ opatch lsinventory
打14143632補丁
  1. [oracle@newfhldb1 ~]$ cd 14143632/
  2. [oracle@newfhldb1 14143632]$ ls
  3. etc files online README.txt
  4. **此處sid username password 均對應當前資料庫的sid,username以及password最好使有dba許可權的使用者**
  5. [oracle@newfhldb1 14143632]$ opatch apply online -connectString :::
  6. Oracle 中間補丁程式安裝程式版本 11.2.0.3.6
  7. 版權所有 (c) 2013, Oracle Corporation。保留所有權利。
  8. Oracle Home : /usr/app/oracle/110203/v01
  9. Central Inventory : /usr/app/oracle/oraInventory
  10. from : /usr/app/oracle/110203/v01/oraInst.loc
  11. OPatch version : 11.2.0.3.6
  12. OUI version : 11.2.0.3.0
  13. Log file location : /usr/app/oracle/110203/v01/cfgtoollogs/opatch/14143632_Dec_27_2016_12_57_10/apply2016-12-27_12-57-10下午_1.log
  14. 補丁程式只應在 '-all_nodes' 模式下應用/回退。
  15. 將 RAC 模式轉換為 '-all_nodes' 模式。
  16. Applying interim patch '14143632' to OH '/usr/app/oracle/110203/v01'
  17. Verifying environment and performing prerequisite checks...
  18. All checks passed.
  19. 提供電子郵件地址以用於接收有關安全問題的通知, 安裝 Oracle Configuration Manager 並啟動它。如果您使用 My Oracle
  20. Support 電子郵件地址/使用者名稱, 操作將更簡單。
  21. 有關詳細資訊, 請訪問 http://www.oracle.com/support/policies.html。
  22. 電子郵件地址/使用者名稱:
  23. 尚未提供電子郵件地址以接收有關安全問題的通知。
  24. 是否不希望收到有關安全問題 (是 [Y], 否 [N]) [N] 的通知: y
  25. Backing up files...
  26. 正在為元件 oracle.rdbms, 11.2.0.3.0 打補丁...
  27. 正在資料庫 'fhlsys' 上安裝和啟用聯機補丁程式 'bug14143632.pch'。
  28. Verifying the update...
  29. Patch 14143632 successfully applied
  30. Log file location: /usr/app/oracle/110203/v01/cfgtoollogs/opatch/14143632_Dec_27_2016_12_57_10/apply2016-12-27_12-57-10下午_1.log
  31. OPatch succeeded.

一共三臺standby資料庫，依次安裝。

補丁安裝成功，在執行相應帶有with且生成臨時表的sql語句，執行成功。





來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30820196/viewspace-2132271/，如需轉載，請註明出處，否則將追究法律責任。

相關文章