Tag Archives: listener

11g, listener, network, OPatch, Oracle, Oracle 10g, oracle 9i

Enable ADMIN Tracing on Oracle Listener:

July 3, 2010 Larry Catt Leave a comment

Enable ADMIN Tracing on Oracle Listener:

1. Logon to your Oracle database server as the Oracle software owner.

2. Open up the LSNRCTL utility.

mylinux:>lsnrctl

LSNRCTL for LINUX: Version 10.2.0.4.0 – Production on 3-Jul-2010 15:17:05

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. If you are not using the default name of the Oracle listener (LISTENER) then define the Oracle listener name with the following command: set current_listener

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If you have established a password for your oracle listener set it in the LSNRCTL session with the following command: set password

LSNRCTL> set password oracle
The command completed successfully
LSNRCTL>

5. Turn on admin level tracing by specifying 10 or admin as the level with the following command: set trc_level

LSNRCTL> set trc_level 10
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to admin
The command completed successfully

LSNRCTL> set trc_level admin
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to admin
The command completed successfully
LSNRCTL>

6. Define the name of the trace file that will be produced by the listener process upon establishment of user connection with the command below: set trc_file

LSNRCTL> set trc_file admin_access_trace
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_file” set to admin_access_trace.trc
The command completed successfully
LSNRCTL>

NOTE: You do not have to set a trace directory by default the trace files are placed under: $ORACLE_HOME/network/trace

7. Save the changes made to the listener.ora file with the following command: save_config

LSNRCTL> save_config
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
Saved listener_orcl configuration parameters.
Listener Parameter File /opt/app/oracle/10.2.0/network/admin/listener.ora
Old Parameter File /opt/app/oracle/10.2.0/network/admin/listener.bak
The command completed successfully
LSNRCTL>

8. Exit out of LSNRCTL utility, change directories to ORACLE_HOME/network/trace, list the files present and you can see that the trace has already begun.

LSNRCTL> exit
mylinux:> ls -lrt
total 1008
-rw-r—– 1 oracle dba 408755 Jul 3 15:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Jul 3 15:40 user_access_trace.trc
-rw-r—– 1 oracle dba 34150 Jul 3 15:42 admin_access_trace.trc

9. Logon to the RDBMS as sysdba and exit.

mylinux:> sqlplus ‘system/devlmgr as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jul 3 15:44:02 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:>

10. Perform another listing of the files under the directory $ORACLE_HOME/network/admin and you will see that the file admin_access_trace.trc has grown by recording the connection
from user in step 9.

mylinux:> ls -lrt
total 1136
-rw-r—– 1 oracle dba 408755 Jul 3 15:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Jul 3 15:40 user_access_trace.trc
-rw-r—– 1 oracle dba 119638 Jul 3 15:47 admin_access_trace.trc
mylinux:>

11 This information can be used to show where connections in you database are coming from and other statistical information about the network connection being established with administrative options.

Larry J. Catt, OCP 91, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Listener Name incorrectly defined in lsnrctl

May 1, 2010 Larry Catt Leave a comment

Listener Name incorrectly defined in lsnrctl

Normally, connection to an Oracle database is performed through the use of an Oracle listener, whose job it is to listen on a specific machine and port for request to connect to a one or more database instances. The listener is controlled by a utility named lsnrctl located under $ORACLE_HOME/bin no matter the OS. By default Oracle will name you listener process LISTENER on a standard Oracle installation. However, this is not a good practice due to security concerns. In this article we will discuss the error you will receive if you have a non-default Listener name and specify the incorrect name while attempting to start the process. This article was written using a windowâ€™s based install but will work just as well on UNIX and Linux.

1. Switch directories to your $ORACLE_HOME/bin directory.

cd C:\oracle\product\10.2.0\db_1\bin
C:\oracle\product\10.2.0\db_1\BIN>

2. Start your listener utility with the command lsnrctl.

C:\oracle\product\10.2.0\db_1\BIN>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 – Production on 01-MAY-2010 10:30
:17

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. Believing that your listener’s name is listener attempt to startup the listener with the command start

LSNRCTL> start listener
Starting tnslsnr: please wait…

Failed to open service , error 106
0.
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 – Production
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or
a
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener_orcl.
log
TNS-01151: Missing listener name, listener, in LISTENER.ORA

Listener failed to start. See the error message(s) above…

LSNRCTL>

4. NOTE: the error message returned by lsnrctl states: that we are missing the listener name in the file listener.ora.

5. Navigate to the directory given for listener.ora file and view its contents. You will see that the actual name of the listener is listener_oracle not listener.

LISTENER_ORACLE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.110)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER_ORACLE =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
)

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

6. There is no reason to change the listener.ora file but only to give the correct listener name in the lsnrctl utilities start command. As shown below the listener startups correctly.

LSNRCTL> start listener_oracle
Starting tnslsnr: please wait…

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 – Production
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or
a
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener_oracl
e.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)
))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)
))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)
))
STATUS of the LISTENER
————————
Alias listener_oracle
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 – Produ
ction
Start Date 01-MAY-2010 11:04:00
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener_ora
cle.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL>

7. As shown above once we present the correct listener name, the listener starts without error.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

hp-ux, itanium, Oracle, Oracle 10g

Listener Configuration on HP-UX Itanium Server: HPUX Error: 239: Connection refused

March 23, 2010 Larry Catt Leave a comment

I was setting up a test instance for one of our development groups today and came across a rather strange error, which I have only encountered once before. Of course I forgot the solution and tried to google it, but found no successful solutions. Thus, this article: A friend of mine tried metalink and found the answer; I have been shying away from metalink since the website redesign.

I was trying to configure the listener for this new system. At first I configured listener.ora as I normally would and the listener appeared to start okay, however when I performed the lsnrctl status command I received the following error:

TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 239: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 239: Connection refused

I immediately went to the standby solution in this scenario and started up netca the network assistance tool to configure the listener and received the exact same error. Below is listed the solution in order of trial and error, I am not sure if this is limited to HP-UX itanium machines or not, but that is where I received the error.

Credit goes to: Muhil Jayaraman, One of the best Core Oracle DBAâ€™s and EBS DBAâ€™s I know. He is proof that after 15 years as an Oracle DBA, you can still learn new things by working with great people.

1. Started the listener with command lsnrctl start.

$ lsnrctl start

LSNRCTL for HPUX: Version 10.2.0.4.0 – Production on 23-MAR-2010 14:22:42

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

Starting /u01/oracle/bin/tnslsnr: please wait…

TNSLSNR for HPUX: Version 10.2.0.4.0 – Production
System parameter file is /u01/oracle/network/admin/listener.ora
Log messages written to /u01/oracle/network/admin/listener.log
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for HPUX: Version 10.2.0.4.0 – Production
Start Date 23-MAR-2010 14:22:42
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/network/admin/listener.ora
Listener Log File /u01/oracle/network/admin/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
$

2. The listener appears to start okay, however when you perform the command lsnrctl status you receive an error message.

$ lsnrctl status

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 239: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 239: Connection refused
$

3. Checking the OS layer and you see that no listener is currently running, with the command ps â€“ef.

$ ps -ef |grep tns
ORCL 21362 8423 0 14:24:15 pts/4 0:00 grep tns
$

4. SOLUTION: Add parameter SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF to listener.ora file.

5. Start the listener with the command lsnrctl start.

$ lsnrctl start

LSNRCTL for HPUX: Version 10.2.0.4.0 – Production on 23-MAR-2010 14:25:03

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

Starting /u01/oracle/bin/tnslsnr: please wait…

TNSLSNR for HPUX: Version 10.2.0.4.0 – Production
System parameter file is /u01/oracle/network/admin/listener.ora
Log messages written to /u01/oracle/network/admin/listener.log
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for HPUX: Version 10.2.0.4.0 – Production
Start Date 23-MAR-2010 14:25:05
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/network/admin/listener.ora
Listener Log File /u01/oracle/network/admin/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

6. Check the status of listener with the command lsnrctl status.

$ lsnrctl status

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for HPUX: Version 10.2.0.4.0 – Production
Start Date 23-MAR-2010 14:26:07
Uptime 0 days 0 hr. 0 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/network/admin/listener.ora
Listener Log File /u01/oracle/network/admin/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
Service “testdb” has 1 instance(s).
Instance “testdb”, status READY, has 1 handler(s) for this service…
Service “testdbXDB” has 1 instance(s).
Instance “testdb”, status READY, has 1 handler(s) for this service…
Service “testdb_XPT” has 1 instance(s).
Instance “testdb”, status READY, has 1 handler(s) for this service…
The command completed successfully

7. Check at the OS layer to see if the listener is running with the command ps â€“ef.

$ ps -ef|grep tns
ORCL 21440 1 0 14:26:07 ? 0:00 /u01/oracle/bin/tnslsnr LISTENER
-inherit
ORCL 21835 21821 0 14:27:28 pts/4 0:00 grep tns
$

And the problem with listener is resolved.

Larry J. Catt, OCP 9i, 10g.
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Securing Oracle Listener with a password:

February 21, 2010 Larry Catt Leave a comment

Normally, connection to an Oracle database is performed through the use of an Oracle LISTENER process which monitors a specific machine and port for request to connect to a one or more database instances. The LISTENER process is control by a utility named LSNRCTL which is located under $ORACLE_HOME/bin. The listener provides the main connection access to most Oracle database systems, thus if it is tempered with, it could prevent use of your database even though the RDBMS is up and running fine. In this article we will review password protecting your oracle LISTENER from unauthorized shutdown. This article was written using LINUX but will work just as well on any OS.

1. Logon to your Oracle database server as the Oracle software owner, switch directories to your $ORACLE_HOME/network/admin and view the file listener.ora with your chose of editor.

mylinux:> cd $ORACLE_HOME/network/admin
mylinux:> cat listener.ora
# LISTENER.ORA Network Configuration File:
/opt/app/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.110)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER_ORCL =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/app/oracle/10.2.0)
)

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/oracle/10.2.0)
(PROGRAM = extproc)
)
)

mylinux:>

NOTE: There exists no tag for PASSWORDS_{listener_name} = {new_password} which has not been established yet.

2. Exit the listener.ora file and startup the lsnrctl utility.

mylinux:> lsnrctl

LSNRCTL for LINUX: Version 10.2.0.4.0 – Production on 21-FEB-2010 16:30:52

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. If you are not using the default name of the Oracle listener (LISTENER) then define the Oracle listener name with the following command: set current_listener {listener_name}

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If your oracle listener is not currently running, start it up with the command: start

LSNRCTL> start
Starting /opt/app/oracle/10.2.0/bin/tnslsnr: please wait…

TNSLSNR for LINUX: Version 10.2.0.4.0 – Production
System parameter file is /opt/app/oracle/10.2.0/network/admin/listener.ora
Log messages written to
/opt/app/oracle/10.2.0/network/log/listener_orcl.log
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
STATUS of the LISTENER
————————
Alias listener_orcl
Version TNSLSNR for LINUX: Version 10.2.0.4.0 – Production
Start Date 21-FEB-2010 16:32:55
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/10.2.0/network/admin/listener.ora
Listener Log File
/opt/app/oracle/10.2.0/network/log/listener_orcl.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL>

5. To set a password for your listener execute the following lsnrctl command: change_password

NOTE: Hit return when asked for current password if none is set.

LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
Password changed for listener_orcl
The command completed successfully
LSNRCTL>

6. Save the changes made in the LSNRCTL utility with the command: save_config

7. Exit out of lsnrctl utility and open up the file listener.ora with you chose of editor.

LSNRCTL> exit
mylinux:> cat listener.ora
# LISTENER.ORA Network Configuration File:
/opt/app/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

#—-ADDED BY TNSLSNR 21-FEB-2010 16:37:01—
PASSWORDS_listener_orcl = 1DF5C2FD0FE9CFA2
#——————————————–
mylinux:>

NOTE: The tag PASSWORDS_{listener_name} = {new_password} has been added to the file listener.ora and the password is encrypted so it will not look like what you typed. You can shutdown you listener with the password string you original entered or the encrypted string. However, without the password you will not be able to shutdown the LISTENER process.

This completes securing oracle listener with a password.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Stop Tracing on Oracle Listener:

February 4, 2010 Larry Catt Leave a comment

Most Oracle database connects are made through the use of an Oracle process called the LISTENER which monitors a certain machine and port for connection requests to one or more database instances. There are times when a DBA or SA will want to monitor connects being made through the listener for various reasons. However, tracing the Oracle LISTENER process at the user, admin, or support level can consume large amounts of disk space; thus it should only be performed on a temporary basis. This article covers stopping of a listener trace at any the three levels and will work on UNIX, Linux, and Windows based machines.

1. Logon to your Oracle database server as the Oracle software owner.

2. Open up the LSNRCTL utility.

mylinux:>lsnrctl

LSNRCTL for LINUX: Version 10.2.0.4.0 – Production on 04-FEB-2010 19:17:05

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. If you are not using the default name of the Oracle listener (LISTENER) then define the Oracle listener name with the following command: set current_listener {listener_name}

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If you have established a password for your oracle listener set it in the LSNRCTL session with the following command: set password {listener_password}

LSNRCTL> set password oracle
The command completed successfully
LSNRCTL>

5. Turn off tracing by specifying 0 or off as the level with the following command: set trc_level {level}

LSNRCTL> set trc_level 0
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to off
The command completed successfully
LSNRCTL>

LSNRCTL> set trc_level off
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to off
The command completed successfully
LSNRCTL>

7. Save the changes made to the listener.ora file with the following command: save_config

8. This completes disabling of the Oracle Listener tracing process.

Larry J. Catt, OCP 91, 10g
oracle@allcompute.com
www.allcompute.com

linux, Oracle, Oracle 10g, oracle 9i

Enable Support Tracing on Oracle Listener:

December 23, 2009 Larry Catt Leave a comment

Most Oracle database connects are made through the use of an Oracle process called the LISTENER which monitors a certain machine and port for connection requests to one or more database instances. There are times when a DBA or SA will want to monitor connects being made through the listener for various reasons. There also exist times when you will have to open a SAR or TAR with oracle support, if they suspect you are having issues with your listener connections, they will ask for a SUPPORT level trace of your Oracle listener. This article covers the setup of SUPPORT level tracing of you Oracle Listener and will work on UNIX, Linux, and Windows based machines.

NOTE: Even very well trained DBAâ€™s may have a difficult time interpreting everything generated by this type of trace. When it is generated, it is normally sent to Oracle support, where someone how specializes in the Oracle LISTENER can review it.

1. Logon to your Oracle database server as the Oracle software owner.

2. Open up the LSNRCTL utility.

mylinux:>lsnrctl

LSNRCTL for LINUX: Version 10.2.0.4.0 – Production on 23-DEC-2009 17:17:05

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. If you are not using the default name of the Oracle listener (LISTENER) then define the Oracle listener name with the following command: set current_listener

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If you have established a password for your oracle listener set it in the LSNRCTL session with the following command: set password

LSNRCTL> set password oracle
The command completed successfully
LSNRCTL>

5. Turn on support level tracing by specifying 16 or support as the level with the following command: set trc_level

LSNRCTL> set trc_level 16
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to support
The command completed successfully
LSNRCTL>

LSNRCTL> set trc_level support
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to support
The command completed successfully
LSNRCTL>

6. Define the name of the trace file that will be produced by the listener process upon establishment of user connection with the command below: set trc_file

LSNRCTL> set trc_file support_access_trace
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_file” set to support_access_trace.trc
The command completed successfully
LSNRCTL>

NOTE: You do not have to set a trace directory by default the listener trace files are placed under: $ORACLE_HOME/network/trace

7. Save the changes made to the listener.ora file with the following command: save_config

8. Exit out of LSNRCTL utility, change directories to
$ORACLE_HOME/network/trace, list the files present and you will see that the trace has already begun.

mylinux:> ls -lrt
total 1440
-rw-r—– 1 oracle dba 408755 Dec 23 17:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Dec 23 17:40 user_access_trace.trc
-rw-r—– 1 oracle dba 231352 Dec 23 19:00 admin_access_trace.trc
-rw-r—– 1 oracle dba 35087 Dec 23 19:01
support_access_trace.trc
mylinux:>

9. Logon to the RDBMS as an administrator, regular user and exit.

mylinux:> sqlplus system/devlmgr@orcl

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Dec 23 19:03:18 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:> sqlplus joe/joe@orcl

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Dec 23 19:03:33 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:>

10. Perform another listing of the files under directory
$ORACLE_HOME/network/admin and you will see that the file support_access_trace.trc has grown by recording the connection from the users in step 9.

mylinux:> ls -lrt
total 1568
-rw-r—– 1 oracle dba 408755 Dec 23 17:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Dec 23 17:40 user_access_trace.trc
-rw-r—– 1 oracle dba 231352 Dec 23 19:00 admin_access_trace.trc
-rw-r—– 1 oracle dba 103777 Dec 23 19:03
support_access_trace.trc
mylinux:>

11 This information can be used to show where connections in you database are coming from and other statistical information about the network connections being established. However, it is designed to be used by Oracle Support.

Larry J. Catt, OCP 91, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, linux, Oracle, Oracle 10g, oracle 9i

Enable ADMIN Tracing on Oracle Listener:

December 13, 2009 Larry Catt Leave a comment