HomeOracle 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).
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, let’s 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,
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.
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.
Oracle 
