SHUTDOWN: Waiting for active calls to complete

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

에러 : SHUTDOWN: Waiting for active calls to complete 

db shutdown immediate 후 바로 종료되지 않고 alert log에 발생하며 hang 이 걸림

$ cd /oracle/app/oracle/diag/rdbms/orcl11/ORCL11/trace
$ vi alert_ORCL11.log
Fri Apr 09 21:58:42 2021
Stopping background process SMCO
SHUTDOWN: Waiting for active calls to complete 

 

 

해결 방법 : 외부 연결 강제 차단, db abort 종료

방법 1. LOCAL=NO 프로세스 종료

외부 연결 확인

$ ps -ef|grep LOCAL=NO
oracle   30697 30344  0 21:58 pts/0    00:00:00 grep LOCAL=NO
oracle 4331 1 0 21:58  ? 0:00 oracleorcl11 (LOCAL=NO)
oracle 4333 1 0 21:58  ? 0:00 oracleorcl11 (LOCAL=NO)
oracle 4335 1 0 21:58  ? 0:00 oracleorcl11 (LOCAL=NO)
oracle 4337 1 0 21:58  ? 0:00 oracleorcl11 (LOCAL=NO)
oracle 4339 1 0 21:58  ? 0:00 oracleorcl11 (LOCAL=NO)
oracle 4341 1 0 21:58  ? 0:00 oracleorcl11 (LOCAL=NO)
oracle 4343 1 0 21:58  ? 0:00 oracleorcl11 (LOCAL=NO)
oracle 4345 1 0 21:58  ? 0:00 oracleorcl11 (LOCAL=NO)
oracle 4347 1 0 21:58  ? 0:00 oracleorcl11 (LOCAL=NO)
.
.
$ ps -ef|grep LOCAL=NO | wc -l
424

약 400개의 외부 연결(LOCAL=NO)가 존재함

1분에 20개 정도씩 없어지고는 있지만 너무 느림

 

 

kill -9 {pid} 로 모두 강제 종료

$ kill -9 4331
$ kill -9     4331
$ kill -9     4333
$ kill -9     4335
$ kill -9     4337
$ kill -9     4339
$ kill -9     4341
$ kill -9     4343
$ kill -9     4345
$ kill -9     4347
.
.

 

 

LOCAL=NO 프로세스 확인

$ ps -ef|grep LOCAL=NO
oracle   30719 30344  0 21:58 pts/0    00:00:00 grep LOCAL=NO
$ ps -ef|grep LOCAL=NO | wc -l
1

정상적으로 모두 종료됨

 

 

잠시 뒤 alert log 확인(약 5분뒤)

Fri Apr 09 22:05:43 2021
Stopping background process CJQ0
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
License high water mark = 5
ALTER DATABASE CLOSE NORMAL
Fri Apr 09 22:05:46 2021
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Apr 09 22:05:46 2021
Shutting down archive processes
Archiving is disabled
Fri Apr 09 22:05:46 2021
ARCH shutting down
ARC3: Archival stopped
Fri Apr 09 22:05:46 2021
ARCH shutting down
ARC2: Archival stopped
Fri Apr 09 22:05:46 2021
ARCH shutting down
ARC1: Archival stopped
Fri Apr 09 22:05:46 2021
ARCH shutting down
ARC0: Archival stopped
Thread 1 closed at log sequence 25
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Fri Apr 09 22:05:48 2021
Stopping background process VKTM
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Fri Apr 09 22:05:51 2021
Instance shutdown complete

db가 정상적으로 종료됨

 

 

방법 2. db abort 후 restrict 기동 shutdown normal 종료

SQL> 
shutdown abort
startup restrict
shutdown normal

hang 걸린 db sqlplus 접속 후 shutdown abort 후 

startup restrict로 기동 후 

다시 shutdown normal로 종료

 

 

원인 : 좀비 세션 또는 종료되지 않은 세션 문제

이런 문제가 자주 발생하는 경우

db를 shutdown 하기 전에 v$session 또는 v$process에서 active 프로세스가 많은지 확인하는것이 좋음

 

 

db 종료전 확인한 세션 수

SQL> select status, count(status) from v$session group by status;
 
STATUS     COUNT(STATUS)
-------- -------------
INACTIVE         1800
ACTIVE            1020

현재 db의 파라미터 processes 값(3000)에 근접함

 

 

참조 : 1039389.6, 416658.1

docs.oracle.com/cd/E11882_01/server.112/e25494/start.htm

positivemh.tistory.com/711