The Oracle9iAS system contains numerous logs file, some of which are stored in flat files while others are stored inside the Iasdb instance. It is important to remember that log files and audit trails may exist in many places so that you will know the proper places to seek error messages and audits.

In practice, working Oracle9iAS administrators use shell scripts with SQL*Plus to automate this task, and filter out unwanted messages so they can see only those messages that are germane to their current needs. When the Iasdb database is initially loaded, log files are created in the $ORACLE_HOME/config directory. These include the following log files:

• schemaload.log — This file reports on the Iasdb load process.
• useinfratool.log — This file reports on all tools whose definitions have been loaded into the Iasdb instance.
• infratool_instance_jazn.log  — This reports on the Java Authorization (JAZN) install using Oracle’s Java Authentication and Authorization Service ( JAAS).
• infratool_mod_osso.log — This file reports on the mod_osso load process.

After the Iasdb initial load, it is a good idea to check these files for errors. Oracle9iAS will report on all successful Oracle9iAS component install in these logs and the status can easily be checked with a single command:

root> grep -i succeeded $ORACLE_HOME/config/*.log

infratool_instance_jazn.log:Configuration succeeded for IASProperty
infratool_instance_jazn.log:Configuration succeeded for IAS
infratool_instance_jazn.log:Configuration succeeded for LDAP
infratool_mod_osso.log:Configuration succeeded for JAZN
infratool_mod_osso.log:Configuration succeeded for HTTPD
infratool_mod_osso.log:Configuration succeeded for MODOSSO
schemaload.log:Configuration succeeded for SchemaLoad

Of course there are many other flat files for logs within Oracle9iAS and they are fully discussed in later chapters. Next let’s look at the Oracle9iAS OEM console interface for displaying Oracle9iAS log messages.

While the OEM Console GUI is great for ad-hoc queries, the Oracle9iAS administrator often supplements this GUI with custom scripts to extract and email important error messages. Let’s take a closer look at how this works.

Writing your own Infrastructure Repository Log Scripts

While the OEM viewer is great for quick online queries, most Oracle9iAS administrator will write SQL*Plus scripts to directly extract the repository log message, often emailing them to the desktop. To see how this works, here is a sample Korn shell script that will extract the online repository logs for SSO and mail them to the Oracle9iAS administrator:

mail_logs.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
 
# Get the server name
host=`uname -a|awk '{ print $2 }'`
${ORACLE_HOME}/bin/sqlplus system/`cat password.txt`<<!
spool log_rpt_mgt.lst
@sso_audit_log.sql
spool off;
exit;
!
#************************************
# Filter only error messages
#************************************
grep –i error log_rpt_mgt.lst > errors_log.lst
 
#************************************
# Mail the Object Statistics Reports
#************************************
cat error_rpt_mgt.lst|mailx -s "Oracle9iAS Repository SSO Messages" \
   larry_lizard@us.oracle.com \
   graham_cracker@oracle.com \
   bob_white@oracle.com

Note the password security in the SQL*Plus invocation line. We can save the SYSTEM password on our server in a file called password.txt, and protect it by setting the file permissions such that only the Oracle user may view the password:

oracle> chmod      700 password.txt

oracle> ls      -al *.txt
-rwx------    1 oracle   oracle         13 Aug 18 05:35 password.txt

Now that we see how easy it is to write SQL*Plus scripts against the iasdb instance, let’s take a look at the log tables and see which are the most important to the Oracle9iAS administrator.

Viewing the Repository Log Tables

Because Oracle has been very careful to use uniform table naming conventions, you can write a very simple SQL*Plus query to see the Oracle9iAS log tables. Below we select all Iasdb tables that contain the string LOG (Listing 2.2):

select owner, table_name
  from dba_tables
  where table_name like '%LOG%';

AURORA$JIS$UTILITY$       JAVA$HTTP$LOG$

OSE$HTTP$ADMIN       HTTP$LOG$
                     EVENT$LOG
                     ERROR$LOG

ODS PLG_DEBUG_LOG 
   DS_LDAP_LOG 
   ODS_CHG_LOG 
   ASR_CHG_LOG    

OCA                  OCM_ERROR_LOG

WKSYS                WK$_TDS_LOG 

ORASSO               WWSEC_SSO_LOG$
                     WWLOG_ACTIVITY_LOG1$
                     WWLOG_ACTIVITY_LOG2$
                     WWLOG_EVENT$
                     WWLOG_REGISTRY$
                     WWSSO_LOG$
                     WWSSO_AUDIT_LOG_TABLE_T


PORTAL               WWSEC_SSO_LOG$
                     WWLOG_ACTIVITY_LOG1$
                     WWLOG_ACTIVITY_LOG2$
                     WWLOG_EVENT$
                     WWLOG_REGISTRY$
                     WWUTL_EXPORT_IMPORT_LOG$
                     WWPTL_CONTENT_LOGS$
                     WWPTL_CONTENT_LOG_HEADERS$
                     WWWCP_RENDER_LOG$

UDDISYS              SUBSCRIPTION_APP_LOG_LEVEL

WCRSYS               WWWCP_RENDER_LOG$

WIRELESS             PTG_LBS_LOG
                     PTG_DEBUG_LOG
                     PTG_SERVICE_LOG
                     PTG_SESSION_LOG
                     TRANS_REQUEST_LOG
                     TRANS_HANDLE_LOG
                     TRANS_PROCESS_LOG
                     TRANS_ENQUEUE_LOG
                     TRANS_DEQUEUE_LOG
                     ASYNC_STATISTICS_LOG
                     MESSAGING_OUTGOING_LOG
                     LBEVENT_ENQUEUE_LOG
                     LBEVENT_DEQUEUE_LOG
                     LBEVENT_MSG_LOG
                     LBEVENT_ACTIVATION_LOG
                     STUDIO_LOG_MESSAGES
                     PROVISIONING_TRANSACTION_LOG
                     BILLING_SDR_LOG
                     SYS_LOGGER_TABLE
                     WWSEC_SSO_LOG$
  
OWF_MGR              ECX_OUTBOUND_LOGS
                     ECX_DOCLOGS
                     ECX_EXTERNAL_LOGS
                     ECX_OXTA_LOGMSG
                     ECX_INBOUND_LOGS
                     ECX_MSG_LOGS

IP                   TIP_ERRORLOGINSTANCE_T_AUD
                     TIP_ERRORLOGRECORDDATA_AUD
                     TIP_ERRORLOGINSTANCE_RT
                     TIP_ERRORLOGRECORDDATAINSTA_RT
                     TIP_RTLOG
                     B2BERROR_LOG

Listing 2.2: The Iasdb repository log tables.

Here we see each of the Iasdb schema and their associated log tables. Remember, not all of the log tables are populated with meaningful information, so we must carefully examine each log file to see their contents.

Infrastructure Logs Reports

The following script can be run to easily display all of the Iasdb logs in your system. Below is a handy script called display_all_log_tables.sql 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 20
set pages 999
set echo off
set feedback off
set long 4000;
spool runme.sql
   
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.lst
grep –i warning all_logs.lst > warning_log.lst

 
#************************************
# Mail the Object Statistics Reports
#************************************
cat error_log.lst|mailx -s "Oracle9iAS Repository Error Messages" \
   graham_cracker@oracle.com \
   tom_thumb@oracle.com


cat error_log.lst|mailx -s "Oracle9iAS Repository Warning Messages" \
   graham_cracker@oracle.com \
   tom_thumb@oracle.com 

Note that once you have run this script and offloaded all repository log messages, you can then use the UNIX grep command to extract selected contents.

---

The above text is an excerpt from the Oracle Application Server 10g Administration Handbook Check-out the whole series of exciting new Oracle10g books at http://shop.osborne.com/cgi-bin/oraclepress/

John Garmany is a leading expert in Oracle Application Server Consulting and Support.  He specializes in Oracle9iAS and Oracle Application Server 10g configuring and tuning.

Donald K. Burleson has been a working DBA for more than 20 years and specializes in creating large-scale web-enabled Oracle Database systems.