有时工作需要，无法快速关闭数据库的因素：

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，需要回滚，占用资源导致 的问题，因此需要先释放资源，完成数据库的关闭。