Home arrow Oracle arrow Page 7 - The Oracle Application Server 10g Infrastructure

Infrastructure Log Reports - Oracle

If you need to handle the administration and management of Oracle Application Server 10g, this article covers the metadata repository (iasdb), the Single Sign-On (SSO) security framework, and the Oracle Application Server 10g Management Services. It is excerpted from chapter 2 of the book, Oracle Application Server 10g Administration Handbook, written by John Garmany and Donald Burleson (McGraw-Hill/Osborne, 2004; ISBN: 0072229586).

TABLE OF CONTENTS:
  1. The Oracle Application Server 10g Infrastructure
  2. The Infrastructure Repository
  3. Workflow iasdb Schemas
  4. Viewing the Whole iasdb Instance
  5. The Infrastructure Log Tables
  6. Writing Your Own Infrastructure Repository Log Scripts
  7. Infrastructure Log Reports
  8. Repository Administration and Management
  9. Single Sign-On (SSO)
  10. Using the SSO Audit Log Tables
  11. SSO Administration Using the mod_osso Utility
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 12
May 05, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

The following script can be run to display all of the iasdb logs in your system. Next is a handy script called display_all_log_tables.ksh that can be embedded into a shell script to extract all log messages into a flat file.

display_all_log_tables.ksh

#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=iasdb
export ORACLE_SID

ORACLE_HOME=`cat /etc/oratab|grep $ORACLE_SID:|cut -f2 -d':'` export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
${ORACLE_HOME}/bin/sqlplus system/`cat password.txt`<<!
ttitle off
set heading off
set lines 200
set pages 999
set echo off
set feedback off
set long 4000;
spool runme.sql
select 'spool all_logs.lst' from dual;
select 'set echo on' from dual;
select
  'select * from '||owner||'.'||table_name||';'
from
  dba_tables
where
  table_name like '%LOG%'
and
  owner not in ('SYS','SYSTEM')
;
select 'spool off' from dual;
spool off;
@runme.sql
spool off;
exit;

!
#************************************
# Filter only error messages
#************************************
grep i error all_logs.lst > error_log.
grep i warning all_logs.lst > warning_
#************************************
# Mail the Object Statistics Reports
#************************************
cat error_log.lst|mailx -s "Oracle AS 10g Repository Error Messages" \
  graham_cracker@oracle.com \
tom_thumb@oracle.com
cat error_log.lst|mailx -s "Oracle AS 10g Repository Warning Messages" \
  graham_cracker@oracle.com \
  tom_thumb@oracle.com

Note that once you have run this script and off-loaded all repository log messages, you can then use the UNIX grep command to extract selected contents. Next, lets look at special types of iasdb repository log tables and see scripts to extract their messages.

Oracle Servlet Log Tables

The Application Server 10g servlet engine has several log tables in the 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, 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 following report references the portal.wwlog_activity_log1$ and portal.wwlog_activity_log2$ tables and produces a useful 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
ol c3 heading 'Action' format a12

col c4 heading URL'

format a15

col c5 heading nfo' format a20
col c6 heading ows' 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
  ORTAL.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
  ORTAL.WWLOG_ACTIVITY_LOG2$
group by

  to_char(start_time,'yyyy-mm-dd'),
  action
;
prompt *********************************************
prompt Portal Detail Summary Report prompt     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$ ;

You can see the report output in the following listing. It shows 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:51PORTAL 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
                           ground_inval                0
2003-06-03 18:39:13 PORTAL   edit                      0

The iasdb repository also contains a wwlog_event$ table that provides total counts of Portal actions. This report is 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
;

The following listing shows the output. Here you 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

Now were ready to look at the generic infrastructure management tools and components.



 
 
>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: