반응형
오라클 서버 클라이언트 접속 및 통신 이슈
클라이언트 오라클 서버 접속 관련하여 문제 발생 및 ORA-28040
| SERVER IP(Linux) | 192.168.56.103 |
| Client IP(Windows) | 192.168.56.1 |
| Oracle Port | 1521 |
확인 및 조치 사항
| Server 클라이언트 |
| netstat -an 1 | findstr <서비스IP> |
| Client 서버 |
| netstat -an | grep 1521 |
조치사항
| Firewall Configuration |
| firewalld 서비스 상태 확인. systemctl status firewalld.service firewalld 서비스 중지. systemctl stop firewalld.service firewalld 서비스 비활성화. systemctl disable firewalld.service firewalld 서비스 상태 확인. systemctl status firewalld.service |
| SQLNET.ORA 파일 수정 |
| vi /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) SQLNET.ALLOWED_LOGON_VERSION_SERVER=9 SQLNET.ALLOWED_LOGON_VERSION_CLIENT=9 |
확인방법
| #비정상 확인 |
| netstat -an | grep 1521 tcp 0 0 192.168.56.103:27441 192.168.56.103:1521 ESTABLISHED tcp 0 0 192.168.56.103:27607 192.168.56.103:1521 ESTABLISHED tcp 0 0 192.168.56.103:27433 192.168.56.103:1521 ESTABLISHED tcp6 0 0 :::1521 :::* LISTEN --비정상 상태 tcp6 0 0 192.168.56.103:1521 192.168.56.103:27433 ESTABLISHED tcp6 0 0 192.168.56.103:1521 192.168.56.103:27715 TIME_WAIT tcp6 0 0 192.168.56.103:1521 192.168.56.103:27607 ESTABLISHED tcp6 0 0 192.168.56.103:1521 192.168.56.103:27441 ESTABLISHED unix 2 [ ACC ] STREAM LISTENING 37455 /var/tmp/.oracle/sEXTPROC1521 unix 3 [ ] STREAM CONNECTED 61521 /run/systemd/journal/stdout |
| #정상 확인 |
| netstat -an | grep 1521 tcp 0 0 192.168.56.103:1521 0.0.0.0:* LISTEN --정상상태 tcp 0 0 192.168.56.103:1521 192.168.56.103:27726 ESTABLISHED tcp 0 0 192.168.56.103:1521 192.168.56.103:27725 TIME_WAIT tcp 0 0 192.168.56.103:27726 192.168.56.103:1521 ESTABLISHED tcp 0 0 192.168.56.103:1521 192.168.56.103:27724 TIME_WAIT tcp 0 0 192.168.56.103:1521 192.168.56.103:27729 TIME_WAIT tcp 0 0 192.168.56.103:1521 192.168.56.103:27730 TIME_WAIT tcp 0 0 192.168.56.103:1521 192.168.56.103:27727 TIME_WAIT tcp 0 0 192.168.56.103:1521 192.168.56.103:27728 TIME_WAIT tcp6 0 0 192.168.56.103:1521 192.168.56.103:27607 TIME_WAIT tcp6 0 0 192.168.56.103:1521 192.168.56.103:27719 TIME_WAIT tcp6 0 0 192.168.56.103:1521 192.168.56.103:27441 TIME_WAIT tcp6 0 0 192.168.56.103:1521 192.168.56.103:27718 TIME_WAIT unix 2 [ ACC ] STREAM LISTENING 291912 /var/tmp/.oracle/sEXTPROC1521 unix 3 [ ] STREAM CONNECTED 61521 /run/systemd/journal/stdout |
| #TCP 덤프 확인 |
| [root@ole7ora19casm bin]# tcpdump -nn -i enp0s8 port 1521 tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on enp0s8, link-type EN10MB (Ethernet), capture size 262144 bytes |
| #비정상 = CLIENT -> SERVER -> X |
| 13:47:53.232282 IP 192.168.56.1.50919 > 192.168.56.103.1521: Flags [S], seq 2428581055, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 13:47:54.232313 IP 192.168.56.1.50919 > 192.168.56.103.1521: Flags [S], seq 2428581055, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 13:47:56.232718 IP 192.168.56.1.50919 > 192.168.56.103.1521: Flags [S], seq 2428581055, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 13:48:00.233574 IP 192.168.56.1.50919 > 192.168.56.103.1521: Flags [S], seq 2428581055, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 13:48:08.234446 IP 192.168.56.1.50919 > 192.168.56.103.1521: Flags [S], seq 2428581055, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 13:54:51.961836 IP 192.168.56.1.51038 > 192.168.56.103.1521: Flags [S], seq 2679325827, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 13:54:52.962533 IP 192.168.56.1.51038 > 192.168.56.103.1521: Flags [S], seq 2679325827, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 13:54:54.964034 IP 192.168.56.1.51038 > 192.168.56.103.1521: Flags [S], seq 2679325827, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 |
| #정상 = CLIENT -> SERVER -> CLIENT 13:54:58.964832 IP 192.168.56.1.51038 > 192.168.56.103.1521: Flags [S], seq 2679325827, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 13:55:06.965138 IP 192.168.56.1.51038 > 192.168.56.103.1521: Flags [S], seq 2679325827, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 13:56:45.803404 IP 192.168.56.1.51201 > 192.168.56.103.1521: Flags [S], seq 2291961390, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0 13:56:45.803432 IP 192.168.56.103.1521 > 192.168.56.1.51201: Flags [S.], seq 2779303454, ack 2291961391, win 29200, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 0 13:56:45.803654 IP 192.168.56.1.51201 > 192.168.56.103.1521: Flags [.], ack 1, win 8212, length 0 13:56:45.814599 IP 192.168.56.1.51201 > 192.168.56.103.1521: Flags [P.], seq 1:88, ack 1, win 8212, length 87 13:56:45.814626 IP 192.168.56.103.1521 > 192.168.56.1.51201: Flags [.], ack 88, win 229, length 0 13:56:45.815870 IP 192.168.56.103.1521 > 192.168.56.1.51201: Flags [P.], seq 1:66, ack 88, win 229, length 65 13:56:45.816812 IP 192.168.56.1.51201 > 192.168.56.103.1521: Flags [F.], seq 88, ack 66, win 8212, length 0 13:56:45.817369 IP 192.168.56.103.1521 > 192.168.56.1.51201: Flags [F.], seq 66, ack 89, win 229, length 0 13:56:45.818588 IP 192.168.56.1.51201 > 192.168.56.103.1521: Flags [.], ack 67, win 8212, length 0 |
반응형
댓글