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);

新增 Listener 造成 ORA-12547 錯誤案例

Oracle 版本: 19.14 , RAC

OS 版本: Linux 7.7

問題描述:

當前 RAC 架構下，對外 Service 所使用的網段為 192.168.49.0/24 ，並以 1521 Port 作為 Service 連線所用，在同樣網段新增一個 Listener 使用 1528 Port 作為 Data Guard 所用 :

[grid@rac19c1 ~]$ srvctl add listener -listener listener_dg -endpoints TCP:1528

在新增完 listener_dg 之後，原本 Client 透過 scan 的連線產生異常，有時候可以正常連線，有時候則產生 ORA-12547: TNS: lost contact 錯誤 :

問題分析:

開啟 SQL Net Trace 對 Client 連線進行 Trace ，於 sqlnet.ora 設置參數如下 :

TRACE_UNIQUE_CLIENT = ON TRACE_LEVEL_CLIENT = 16 TRACE_DIRECTORY_CLIENT = /tmp TRACE_FILE_CLIENT = sqlnettrace.trc TRACE_TIMESTAMP_CLIENT = ON DIAG_ADR_ENABLED = OFF

檢視連線異常時的 trace ，發現使用了 1528 Port 進行連線 :

由於 1528 Port 並沒有開通 Firewall 給 Service 連線所用，因此產生了 ORA-12547 錯誤。

檢視 tnsnames.ora 設定，是使用 scan 連線並且指定 1521 Port ，為何 SQL Net Trace 出現了 1528 Port 的連線 ?

檢查資料庫的 local_listener 參數，發現 1528 Port 被註冊到 local_listener 了 :

由於 local_listener 註冊了 1528 Port ，因此透過 scan 連線時就有可能重新導向為 1528 Port 來連線。

在沒有手動設定 local_listener 的情況下， CRS 自動會將 DB 註冊至 network 1 的網段上，因此在新增一個 1528 Port 的 listener_dg 之後， DB 的 local_listener 也自動將 1528 Port 註冊上去，導致透過 scan的連線異常。

解決方法:

手動設定 local_listener 參數，只綁定 1521 Port ，這樣另一個 1528 Port 就不會自己加進來了 :

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.49.193)(PORT=1521))' sid='orcl19c1'; SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.49.194)(PORT=1521))' sid='orcl19c2';

執行 adrci 出現 No ADR base is set

Oracle 版本: 19.13

OS 版本: Linux 7.5

問題描述:

執行 adrci 時出現 No ADR base is set ， 必須要自行 set base 才能夠看 log :

[oracle@db19c ~]$ adrci ADRCI: Release 19.0.0.0.0 - Production on Fri Mar 22 14:23:14 2024 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. No ADR base is set adrci>

問題分析:

adrci 是藉由 $ORACLE_HOME/log/diag 底下的 adrci_dir.mif 這個檔案來設定 ADR base ，發生 No ADR base is set 的問題是由於 adrci_dir.mif 此檔案不存在所致。

解決方法:

自行建立 adrci_dir.mif 即可:

$ mkdir -p $ORACLE_HOME/log/diag $  printf "%s" "/u01/app/oracle" > $ORACLE_HOME/log/diag/adrci_dir.mif $ chmod 640 $ORACLE_HOME/log/diag/adrci_dir.mif $ adrci ADRCI: Release 19.0.0.0.0 - Production on Fri Mar 22 14:39:29 2024 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. ADR base = "/u01/app/oracle" adrci>