EdStevens wrote:
> DA Morgan wrote:
>> EdStevens wrote:
>>> Client environment: HP-UX B.11.00 U 9000/800 622329393 unlimited-user
>>> license
>>> Oracle 8.1.7.4
>>>
>>> Server environment: SunOS 5.9 Generic_118558-25 sun4u sparc
>>> SUNW,Sun-Fire-880
>>> Oracle8i Enterprise Edition Release 8.1.7.4.0
>>> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
>>>
>>> The 8i database contains nothing but a bunch of synonyms and a dblink
>>> to the 10g database, so it serves as a proxy client for some programs
>>> compiled with Pro*C 8.1.7. That's a long story that I'll spare for
>>> now, and yes I know it is an unsupported combination. Tell that to the
>>> suits .... ;-)
>>>
>>> Problem: Shell script on the HP client executes this:
>>>
>>> #---- begin quote from shell scrip -----
>>> echo "PRE-EOD in progress ..."
>>> OraStr=`vdbctl -crmsg -G`
>>> sql_filename=$OHHOME/sql/Db/preeod.sql
>>> sqlplus -s $OraStr <<!
>>> @$sql_filename
>>> commit;
>>> exit;
>>> !
>>> echo "PRE-EOD is done ...\n"
>>> #--- end quote
>>>
>>> and the script preeod.sql looks like this:
>>>
>>> -- ---begin quote from sql script ---
>>> declare
>>> presentcutofftime char(20);
>>>
>>> begin
>>> SELECT to_char(SYSDATE,'YYYYMMDDHH24MiSS')
>>> INTO presentcutofftime
>>> FROM DUAL;
>>> --
>>> dbms_output.put_line('select of sysdate completed');
>>> --
>>> INSERT ...
>>> --
>>> COMMIT;
>>> --
>>> UPDATE ...
>>> --
>>> COMMIT;
>>> --
>>> UPDATE ...
>>> --
>>> COMMIT;
>>> --
>>> INSERT ...
>>> --
>>> COMMIT;
>>> --
>>> END;
>>> /
>>> exit;
>>> -- --- end quote ---
>>>
>>> The shell script is executed once a day as part of end-of-day
>>> processing, and has been running on this platform since the first of
>>> June. On Wednesday the sql script failed to run and did not return any
>>> error msgs. After the first failure, we placed 'dbms_output' lines
>>> immediately after each SQL statement, to get a better handle on things.
>>> It was successful on the next (Thursday) run. Firday failed and the
>>> only dbms_output line we got was the first one - after the SELECT ..
>>> FROM DUAL. So we are getting a connection and submitting the sql, but
>>> it appears to fail in executing the first INSERT, and dies without a
>>> trace.
>>>
>>> We are working a TAR with Oracle Support and they are being as helpful
>>> as they can, given the unsupported nature of our architecture. I
>>> thought it might be helpful to get a 10046 trace to get a finer-grained
>>> picture. I really only have two options (that I see) for initiating
>>> that trace. First is to add the necessary ALTER SESSION statements to
>>> the sql script itself. We actually did that, but the session did not
>>> fail; in addition, setting the trace there means we're tracing the
>>> session in the 8i database, which is not where the work is really being
>>> done .. we're essentially tracing a client. The second option is to
>>> set an AFTER LOGON trigger on the 10g database. I've done this
>>> several times in other situations, using this code:
>>>
>>> create or replace trigger logon_trigger
>>> after logon on database
>>> begin
>>> if (user = '***some username***' ) then
>>> execute immediate 'ALTER SESSION SET
>>> TRACEFILE_IDENTIFIER=''UC7001''';
>>> execute immediate 'alter session set timed_statistics=true';
>>> execute immediate 'ALTER SESSION SET EVENTS ''10046 TRACE NAME
>>> CONTEXT FOREVER, LEVEL 12''';
>>> end if;
>>> exception
>>> when others then
>>> null;
>>> end;
>>> end;
>>>
>>> But this one presents a special challenge. Since the only real client
>>> the 10g database has is the 8i exercising db_links it would seem that
>>> it would seem that there would be no information available to the
>>> trigger to know whether or not actually set the trace for any given
>>> connection. Am I overlooking something in this respect?
>>>
>>> Since this part of the app doesn't require the 8i intermediary, we're
>>> trying to get the apps people to connect directly to the 10g for this
>>> process, but that has also been a struggle partly due to change control
>>> restrictions.
>>>
>>> Any suggestions on how I might proceed (other than application of a
>>> lead pipe to the people who mandated this architecture?)
>> Looks like a procedure written with no attention to Oracle. What is the
>> point of the incremental commits other than fishing for an ORA-01555?
>>
>> And why a shell script? Write it as a database job and use
>> DBMS_APPLICATION_INFO to trace the progress.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
>
> Vaild questions, which we have already asked the apps people. Their
> answer, and that of their manager, is "well, it has worked this way for
> several years (well, and two and a half months on the current setup,
> with this 8i to 10g link) and just now breaks? What's causing it to
> die?"
I can't tell you what is causing it to die. But am more than ready to
offer an opinion on the response you got.
Assuming that their logic is good then revert to dictionary managed
tablespaces. Revert to manual redo with rollback segments. Stop using
array processing and return to single row cursor fetches. Set your CBO
to Level 0. And by all means use cache-hit ratios for tuning? What a
pile of ....
The thing I personally hate about shell scripts and cron jobs, other
than the fact that they require interacting with SysAdmins who are
usually part of another department is that I must give up the huge
amount of quality exception handling and metrics collection already
purchased from Oracle.
And when was the last time a cron job was run through dev and test
environments before being thrown into production? ;-)
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sun Aug 20 2006 - 22:27:54 CDT
