在PL/SQL程式碼中或SQL*Plus命令列中均可以使用“SYS.DBMS_SYSTEM.KSDWRT”來實現向Oracle警告日誌中寫入資訊的目的。

1.進入到alert目錄存放位置
ora10g@asdlabdb01 /home/oracle$ cd $ORACLE_BASE/admin/$ORACLE_SID/bdump

2.檢視警告日誌中最後10行的資訊
ora10g@asdlabdb01 /oracle/app/oracle/admin/ora10g/bdump$ tail -10 alert_ora10g.log
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=32, OS id=31597
Thu Dec 15 06:23:57 2010
Completed: ALTER DATABASE OPEN
Thu Dec 15 06:23:57 2010
db_recovery_file_dest_size of 4096 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

此處是為了對比寫入前後資訊的不同。

3.在SQL*Plus中實現向alert日誌中寫入資訊的目的
ora10g@asdlabdb01 /oracle/app/oracle/admin/ora10g/bdump$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 15 06:24:33 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

sys@ora10g> exec SYS.DBMS_SYSTEM.KSDWRT(2, 'Alert message writed by Secooler.');

PL/SQL procedure successfully completed.

sys@ora10g> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

4.檢視alert日誌中寫入的內容
ora10g@asdlabdb01 /oracle/app/oracle/admin/ora10g/bdump$ tail -10 alert_ora10g.log
QMNC started with pid=32, OS id=31597
Thu Dec 15 06:23:57 2010
Completed: ALTER DATABASE OPEN
Thu Dec 15 06:23:57 2010
db_recovery_file_dest_size of 4096 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Dec 15 06:24:39 2010
Alert message writed by Secooler.

可見alert日誌中的最後兩行記錄了這條寫入的資訊（第一行表示寫入的時間，第二行便是寫入資訊的內容）。

5.知識擴充套件
1）DBMS_SYSTEM的引數資訊
sys@ora10g> desc SYS.DBMS_SYSTEM
……
PROCEDURE KSDWRT
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
DEST                           BINARY_INTEGER          IN
TST                            VARCHAR2                IN
……

從引數資訊上可以看到KSDWRT這個PROCEDURE有兩個引數。第二個引數TST便是寫入的資訊內容；那麼第一個引數都有幾種可用的值呢？具體有如下三種可取的值：
“1”: Write to the standard trace file（向標準Trace檔案中寫入資訊）
“2”: Write to the alert log（向alert日誌檔案中寫入資訊）
“3”: Write to both files at once（同時向Trace檔案和alert日誌檔案中寫入資訊）

本例中我們用到了“2”這個選項實現的向alert日誌檔案中寫入資訊。

如果使用“1”或“3”，我們也可以實現向Trace檔案中寫入資訊，該標準trace檔案位於“$ORACLE_BASE/admin/$ORACLE_SID/udump”目錄中。

6.小結
注意，使用“SYS.DBMS_SYSTEM.KSDWRT”實現向alert檔案和trace檔案中寫入資訊的方法並未經過Oracle驗證和支援！請評估並測試後慎用。

Good luck.

secooler
10.12.15

-- The End --

【alert】使用SYS.DBMS_SYSTEM.KSDWRT向Oracle實現向警告日誌中寫入資訊

相關文章