OS 환경 : Oracle Linux 7.9 (64bit)
DB 환경 : Oracle Database 19.3.0.0
에러 : ORA-27086: unable to lock file - already in use
기동시 발생하는 에러 메세지
db 기동
1
2
3
4
5
|
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
|
db가 nomount 에서 mount 로 올라가지 못함
alert log 확인
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
2024-05-23T22:09:01.994505+09:00
ALTER DATABASE MOUNT
2024-05-23T22:09:02.023139+09:00
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/ORA19/app/oracle/oradata/ORACLE19/control02.ctl'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 2002
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/ORA19/app/oracle/oradata/ORACLE19/control01.ctl'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 2002
ORA-205 signalled during: ALTER DATABASE MOUNT...
2024-05-23T22:09:02.031108+09:00
Errors in file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_mz00_20186.trc:
ORA-00202: control file: '/ORA19/app/oracle/oradata/ORACLE19/control01.ctl'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 2002
2024-05-23T22:09:02.081690+09:00
Errors in file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_mz00_20186.trc:
ORA-00202: control file: '/ORA19/app/oracle/oradata/ORACLE19/control02.ctl'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 2002
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/ORA19/app/oracle/oradata/ORACLE19/control01.ctl'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 2002
|
해결 방법 : 컨트롤파일을 사용중인 프로세스 종료 후 기동
db 종료
1
2
3
|
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
|
pmon 확인
1
2
|
$ ps -ef|grep pmon
oracle 29502 15682 0 22:29 pts/1 00:00:00 grep --color=auto pmon
|
pmon이 실행중이지 않음
정상적으로 종료된듯함
현재 컨트롤파일이 lock 이 걸려있어 제대로 기동을 하지 못하는 상태임
lock을 풀어줘야 정상적으로 기동됨
컨트롤파일 경로로 이동
1
2
3
4
5
6
7
8
9
10
|
$ cd /ORA19/app/oracle/oradata/ORACLE19/
$ ls -al
total 1557228
drwxr-x--- 2 oracle oinstall 134 May 23 22:21 .
drwxr-x--- 4 oracle oinstall 45 May 10 08:59 ..
-rw-r----- 1 oracle oinstall 10829824 May 23 16:47 control01.ctl
-rw-r----- 1 oracle oinstall 10829824 May 23 16:47 control02.ctl
-rw-r----- 1 oracle oinstall 524288512 May 23 16:47 redo01.log
-rw-r----- 1 oracle oinstall 524288512 May 23 16:47 redo02.log
-rw-r----- 1 oracle oinstall 524288512 May 23 16:47 redo03.log
|
lsof 명령으로 컨트롤파일을 사용중인 프로세스 확인
*lsof는 list open files의 약자로 시스템에서 열려있는 파일에 대한 정보를 출력해주는 명령임
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ lsof | fgrep control01.ctl
lsof: WARNING: can't stat() fuse.gvfsd-fuse file system /run/user/0/gvfs
Output information may be incomplete.
ora_ckpt_ 2002 oracle 256uW REG 253,0 10829824 4844737 /ORA19/app/oracle/oradata/ORACLE19/control01.ctl
ora_mmon_ 2030 oracle 258u REG 253,0 10829824 4844737 /ORA19/app/oracle/oradata/ORACLE19/control01.ctl
ora_m000_ 2048 oracle 266u REG 253,0 10829824 4844737 /ORA19/app/oracle/oradata/ORACLE19/control01.ctl
ora_cjq0_ 2074 oracle 257u REG 253,0 10829824 4844737 /ORA19/app/oracle/oradata/ORACLE19/control01.ctl
$ lsof | fgrep control02.ctl
lsof: WARNING: can't stat() fuse.gvfsd-fuse file system /run/user/0/gvfs
Output information may be incomplete.
ora_ckpt_ 2002 oracle 257uW REG 253,0 10829824 4844738 /ORA19/app/oracle/oradata/ORACLE19/control02.ctl
ora_mmon_ 2030 oracle 259u REG 253,0 10829824 4844738 /ORA19/app/oracle/oradata/ORACLE19/control02.ctl
ora_m000_ 2048 oracle 267u REG 253,0 10829824 4844738 /ORA19/app/oracle/oradata/ORACLE19/control02.ctl
ora_cjq0_ 2074 oracle 258u REG 253,0 10829824 4844738 /ORA19/app/oracle/oradata/ORACLE19/control02.ctl
|
확인결과 ckpt, mmon, m000, cjq0 프로세스가 컨트롤파일을 사용중임
분명 shutdown immediate 명령으로 db를 내렸지만 이 프로세스들이 컨트롤파일을 잡고 있음
오라클 관련 프로세스 확인
1
2
3
4
5
6
7
8
9
|
$ ps -ef | grep ora_ | grep -v grep
oracle 1966 1 0 16:40 ? 00:00:00 ora_vktm_oracle19
oracle 1996 1 0 16:40 ? 00:00:00 ora_dia0_oracle19
oracle 2002 1 0 16:40 ? 00:00:00 ora_ckpt_oracle19
oracle 2030 1 0 16:40 ? 00:00:01 ora_mmon_oracle19
oracle 2036 1 0 16:40 ? 00:00:00 ora_mmnl_oracle19
oracle 2048 1 0 16:40 ? 00:00:00 ora_m000_oracle19
oracle 2052 1 0 16:40 ? 00:00:00 ora_m001_oracle19
oracle 2074 1 0 16:40 ? 00:00:00 ora_cjq0_oracle19
|
일부 프로세스들이 아직 살아있음
해당 프로세스들 kill
1
2
3
4
5
6
7
8
9
|
$ kill -9 1966
$ kill -9 1966
$ kill -9 1996
$ kill -9 2002
$ kill -9 2030
$ kill -9 2036
$ kill -9 2048
$ kill -9 2052
$ kill -9 2074
|
오라클 관련 프로세스 재확인
1
2
|
$ ps -ef | grep ora_ | grep -v grep
(없음)
|
정상적으로 kill 됨
lsof 확인
1
2
3
4
5
6
7
|
$ lsof | fgrep control01.ctl
lsof: WARNING: can't stat() fuse.gvfsd-fuse file system /run/user/0/gvfs
Output information may be incomplete.
$ lsof | fgrep control02.ctl
lsof: WARNING: can't stat() fuse.gvfsd-fuse file system /run/user/0/gvfs
Output information may be incomplete.
|
컨트롤파일을 잡고 있는 프로세스가 없음
db 기동 시도(nomount)
1
2
3
4
5
6
7
8
|
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 520093696 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7876608 bytes
|
nomount 잘됨
db mount로 변경
1
2
3
|
SQL> alter database mount;
Database altered.
|
정상적으로 mount 모드로 됨
db open으로 변경
1
2
3
|
SQL> alter database open;
Database altered.
|
정상적으로 open 됨
원인 : 컨트롤파일을 종료되지 않은 다른 프로세스가 잡고 있어서 발생한 문제
컨트롤파일을 종료되지 않은 다른 프로세스가 잡고 있어서 발생한 문제
이전 테스트에서 오라클 모든 프로세스를 강제로 kill -9 명령으로 kill 한뒤 startup force 등
abort로 내리는 테스트를 해서 문제가 발생했던것으로 추정됨
참조 :
https://groups.google.com/g/comp.databases.oracle.server/c/JlhFeCW83Jo?pli=1
https://positivemh.tistory.com/606
'ORACLE > Trouble Shooting' 카테고리의 다른 글
ORA-63805: Maximum number of tuples in Table Value Constructor exceeded (0) | 2024.06.18 |
---|---|
[DBT-05509] Failed to connect to the specified database (Free). (0) | 2024.04.24 |
ORA-09968: unable to lock file (0) | 2024.04.20 |
ORA-00441: Oracle Free Edition SID violation. Expected:free vs Actual:NON (0) | 2024.04.16 |
ORA-01033: ORACLE initialization or shutdown in progress (0) | 2024.03.28 |