Wednesday, May 19, 2010

Possible scenarios when receiving optisk error in Oracle Database, or when sessions disconnect abnormally

 A problem occurs at database level after requesting huge data from database. Database sessions disconnect abnormally while there is still packets being received. I was able to detect the problem using network monitoring tools.

One possible issue is related to the timing required to process data on one side and the expire time for inactive socket connection. The firewall could interpret the time spent by one side as inactive connection and after a while to drop the connection. Situation like this could be avoided by using the sqlnet.expire_time <> 0 on server side. By using this parameter there is no forced disconnection, a pulse sent to client understood by firewall as traffic but not visible to the client application.


Another issue that is when running via a firewall related to data contents / type of data or queries. FIXUP or FIXIT or DEEP PACKET INSPECTIONS have caused issues on sqlnet communication. I short those features change the data packet but if does not change the header details, due to this when sqlnet reads the header and the data doesn't match, it will report a packet corruption. This is possible when the firewall have these features turned on.

Using Intrusion Protection System (IPS) also could cause the same problem since IPS will lookup data packets try to modify it, or in some cause will delay packet transferee due to data fragmentation.

For more details reference:
Note 805088.1 ORA-3135 with Recovery Catalog Creation Across the Network.
Note 815165.1 ORA-12569 While Trying To Query SYS.DBA_AUDIT_TRAIL Via Oracle Net.

Thing would help further is tracing client - server connection and checking the listener.log and altert.log file if any errors reported.


Monday, May 10, 2010

Oracle Views with some examples

Oracle Views
Some useful Oracle views
dba_mview_logs
dba_mview_refresh_times
dba_mviews
v$archived_log
v$asm_disk
v$backup_datafile
v$controlfile
v$database
v$datafile
v$db_cache_advice
v$filestat
v$instance
v$lock
v$locked_object
v$log
v$logfile
v$parameter
v$process
v$segment_statistics
v$session
v$session_longops
v$session_wait
v$sesstat
v$sort_usage
v$sqlarea
v$statname
v$sysstat
v$system_event
v$tempfile
v$transaction
dba_part_tables
dba_queues
dba_resumable
dba_tab_partitions