Refresh MATERIALIZED View 產生 ORA-31934 錯誤案例

Oracle 版本: 19.16

OS 版本: Aix 7.2

問題描述:

MATERIALIZED View Refresh 失敗，於 DB alert log 發現 ORA-31934 錯誤訊息:

2024-08-18T00:14:15.350739+08:00 MVRF: kkzlShrinkMVLog: executed: alter table "SCOTT"."MLOG$_REGIONS" shrink space ORA-31934: error occurred while shrinking the materialized view log at kkzlRunSA:execute ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1

問題分析:

從 Oracle 12c 開始，在 MATERIALIZED View Refresh 之後會自動觸發 Segment Advisor 並對 MATERIALIZED View Log 進行 Shrink 的動作，由於 MATERIALIZED View Log 預設是使用 delete 進行資料的刪除，進行 Shrink 的動作可以避免 MATERIALIZED View Log 的空間越來越大， ORA-31934 就是由於 Segment Advisor 所觸發 Shrink 動作所造成的錯誤。

ORA-31934 很有可能是由於 Bug 32433627 所引起的，原因在於 Segment Advisor 對 MATERIALIZED View Log 的處理上不夠完善，可以設定參數 "_mv_refresh_shrink_log"=false 來停用 MATERIALIZED View Refresh 時所觸發的 Segment Advisor 。

解決方法:

設定參數 "_mv_refresh_shrink_log"=false 避免觸發 Segment Advisor 。

SQL> alter system set "_mv_refresh_shrink_log"=false;

除此之外，設定 atomic_refresh 為 false 來進行 MATERIALIZED View Refresh 的動作。 atomic_refresh 預設為 true ，表示 MATERIALIZED View Log 會使用 delete 來刪除資料，設定為 false 表示使用 truncate 來刪除MATERIALIZED View Log 的資料。

由於設定了 "_mv_refresh_shrink_log"=false 使得 MATERIALIZED View Log 不再 Shrink ，此時使用 truncate 來刪除 MATERIALIZED View Log 就可以避免其空間越來越大。

BEGIN    DBMS_MVIEW.REFRESH( 'V_REGIONS',method => '?', atomic_refresh => false); END; /

ssh 造成 ORA-27515 錯誤案例

Oracle 版本: 12.2.0.1

OS 版本: Linux 7.6

問題描述:

執行程式報錯，於資料庫的 alert log 顯示 ORA-27515 錯誤 :

ORA-00603: ORACLE server session terminated by fatal error ORA-27515: inadequate memlock limit or driver settings opiodr aborting process unknown ospid (125480) as a result of ORA-603

由錯誤訊息顯示 memlock 的設定不足，檢查 ulimit -a 的 max locked memory 只有 64 :

但是 /etc/security/limits.conf 的設定是對的 :

limits.conf 的設定並沒有生效。

但是使用 root 登入後再 su - oracle ， limits.conf 的設定是有生效的 :

問題分析:

這個現象只有在使用 oracle 直接登入時才會發生 limits.conf 沒有生效，需要調整 ssh 設定。

解決方法:

於 /etc/pam.d/sshd 新增 pam_limits.so 設定如下 :

session required pam_limits.so

並確認 /etc/ssh/sshd_config 裡面的UsePrivilegeSeparation 設定為 NO

UsePrivilegeSeparation no

重啟 ssh 之後，重新使用 oracle 登入即可 。

[root@db19c ~]# systemctl restart sshd

Nologging 造成 corruption 案例

Oracle 版本: 12.2.0.1

OS 版本: Linux 6.7

問題描述:

Standby DB 出現 file corruption 錯誤:

ORA-01578: ORACLE data block corrupted (file # 5, block # 135) ORA-01110: data file 5: '/opt/app/oradata/ORA12st/users021.dbf' ORA-26040: Data block was loaded using the NOLOGGING option

問題分析:

由錯誤訊息顯示， data file 5 因為來源端的 nologging 操作造成 corruption ，使得這個檔案無法進行同步。這個問題常常是因為 Primary DB 沒有設定 force logging 並且有 nologging 的操作所導致:

解決方法:

在 Oracle 12.2 的版本之前要解決這個錯誤只能重新 restore datafile ，從 12.2 的版本開始可以直接 recover nonlogged block ，於 standby 查詢 nonlogged block :

SQL> select file#,block#,blocks,object#,reason from v$nonlogged_block;

停止 MRP 後使用 RMAN 來 recover nonlogged block :

SQL> alter database recover managed standby database cancel; SQL> exit; [oracle@db12c rman]$ rman target / RMAN> recover database nonlogged block;

recover 成功後查詢 v$nonlogged_block 已無資料 :

重新啟動 MRP 恢復同步即可 :

impdp 產生 ORA-02019 錯誤案例

Oracle 版本: 19.19

OS 版本: Linux 8.6

問題描述:

透過 DB Link 執行 impdp 作業時發生 ORA-02019 錯誤 :

$ impdp \'/ as sysdba\' directory=impdir schemas=scott network_link=uat19c logfile=imp_scott.log Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=impdir schemas=scott network_link=uat19c logfile=imp_scott.log  Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "SCOTT"."DEPT"             5.937 KB    198 rows . . imported "SCOTT"."EMP"              8.570 KB   2616 rows ORA-31693: Table data object "SCOTT"."LOC" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-02019: connection description for remote database not found ORA-02019: connection description for remote database not found . . imported "SCOTT"."JOS"              3.127 KB     53  rows Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at MON AUG 15 03:20:29 2022 elapsed 0 00:00:08

測試 DB Link 是可以正常運行的 :

問題分析:

這個現象可能與 Bug 27164609 : NETWORK_LINK IMPDP FAILS TO LOAD DATA DUE TO ORA-2019 WHEN DB_DOMAIN IS NULL 有關。

主要是因為 impdp 透過 DB Link 的行為從 12.2 版本開始改變，在 12.2 之前使用的 access_method 是 INSERT_AS_SELECT ，但 12.2 開始將 access_method 的預設模式改為 direct_path ，而 direct_path 這個模式沒辦法正確的解析 DB Link 是否含有 domain name 而造成這個錯誤。

檢查目前所使用的 uat19c 這個 DB Link ，的確內容是沒有包含任何 domain name :

即便如此還是發生了 ORA-2019 問題。

徹底解決方式還是使用 access_method=insert_as_select ，使用 12.2 之前的行為模式，不受 domain name 的影響。

解決方法:

將 access_method=insert_as_select 加入 impdp 即可 :

$ impdp \'/ as sysdba\' directory=impdir schemas=scott network_link=uat19c access_method=insert_as_select logfile=imp_scott.log Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=impdir schemas=scott network_link=uat19c access_method=insert_as_select  logfile=imp_scott.log  Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "SCOTT"."DEPT"             5.937 KB    198 rows . . imported "SCOTT"."EMP"              8.570 KB   2616 rows . . imported "SCOTT"."LOC"              1.435 KB     25 rows . . imported "SCOTT"."JOS"              3.127 KB     53  rows Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at MON AUG 15 03:29:43 2022 elapsed 0 00:00:48

Last Login Time 造成的效能問題

Oracle 版本: 12.2.0.1

OS 版本: AIX 5.3

問題描述:

資料庫發生了嚴重的 Concurrency Wait Event :

問題分析:

由 Concurrency Wait 的 sql 顯示語法為 :

UPDATE user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1

這句語法主要的功能是用來記錄使用者上次成功的登入時間， Last Successful Login Time 為 12c 的新功能，比對 11g 與 12c 的登入畫面， 12c 多了 Last Successful login time :

不過這個功能有個嚴重的 Bug 33121934 ， update user$ 這段語法產生大量的 Library Cache Lock 造成嚴重的效能問題。

解決方法:

1. Apply Patch 33121934 。

2. 升級至 19.14 版本以上， Bug 33121934 於此版本中修復。

3. 將參數 _disable_last_successful_login_time 設定為 TRUE ，停用此功能。

Failed Logon 造成系統資源不足案例

Oracle 版本: 12.1.0.2.0 ， RAC

OS 版本: AIX 5.3

問題描述:

資料庫發生異常，檢查 DB alert log 發現 ORA-27300 、 ORA-27301 等訊息 :

在 AIX 環境上出現 Not enough space 往往指的是系統記憶體不足的訊息，由 alert log 顯示由於系統資源不足導致 Oracle 無法產生新的 process 而造成異常。

問題分析:

由系統 NMON 等監控發現消耗系統資源的，仍然多數為 oracle process ，查詢 gv$resource_limit 發現 oracle process 數量異常 :

平常時段的 process 數量最多大約 500 多個，到了事件時段 process 數量異常上升到了 2000 多個。

由 Awr Report 的 Top Event 顯示大量 Failed Logon Delay 的等待事件 :

資料庫的 Failed Logon Delay 機制原本是為了避免過度的 Logon 造成 Logon Strom / Connection Strom ，但在 12.1 的版本仍然有 Bug 19867671 - "library cache lock" caused by wrong password login ，在 Logon Delay 的過程中產生了 library cache lock 導致 session 無法及時退出，最終導致 process 越積越多，由 ash 也顯示出當時資料庫也出現了不少 library cache lock 等待 :

查詢 audit log 找出 Failed Logon 的來源 :

SQL> select os_username,userhost,dbusername,CLIENT_PROGRAM_NAME program,event_timestamp,action_name,return_code   from unified_audit_trail a  where a.return_code=1017   and a.unified_audit_policies ='ORA_LOGON_FAILURES'   and a.event_timestamp between to_date('2021-12-18 08:30:00','yyyy-mm-dd hh24:mi:ss') and to_date('2021-12-18 08:44:59','yyyy-mm-dd hh24:mi:ss')

Failed Logon 來自於 AP Server ，判斷應為 DB 的 AP User 改密碼而 AP Server 端沒有更改到導致 Logon Failed ，再加上 AP 的 retry 機制最終導致問題。

解決方法:

可以從幾個面向來解決此問題 :

1. 資料庫升級至 12.2 以上版本，Bug 19867671 於 12.2 版本以上修復。

2. 於 user profile 設置 FAILED_LOGIN_ATTEMPTS 次數，超過次數的 Failed Logon 則將 user lock 。

3. 設置隱藏參數 _sys_logon_delay 為 0 ，表示停用 Failed Logon Delay 機制，不過更改隱藏參數是比較不建議的做法。

mount point 權限造成的安裝問題

Oracle 版本: 19.3.0.0

OS 版本: AIX 7.2

問題描述:

在 AIX 7.2 搭配 vxfs 的環境下安裝 19.3 grid ，執行 gridSetup.sh 後出現以下錯誤 :

/u01/19.0.0/grid $ ./gridSetup.sh  Exception in thread "Attach API initializer" java.lang.ExceptionInInitializerError         at java.lang.J9VMInternals.ensureError(J9VMInternals.java:146)         at java.lang.J9VMInternals.recordInitializationFailure(J9VMInternals.java:135)         at java.nio.file.FileSystems.getDefault(FileSystems.java:187)         at java.nio.file.Paths.get(Paths.java:95)         at com.ibm.tools.attach.target.IPC.checkOwnerAccessOnly(IPC.java:142)         at com.ibm.tools.attach.target.TargetDirectory.createMyDirectory(TargetDirectory.java:90)         at com.ibm.tools.attach.target.AttachHandler.createFiles(AttachHandler.java:207)         at com.ibm.tools.attach.target.AttachHandler.initialize(AttachHandler.java:306)         at com.ibm.tools.attach.target.AttachHandler.run(AttachHandler.java:239) Caused by: java.lang.RuntimeException: default directory must be absolute

由 java Initialize Error ，懷疑是否 java 的問題，執行 find . -name java 產生了權限錯誤 :

/u01/19.0.0/grid $ find . -name java fch: The file access permissions do not allow the specified action.

檢查了 /u01 權限，看起來是沒有問題的 :

# ls –ld /u01 drwxr-x---   67 grid   oinstall       8192 Jun 05 16:32 /u01

問題分析:

在 AIX 環境上不僅要檢查目錄的權限，還要檢查 underlying mount point permissions ，將 /u01 umount 之後再檢查權限 :

# umount /u01 # ls –ld /u01 drwxr-x---   67 root  root       8192 Jun 05 16:32 /u01

發現原本 /u01 的權限為 root ，這邊導致問題所在。

解決方法:

更改 /u01 的權限之後再重新 mount 即可 :

# chown grid:oinstall /u01 # ls –ld /u01 drwxr-x---   67 grid  oinstall       8192 Jun 05 16:32 /u01 # mount /dev/vx/dsk/oradg/oravol /u01

foreign key 造成 TM contention 案例

Oracle 版本: 12.1.0.2.0

OS 版本: AIX 7.1

問題描述:

對 Table 進行 delete 動作時卡住無法成功執行。

SQL> SQL> delete regions where region_id=7;  (毫無反應)

問題分析:

從 v$session 可以發現等待事件為 enq: TM – contention 且被 sid 為 80 的 session 咬住 :

不過 sid 為 80 的 session 當前狀態為 SQL*Net message from client ，是一個 idle 的事件，無法確定 sid 80 是執行了什麼導致 TM – contention ，進一步使用 hanganalyze 分析 :

SQL> oradebug setospid 5436 Oracle pid: 52, Unix process pid: 5436, image: oracle@orcl (TNS V1-V3) SQL> oradebug hanganalyze 3

由 trace 當中發現等待的是 mode 4 的 TM lock :

mode 4 的 TM lock 一般與 foreign key 有關，當具有 primary key 的 table 進行 update / delete 操作時，需要先確認此筆資料是否存在於 child table ，此時若 child table 上的 foreign key 欄位沒有建立 index ，則會對 child table 進行 Share mode 的 DML lock ，也就是 mode 4 lock ，由 trace 顯示無法完成 mode 4 lock ，所以整個語法無法完成，有可能此時 child table 進行了尚未 commit 的 DML 操作所以發生了 TM – contention 。

檢查 reference 到 REGIONS 的所有 table :

檢查 COUNTRIES 與 PEO 的 foreign key 欄位是否缺少 index :

CONTRIES 與 PEO 作為 foreign key 欄位的 REGION_ID 都沒有建立 index 。

解決方法:

將所有 foreign key 欄位建立 index 即可 :

SQL> create index ix_countries on countries(region_id); SQL> create index ix_peo on peo(region_id);