May 12, 2011

How to get ORACLE_HOME by SQL

I had to get Oracle alert log file path by SQL on 9i, 10g.
Because I didn't want to force a user input it.

First I tried the following SQL sentence.
SQL> select * from sysman.mgmt$software_homes;

win2k3svrvminst OraHome92 ORACLE_HOME D:\oracle\ora92

win2k3svrvminst OraDb10g_home1 ORACLE_HOME F:\oracle\product\10.2.0\db_1
win2k3svrvminst Independent Products INDEPENDENT N/A
(copied on SQL Developer)

I didn't know which ORACLE_HOME is proper one.

I should keep searching and tried this.


SQL>select substr(file_spec,   1,   instr(file_spec,   '\',   -1,   2) -1) ORACLE_HOME
from dba_libraries
where library_name = 'DBMS_SUMADV_LIB';
(On Unix, substitute with '/'  )

This SQL showed me ORACLE_HOME, C:\oracle\product\10.2.0\db_1, but it required me as sysdba on 10g.

Because my application can't get sysdba's id and password, I had to keep searching.


SQL> set autopri on
SQL> var oracle_home varchar2(255)
SQL> exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME)

It works too, but I don't want to use PL/SQL procedure.

Finally, I quited searching.
But this article can help some people who want to know  "How to get ORACLE_HOME by using SQL".

May 10, 2011

How to know Oracle DB log files' path

ALERT LOG

Oracle writes the alert.log file to the directory as specified by BACKGROUND_DUMP_DEST parameter.
If it is not set, the alert.log will be created in the ORACLE_HOME/rdbms/trace directory.

SQL> show parameter BACKGROUND_DUMP_DEST


NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
background_dump_dest                 string
/u01/app/oracle/diag/rdbms/trace


LISTNER LOG

Oracle writes listener.log file to ORACLE_HOME/NETWORK/log directory in usual.
You can confirm the path of listner.log file using "lsnrctl status" command.


[oracle@helium ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-5月 -2011 14:55:15

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

LSNRCTLへようこそ。詳細は"help"と入力してください。

LSNRCTL> status
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 11.2.0.1.0 - Production
開始日                    13-4月 -2011 15:47:37
稼働時間                  26 日 23 時間 7 分 41 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/helium/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=helium.workslan)(PORT=1521)))
サービスのサマリー...
サービス"kiban.workslan"には、1件のインスタンスがあります。
  インスタンス"kiban"、状態READYには、このサービスに対する1件のハンドラがあります...
サービス"kibanXDB.workslan"には、1件のインスタンスがあります。
  インスタンス"kiban"、状態READYには、このサービスに対する1件のハンドラがあります...
コマンドは正常に終了しました。

May 2, 2011

How to log(write) facility and priority to syslog on Linux(RedHat)

On RHEL 5 or higher version.(CentOS 5 or higher ver.), logging the syslog facility and priority is possible.


Add -S or -SS to SYSLOGD_OPTIONS in /etc/sysconfig/syslog and restart the syslog service for the change to take effect.


example)

  $ cat /etc/sysconfig/syslog
  # Options to syslogd
  # -m 0 disables 'MARK' messages.
  # -r enables logging from remote machines
  # -x disables DNS lookups on messages recieved with -r
  # See syslogd(8) for more details
  SYSLOGD_OPTIONS="-m 0 -SS -r"
  $ service syslog restart



Now you can confirm the facility and priority in your syslog.
  $ cat /var/log messages

  May  1 04:02:02 helium syslogd 1.4.1: restart.
  May  2 13:09:55 helium kernel: Kernel logging (proc) stopped.
  May  2 13:09:55 helium kernel: Kernel log daemon terminating.
  May  2 13:09:57 helium exiting on signal 15 <-- before 
  May  2 13:09:57 helium syslogd 1.4.1: restart (remote reception). <-- after
  May  2 13:09:57 helium kernel: klogd 1.4.1, log source = /proc/kmsg started.