Tuesday, May 10, 2011

Write a message to oracle alert log or trace file manually

Write a message to oracle alert log or trace file manually
----------------------------------------------------------

for this you should use dbms_system package .

for example :-
I want to write a warning message in the alert log or in the trace file
such as message is "Warning! your DB need Restart now"

for this:-

dbms_system.ksdwrt (dest IN BINARY_INTEGER, tst IN VARCHAR2);

I) dest valid value will be folow

if value (1) - Write to the standard trace file
if value (2) - Write to the alert log
if value (3) - Write to both files at once

II) tst value is - your target message body .

example:-
======================================


1) Write to the standard trace file
================================

SQL*Plus: Release 11.1.0.6.0 - Production on Tue May 10 15:16:20 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> exec dbms_system.ksdwrt(1,'Warning! your DB need Restart now');

PL/SQL procedure successfully completed.

SQL> select value from v$parameter where name='background_dump_dest';

VALUE
-------------------------------------------------------------------------

g:\app\administrator\diag\rdbms\halimdb\halimdb\trace

-----content of trace file
================================
-------------------------------------------------------------------------------------------
Trace file g:\app\administrator\diag\rdbms\halimdb\halimdb\trace\halimdb_ora_3648.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows XP Version V5.1 Service Pack 3
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1077M/2036M, Ph+PgF:2800M/3929M, VA:1190M/2047M
Instance name: halimdb
Redo thread mounted by this instance: 1
Oracle process number: 19
Windows thread id: 3648, image: ORACLE.EXE (SHAD)


*** 2011-05-10 15:20:11.203
*** SESSION ID:(170.83) 2011-05-10 15:20:11.203
*** CLIENT ID:() 2011-05-10 15:20:11.203
*** SERVICE NAME:(SYS$USERS) 2011-05-10 15:20:11.203
*** MODULE NAME:(sqlplus.exe) 2011-05-10 15:20:11.203
*** ACTION NAME:() 2011-05-10 15:20:11.203

Warning! your DB need Restart now
---------------------------------------------------------------------------------------------


2) Write to the alert log
==============================

sQL> exec dbms_system.ksdwrt(2,'Warning! your DB need Restart now');

PL/SQL procedure successfully completed.


-------------------------content of alert log file--------------------
Completed: alter database open
Tue May 10 11:17:05 2011
Starting background process CJQ0
Tue May 10 11:17:05 2011
CJQ0 started with pid=19, OS id=1880
Tue May 10 07:52:40 2011
Stopping background process CJQ0
Tue May 10 08:46:16 2011
[YOURAPP] A Critical error was detected in module: INIT
Tue May 10 15:22:20 2011
Warning! your DB need Restart now
------------------------------------------------------------------------


3) Write to both files at once
================================


sQL> exec dbms_system.ksdwrt(3,'Warning! your DB need Restart now');

PL/SQL procedure successfully completed.