It is known that Oracle connections via ODBC (using Oracle own ODBC driver) also connections made via SQL*Net cause resource leaking (memory and handles). Recurring jobs run frequently can quickly dry available resource which in turn can lead to all other kinds of problems. It is recommended that all database jobs are set to run detached. Detached jobs run as separate processes which in most cases allow system to release any resources not released by the process. : In some other threads, I've been trying to
: resolve the serious issue our site
: has with 24x7 job queues getting stuck
: with a job in them that has either not
: run or has finished running. I've determined
: that one cause of these problems may
: be a configuration with Oracle server and
: 24x7 residing on the same server. In this
: configuration we have lots of scripts
: that query the (local) Oracle database for
: information needed later in the script. : On occassion, it appears that the database
: access fails and blows the script up,
: leaving it hung in the queue. Normally, no
: error messages appear and nothing is posted in the
: Event log; however, in one test, I did see an
: event log message that says : Queue 1 Test script: A run-time error occurred
: while executing automation script: Line 38.
: ORA-00942: table or view does not exist
: Select spr_name, spr_value from linc.abadb_spref
: where spr_name = 'CLEANUP' : followed by : Queue 1 Test script: An error occurred while
: executing automation script: Line 39: Result set does not exist. : The table that that the error says does not exist
: most certainly does exist, and I can query it
: repeatedly all day long from other
: applications. : A simple script that does the above might look like : dim SQLText string
: dim RowCount number
: dim new_date string
: dim MessageLogText string
: dim temp_string string : DatabaseConnect " "
: Concat "Select spr_name, spr_value from ", "abadb_spref where
: spr_name = 'CLEANUP' ", SQLText
: DatabaseRetrieve (SQLText, RowCount)
: set new_date @T"yyyymmdd"
: DatabaseGet 1,2 temp_string
:
: ConcatEx("@V"job_name"","--","@V"computer"","
: ","I found this in the database
: ",temp_string,MessageLogText)
:
: LogAddMessageEx("INFO",@V"job_id","@V"job_name"",MessageLogText)
: DatabaseDisconnect : By setting up several jobs that do this kind of database
: access and having them run with file sempaphores,
: I have consistently been able to hang two
: different 24x7 instances' queues running on the same
: server as the Oracle database. When Tracing is
: turned on the problem doesn't seem to occur--or at
: least I haven't been able to get it to happen yet. : Are there any known problems/patches with having
: Oracle on the same server as 24x7? I've
: reproduced the problem with a Win2K Advanced Server
: box running Oracle 8i and a WinXP box running Oracle
: 9i.
|
