Connection Timeouts and DNS

On an Oracle 11.1.0.7.0 database running on AIX system, clients were complaining about connection timeouts. Tnsping was showing latency more than 1000 ms.

I opened a trace for tnsping using AIX truss:

$ truss -aefo tnsping.trc tnsping <tns_db>

30539848: 31654123: _getpid()                           = 30539848
30539848: 31654123: kopen("/etc/resolv.conf", O_RDONLY) = 5
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kread(5, " n a m e s e r v e r   1".., 4096) = 114
30539848: 31654123: kread(5, " n a m e s e r v e r   1".., 4096) = 0
30539848: 31654123: statx("/etc/resolv.conf", 0x0FFFFFFFFFFF9108, 176, 0) = 0
30539848: 31654123: close(5)                            = 0
30539848: 31654123: socket(1, 1, 0)                     = 5
30539848: 31654123: kfcntl(5, F_SETFD, 0x0000000000000001) = 0
30539848: 31654123: connext(5, 0x0FFFFFFFFFFF8BD8, 1025)        Err#2  ENOENT
30539848: 31654123: close(5)                            = 0
30539848: 31654123: kopen("/etc/netsvc.conf", O_RDONLY) = 5
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kread(5, " #   @ ( # ) 4 3        ".., 4096) = 4096
30539848: 31654123: kread(5, " o n   a n d   r e s o l".., 4096) = 638
30539848: 31654123: close(5)                            = 0
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: kopen("/etc/hesiod.conf", O_RDONLY) Err#2  ENOENT
30539848: 31654123: __libc_sbrk(0x0000000000010020)     = 0x00000001106A4D40
30539848: 31654123: kopen("/etc/irs.conf", O_RDONLY)    Err#2  ENOENT
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: getdomainname(0x09001000A02273B0, 1024) = 0
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: _thread_self()                      = 31654123
30539848: 31654123: kopen("/etc/hosts", O_RDONLY)               = 5
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kfcntl(5, F_SETFD, 0x0000000000000001) = 0
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kread(5, " #   @ ( # ) 4 7\t 1 . 2".., 4096) = 2018
30539848: 31654123: kread(5, " #   @ ( # ) 4 7\t 1 . 2".., 4096) = 0
30539848: 31654123: close(5)                            = 0
30539848: 31654123: kopen("/etc/hosts", O_RDONLY)               = 5
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kfcntl(5, F_SETFD, 0x0000000000000001) = 0
30539848: 31654123: kioctl(5, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
30539848: 31654123: kread(5, " #   @ ( # ) 4 7\t 1 . 2".., 4096) = 2018
30539848: 31654123: kread(5, " #   @ ( # ) 4 7\t 1 . 2".., 4096) = 0
30539848: 31654123: socket(2, 2, 0)                     = 6
30539848: 31654123: getsockopt(6, 65535, 4104, 0x0FFFFFFFFFFF7BE4, 0x0FFFFFFFFFFF7BE0) = 0
30539848: 31654123: connext(6, 0x09001000A0167878, 16)  = 0
30539848: 31654123: _esend(6, 0x0FFFFFFFFFFF8AF0, 35, 0, 0x0000000000000000) = 35
30539848: 31654123: _poll(0x0FFFFFFFFFFF7CB0, 1, 1000)  = 1
30539848: 31654123: _enrecvfrom(6, 0x0FFFFFFFFFFF9E20, 1024, 0, 0x0FFFFFFFFFFF8470, 0x0FFFFFFFFFFF7C98, 0x0000000000000000) = 104
30539848: 31654123: _esend(6, 0x0FFFFFFFFFFF8AF0, 47, 0, 0x0000000000000000) = 47
30539848: 31654123: _poll(0x0FFFFFFFFFFF7CB0, 1, 1000)  = 1
30539848: 31654123: _enrecvfrom(6, 0x0FFFFFFFFFFF9E20, 1024, 0, 0x0FFFFFFFFFFF8470, 0x0FFFFFFFFFFF7C98, 0x0000000000000000) = 122
30539848: 31654123: close(6)                            = 0
30539848: 31654123: close(5)                            = 0

As you might notice, what tnsping and also any other Oracle Net service is doing is to check DNS server first then use local hosts file for name resolution. This is the cause of the latency because a public DNS server was set for this server and naturally there was no record for it on the DNS. Even you set hosts file as primary source in the netsvc.conf, Oracle does not care and uses DNS at first. 

After clearing DNS configuration on the server, connection times get into order. It's also a known bug that is fixed in 11.2.0.2. So, you need to upgrade for an appropriate solution.

Spatial Index Creation Fails with ORA-01031

While re-creating a spatial index it failed as follow:

DROP INDEX TEST.SIX_POLYGON;

CREATE INDEX TEST.SIX_POLYGON ON TEST.POLYGON (GEOLOC) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('TABLESPACE=INDEX_TS') NOPARALLEL;

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MDRS_1EEB04$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE SEQUENCE "TEST".MDRS_1EEB04$ ORDER START WITH 1 CACHE 100
ORA-29400: data cartridge error
ORA-01031: insufficient privileges
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10

After some digging in, I found the missing privilege: create sequence. You can check if the user has required rights by:

select privilege
from DBA_SYS_PRIVS
where privilege in ('CREATE TABLE', 'CREATE SEQUENCE' )
and grantee = 'TEST';

All needed is to grant create sequence to the target user before creating index. Afterwards you can revoke the right:

GRANT CREATE SEQUENCE TO TEST;

CREATE INDEX TEST.SIX_POLYGON ON TEST.POLYGON (GEOLOC) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('TABLESPACE=INDEX_TS') NOPARALLEL;


REVOKE CREATE SEQUENCE FROM TEST;