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

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서버의 리스너 확인

$ 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 등록

$ 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 확인

$ 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 등록으로 해결)

# /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 재확인

$ 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서버에서 해당 시간 리스너 로그 확인

$ 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 확인

$ 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 파일 확인

$ 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목록에 추가

$ 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' 또는 재기동

$ 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 또는 '재기동'

$ 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 시도

$ 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서버에서 해당 시간 리스너 로그 확인

$ 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서버로 접속

$ 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서버에서 해당 시간 리스너 로그 확인

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 )

 

 

참조 :