Writing user defined message to Alert Log in Oracle.

In Oracle , there is a undocumented package/API ( dbms_system) to write user defined messages in the alert log.  This can be executed an user with SYSDBA privilege . You may want to use this with caution , as it is not supported by Oracle.

The signature of this procedure call is

dbms_system.ksdwrt ( Trace Identifier , User Defined Message )

Note :
When Trace Identifier is 1 , the message is written to a trace file only.
When Trace Identifier is 2 , the message is written to a alert log only.
When Trace Identifier is 3 , the message is written to a alert log and trace file. 


Here is an example.


SQL> show parameter user_dump_de

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      C:\app\mohideen\diag\rdbms\zmdb
                                                 rd11\zmdbrd11\trace



SQL> exec dbms_system.ksdwrt(1, 'Writing Alert message with 1');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(2, 'Writing Alert message with 2');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(3, 'Writing Alert message with 3');

PL/SQL procedure successfully completed.



C:\app\mohideen\diag\rdbms\zmdbrd11\zmdbrd11\trace>type alert_zmdbrd11.log
Thu Jun 13 15:31:44 2013
Writing Alert message with 2
Writing Alert message with 3



C:\app\mohideen\diag\rdbms\zmdbrd11\zmdbrd11\trace>type zmdbrd11_ora_8928.trc
Trace file C:\APP\MOHIDEEN\diag\rdbms\zmdbrd11\zmdbrd11\trace\zmdbrd11_ora_8928.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 8 - type 8664, 8 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:4194M/12285M, Ph+PgF:14916M/24569M
Instance name: zmdbrd11
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 8928, image: ORACLE.EXE (SHAD)


*** 2013-06-13 15:31:38.973
*** SESSION ID:(131.1077) 2013-06-13 15:31:38.973
*** CLIENT ID:() 2013-06-13 15:31:38.973
*** SERVICE NAME:(SYS$USERS) 2013-06-13 15:31:38.973
*** MODULE NAME:(sqlplus.exe) 2013-06-13 15:31:38.973
*** ACTION NAME:() 2013-06-13 15:31:38.973

Writing Alert message with 1

*** 2013-06-13 15:31:52.171
Writing Alert message with 3

At times , this trick could be useful . You can see my another post on how to monitor the alert log (
http://mfzahirdba.blogspot.com/2010/10/monitoring-alert-log.html) .

Comments Welcome.

Display Trace File's Name and Location using ORADEBUG

There are few ways to determine the trace file name and its location.

ORADEBUG is one of them.  See below for example.

SQL> oradebug setmypid

Statement processed.

SQL> alter database backup controlfile to trace;
Database altered.

SQL> oradebug tracefile_name
C:\APP\MOHIDEEN\diag\rdbms\zdb11g\zdb11g\trace\zdb11g_ora_8856.trc