A diary of a Hungry Software Engineer: May 2011

May 31, 2011

If you want a background process to continue running after you log out, Use nohup command.

nohup command &

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.