TUSC and Oracle Connection

[Alex Almaraz]

Hi admins,

I ran the tusc command with the following parameters:

$tusc -f -o query2.log sqlplus '/ as sysdba'

once in sqlplus, I ran a query, the query started giving me data but after 1 minute it hung.

The tail of the query2.log file has the following:

times(0x800003ffbfff6f80) .............................................................................................................. = 1072175205
write(13, "\015\0\006\0\0\0\0\00305% \0\0\0".., 21) .................................................................................... = 21
read(13, "\adb\0\006\0\0\0\0\00602\0? \0\0".., 2064) ................................................................................... = 2011
read(11, "\adb\0\006\0\0\0\0\0060102\0\0P ".., 2064) ................................................................................... = 2011
write(12, "\adb\0\006\0\0\0\0\0060102\0\0P ".., 2011) .................................................................................. = 2011
read(13, "\adb\0\006\0\0\0\0\00 , - 0 . 9 ".., 2064) ................................................................................... = 2064
read(11, "\adb\0\006\0\0\0\0\03 1 , 1 5 3 ".., 2064) ................................................................................... = 2011
write(12, "\adb\0\006\0\0\0\0\03 1 , 1 5 3 ".., 2011) .................................................................................. = 2011
read(13, "9 9 1 , 1 5 6 3 . 0 0 7 , 1 5 6 ".., 1958) ................................................................................... = 1249
read(13, 0x80000001001c8c9c, 709) ...................................................................................................... [sleeping]


Do you know what "sleeping" does mean?

My problem is that when I launch the query from a site A to Site B, the query hangs and I need to kill the session.

Site A:
HPUX 11.11
L Class server
1 processors
2 GB RAM
Oracle 10gR1

Site B:
Sun Fire V440
Solaris 9
4 Processors
Oracle 10g R2

DB:
Table: 32000 Rows
Columns: 129 (4 blob fields)
Query: Retrieving all the fields except the BLOBs

Using: Public DB Link


Internet Link:

Public T1

When I run the query from Site B to Site A, it finishes in around 2 minutes and gives me all the data I retrieve. Not the same from A to B as explained.

Could you give me a clue about this?

Regards

[Steven E. Protter]

Shalom,

What do the oracle alert logs say.

Is tnsping showing a stable database connection.

Alone tusc ouput won't help. Combined with what the alert logs and other things say, diagnosis is possible.

SEP

[Alex Almaraz]

Well,

The Alert log says nothing, but I was getting some Timeouts before, so I configured the Listener.ora and sqlnet.ora to avoid the INBOUND CONNECT TIMEOUT errors. By reviewing the sqlnet.log files, I found some "TNS lost connection" errors, I guess these are network disconnections and no more, what do you think about it?

Regards

[Yogeeraj_1]

hi Alex,

> By reviewing the sqlnet.log files, I found some "TNS lost connection" errors, I guess these are network disconnections and no more, what do you think about it?


If you are getting "TNS lost connection", then the problem is definitely the network. Try to review the logs at the network level (switch, router, etc).

how frequently are you getting these errors?
$ grep -i "tns lost" listener.ora |wc -l

kind regards
yogeeraj

[BUPA IS]

Alex,
If you are able please can you say what network interface you are using on the solaris side (ifconfig -a ) if you can check the card speed an duplex settings, i can give you thr correct command for thsi onec I know the nic type. Also which file system are you using on the solaris system ufs or vxfs.

use landmin to check the network settings (speed and duplex) on the hp side.
regards
Mike

[Alex Almaraz]

Well,

By the Solaris side:

ce0: flags=1000843 mtu 1500 index 2
inet 10.100.1.41 netmask ffff0000 broadcast 10.100.255.255
ether 0:3:ba:7e:54:61
ce1: flags=1000843 mtu 1500 index 3
inet 135.149.199.14 netmask ffffff00 broadcast 135.149.199.255
ether 0:3:ba:7e:54:62


Using ce0

adv_autoneg_cap
1
adv_1000fdx_cap
1
adv_1000hdx_cap
1
adv_100T4_cap
0
adv_100fdx_cap
1
adv_100hdx_cap
1
adv_10fdx_cap
1
adv_10hdx_cap
1

stats:
ce:0:ce0:adv_cap_1000fdx 1
ce:0:ce0:adv_cap_1000hdx 1
ce:0:ce0:adv_cap_100T4 0
ce:0:ce0:adv_cap_100fdx 1
ce:0:ce0:adv_cap_100hdx 1
ce:0:ce0:adv_cap_10fdx 1
ce:0:ce0:adv_cap_10hdx 1
ce:0:ce0:adv_cap_asmpause 0
ce:0:ce0:adv_cap_autoneg 1
ce:0:ce0:adv_cap_pause 0
ce:0:ce0:alignment_err 0
ce:0:ce0:brdcstrcv 10285847
ce:0:ce0:brdcstxmt 20773
ce:0:ce0:cap_1000fdx 1
ce:0:ce0:cap_1000hdx 1
ce:0:ce0:cap_100T4 0
ce:0:ce0:cap_100fdx 1
ce:0:ce0:cap_100hdx 1
ce:0:ce0:cap_10fdx 1
ce:0:ce0:cap_10hdx 1
ce:0:ce0:cap_asmpause 0
ce:0:ce0:cap_autoneg 1
ce:0:ce0:cap_pause 0
ce:0:ce0:class net
ce:0:ce0:code_violations 0
ce:0:ce0:collisions 0
ce:0:ce0:crc_err 0
ce:0:ce0:crtime 126.0686817
ce:0:ce0:excessive_collisions 0
ce:0:ce0:first_collision 0
ce:0:ce0:ierrors 0
ce:0:ce0:ifspeed 100000000
ce:0:ce0:ipackets 39257750
ce:0:ce0:ipackets64 39257750
ce:0:ce0:ipackets_cpu00 14668324
ce:0:ce0:ipackets_cpu01 8308685
ce:0:ce0:ipackets_cpu02 8403272
ce:0:ce0:ipackets_cpu03 7877469
ce:0:ce0:late_collisions 0
ce:0:ce0:lb_mode 0
ce:0:ce0:length_err 0
ce:0:ce0:link_T4 0
ce:0:ce0:link_asmpause 0
ce:0:ce0:link_duplex 2
ce:0:ce0:link_pause 0
ce:0:ce0:link_speed 100
ce:0:ce0:link_up 1
ce:0:ce0:lp_cap_1000fdx 0
ce:0:ce0:lp_cap_1000hdx 0
ce:0:ce0:lp_cap_100T4 0
ce:0:ce0:lp_cap_100fdx 1
ce:0:ce0:lp_cap_100hdx 1
ce:0:ce0:lp_cap_10fdx 1
ce:0:ce0:lp_cap_10hdx 1
ce:0:ce0:lp_cap_asmpause 0
ce:0:ce0:lp_cap_autoneg 1
ce:0:ce0:lp_cap_pause 0
ce:0:ce0:mac_mtu 1522
ce:0:ce0:mac_reset 0
ce:0:ce0:mdt_hdr_bind_fail 0
ce:0:ce0:mdt_hdr_bufs 0
ce:0:ce0:mdt_hdrs 0
ce:0:ce0:mdt_pkts 0
ce:0:ce0:mdt_pld_bind_fail 0
ce:0:ce0:mdt_pld_bufs 0
ce:0:ce0:mdt_plds 0
ce:0:ce0:mdt_reqs 0
ce:0:ce0:multircv 56732
ce:0:ce0:multixmt 5250
ce:0:ce0:norcvbuf 0
ce:0:ce0:noxmtbuf 0
ce:0:ce0:obytes 1119755995
ce:0:ce0:obytes64 5414723291
ce:0:ce0:oerrors 0
ce:0:ce0:opackets 27452697
ce:0:ce0:opackets64 27452697
ce:0:ce0:pci_bad_ack_err 0
ce:0:ce0:pci_bus_speed 66
ce:0:ce0:pci_dmarz_err 0
ce:0:ce0:pci_dmawz_err 0
ce:0:ce0:pci_drto_err 0
ce:0:ce0:pci_err 0
ce:0:ce0:pci_parity_err 0
ce:0:ce0:pci_rma_err 0
ce:0:ce0:pci_rta_err 0
ce:0:ce0:peak_attempts 0
ce:0:ce0:promisc off
ce:0:ce0:qos_mode 0
ce:0:ce0:rbytes 400361297
ce:0:ce0:rbytes64 8990295889
ce:0:ce0:rev_id 32
ce:0:ce0:rx_allocb_fail 0
ce:0:ce0:rx_hdr_drops 0
ce:0:ce0:rx_hdr_pkts 31764515
ce:0:ce0:rx_inits 0
ce:0:ce0:rx_jumbo_pkts 0
ce:0:ce0:rx_len_mm 0
ce:0:ce0:rx_msgdup_fail 0
ce:0:ce0:rx_mtu_drops 0
ce:0:ce0:rx_mtu_pkts 7493235
ce:0:ce0:rx_new_hdr_pgs 992641
ce:0:ce0:rx_new_mtu_pgs 1873308
ce:0:ce0:rx_new_nxt_pgs 0
ce:0:ce0:rx_new_pages 2865949
ce:0:ce0:rx_no_buf 0
ce:0:ce0:rx_no_comp_wb 0
ce:0:ce0:rx_nocanput 0
ce:0:ce0:rx_nxt_drops 0
ce:0:ce0:rx_ov_flow 0
ce:0:ce0:rx_pkts_dropped 0
ce:0:ce0:rx_rel_bit 10391043
ce:0:ce0:rx_rel_flow 0
ce:0:ce0:rx_reused_pgs 2865663
ce:0:ce0:rx_split_pkts 0
ce:0:ce0:rx_tag_err 0
ce:0:ce0:rx_taskq_waits 0
ce:0:ce0:snaptime 2738365.9880952
ce:0:ce0:trunk_mode 0
ce:0:ce0:tx_allocb_fail 0
ce:0:ce0:tx_ddi_pkts 2161641
ce:0:ce0:tx_dma_bind_fail 0
ce:0:ce0:tx_dvma_pkts 1346801
ce:0:ce0:tx_hdr_pkts 26249210
ce:0:ce0:tx_inits 0
ce:0:ce0:tx_jumbo_pkts 0
ce:0:ce0:tx_max_pend 110
ce:0:ce0:tx_max_pkt_err 0
ce:0:ce0:tx_msgdup_fail 0
ce:0:ce0:tx_no_desc 0
ce:0:ce0:tx_nocanput 0
ce:0:ce0:tx_queue0 8363677
ce:0:ce0:tx_queue1 6317959
ce:0:ce0:tx_queue2 6397323
ce:0:ce0:tx_queue3 6373830
ce:0:ce0:tx_starts 27452761
ce:0:ce0:tx_uflo 0
ce:0:ce0:xcvr_addr 1
ce:0:ce0:xcvr_id 2121953
ce:0:ce0:xcvr_inits 1
ce:0:ce0:xcvr_inuse 1




HPUX side:

PPA Number = 0
Description = lan0 HP PCI 10/100Base-TX Core [100BASE-TX,FD,AUTO,TT=1500]
Type (value) = ethernet-csmacd(6)
MTU Size = 1500
Speed = 100000000
Station Address = 0x306e0cfb85
Administration Status (value) = up(1)
Operation Status (value) = up(1)
Last Change = 1036064408
Inbound Octets = 714478374
Inbound Unicast Packets = 150377611
Inbound Non-Unicast Packets = 5943658
Inbound Discards = 1281
Inbound Errors = 0
Inbound Unknown Protocols = 35
Outbound Octets = 2108323629
Outbound Unicast Packets = 176872489
Outbound Non-Unicast Packets = 511765
Outbound Discards = 0
Outbound Errors = 0
Outbound Queue Length = 0
Specific = 655367


Ethernet-like Statistics Group

Index = 1
Alignment Errors = 0
FCS Errors = 0
Single Collision Frames = 0
Multiple Collision Frames = 0
Deferred Transmissions = 0
Late Collisions = 0
Excessive Collisions = 0
Internal MAC Transmit Errors = 0
Carrier Sense Errors = 0
Frames Too Long = 0
Internal MAC Receive Errors = 0


Thanks

[BUPA IS]

hello,
oh the fun of finding solaris card settings

> ce:0:ce0:link_duplex 2 for ce nics 2 means full duplex 1 means half (other card 1 full!)

> ce:0:ce0:late_collisions 0
> ce:0:ce0:oerrors 0
duplex full and zero fo the above counters mean that you do not have the flapping half duplex full duplex problem which some solaris cards with some switches are prone to when auto negotiate fails to complete. Similary the hp side is being well behaved too. It is unlikely ot be a lan problem .

Is the database on solaris using a ufs or vxfs file system and what are the mount options from mnttab ?
Mike

[Alex Almaraz]

The FS are UFS type:

/u04 on /dev/md/dsk/d100 read/write/setuid/intr/largefiles/logging/xattr/onerror=panic/dev=1540064 on Mon Apr 7 15:10:27 2008
/u03 on /dev/md/dsk/d60 read/write/setuid/intr/largefiles/logging/xattr/onerror=panic/dev=154003c on Mon Apr 7 15:10:27 2008
/u06 on /dev/md/dsk/d120 read/write/setuid/intr/largefiles/logging/xattr/onerror=panic/dev=1540078 on Mon Apr 7 15:10:27 2008
/u05 on /dev/md/dsk/d110 read/write/setuid/intr/largefiles/logging/xattr/onerror=panic/dev=154006e on Mon Apr 7 15:10:27 2008
/u01 on /dev/md/dsk/d50 read/write/setuid/intr/largefiles/logging/xattr/onerror=panic/dev=1540032 on Mon Apr 7 15:10:27 2008


Thanks

Regards

[BUPA IS]

Hello Alex,

Oracle doesn't get on very well with ufs caching rather let oracle do all the caching .

on solaris add the forcedirectio option to ufs mount options /etc/vfstab to your oracle mount points

this turns off the ufs cache and allows oracle to do all the buffering increase your oracle sga sizes if required.
e.g.

/dev/md/dsk/d100 /dev/md/rdsk/d100 /u04 ufs 1 yes logging,forcedirectio,largefiles

It gets on somewhat better with jfs but you can also help it along here .

the equivalent for online jfs on hp in /etc/fstab is convosync=direct,mincache=direct

e.g.

/dev/vg02/lvol25 /oradata/vg02_lvol25 vxfs rw,suid,largefiles,delaylog,convosync=direct,mincache=direct 0 2

you can set it for this boot with remount but you must put all the options in the command otherwise you get the defaults .

e.g.

solaris ufs
mount -o remount,largefiles,forcedirectio,logging /dev/md/dsk/d100 /u04

hp online jfs
mount -o remount,largefiles,delaylog,convosync=direct,mincache=direct /dev/vg02/lvol25 /oradata/vg02_lvol25

[Alex Almaraz]

Ok, let me try that.

Regards