 |
|
Oracle Servlet Log Tables
Oracle Application Server Tips by Burleson
Consulting |
The Oracle9iAS servlet engine has several
log tables with the Oracle9iAS Repository that are used to track
servlet errors.
-
ose$http$admin.error$log - This table
contains the error message number and associated text
-
ose$http$admin.event$log ? This table
contains servlet event numbers and their associated text messages.
-
ose$http$admin.http$log$ - This is the
repository log table that contains specific log information about
remote user servlet messages. The table contains the remote user
ID, and time of the servlet request and the referrer URL. The
referrer column is most useful because you can use it to track the
source of servlet requests:
SQL> desc ose$http$admin.http$log$;
Name Null? Type
----------------------------- --------
-------------------- SERVER_NAME
VARCHAR2(80)
TIMESTAMP DATE
REMOTE_HOST
RAW(4)
REMOTE_USER
VARCHAR2(80)
REQUEST_LINE
VARCHAR2(256)
STATUS
NUMBER(3)
RESPONSE_SIZE
NUMBER(38)
REQUEST_METHOD
RAW(1)
REFERER
VARCHAR2(80)
AGENT
VARCHAR2(80)
Portal Repository Log Audit Reports
Oracle Portal has several log tables
in the Iasdb repository, and these can be referenced with SQL to
create developer activity reports for Portal. This produces a
report similar to using the Oracle DDL system-level trigger, and
tracks all Portal changes made by your development staff. The
report below references the portal.wwlog_activity_log1$ and
portal.wwlog_activity_log2$ tables and produces a great report of
all Portal development activity.
portal_summary_report.sql
set echo off
set feedback off
ttitle off
clear computes
set heading on
set pages 999
set lines 70
col c1 heading 'Date' format a20
col c2 heading 'User' format a10
col c3 heading 'Action' format a12
col c4 heading 'URL' format a15
col c5 heading 'Info' format a20
col c6 heading 'Rows' format 99,999
prompt
***************************************************
prompt Portal Row Count Summary Report
prompt
***************************************************
alter session set nls_date_format = 'YYYY MM
DD';
break on c1 skip 2
select
to_char(start_time,'yyyy-mm-dd') c1,
sum(row_count) c6
from
PORTAL.WWLOG_ACTIVITY_LOG1$
group by
to_char(start_time,'yyyy-mm-dd')
UNION
select
to_char(start_time,'yyyy-mm-dd') c1,
sum(row_count) c6
from
PORTAL.WWLOG_ACTIVITY_LOG2$
group by
to_char(start_time,'yyyy-mm-dd')
;
prompt
***************************************************
prompt Portal Action Summary Report
prompt
***************************************************
select
to_char(start_time,'yyyy-mm-dd') c1,
action c3,
sum(row_count) c6
from
PORTAL.WWLOG_ACTIVITY_LOG1$
group by
to_char(start_time,'yyyy-mm-dd'),
action
UNION
select
to_char(start_time,'yyyy-mm-dd') c1,
action c3,
sum(row_count) c6
from
PORTAL.WWLOG_ACTIVITY_LOG2$
group by
to_char(start_time,'yyyy-mm-dd'),
action
;
prompt
***************************************************
prompt Portal Detail Summary Report
prompt
***************************************************
select
to_char(start_time,'yyyy-mm-dd
hh24:mi:ss') c1,
userid c2,
action c3,
url c4,
row_count c6
from
PORTAL.WWLOG_ACTIVITY_LOG1
UNION
select
to_char(start_time,'yyyy-mm-dd
hh24:mi:ss') c1,
userid c2,
action c3,
url c4,
row_count c6
from
PORTAL.WWLOG_ACTIVITY_LOG2$
;
Here we see the report output in Listing
2.3. This report shows us the total number of rows processed by
Portal developers, aggregated by date, and a summary of all Portal
developer activity by date. This administration report is
especially useful for change control tracking and quality control
functions.
***************************************************
Portal Row Count Summary Report
***************************************************
Date
Rows
--------------------
-------
2003-05-05
1,741
2003-06-03 44,321
2003-06-04
6,321
2003-06-05
83,301
***************************************************
Portal Action Summary Report
***************************************************
Date Action
Rows
-------------------- ------------
-------
2003-05-05 add_to_page
13
create
375
delete
99
edit
87
error 3,123
move
3
portlet
405
provider
948
2003-06-03 acl_event
77
add_to_page
54
create
377
delete
85
edit
42
portlet
923
process_back
37
ground_inval
9
provider
15
2003-06-04 create
53
edit
374
login
671
logout
102
***************************************************
Portal Detail Summary Report
***************************************************
Date User Action
URL Rows
-------------------- ---------- ------------
--------------- -------
2003-05-05 17:50:26 PORTAL
create 0
2003-05-05 17:51:59 PORTAL
create 0
2003-05-05 17:53:50 PORTAL create
URL/PAGE/SHARED 0
/SAMPLE_BANNER1
/?_mode=16
PORTAL edit
URL/PAGE/SHARED 0
/SAMPLE_BANNER1
/?_mode=16
2003-05-05 17:53:51 PORTAL add_to_page
URL/PAGE/SHARED 0
/SAMPLE_BANNER1
/?_mode=16
PORTAL edit
URL/PAGE/SHARED 0
/SAMPLE_BANNER1
/?_mode=16
2003-06-03 18:39:11 PORTAL process_back
0
ground_inval
2003-06-03 18:39:13 PORTAL
edit 0
Listing 2.3: Oracle Portal developer
activity report
The Iasdb repository also contains a
wwlog_event$ table that provides total counts of Portal actions.
This report is also useful for Portal development auditing.
portal_actions_summary.sql
col c1 heading 'Action' format a20
col c2 heading 'Count' format 999,999
select
action c1,
count(*) c2
from
PORTAL.WWLOG_EVENT$
group by
action
order by
c2 desc
;
Here is the output (Listing 2.4). Here we
see all of the Portal activities and total counts for each activity.
Action
Count
--------------------
--------
view 18
create
15
delete
15
edit
15
access_control
12
export
12
copy
12 execute 12
generate
12
insert
12 update
12
save
12
rename
12
query
12
manage
12
move
2
add_to_page
1
search
1
show 1
delete_from_page
1
debug
1
customize
1
hide
1
checkin
1
Listing 2.4: Portal Activity Summary Report
Now that we have a basic understand of the
components of the Infrastructure database we are now ready to look
at the generic infrastructure management tools and components.
This is an excerpt from "Oracle
10g Application Server Administration Handbook" by Don Burleson
and John Garmany.
and include the URL for the page.
