OLF: Configuration Settings
Configuration settings in the Oracle Logging Framework (OLF) consist of adding or modifying rows in the dblog_config table that correspond to the task and its logging options. The table has the following columns:
SQL> desc dblog_config Name Null? Type ----------------------------------------- -------- ---------------------------- CONFIG_ID NOT NULL NUMBER MODULE_NAME VARCHAR2(30) ACTION_NAME VARCHAR2(30) USERNAME VARCHAR2(30) SID NUMBER INST_ID NUMBER LOG_LEVEL NOT NULL VARCHAR2(10) TRACE_ENABLED NOT NULL CHAR(1) SQL>
There is a unique index on the columns: ( module_name, action_name, username, sid, inst_id ). This is done to try and prevent duplicate records. The logging level and whether to turn on trace are set by updating the log_level and trace_enabled columns.
The query of this table has an ORDER BY in it to attempt to get the most restrictive qualifying record. For example the following three records are valid:
SQL> select * from dbamon.dblog_config;
CONFIG_ID MODULE_NAME ACTION_NAME USERNAME SID INST_ID LOG_LEVEL TRACE_ENABLED
--------- ----------- ----------- -------- ----- ------- --------- -------------
1 module action DEBUG Y
3 module action dbamon INFO N
4 module action dbamon 42 1 ERROR Y
SQL> select user, SYS_CONTEXT('USERENV','SID'), SYS_CONTEXT('USERENV','INSTANCE') from dual;
USER
------------------------------
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','INSTANCE')
--------------------------------------------------------------------------------
DBAMON
42
1
SQL>
The most restrictive record is config_id 4. If we query dblog.get_current_level we should see a log_level of ERROR:
SQL> select dblog.get_current_level from dual;
GET_CURRENT_LEVEL
--------------------------------------------------------------------------------
INFO
SQL> exec ilo_task.begin_task('module','action');
PL/SQL procedure successfully completed.
SQL> select dblog.get_current_level from dual;
GET_CURRENT_LEVEL
--------------------------------------------------------------------------------
ERROR
SQL>
Note that our first attempt returned the default level of INFO. It wasn’t until we set our module and action, I had already verified the username, sid and inst_id above, that dblog picked up the most restrictive record and returned a log level of ERROR. The same holds true for dblog.is_trace_enabled:
SQL> select dblog.is_trace_enabled from dual; IS_TRACE_ENABLED -------------------------------------------------------------------------------- Y SQL>
So now we can set the log_level and/or whether to trace on the fly for any Oracle process that matches our module and action and/or username and/or sid and instance id.
Next up we’ll talk about the integration with the Method-R Instrumentation Library for Oracle and how together we have an Oracle Logging Framework.
Entries