oracle ADG ora-30927

環境：centos6.8  11.2.0.3.10（64bit）

本來是沒有這個問題的，但是由于最近對只讀庫做了一定的調整，在重啟后，發現觸發了這個bug。這個bug以前出現過，以前是通過重啟只讀庫，重新同步就解決了，但是這次多次重啟發現還是沒能規避這個bug，我沒有mos賬號，無法下載補丁。這里通過多次測試，并對比調整前后的設置，發現只是恢復并行度不同，以前重啟是使用了默認的并行度，這次特意關閉了并行恢復，在講并行度設置為2后暫時規避了改問題，這里記錄一下。另外在asktom上有人說加hint（這個 /*+ inline */ ）也能暫時解決， 對于改解決方式，他們的回復是，hint不是每次都會生效，所以錯誤會不可預期的還會出現。

以下為轉載的打補丁升級方式解決問題：

http://blog.itpub.net/30820196/viewspace-2132271/

一、環境

數據庫環境為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/opatch3016-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:
42. 14143632
43. -------------------------------------------------------------------------------
44. 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 語句，執行成功。