shutdown immediate 持久無法關閉資料庫之解決方案

有時工作需要，無法快速關閉資料庫的因素：

1、連線的客戶端程式數量

2、未提交的事務佔用的UNDO BLOCK數量

3、長時間執行的會話

4、SMON程式清理臨時段的速度

我本次遇到的問題是，執行了大量的sql， 未提交的事務佔用的UNDO BLOCK數量的原因，

檢視預警日誌檔案（alert_sid.log）的位置

SQL> show parameter dump

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

background_core_dump      string partial

background_dump_dest      string /app/oracle/product/19.0.0/dbhtest

ome_1/rdbms/log

core_dump_dest      string /app/oracle/diag/rdbms/test

/eastods/cdump

max_dump_file_size      string unlimited

shadow_core_dump      string partial

user_dump_dest      string /app/oracle/product/19.0.0/dbh

ome_1/rdbms/log

SQL> 

檢視alert日誌如下：/app/oracle/diag/rdbms/test/alert_test.log 以下資訊是樣例

2023-10-24T07:36:13.600147+08:00

Shutting down ORACLE instance (immediate) (OS id: 6904)

2023-10-24T07:36:15.986718+08:00

Stopping background process SMCO

2023-10-24T07:36:18.111748+08:00

Shutting down instance: further logons disabled

2023-10-24T07:36:57.619859+08:00

Stopping background process CJQ0

2023-10-24T07:37:04.839242+08:00

Killed process oracle@dlidcsjbzhdbtest.aeonlife.com.cn (QM02) with pid is 86, OS pid 23619

Stopping background process MMNL

2023-10-24T07:37:04.920188+08:00

Process termination requested for pid 23619 [source = rdbms], [info = 2] [request issued by pid: 6904, uid: 54321]

2023-10-24T07:37:07.838995+08:00

Stopping background process MMON

2023-10-24T07:37:14.840298+08:00

License high water mark = 89

2023-10-24T07:37:48.120219+08:00

Thread 1 advanced to log sequence 40380 (LGWR switch)

  Current log# 3 seq# 40380 mem# 0: /app/oracle/oradata/test/redo03.log

2023-10-24T07:40:21.124437+08:00

Thread 1 advanced to log sequence 40381 (LGWR switch)

  Current log# 1 seq# 40381 mem# 0: /app/oracle/oradata/test/redo01.log

2023-10-24T07:42:13.956519+08:00

Active process 25052 user 'oracle' program 'oracle@test.com.cn (TNS V1)', waiting for 'db file sequential read'

Active process 1116 user 'oracle' program 'oracle@test.com.cn', waiting for 'db file sequential read'

SHUTDOWN: waiting for active calls to complete.test

2023-10-24T07:43:33.283164+08:00

Thread 1 advanced to log sequence 40382 (LGWR switch)

  Current log# 2 seq# 40382 mem# 0: /app/oracle/oradata/test/redo02.log

2023-10-24T07:47:36.568838+08:00

手動kill掉這些資料庫連執行緒式：紅色字型的地方

eg：

[test]$kill -9 25052 

另外如果程式之多可以使用：

ps aux |grep "LOCAL=NO" |awk '{printf "%s ", $2}' |xargs kill -91

或

ps -ef|grep $ORACLE_SID|grep -v ora_|grep LOCAL=NO|awk '{print $2}'|xargs kill1

命令

隨機再次關閉資料庫：

SQL>  shutdown immediate

Database closed.

Database dismounted.

總結：

這是由於程式執行大量SQL，需要回滾，佔用資源導致 的問題，因此需要先釋放資源，完成資料庫的關閉。