Shulou(Shulou.com)06/01 Report--

Record an experiment that has just been done to configure multiple listener for a database (T02). The process is a bit tortuous.

(1) add two new test listeners. The configuration content of listener.ora (you can edit the file manually or use netca) is as follows:

(LISTENER is the original system; L1 and L2 are new monitoring for testing)

[sql] view plain copy# listener.ora Network Configuration File: d:\ oracle\ product\ 10.2.0\ db_1\ network\ admin\ listener.ora# Generated by Oracle configuration tools.L2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = PC1255-20110528) (PORT = 1523) L1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = PC1255-20110528) (PORT = 1522) SID_LIST_LISTENER = (SID_ LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:\ oracle\ product\ 10.2.0\ db_1) (PROGRAM = extproc)) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP) (HOST = PC1255-20110528) (PORT = 1521)

(2) configure tnsnames.ora so that the database (T02) can use the above three listeners at the same time:

[sql] view plain copy# tnsnames.ora Network Configuration File: d:\ oracle\ product\ 10.2.0\ db_1\ network\ admin\ tnsnames.ora# Generated by Oracle configuration tools.T01 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = PC1255-20110528) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = T01)) T02 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = PC1255-20110528) (PORT = 1521)) PROTOCOL = TCP) (HOST = PC1255-20110528) (PORT = 1522) (ADDRESS = (PROTOCOL = TCP) (HOST = PC1255-20110528) (PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = T02)) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1)) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)

(3) check and test and find problems:

Upon inspection, it is found that L1 and L2 can never register for the service except for the default LISTENER snooping:

[sql] view plain copyC:\ > lsnrctlLSNRCTL for 32-bit Windows: Version 10.2.0.1.0-Production on 24-July-2011 22:19:24Copyright (c) 1991, 2005, Oracle. All rights reserved. Welcome to LSNRCTL. Type "help" for information. LSNRCTL > status is connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1) LISTENER's STATUS---- alias LISTENER version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0-Production launch date 24-July-2011 22:14:18 uptime 0 days 0 hours 5 minutes 13 seconds tracking level off security ON: Local OS AuthenticationSNMP OFF listeners Parameter file D:\ oracle\ product\ 10.2.0\ db_1\ network\ admin\ listener.ora listener log file D:\ oracle\ product\ 10.2.0\ db_1\ network\ log\ listener.log listener endpoint summary. (DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (PIPENAME=\\.\ pipe\ EXTPROC1ipc) (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=PC1255-20110528) (PORT=1521)) service summary.. The service "+ ASM_XPT" contains 1 routine. Routine "+ asm", status BLOCKED, contains 1 handler for this service. The service "+ asm" contains 1 routine. Routine "+ asm", status BLOCKED, contains 1 handler for this service. The service "PLSExtProc" contains 1 routine. Routine "PLSExtProc", status UNKNOWN, contains 1 handler for this service. The service "T01" contains 1 routine. Routine "T01", status READY, contains a handler for this service. The service "t01XDB" contains 1 routine. Routine "T01", status READY, contains a handler for this service. The service "t01_XPT" contains 1 routine. Routine "T01", status READY, contains a handler for this service. Command execution succeeded LSNRCTL > stop is connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1) Command execution succeeded LSNRCTL > set curr L1 the current listener is L1LSNRCTL > status is connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=PC1255-20110528) (PORT=1522)) LISTENER's STATUS---- alias TNSLSNR for 32-bit Windows: Version 10.2.0.1.0-Production Startup date 24-July-2011 22:14:19 uptime 0 days 0 hours 5 minutes 26 seconds tracking level off security ON: Local OS AuthenticationSNMP OFF listener parameter file D:\ oracle\ product\ 10.2.0\ db_1\ network\ admin\ listener.ora listener log file D:\ oracle\ product\ 10.2.0\ db_1\ network\ log\ l1.log listener endpoint summary. (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=PC1255-20110528) (PORT=1522)) listeners do not support successful execution of service commands LSNRCTL > set curr L2 listeners are currently L2LSNRCTL > status listeners are connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=PC1255-20110528) (PORT=1523)) STATUS---- alias version of LISTENER TNSLSNR for 32-bit Windows: Version 10.2.0.1.0-Production launch Date 24-July-2011 22:14:19 uptime 0 days 0 hours 5 minutes 38 seconds tracking level off security ON: Local OS AuthenticationSNMP OFF listener parameter file D:\ oracle\ product\ 10.2.0\ db_1\ network\ admin\ listener.ora listener log file D:\ oracle\ product\ 10.2.0\ db_1\ network\ log\ l2.log listener endpoint summary. (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=PC1255-20110528) (PORT=1523)) listeners do not support successful execution of service commands

After the default LISTENER snooping is stopped, an error (ORA-12514) is reported when using tns for connection testing:

[sql] view plain copyC:\ Documents and Settings\ Administrator > sqlplus scott/tiger@t02SQL*Plus: Release 10.2.0.1.0-Production on Sunday July 24 22:20:06 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.ERROR:ORA-12514: TNS: the listener currently does not recognize the service requested in the connection descriptor

(4) Analysis of problems:

After a while of google, you find that if the default port is not 1521, you need to set up LOCAL_LISTENER to manually specify the listening alias:

When configuring the listener to listen on TCP/IP, you should enter the default port of 1521. If you do not, you must configure the LOCAL_LISTENER parameter in the intialization parameter file and resolve the listener name through a naming method.

Http://download.oracle.com/docs/cd/B13789_01/network.101/b10775/listenercfg.htm

(5) solve the problem (T02 here is T02 in the tnsnames.ora of step 2):

[sql] view plain copyC:\ Documents and Settings\ Administrator > set oracle_sid=t02C:\ Documents and Settings\ Administrator > sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0-Production on Sunday July 24 22:21:55 2011Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle routine. SQL > select status,instance_name from vantage instancecontrol status INSTANCE_NAME---- OPEN t02SQL > alter system set local_listener= "T02"; the system has changed. SQL >

(6) verify and retest:

Verify and find that L1 and L2 have been able to register for services normally:

[sql] view plain copyLSNRCTL > set curr L1 listener is L1LSNRCTL > status is connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=PC1255-20110528) (PORT=1522) STATUS---- alias L1 version of LISTENER TNSLSNR for 32-bit Windows: Version 10.2.0.1.0-Production start date 24-July-2011 22:14:19 uptime 0 days and 0 hours 32 minutes 54 seconds trace level off security ON: Local OS AuthenticationSNMP OFF listener parameter file D:\ oracle\ product\ 10.2.0\ db_1\ network\ admin\ listener.ora listener log file D:\ oracle\ product\ 10.2.0\ db_1\ network\ log\ l1.log listener endpoint summary. (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=PC1255-20110528) (PORT=1522)) Service Summary.. The service "T02" contains 1 routine. Routine "T02", status READY, contains a handler for this service. The service "t02XDB" contains 1 routine. Routine "T02", status READY, contains a handler for this service. The service "t02_XPT" contains 1 routine. Routine "T02", status READY, contains a handler for this service. Command executed successfully LSNRCTL > set curr L2 the current listener is L2LSNRCTL > status is connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=PC1255-20110528) (PORT=1523)) STATUS---- alias L2 version of LISTENER TNSLSNR for 32-bit Windows: Version 10.2.0.1.0-Production start date 24-July-2011 22:14:19 uptime 0 days small 33 minutes 6 seconds trace level off security ON: Local OS AuthenticationSNMP OFF listener parameter file D:\ oracle\ product\ 10.2.0\ db_1\ network\ admin\ listener.ora listener log file D:\ oracle\ product\ 10.2.0\ db_1\ network\ log\ l2.log listener endpoint summary. (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=PC1255-20110528) (PORT=1523)) service summary.. The service "T02" contains 1 routine. Routine "T02", status READY, contains a handler for this service. The service "t02XDB" contains 1 routine. Routine "T02", status READY, contains a handler for this service. The service "t02_XPT" contains 1 routine. Routine "T02", status READY, contains a handler for this service. Command executed successfully LSNRCTL >

After re-testing, it is found that scott users can log in normally:

[sql] view plain copyC:\ Documents and Settings\ Administrator > sqlplus scott/tiger@t02SQL*Plus: Release 10.2.0.1.0-Production on Sunday July 24 22:24:43 2011Copyright (c) 1982, 2005, Oracle. All rights reserved. Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-ProductionWith the Partitioning, OLAP and Data Mining optionsSQL >

(7) Summary

When the listening port is the default 1521, PMON will dynamically register the listening (listener)

When the listening port is not the default 1521, you need to use alter system set local_listener= "T02"; to register the listening manually.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.