Finding root cause for ORA-08103 through TNS packet analysis

Symptoms

ORA-08103 Object No Longer Exists reported in applicatoin log. Nothing reported in DB alert log.

Server: Oracle 11.2.0.3 RAC
OS: AIX 7.1

Diagnosis

With little information available, I started searching MOS and try to rule out possible causes one by one. For example, I checked that NO DDL was happening when the error was raised. Besides, NO physical or logical corrupt block exist in DB. All other possible causes remain opaque.

Realizing that this methodology would possibly NOT lead to the root cause, I tried another way. After collecting network traffic dump , I spotted the following key packets:

18:19:46.002704 Request, Data(6), Piggy back function follow
18:19:46.003151 Response, Marker(12)
18:19:46.003164 Response, Marker(12)
18:19:46.042660 App Tcp Ack for Marker
18:19:46.952945 Request, Marker(12)  <<<<<< Request should NOT be sent by client
18:19:46:953225 Response, Data(6), Return Status <<<< ORA-08103 error raised

# Error Fetch Call from App
# Tns Data: 0019 0000 0600 0000 0000 1169 0001 0101 010b 0305 0001 0201 0a
# 0019: Packet length – 25
# 0000: Packet checksum
# 06 : Packet type – Data
# 00 : reserved
# 0000: Header checksum
# 0000: Data Flag
# 11 : Piggy back function follow
# 69 : Cursor close all
# 0305: Fetch a row
# Question: Where is the last unfinished query for it to fetch?

2019-10-14 06:19:46.002704 IP 200.31.87.11.49030 > 200.31.122.23.1522: Flags [P.], seq 3114951:3114976, ack 1055703, win 1424, options [nop,nop,TS val 1880685066 ecr 1629796570], length 25
        0x0000:  0000 0c9f f057 0050 56b6 560d 8100 0057  .....W.PV.V....W
        0x0010:  0800 4500 004d 06b1 4000 4006 d298 c81f  ..E..M..@.@.....
        0x0020:  570b c81f 7a17 bf86 05f2 a6ea 0ef2 ece9  W...z...........
        0x0030:  e822 8018 0590 1290 0000 0101 080a 7018  ."............p.
        0x0040:  fa0a 6124 b8da 0019 0000 0600 0000 0000  ..a$............
        0x0050:  1169 0001 0101 010b 0305 0001 0201 0a60  .i.............`
        0x0060:  0d84 e2                                  ...        

# DB response, Marker packet
# Every so often you’ll see a packet of type 12 (0x0C) – this is a Marker packet,
# which is used for interrupting. For example, if the server wishes the client to stop sending data,
# then it will send the client a Marker packet.
# This means DB Server can NOT find which cursor the client was trying to fetch

# Tns Data: 000b 0000 0c00 0000 0100 01
# 000b: Packet length – 11
# 0000: Packet checksum
# 0c : Packet type – marker
# 00 : reserved
# 0000: Header checksum
# 01 : data marker – 1 data byte
# 00 : marker data type, break
# 01 : marker data type, break

2019-10-14 06:19:46.003151 IP 200.31.122.23.1522 > 200.31.87.11.49030: Flags [P.], seq 1055703:1055714, ack 3114976, win 65160, options [nop,nop,TS val 1629796570 ecr 1880685066], length 11
        0x0000:  0000 0c9f f07a 3440 b5f5 e2a5 8100 007a  .....z4@.......z
        0x0010:  0800 4500 003f 5e86 4000 3c06 7ed1 c81f  ..E..?^.@.<.~...
        0x0020:  7a17 c81f 570b 05f2 bf86 ece9 e822 a6ea  z...W........"..
        0x0030:  0f0b 8018 fe88 3417 0000 0101 080a 6124  ......4.......a$
        0x0040:  b8da 7018 fa0a 000b 0000 0c00 0000 0100  ..p.............
        0x0050:  01a3 857e 6d                             ...~m

Cause

This may be a JDBC bug because:
1. client shoud NOT send request WITHOUT specific statements
2. client should NOT send marker request AT ALL

When little information is available from app and DB log, you may try to get network traffic dump and delve into TNS protocol deeply. You may be surprised by the information hidden in the corners.

Bibliography

Repost: Oracle Protocol


http://2014.zeronights.org/assets/files/slides/oracle-database-communication-protocol.pdf


SQL*NET PACKET STRUCTURE: NS PACKET HEADER (文档 ID 1007807.6)

http://www.nyoug.org/Presentations/2008/Sep/Harris_Listening%20In.pdf


OERR: ORA-8103 “Object No Longer Exists” Master Note / Troubleshooting, Diagnostic and Solution (文档 ID 8103.1)

http://www.nsfocus.com.cn/upload/contents/2015/03/o_19ff3125g1edm1df51r84ir61kggb.pdf


https://flylib.com/books/en/2.680.1/the_oracle_network_architecture.html
https://docs.oracle.com/en/database/oracle/oracle-database/18/netag/troubleshooting-oracle-net-services.html#GUID-63478898-61B3-4E24-B879-238448DCE0E2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close