Solve connection time taking long with Inbound Connect Timeout of SQLNET

การแก้ปัญหาเกี่ยวกับการเชื่อมต่อ Database ของ Client ที่ใช้ระยะเวลานาน โดยที่ยังไม่สามารถ authenticate connection ได้

เกี่ยวกับ SQLNET.INBOUND_CONNECT_TIMEOUT

- หน่วยวินาที default = 60

- สำหรับเมื่อ client มีการขอเชื่อมต่อกับเครือข่ายหรือเรียกว่ามีการสร้าง connection request แล้ว แต่ยังไม่สามารถ authenticate connection หรือ

 ไคลเอนต์ไม่สามารถสร้างการเชื่อมต่อ establish a connection และรับรองความถูกต้อง complete authentication ให้เสร็จสิ้นภายในเวลาที่กำหนด นั่นคือ connection ยังไม่ complete

   จนหมดเวลาที่กำหนดใน SQLNET.INBOUND_CONNECT_TIMEOUT แล้ว  database server จะตัดการเชื่อมต่อ เรียกว่า connection time taking long

- ใช้ป้องกัน listener และ Database Server จากการโจมตีของ Denial-of-Service attack ที่ส่ง connection มาจำนวนมากแต่ไม่ถูกใช้หรือไม่มีการปิดจาก client

  โดยตรวจสอบจาก sqlnet.log ว่ามาจาก IP ที่แปลกปลอมหรือไม่

- แสดง error ที่ sqlnet.log file คือ ORA-12170: TNS:Connect timeout occurred

- แสดง error ที่ Client คือ ORA-12547: TNS:lost contact หรือ ORA-12637: Packet gets failed

กำหนดค่า SQLNET.INBOUND_CONNECT_TIMEOUT

--> lsnrctl
LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CSPDDB001)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully

LSNRCTL> set inbound_connect_timeout 120
LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CSPDDB001)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 120
The command completed successfully

ตรวจสอบ SQLNET.INBOUND_CONNECT_TIMEOUT ด้วยการเชื่อมต่อเครื่อง Database ผ่าน port 1521 เพื่อให้เกิดการสร้าง connection request

ที่ Local ดู log
--> tail -f /oracle/diag/tnslsnr/oradb12c/listener1/alert/log.xml

ที่ Client ทดสอบสร้าง connection request ด้วย telnet และปล่อยทิ้งไว้
--> telnet 192.168.1.128 1521

ที่ Local ดู process ผ่าน netstat จะพบว่า listener จับกับ server process ของ client นั้นๆ เพราะ listener ตัวนี้กำหนดให้จับ client process ที่ติดต่อผ่าน port 1521
จะได้ PID = 26181
--> netstat -anp | grep 192.168.1.1
...
....
.....     
tcp        0      0 ::ffff:192.168.1.128:1521   ::ffff:192.168.1.1:9494     ESTABLISHED 26181/tnslsnr

ที่ Local ดู process จะพบ listener ชื่อ listener1
--> ps -ef|grep 26181
oracle    26181      1  0 Nov25 ?        00:00:38 /oracle/product/12.2.0/dbhome_1/bin/tnslsnr listener1 -inherit

ที่ Local ดู log จะพบ error นี้เมื่อเกินเวลาที่กำหนด
TNS-12525: TNS:listener has not received client's request in time allowed
หรือ TNS-12535: TNS:operation timed out
--> tail -f /oracle/diag/tnslsnr/oradb12c/listener1/alert/log.xml
...
....
.....
 type='UNKNOWN' level='16' host_id='oradb12c.localdomain'
 host_addr='192.168.1.128' pid='26181'>
 TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred

Dead Connection Detection (CDC) with Expire Time of SQLNET

เมื่อ client เชื่อมต่อกับ Database แล้ว แต่เกิดปัญหาบางอย่างทำให้ client และ server processes ถูก terminate connection เป็นการเชื่อมต่อในระดับ OS
กระบวนการนี้  process ยังคง running บน server และ session ใน Database อาจจะยังไม่ถูก terminate

Dead Connection Detection

จาก http://www.nazmulhuda.info/dead-connection-detection-resource-limits-v-session-v-process-and-os-processes

- เมื่อ client เชื่อมต่อกับ Database แล้ว แต่เกิดปัญหาบางอย่างทำให้ client และ server processes ถูก terminate connection เป็นการเชื่อมต่อในระดับ OS

- กระบวนการนี้  process ยังคง running บน server และ session ใน Database อาจจะยังไม่ถูก terminate

  โดย session จะมี status คือ INACTIVE ใน V$SESSION

  และ process จะเรียกว่า Shadow Process คือ process ที่ไม่ได้ใช้งาน

- ขั้นตอนการทำงาน

1.หากไคลเอนต์ไม่มี respond กลับไปยัง DCD probe packet

2.กระบวนการฝั่งเซิร์ฟเวอร์ถูกทำเครื่องหมายว่าเป็นการเชื่อมต่อที่ไม่ทำงาน (marked as a dead connection)

    3.PMON ทำการ clean up Database processes และ resources

4.client OS processes ถูก terminate

- ตัวอย่างเหตุการณ์ 

- ขณะที่เครื่องของ user เชื่อมต่อ Database โดยเชื่อมต่อเฉยๆ หรือ run query จบแล้ว แต่ยังไม่จบ transaction 

- เกิดเหตุการณ์  CDC

- ถ้า user มีการ reboot หรือปิดเครื่อง โดยไม่ log off หรือ disconnect จาก Database

- ถ้ามีปัญหาจาก network ระหว่าง client กับ server เช่น การ disable ที่ Network Adapter อันที่กำลังใช้ connect เครื่อง Database

- ไม่เกิดเหตุการณ์  CDC

- ถ้าใช้ tool SSH ต่อ OS และเชื่อมต่อ Database ด้วย SQLPlus แล้วทำการ close tool SSH เลย โดยไม่ disconnect จาก SQLPlus ก่อน จะทำให้ transaction ทำการ rollback

- ถ้าเชื่อมต่อ Database ด้วย SQL Developer แล้วทำการ kill process ของ SQL Developer เลย โดยไม่ disconnect ก่อน จะทำให้ transaction ทำการ rollback

- ขณะที่เครื่องของ user เชื่อมต่อ Database โดยกำลัง run query อยู่

- เกิดเหตุการณ์  CDC

- ถ้า user มีการ reboot หรือปิดเครื่อง โดยไม่ log off หรือ disconnect จาก Database

- ถ้ามีปัญหาจาก network ระหว่าง client กับ server เช่น การ disable ที่ Network Adapter อันที่กำลังใช้ connect เครื่อง Database

- ถ้าใช้ tool SSH ต่อ OS และเชื่อมต่อ Database ด้วย SQLPlus แล้วทำการ close tool SSH เลย โดยไม่ disconnect จาก SQLPlus ก่อน

- ถ้าเชื่อมต่อ Database ด้วย SQL Developer แล้วทำการ kill process ของ SQL Developer เลย โดยไม่ disconnect ก่อน

- ถ้าเชื่อมต่อ Database ด้วย SQLPlus แล้วทำการปิดโปรแกรมทันที โดยไม่ disconnect ก่อน

- ไม่เกิดเหตุการณ์  CDC

- จากเหตุการณ์ต่างๆที่ทำให้เกิดเหตุการณ์  CDC ถ้า query ที่ active อยู่ เป็นลักษณะการหยุดรอด้วยการใช้คำสั่ง dbms_lock.sleep(...) (บางครั้งเกิด CDC ได้)

Database Resource Limits

- สามารถจัดการ Database Resource Limits ด้วยวิธีดังนี้

- กำหนด RESOURCE_LIMIT = TRUE ใน  parameter file (spfile หรือ pfile) เมื่อทำการ startup Database

- สร้าง user profile เพื่อใช้กำหนด resource limit และกำหนดให้กับ user ที่ต้องการ

- PMON สามารถจัดการ session ได้เพียงระดับ Database สามารถดูที่ V$SESSION แต่ไม่สามารถจัดการ process ระดับ OS ดูที่ V$PROCESS

      สังเกตุว่า SQLNET ยังคง send packet ได้ตามปกติจนกว่า session จะ logged off

                 เกิดปัญหาบางอย่างตามมา เช่น ระบบปฏิบัติการอาจถูกใช้ resource แบบสิ้นเปลืองให้กับ abandoned processes (โปรเซสที่ถูกละทิ้ง) ที่ไม่ได้ใช้งานแล้ว

      ทำให้ V$PROCESS ยังมี process นั้นอยู่ แต่ใน V$SESSION ไม่มี session นั้นทำงานด้วย

                 วิธีแก้ไขคือต้อง cleanup OS process ด้วย

- ขั้นตอนการทำงาน

ตัวอย่าง IDLE_TIME

1. เมื่อ resource ถูกใช้เกินที่กำหนดใน IDLE_TIME

2. PMON จะ mark status = SNIPED ดูที่ V$SESSION 

3. PMON ทำการ clean up database resources ของ session

4. PMON ทำการ kill session

Resource Manager

- สามารถจัดการ kill session ที่มีการใช้งานเกินค่าที่กำหนดได้

- PMON สามารถจัดการ session ได้เพียงระดับ Database เหมือน Database Resource Limits

- ขั้นตอนการทำงาน

ตัวอย่าง MAX_IDLE_TIME 

1. เมื่อ session มีสถานะ INACTIVE เกินที่กำหนดใน MAX_IDLE_TIME

2. PMON จะ mark status = KILLED ดูที่ V$SESSION 

3. PMON ทำการ clean up database resources ของ session

4. PMON ทำการ kill session

SQLNET

- สามารถจัดการ CDC ได้ โดยกำหนดระยะเวลา ด้วย parameter เหล่านี้

SQLNET.SEND_TIMEOUT

SQLNET.RECV_TIMEOUT

SQLNET.EXPIRE_TIME 

Firewall Configuration ในส่วนของ TCP keep-alive

- สามารถจัดการ CDC ได้ โดยกำหนดระยะเวลา ด้วย parameter เหล่านี้

  ใน Linux

tcp_keepalive_time

tcp_keepalive_intvl

tcp_keepalive_probes