Trace and Alert log files on Oracle Database Server (11g)

Trace and Alert log files

Root ‘log dump’ location:
[sql]select value from v$parameter where name = ‘background_dump_dest’;[/sql]

Find the trace file path of current session use the following PL/SQL snippet:
[sql]select u_dump.value || ‘/’ || db_name.value || ‘_ora_’ || v$process.spid ||
nvl2(v$process.traceid, ‘_’ || v$process.traceid, null) || ‘.trc’ “Trace File”
from v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session on v$process.addr = v$session.paddr
where = ‘user_dump_dest’
and = ‘db_name’
and v$session.audsid = sys_context(‘userenv’, ‘sessionid’);[/sql]

This would output something similar to:

The ALERT file would be:

You could also create a DB table that would keep the alert log file and you do a select
to view a list of the logged items.


Credits to: Rene Nyffenegger


How to set an Oracle DB 10g to start automatically

Login to where the Oracle DB is installed and vi as root

[bash]vi /etc/oratab[/bash]

Edit the file, find and set the lines as follows:

vi cmd to type Insert key
vi cmd Esc to exit editing
vi cmd to write/save and exit :wq

Edit dbora, which is mainly to set the ORA home and owner and execute the listener to start or stop

[bash]vi /etc/init.d/dbora #file will be created if it does not exist[/bash]

Edit the file and enter the following as specific to your ORA environment vars:

# description: Oracle auto start-stop script.
# # Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
# # Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.



if [ ! -f $ORA_HOME/bin/dbstart ] then
echo “Oracle startup: cannot start”
exit fi

case “$1” in
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su $ORA_OWNER -c “$ORA_HOME/bin/dbstart $ORA_HOME” ;

# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su $ORA_OWNER -c “$ORA_HOME/bin/dbshut $ORA_HOME” ;

Set the appropriate privileges to the file:
[bash]chmod 750 /etc/init.d/dbora[/bash]

May need to set in /u01/app/oracle/product/10.2.0/db_1/bin/dbstart

…in some cases where the listener won’t start.

How to find the ORACLE_HOME path for Oracle DB 10g

Login to the server which you have the DB installed on
[bash]sqlplus /nolog #login to SQL*Plus console[/bash]
From SQL*Plus console:
[sql]conn user/pass as sysdba
var ohp varchar2(100);  –set a variable ‘ohp’
exec dbms_system.get_env(‘ORACLE_HOME’, &#58ohp) ; –call the get_env system-procedure
print ohp; –prinout the result[/sql]

You should then see an output print of the ORACLE_HOME path for the DB installed:



how to configure tnsname.ora, hosts and internet browser correctly

The following configuration should fix problems between tnsname, the hosts file and browser:

tnsnames.ora (file)


hosts (file)
[text]#       localhost    WEB1    WEB2[/text]

internet browser

under network/connection > LAN > Do not use proxy for: