외부에서 오라클 db가 접속 안될 때 확인사항

2019. 11. 19. 17:42 내맘대로긍정

프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 11.2.0.4

방법 : 외부에서 오라클 db가 접속 안될 때 확인사항

상황 설명 : 기존에 DB서버(192.168.0.30)에 오라클db가 구성되어 있고

AP서버(192.168.0.20)에서도 DB서버로 연결해서 사용중인 상태

하지만 사용자A(192.168.0.40)의 개인 PC에서만 접속이 제한되고 있는 상태에서

접속을 위해 확인해봐야 할 사항들 정리

DB서버의 리스너 확인

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

$ lsnrctl status LISTENER1527
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2019 17:10:59
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1527)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1527
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-NOV-2019 17:05:19
Uptime                    0 days 0 hr. 5 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1527)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1527)))
Services Summary...
Service "ORCL11" has 1 instance(s).
  Instance "ORCL11", status READY, has 1 handler(s) for this service...
The command completed successfully

1527포트로 리스너가 정상 기동중인 상태

사용자A의 PC에서 tnsnames.ora 등록

1
2
3
4
5
6
7
8
9
10
11
12

$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
ORCL11_NPORT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1527))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL11)
    )
  )

사용자A -> DB서버로 tnsping 확인

1
2
3
4
5
6
7
8
9
10
11
12

$ tnsping ORCL11_NPORT
 
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 23-NOV-2019 17:20:50
 
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL11)))
TNS-12543: TNS:destination host unreachable

TNS-12543: TNS:destination host unreachable 발생

DB서버에서 방화벽 확인(테스트 서버라 중지함)

(실제 업무환경에서는 방화벽에 포트, IP 등록으로 해결)

1
2
3
4

# /etc/init.d/iptables stop
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]

사용자A -> DB서버로 tnsping 재확인

1
2
3
4
5
6
7
8
9
10
11
12

$ tnsping ORCL11_NPORT
 
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 23-NOV-2019 17:28:51
 
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL11)))
TNS-12547: TNS:lost contact

이번엔 TNS-12547: TNS:lost contact 발생

DB서버에서 해당 시간 리스너 로그 확인

1
2
3
4
5
6
7

$ tail -300f /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527/trace/listener1527.log 
Sun Nov 23 17:28:51 2019
Incoming connection from 192.168.0.40 rejected 
23-NOV-2019 17:28:51 * 12546
TNS-12546: TNS:permission denied
 TNS-12560: TNS:protocol adapter error
  TNS-00516: Permission denied

Permission denied 발생

사용자A가 연결 시도중일 때 DB서버에서 netstat -an 확인

1
2
3
4
5
6
7
8
9

$ while true
do
netstat -an | grep "1527"
sleep 1
done
 
tcp        0      0 192.168.0.30:46077          192.168.0.30:1527           ESTABLISHED 
tcp        0      0 :::1527                     :::*                        LISTEN      
tcp        0      0 ::ffff:192.168.0.30:1527    ::ffff:192.168.0.30:46077   ESTABLISHED

DB서버에서 sqlnet.ora 파일 확인

1
2
3
4

$ vi $ORACLE_HOME/network/admin/sqlnet.ora
TCP.VALIDNODE_CHECKING = YES
TCP.INVITED_NODES=(192.168.0.10,192.168.0.20,192.168.0.30)
#TCP.EXCLUDED_NODES =()

특정 IP 외에는 차단하게끔 정책이 적용되어 있음

사용자A의 PC IP(192.168.0.40)도 IP목록에 추가

1
2
3
4

$ vi $ORACLE_HOME/network/admin/sqlnet.ora
TCP.VALIDNODE_CHECKING = YES
TCP.INVITED_NODES=(192.168.0.10,192.168.0.20,192.168.0.30,192.168.0.40)
#TCP.EXCLUDED_NODES =()

DB서버 리스너 'reload' 또는 재기동

1
2
3
4
5
6
7
8
9
10
11

$ lsnrctl
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2019 17:35:14
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Welcome to LSNRCTL, type "help" for information.
 
LSNRCTL> reload LISTENER1527
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1527)))
The command completed successfully

DB서버 리스너 reload 또는 '재기동'

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
37
38
39
40

$ lsnrctl stop LISTENER1527
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2019 17:36:36
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1527)))
The command completed successfully
 
$ lsnrctl start LISTENER1527
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2019 17:36:44
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Starting /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1527)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1527)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1527)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1527
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-NOV-2019 17:36:44
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1527)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1527)))
The listener supports no services
The command completed successfully

사용자A -> DB서버로 tnsping 시도

1
2
3
4
5
6
7
8
9
10
11
12

$ tnsping ORCL11_NPORT
 
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 23-NOV-2019 17:40:55
 
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL11)))
OK (10 msec)

tnsping 성공함

DB서버에서 해당 시간 리스너 로그 확인

1
2
3

$ tail -300f /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527/trace/listener1527.log 
Sun Nov 23 17:40:55 2019
23-NOV-2019 17:40:55 * ping * 0

정상적으로 ping 나타남

사용자A PC에서 DB서버로 접속

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

$ sqlplus system/oracle@ORCL11_NPORT
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 23 17:46:18 2019
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select instance_name, version, status from v$instance;
 
INSTANCE_NAME     VERSION       STATUS
---------------- ----------------- ------------
ORCL11         11.2.0.4.0       OPEN

정상적으로 접속됨

DB서버에서 해당 시간 리스너 로그 확인

1
2
3
4

Sun Nov 23 17:46:18 2019
23-NOV-2019 17:46:18 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL11)(CID=(PROGRAM=sqlplus)(HOST=ORACLE12)(USER=oracle))) 
* (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.40)(PORT=15600)) * establish * ORCL11 * 0
23-NOV-2019 17:46:18 * service_update * ORCL11 * 0

정상적인 로그가 나옴

참고용

이외에 체크해볼만한 부분

1. 클라이언트, DB 간 호환성 체크

참고자료 : Client / Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1)

2. 다른 클라이언트에서는 제대로 연결되는지 여부 체크(tnsping, sqlplus 등)

참고자료 : 외부에서 오라클 db가 접속 안될 때 확인사항(본문) ( https://positivemh.tistory.com/473 )

3. DB에서 v$resource_limit을 확인하여 processes 가 최대값에 도달한건 아닌지 확인

참고자료 : 오라클 세션수 및 프로세스수 변경하기 ( https://positivemh.tistory.com/318 )

4. 로컬 리스너(local_listener) 설정이 제대로 되어있는지 확인

참고자료 : 오라클 다른 포트 리스너 설정(로컬리스너 설정)(RAC포함) ( https://positivemh.tistory.com/458 )

5. /etc/hosts 에 hostname이나 ip가 제대로 들어가 있는지, 권한은 충분한지 확인

참고자료 : 오라클 19c /etc/hosts 권한 부족시 발생하는 문제 ( https://positivemh.tistory.com/662 )

참고자료 : 오라클 11g R2 /etc/hosts 권한 부족시 발생하는 문제 ( https://positivemh.tistory.com/663 )

6. DB 서버의 cpu나 memory 가 충분한지 확인

참고자료 : Oracle Linux 6 서버 cpu, memory 사용량 확인 ( https://positivemh.tistory.com/941 )

7. 접속 지연시 리스너 log on/off 시 속도 차이 확인

참고자료 : 오라클 11g R2 리스너 로그 관리 정리 방법 ( https://positivemh.tistory.com/172 )

8. /etc/resolv.conf의 dns 연결 정보가 정상인지 확인

참고자료 : 오라클 19c 클라이언트 서버의 /etc/resolv.conf 의 잘못된 dns 주소로 인한 sqlplus tns 연결 지연 테스트 ( https://positivemh.tistory.com/991 )

참조 :

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 .bash_profile 설정 (0)	2020.01.07
오라클 Table Compress 기능 중 oltp와 advanced 옵션 차이점 (0)	2019.11.22
오라클 다른 포트 리스너 설정(로컬리스너 설정)(RAC포함) (0)	2019.10.17
오라클 30gb 정통 export import 테스트, 시간 측정 (0)	2019.10.14
데이터 펌프 작업 모니터링 쿼리 (2)	2019.02.26

내맘대로긍정

박영민 OracleDBA@kakao.com

Category

Menu

Comment

Link