<!-- Title: 08:41:47  Wed Nov  6 2002 -->
<!-- Crewchief: New Day -->
<!-- Op1: &nbsp; -->
<!-- Op2: &nbsp; -->
<!-- Op3: &nbsp; -->
<!-- Op4: &nbsp; -->
<!-- Op5: &nbsp; -->
<!-- Op6: &nbsp; -->
<!-- Notes: -->
<pre>database activity</pre>


<!-- Date: Wed Nov 6 09:26:45 2002 -->
<pre>Alan 
 
I will contact Richard and review the sar with him 
to see how long this spike lasted. 
 
Looking at the db access is seems a user 
'holloway' was the only consistent user between 
7:52 and 10:27 and 1 of these process 12770 
took up alot of cpu. 
 
  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND 
12770 oracle    25   0 25260  24M 10508 R    99.9  0.6   1:52 oracle 
 
users.19_52:999         33   59952 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.19_57:999         33   59952 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_02:2389                33   59954 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_07:3038                56   26142 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_12:3038                56   26142 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_17:4981                22    9936 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_22:6416                22    9944 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_27:7384                22    9946 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_32:7711                76   10140 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_37:8342                18   13766 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_42:9620                43   43153 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_47:9620                43   43153 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_52:10573               76   10488 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.20_57:11865               76   10492 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.21_02:11865               76   10492 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.21_07:12770               56   27121 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.21_12:14310               15   41840 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.21_17:14310               15   41840 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.21_42:18734               22   10743 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.22_12:24152               76   11389 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.22_17:24152               76   11389 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.22_22:24152               76   11389 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
users.22_27:26667               76   11405 CDF_READER   holloway     harv11.fnal.gov      ./bin/Linux2-KCC_4_0/TRGSim++@harv11.fna 
 
----- Original Message ----- 
From: "Alan Sill" &lt;Alan.Sill@TTU.EDU&gt; 
To: &lt;cdfdb-support@fnal.gov&gt; 
Sent: Wednesday, November 06, 2002 12:13 AM 
Subject: first spike to 100% cpu time on cdfrep01 11/5/2002 
 
 
&gt; CAF was not very busy at this time, so this must 
&gt; have been from some of our off-site users... 
&gt; 
&gt; Alan 
&gt; 
&gt; 
&gt; -- 
&gt; ====================================================================== 
&gt; :  Alan Sill, Texas Tech University   Office: Science 117, MS 41051  : 
&gt; :  e-mail: Alan.Sill@ttu.edu    ph. 806-742-3730   fax 806-742-1182  : 
&gt; :  CDF trailer 170-D       ph. 630-840-2114       cell 806-790-7462  : 
&gt; ====================================================================== 
&gt; 
</pre>
<!-- Author: nstanfield  -->

<!-- Date: Wed Nov 6 09:30:24 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 

Alert 
The following errors are in the ALERT file : ORA-12012: error on auto execute of job 3696 ORA-12008: error in snapshot refresh path ORA-02049: timeout: distributed transaction waiting for lock ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674 ORA-06512: at "SYS.DBMS_IREFRESH", line 577 ORA-06512: at "SYS.DBMS_REFRESH", line 211 ORA-06512: at line 1. 
WARNING 
Nov 06 2002 09:29:48 AM 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Wed Nov 6 09:36:33 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 

Alert 
The following errors are in the ALERT file : ORA-12012: error on auto execute of job 3696 ORA-12008: error in snapshot refresh path ORA-02049: timeout: distributed transaction waiting for lock ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674 ORA-06512: at "SYS.DBMS_IREFRESH", line 577 ORA-06512: at "SYS.DBMS_REFRESH", line 211 ORA-06512: at line 1. 
WARNING 
Nov 06 2002 09:34:49 AM 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Wed Nov 6 09:41:57 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 

Alert 
The following errors are in the ALERT file : ORA-12012: error on auto execute of job 3696 ORA-12008: error in snapshot refresh path ORA-02049: timeout: distributed transaction waiting for lock ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674 ORA-06512: at "SYS.DBMS_IREFRESH", line 577 ORA-06512: at "SYS.DBMS_REFRESH", line 211 ORA-06512: at line 1. 
WARNING 
Nov 06 2002 09:39:50 AM 


</pre>
<!-- Author: oem-admin (email) -->



<!-- Date: Wed Nov 6 09:47:18 2002 -->
<pre>Refresh for Slow Control schema on cdfofprd failed due to timeout on distributed transaction waiting for lock.  
 
SENSOR_HISTORIES, LIMIT_HISTORIES and  
SENSOR_LIMIT has been added to the refresh group.  
 
Job will be resubmitted again.  
</pre>
<!-- Author: akumar -->


<!-- Date: Wed Nov 6 09:47:23 2002 -->
<pre>Preparing to refresh of cdftrig from prd to int.  
 
The following tablespaces in cdfonint require coalescing: 
 
Tablespace Tablespace                     Coalescable 
    Number Name                               Extents 
---------- ------------------------------ ----------- 
         8 CDFTRIG_DATA                            13 
         9 CDFTRIG_IDX                             43 
        18 MLOG_DATA                               33 
         8 CDFTRIG_DATA                            21 
         9 CDFTRIG_IDX                             25 
 
beginning coalescing of cdfonint 
after the coalesce for cdfonint</pre>
<!-- Author: nstanfield  -->
<!-- Comment: Wed Nov  6 09;59;05 comment by...nstanfield  -->
<pre>Per Donatella's request, she will be testing an application
that retires Trigger DB objects no longer needed by current
Physics Tables

We have refreshed the integration cdftrig schema
with production schema/data.

Information for: cdfonint

Tablespace Name Tablespace     Size Used (in bytes) Free (in bytes) Pct. Used

-------------------- ---------------- ---------------- ---------------- ---------

CDFTRIG_IDX      81,788,928     57,368,576         24,420,352             71
CDFTRIG_DATA     83,886,080     32,268,288         51,617,792             39



</pre>
<!-- Date: Wed Nov 6 09:51:19 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 

Alert 
The following errors are in the ALERT file : ORA-12012: error on auto execute of job 3696 ORA-12008: error in snapshot refresh path ORA-02049: timeout: distributed transaction waiting for lock ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674 ORA-06512: at "SYS.DBMS_IREFRESH", line 577 ORA-06512: at "SYS.DBMS_REFRESH", line 211 ORA-06512: at line 1. 
WARNING 
Nov 06 2002 09:49:52 AM 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Wed Nov 6 09:56:26 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 

Alert 
The following errors are in the ALERT file : ORA-12012: error on auto execute of job 3696 ORA-12008: error in snapshot refresh path ORA-02049: timeout: distributed transaction waiting for lock ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674 ORA-06512: at "SYS.DBMS_IREFRESH", line 577 ORA-06512: at "SYS.DBMS_REFRESH", line 211 ORA-06512: at line 1. 
WARNING 
Nov 06 2002 09:54:53 AM 


</pre>
<!-- Author: oem-admin (email) -->




<!-- Date: Wed Nov 6 10:26:28 2002 -->
<pre>SENSOR_HISTORIES table was added to the refresh group during the  
index being created on this table. So refresh of this table couldn't acquire the lock. Took off the SENSOR_HISTORIES table 
from the refresh group. Once indexes will be craeted on this table, it will be added to the refresh group.  
 
In Summary, Replication refresh is going on for all schemas on cdfofprd except SENSOR_HISTORIES table. It will be added once  
indexes will be done on this table.  
 
 
 
</pre>
<!-- Author: akumar -->

<!-- Date: Wed Nov 6 10:31:02 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Wed Nov 6 10:31:02 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Wed Nov 6 10:31:02 2002 -->
<pre>
</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Wed Nov 6 10:31:02 2002 -->
<pre>
</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Thu Nov 7 09:23:56 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 

Unscheduled Jobs 
Event test "Unscheduled Jobs" is cleared 
CLEARED 
Nov 07 2002 09:23:31 AM 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Thu Nov 7 09:26:01 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 

Alert 
The following errors are in the ALERT file : ORA-12012: error on auto execute of job 27040 ORA-00030: User session ID does not exist. ORA-06512: at "BADGETT.CDFDB", line 43 ORA-06512: at line 1. 
WARNING 
Nov 07 2002 09:25:33 AM 


</pre>
<!-- Author: oem-admin (email) -->



<!-- Date: Thu Nov 7 09:28:36 2002 -->
<pre>Started the refresh of Slow Control Schema with SENSOR_HISTORIES 
on cdfofprd. SENSOR_HISTORIES has delta of 614,287 rows. </pre>
<!-- Author: akumar -->

<!-- Date: Thu Nov 7 10:04:53 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 

Unscheduled Jobs 
Event test "Unscheduled Jobs" is cleared 
CLEARED 
Nov 07 2002 10:03:33 AM 


</pre>
<!-- Author: oem-admin (email) -->



<!-- Date: Thu Nov 7 10:28:13 2002 -->
<pre>SENSOR_HISTORIES table has been refreshed on cdfofprd.  
Now this table is part of refresh group.  
 
There were 614K rows in SENSOR_HISTORIES to be refreshed  
and it took around 35 Minutes.  
 
Complete refresh and indexes on SENSOR_HISTORIES table  
took 7 hours. There were around 101Millions rows in that table. 
 
</pre>
<!-- Author: akumar -->

<!-- Date: Thu Nov 7 10:30:58 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Thu Nov 7 10:30:58 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Thu Nov 7 10:30:58 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Fri Nov 8 10:31:05 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Fri Nov 8 10:31:05 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Fri Nov 8 10:31:05 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Fri Nov 8 10:31:05 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Fri Nov 8 10:33:10 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Fri Nov 8 10:33:10 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Sat Nov 9 10:32:11 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Sat Nov 9 10:32:11 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Sat Nov 9 10:32:11 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Sat Nov 9 10:32:11 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Sun Nov 10 10:32:13 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Sun Nov 10 10:32:13 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Sun Nov 10 10:32:13 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Sun Nov 10 10:32:13 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Mon Nov 11 06:32:57 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 

Alert 
The following errors are in the ALERT file : ORA-12012: error on auto execute of job 27040 ORA-00030: User session ID does not exist. ORA-06512: at "BADGETT.CDFDB", line 43 ORA-06512: at line 1. 
WARNING 
Nov 11 2002 06:31:52 AM 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Mon Nov 11 10:32:03 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Mon Nov 11 10:32:03 2002 -->
<pre>
Target Name : cdfofprd.fnal.gov 
Target Type : Database 
Node Name : fcdfora1.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Mon Nov 11 10:32:03 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->


<!-- Date: Mon Nov 11 10:32:03 2002 -->
<pre>
Target Name : cdfonprd.fnal.gov 
Target Type : Database 
Node Name : b0dau35.fnal.gov 


</pre>
<!-- Author: oem-admin (email) -->