如何跟踪Oracle动态服务注册

Comments

1. maclean says

   2010/08/20 at 11:56

   PMON No Longer Registering Services & ‘ALTER SYSTEM REGISTER;’ Does Not Force Re-Registration of Services with TNS Listeners [ID 760948.1]

   Applies to:

   Oracle Net Services – Version: 10.2.0.2.0 to 10.2.0.4.0
   This problem can occur on any platform.

   Symptoms

   Server: 2-node Oracle10g R2 (10.2.0.3) RAC.

   After restarting the TNS Listeners while both RAC instances were up, ‘lsnrctl status’ no longer shows the RAC services register for the RAC instances.

   In other words the PMON is not registering with the restarted Listener whereas the other non-RAC instances are registering with this listener correctly.

   Attempts to for the re-registration of the instance using ‘ALTER SYSTEM REGISTER;’ does not help or have any affect on the PMON registration process with the TNS Listeners.

   Verified the LOCAL_LISTENER and REMOTE_LISTENER initialization parameters are set correctly for each RAC instance.

   Errors reported attempting to connect to the RAC services via Oracle Net:

   ORA-12514 TNS:listener could not resolve SERVICE_NAME given in connect descriptor 

   Other possible errors that may be reported when PMON has stopped registering its services with the TNS Listeners include:

   ORA-12516 TNS:listener could not find instance with matching protocol stack 
   ORA-12518 TNS:listener could not hand off client connection 
   ORA-12519 TNS:no appropriate service handler found 
   ORA-12520 TNS:listener could not find available handler for requested type of server 
   ORA-12528 TNS:listener: all appropriate instances are blocking new connections

   (Note: Some of these errors are more likely to occur when PMON has stopped registering/providing Service updates to the TNS Listeners, but before the TNS Listeners have been restarted.)

   Cause

   PMON spinning, not sending Service update details to TNS Listener.

   This is evident when the following PMON Trace Events have been set, but do not produce any output:

   Event 10246 was set – no output produced
   Event 10257 was set – no output produced

   ALTER SYSTEM REGISTER was issued – PMON still does not register

   This issue is caused by Bug 6017068 ‘PMON Spinning in Networking Code’

   Solution

   PMON is spinning, not sending Service update details to TNS Listener. Once PMON enters this condition, the only way out is to bounce the instance(s).

   To clear the problem schedule a restart the RAC instances.

   To fix the problem, download and apply patch 6017068 to avoid problem in the future.

   Reference:

   Bug 6017068 ‘PMON Spinning in Networking Code’

    

   回复
2. admin says

   2010/08/20 at 11:59

   Search Order for TNS files – listener.ora, sqlnet.ora, tnsnames.ora ..etc.

   Applies to:

   Oracle Net Services – Version: 9.2.0.1 to 11.0
   Information in this document applies to any platform.

   Goal

   The goal of this document is to have understanding on how Oracle Net searches for the configuration files listener.ora, sqlnet.ora, tnsnames.ora and cman.ora. This understanding would be useful while troubleshooting Oracle Net issues.

   Solution

   Oracle Net Services configuration files are not always located in the $ORACLE_HOME/network/admin directory. Depending on the file, Oracle Net uses a different search order to locate the file.

   
   Search order for the sqlnet.ora
   

       1. The directory specified by the TNS_ADMIN environment variable, if set .
      
   2. The $ORACLE_HOME/network/admin directory. 

    

   Search order for the cman.ora, listener.ora, and tnsnames.ora
   

       1. The directory specified by the TNS_ADMIN environment variable, if set.
      
   2. One of the following directories: 
           
   On Solaris:
                
   /var/opt/oracle
          
   On other platforms: 
               
   /etc 
      
   3. The $ORACLE_HOME/network/admin directory.

    

   回复
3. admin says

   2010/08/20 at 12:02

   Dynamic Registration and TNS_ADMIN [ID 181129.1]
   PURPOSE
   ——-

   The purpose of this document is to explain how dynamic service registration
   by PMON to the listener can cause intermittent and unexpected Net errors.

   SCOPE & APPLICATION
   ——————-

   This document is primarily aimed at DBAs and Support personnel.

   1.0 INTRODUCTION
   ================

   Note:76636.1 discusses the concept of dynamic registration in Net8i. As a
   result of dynamic registration of an instance by PMON, the listener will
   potentially lose some of the ‘static information’ it has. Specifically, this
   article discusses the effects of changing the static value of the TNS_ADMIN
   environment variable by PMON registration with the listener.

   A server process that has been spawned by the listener will inherit TNS_ADMIN
   from the listener. If this server process itself needs to create a Net connection,
   then the value of TNS_ADMIN is checked, in order to read Net config files (most
   commonly tnsnames.ora and sqlnet.ora). An example of such an operation is opening
   a database link.

   It is important to note that this article is pertinent only to the case where the
   client has initially made a remote connection (as opposed to a local (bequeath)
   connection), and where the client’s server process then makes a Net connection.

   The article is written for UNIX platforms.

   2.0 HOW IS TNS_ADMIN SET?
   =========================

   The TNS_ADMIN value used by the server is inherited from the listener. The
   value set in the listener is derived from one (or a combination) of three
   mechanisms:

   a. set TNS_ADMIN as an environment variable
   % setenv TNS_ADMIN /oracle/TNS_ADMIN

   When the listener is started, this environment variable is picked up, and
   inherited by the server processes spawned by the listener whenever a connection
   is made.

   b. explicity set TNS_ADMIN in the listener.ora
   The following excerpt from a listener.ora shows how to configure this:

   SID_LIST_LISTENER =
   (SID_LIST =
   …
   (SID_DESC =
   (SID_NAME = V817)
   (ORACLE_HOME = /oracle2/OFA_base/app/oracle/product/8.1.7)
   (ENVS = ‘TNS_ADMIN=/home/hpsupp/TNS_ADMIN’)
   )
   …

   When the listener is started, the value of TNS_ADMIN in the listener.ora
   overwrites the value picked up from the environment.

   c. PMON registers the instance
   At instance startup, PMON picks up the TNS_ADMIN environment variable (in
   the same way that the listener does in Section (a) above). When PMON
   subsequently registers this instance, this value of TNS_ADMIN is passed to
   the listener; causing PMON’s TNS_ADMIN value to overwrite the value the
   listener currently has.

   NB. If TNS_ADMIN is not set when PMON starts, then after registration, the
   listener’s TNS_ADMIN value is cleared (ie, behaves as if not set).

   3.0 VERIFYING TNS_ADMIN VALE BEING USED
   =======================================

   The three methods given here are commonly used to determine the TNS_ADMIN
   setting.

   a. Net listener level 16 trace

   A level 16 listener trace will show the service registration packets sent
   by PMON. As discussed in Note:76636.1, PMON initiates registration by
   sending the following CONNECT packet

   (CONNECT_DATA=
   (COMMAND=service_register_NSGR)
   )

   This is then followed by DATA packets, one of which will contain the
   environment variables passed to the listener, as shown in the following
   excerpt from a packet in the listener trace:
   |man,TNS_|
   |ADMIN=/h|
   |ome/hpsu|
   |pp/TNS_A|
   |DMIN,COB|

   This shows TNS_ADMIN=/home/hpsupp/TNS_ADMIN

   b. Net server level 16 trace

   A server trace will not show explicitly show the value of TNS_ADMIN.
   However, it will show the location and contents of the sqlnet.ora and
   tnsnames.ora files found (if any)

   For example, these are excerpts from a server trace file:
   Attempted load of system pfile source /home/hpsupp/TNS_ADMIN/sqlnet.ora
   Parameter source loaded successfully

   nncpldf_load_addrfile: initial load of names file
   /home/hpsupp/TNS_ADMIN/tnsnames.ora
   nncpldf_load_addrfile: success

   c. operating system trace of the listener

   If the operating system has a truss-like tracing utility, then this can be
   used to show the value of TNS_ADMIN inherited by server processes from the
   listener.
   Ensure that the truss (or equivalent) options used will follow forks, and
   print environment variables.

   eg,
   % truss -faeo /tmp/truss.out -p

   The following excpert shows the value of TNS_ADMIN, and the location of the
   config files:

   [535] execve(“/oracle2/OFA_base/app/oracle/product/8.1.7/bin/oracle”,
   0x7f7f0944, 0x7f7f0950) [entry]
   ….
   env[14] @ 7f7f0465: “TNS_ADMIN=/home/hpsupp/TNS_ADMIN1”
   ….
   [535] open(“<$ORACLE_HOME>/network/admin/tnsnames.ora”, O_RDONLY, 0666) = 12
   …
   [535] access(“<$ORACLE_HOME>/network/admin/sqlnet.ora”, F_OK) = 14
   …

   It may be useful to know that the search order for tnsnames.ora and
   sqlnet.ora is as follows:

   tnsnames.ora
   search for private tnsnames.ora
   $HOME/.tnsnames.ora (this is a hidden file)

   in addition to $HOME/.tnsnames.ora, search for system tnsnames.ora
   – this search is halted after the first success
   $TNS_ADMIN/tnsnames.ora
   /etc/tnsnames.ora or /var/opt/oralce/tnsnames.ora (platform dependant)
   $ORACLE_HOME/network/admin/tnsnames.ora

   sqlnet.ora
   $TNS_ADMIN/sqlnet.ora
   $ORACLE_HOME/network/admin/sqlnet.ora

   NB, for server processes $HOME/.sqlnet.ora is not read. This file is for
   client processes only.

   4.0 RECTIFYING THE PROBLEM
   ==========================

   The problem commonly occurs when the user that has started the database does
   not have the correct value of the environment variable TNS_ADMIN set.

   For example, the oracle user may normally start the database, and has TNS_ADMIN
   set correctly. However, if another user shutsdown and startsup the database as
   part of a nightly backup strategy, then this other user must also have
   TNS_ADMIN set correctly.

   The easiest way to resolve this is to ensure TNS_ADMIN is set correctly for the
   ‘other’ user.

   Alternatively, dynamic registration can be stopped.
   See Note:130574.1 and Note:103143.1 for discussions on this.

   5.0 SYMPTOMS
   ============

   The symptoms of this problem depend very much on the operation in question.
   Essentially, any opertation that relies on a server process reading Net8 config
   files that reside in the correct TNS_ADMIN will potentially fail or behave
   incorrectly. Examples are given below.

   The misbehaviour may well be intermittent, as there can be a window where PMON
   has not yet registered the instance with a previously started listener.

   Also, be aware that the symptoms given here cannot be solely attributed to
   dynamic registration of TNS_ADMIN.

   a. net server tracing

   To enable Net server tracing, sqlnet.ora must first be read. If TNS_ADMIN
   is incorrect, then it may be possible that
   i. server tracing is not produced
   ii. server trace written to unexpected location

   b. database links

   To resolve the alias for a database link, tnsnames.ora and (possibly)
   sqlnet.ora will need to be read. If TNS_ADMIN is not correctly set, this may
   result in

   ORA-12154: TNS:could not resolve service name

   c. external procedures

   Similarly to database links, invoking extproc will read tnsnames.ora and
   (possibly) sqlnet.ora. If TNS_ADMIN is not correctly set, this may result in

   ORA-28575: unable to open RPC connection to external procedure agent

   d. Advanced Security Option

   Any ASO operation that relies on a server process checking the settings in
   sqlnet.ora.

   The simplest way to diagnose these errors is to start with a level 16 (aka
   SUPPORT) Net server trace (unless (a) is being diagnosed !)

   回复
4. admin says

   2010/08/20 at 12:03

   Hdr: 6017068 10.2.0.2 NET 10.2.0.2 PRODID-115 PORTID-46
   Abstract: PMON SPINNING IN NETWORKING CODE
   Problem:
   ——–
   This was logged out of the original bug 5944584 for clarity. In that bug, the
   customer reports that PMON stops registering instance information with the
   listener. After a while the service becomes BLOCKED and the clients are
   unable to connect due to ORA-12516 errors.

   ALTER SYSTEM REGISTER was issued – PMON still does not register
   Event 10246 was set – no output produced
   Event 10257 was set – no output produced
   “strace” of PMON process – produces no output

   Once PMON enters this condition, the only way out is to bounce the instance

   Versions:
   ———
   10.2.0.2

   Diagnostic Analysis:
   ——————–
   Will follow later in the bug

   Reproducibility:
   —————-
   Inconsistent. At customer site only

   Test Case:
   ———-
   Not feasible

   Workaround:
   ———–
   None known

   Related Bug(s):
   —————
   Bug 5944584 – RDBMS bug for same problem

   Location Of Information (i.e. WRVMS):
   ————————————-

   Topography:
   ———–
   N/A

   Stack Trace and Errors:
   ———————–
   nlsqInsert
   nstoHandleEventTO
   nstoToqWalk
   nsevwait
   ksnwait
   ksliwat
   kslwaitns
   kskthbwt
   kslwait
   ksuclnwt
   ksucln
   ksbrdp
   opirip
   opidrv
   sou2o
   opimai_real
   main

   Configuration Files:
   ———————
   Available if needed

   24-hour Contact Information For P1 Bugs:
   —————————————-

   Dial-In Information:
   ——————–

   Additional Environment Information:
   ———————————–

   Memory Leak Information:
   ————————

   Network Tracing:
   —————-
   Please see problem statement for tracing tried.

   NET tracing was not requested because problem is unpredictable. It occurs
   within the PMON process and will go away if instance is bounced.

   回复
5. admin says

   2010/08/20 at 12:04

   Issues Affecting Automatic Service Registration

   Contents:

   1.0 Service registration
   1.1 Configuring service registration
   1.2 Registering information with the default local listener
   1.3 Registering information with a non-default listener
   1.4 Registering information with a remote listener
   2.0 Known issues with instance registration
   2.1 ORA-12514 errors connecting to registered instances
   2.2 Listeners intermittently crashing
   2.3 Instance fails to register with instance if ANO in use
   2.4 SDU and TDU are overwritten by dynamically registered instances
   2.5 Dynamically registered instances don’t work with prespawned servers
   2.6 Environment picked up from registered instance, not the listener
   2.7 ORA-00119 Error starting a 9.x instance with LOCAL_LISTENER defined
   3.0 How to disable automatic instance registration

   1.0 Service registration
   ————————

   Prior to Oracle8i (8.1.x), information about database instances had to be
   manually configured in the LISTENER.ORA file. From Oracle8i onward, database
   instances can register themselves with the Oracle Net Listener upon instance
   startup.

   Automatic Service Registration reduces the administrative overhead for
   multiple databases or instances. Information about the services, to which the
   Listener forwards client requests, is registered with the Listener.

   Service registration is performed by the PMON process, a background process
   of each instance, to register instance-specific information with the local
   Listener. PMON sends the current state and load of the instance and Shared
   Servers/Dispatchers. The registered information enables the Listener to
   forward client connection requests to the appropriate service handler. Service
   registration does not require any configuration in the LISTENER.ORA file.

   The initialisation parameter SERVICE_NAMES identifies which database service(s)
   an instance belongs to. Upon startup, each instance registers with the
   Listeners of other instances belonging to the same service. During database
   operations, instances of each service pass information about CPU usage and
   current connection counts to all of the Listeners of the same services. This
   enables dynamic load balancing and connection failover.

   For Oracle8i/9i databases, the Listener uses the dynamic service information
   about the database/instance it receives through Automatic Service Registration
   before using statically configured information in the LISTENER.ORA file.
   Dynamic service registration is configured in the database initialization file.
   It does not require any configuration in the LISTENER.ORA file. However,
   Listener configuration must be synchronized with the information in the
   database initialization file.

   1.1 Configuring Service Registration
   ————————————

   To ensure that Service Registration works properly, the initialization
   parameter file should contain the following parameters:

   SERVICE_NAMES for the database service name
   INSTANCE_NAME for the instance name

   For example:

   SERVICE_NAMES=sales.us.acme.com
   INSTANCE_NAME=sales

   The value of SERVICE_NAMES parameter defaults to the global database name,
   a name comprising the DB_NAME and DB_DOMAIN parameters in the initialization
   parameter file, entered during installation or database creation. The value
   of INSTANCE_NAME parameter defaults to the SID, entered during installation
   or database creation.

   1.2 Registering information with the default local listener
   ———————————————————–

   By default, the PMON process registers service information with its local
   Listener on the default local address of TCP/IP port 1521. As long as the
   Listener configuration is synchronized with the database configuration, PMON
   can register service information with a non-default local Listener or a remote
   Listener on another node. Synchronization is simply a matter of specifying the
   protocol address of the Listener in the LISTENER.ORA file and the location of
   the Listener in the initialization parameter file.

   1.3 Registering information with a non-default listener
   ——————————————————-

   If you want PMON to register with a local listener that does not use TCP/IP
   port 1521, configure the LOCAL_LISTENER parameter in the initialization
   parameter file to locate the local listener.

   For a Shared Server (aka MTS) environment, you can alternatively use the
   LISTENER attribute of the DISPATCHERS or MTS_DISPATCHERS parameter in the
   initialization parameter file to register Dispatchers with a non-default local
   Listener.

   Because both the LOCAL_LISTENER parameter and the LISTENER attribute enable
   PMON to register Dispatcher information with the Listener, it is not necessary
   to specify both the parameter and the attribute if the Listener values are the
   same.

   Set the LOCAL_LISTENER parameter as follows:
   LOCAL_LISTENER=

   Set the LISTENER attribute in Oracle8i as follows:
   MTS_DISPATCHERS=”(PROTOCOL=tcp)(LISTENER=)”

   Set the LISTENER attribute in Oracle9i as follows:
   DISPATCHERS=”(PROTOCOL=tcp)(LISTENER=)”

   The is resolved to the listening end-point through a naming
   method, such as a TNSNAMES.ORA file on the database server.

   For example, if the Listener is configured to listen on port 1421 rather than
   1521, define the LOCAL_LISTENER parameter in the initialization parameter file
   as follows:

   LOCAL_LISTENER=listener1

   Using the same Listener example, define the LISTENER attribute as follows:

   DISPATCHERS=”(PROTOCOL=tcp)(LISTENER=listener1)”

   Listener1 must be resolvable on the server so it would need to be defined in
   the TNSNAMES.ORA file as follows:

   listener1=
   (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1421))
   )

   You can also dynamically update the LOCAL_LISTENER parameter using the SQL
   statement ALTER SYSTEM SET:

   SQL> ALTER SYSTEM SET LOCAL_LISTENER=’‘ SID=’‘;

   If you set the parameter to null, the default local address of TCP/IP port
   1521 is assumed.

   SQL> ALTER SYSTEM SET LOCAL_LISTENER=”;

   The LISTENER attribute overrides the LOCAL_LISTENER parameter. As a result,
   the SQL statement ‘ALTER SYSTEM SET LOCAL_LISTENER …’ does not affect the
   setting of the LISTENER attribute.

   1.4 Registering information with a remote listener
   ————————————————–

   Registration to remote listeners, such as in the case of Oracle9i Real
   Application Clusters (RAC), can be configured for Shared Server or Dedicated
   Server environments.

   If you want PMON to register with a remote Listener, configure the
   REMOTE_LISTENER parameter in the initialization parameter file to locate the
   remote listener.

   For a Shared Server environment, you can alternatively use the LISTENER
   attribute of the DISPATCHERS parameter in the initialization parameter file to
   register the Dispatchers with any Listener. If running MTS under Oracle8i,
   the LISTENER attribute is set in the MTS_DISPATCHERS parameter.

   Because both the REMOTE_LISTENER parameter and the LISTENER attribute enable
   PMON to register Dispatcher information with the Listener, it is not necessary
   to specify both the parameter and the attribute if the Listener values are the
   same.

   Set the REMOTE_LISTENER parameter as follows:
   REMOTE_LISTENER=

   Set the LISTENER attribute in Oracle8i as follows:
   MTS_DISPATCHERS=”(PROTOCOL=tcp)(LISTENER=)”

   Set the LISTENER attribute in Oracle9i as follows:
   DISPATCHERS=”(PROTOCOL=tcp)(LISTENER=)”

   The is then resolved to the listening end-point through a
   naming method, such as TNSNAMES.ORA file on the database server.

   For example, if separate Listeners are configured to listen on port 1521 on
   servers sales1-server and sales2-server, you can set the REMOTE_LISTENER
   parameter in the initialization file for the instance on host sales1-server as
   follows:

   REMOTE_LISTENER=listener_sales2

   You can set the REMOTE_LISTENER parameter in the initialization file for the
   instance on host sales2-server as follows:

   REMOTE_LISTENER=listener_sales1

   You can then resolve listener_sales2 in the local TNSNAMES.ORA on sales1-server
   as follows:

   listener_sales2=
   (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))
   )

   Similarly, you can resolve listener_sales1 in the local TNSNAMES.ORA file on
   sales2-server as follows:

   listener_sales1=
   (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
   )

   If Shared Server is configured, you can set the DISPATCHERS parameter in the
   initialization parameter file as follows:

   DISPATCHERS=”(PROTOCOL=tcp)(LISTENER=listeners_sales)”

   You can then resolve listener_sales in the local TNSNAMES.ORA as follows:

   listeners_sales=
   (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))
   )

   You can also dynamically update the REMOTE_LISTENER parameter using the SQL
   statement ALTER SYSTEM SET:

   SQL> ALTER SYSTEM SET REMOTE_LISTENER=’listener_alias’;

   If you set the parameter to null, then PMON de-registers information with the
   remote listener with which it had previously registered information.

   SQL> ALTER SYSTEM SET REMOTE_LISTENER=”;

   The LISTENER attribute overrides the REMOTE_LISTENER parameter. As a result,
   the SQL statement ALTER SYSTEM SET REMOTE_LISTENER does not affect the setting
   of this attribute.

   2.0 Known issues with instance registration
   ——————————————-

   There are several known issues with instance registration. Most known
   issues are resolved in the 9.2.0.2 patchset.

   2.1 ORA-12514 errors connecting to registered instances
   ——————————————————-

   When attempting to connect to Oracle8i using a connect descriptor containing a
   SERVICE_NAME entry, you may receive the following error:

   ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
   descriptor

   The error means that the client has connected to the Listener but is
   requesting a connection to SERVICE_NAME that the Listener does not have a
   service handler for. This is usually not a problem with Service Registration,
   but since the SERVICE_NAME entry that the client is attempting to connect to
   is almost always a dynamically registered service, it needs to be addressed
   here.

   There are several reasons that error ORA-12514 might occur:

   1) The instance that the client is attempting to connect to is not registered
   with the Listener because the instance cannot locate the correct Listener
   address in order to register itself.

   If the LOCAL_LISTENER parameter is present in the INIT.ORA file, it will
   attempt to resolve to an address. The instance will attempt to connect to
   a Listener on that address to register itself.

   If the LOCAL_LISTENER parameter is not present in the INIT.ORA file, the
   instance will attempt to connect to the Default Listener using the default
   address of TCP/IP port 1521 or IPC key PNPKEY.

   2) The instance has registered with the listener, but the SERVICE_NAME that
   the client is attempting to connect to does not match the SERVICE that
   PMON has registered with the Listener.

   The following steps show which SERVICE_NAME is registering with the
   Listener:

   – Run the listener control utility (lsnrctl).
   – At the LSNRCTL prompt, type “set displaymode verbose” and press Enter.
   – At the LSNRCTL prompt, type “services” and press Enter.

   Ensure that the TNSNAMES.ORA entry for the SERVICE_NAME parameter exactly
   matches a service that is registered with the Listener.

   3) The instance is down.

   One of the benefits of using service registration is that the Listener is
   aware of the state of the instance.

   When the instance is up, it registers itself with the Listener, allowing
   the Listener to service incoming requests for it.

   When the instance is down, it will not be registered with the Listener. If
   the instance is not registered with the Listener, the Listener will refuse
   incoming requests for it, and clients receive error ORA-12514.

   4) The instance and Listener are both up, but the instance has not yet
   registered with the Listener.

   When the instance is started, it attempts to connect to register with the
   local Listener. It continues to check for the Listener about once every
   60 seconds.

   If the Listener is up when the instance is started, service registration
   should take place as the instance starts. If the Listener comes up after
   the instance, it will take up to a minute or so for the instance to
   register itself with the Listener. This is usually not an issue, but if
   the Listener is stopped then started, users may fail to connect until the
   the instance successfully re-registers with the Listener.

   You can check to see whether the instance has registered itself using the
   lsnrctl commands described in 2).

   To resolve the ORA-12514 errors, you need to either resolve the registration
   problems, resolve the problems with the SERVICE_NAME not matching what the name
   that is actually registering with the listener, or define a static service
   handler in the LISTENER.ORA file and modify the TNSNAMES.ORA file to connect
   to the newly defined SID instead of the SERVICE_NAME.

   2.2 Listeners intermittently crashing
   ————————————-

   Instance registration can cause listeners to intermittently crash.

   In a hybrid setup with a 64-bit Listener and a 32-bit instance, the Listener
   can intermittently hang or crash during Service Registration or updates. This
   may occurs after the Listener has been running for some time.

   In the event of a Listener intermittently crashing, one of the first things
   to check is whether the Listener has service handlers for multiple instances.
   The easiest way to do this is to run the ‘lsnrctl services’ command and check
   whether other instances are registered with the Listener. If there are other
   instances registering with the Listener, the service updates will also appear
   in the LISTENER.LOG file.

   There are several workarounds for this:

   – Disable Service Registration and use only static service handlers. Please
   see below for more information about disabling Automatic Service Registration.

   – Force the 32-bit instance to register exclusively with a 32-bit Listener,
   preferably of the same version as the instance.

   This was patched in 8.1.7.4 and 9.0.1.3 on Solaris and is fixed in 9.2.0.2.

   Refer Bug:2187760.

   2.3 Instance fails to register with instance if ASO in use
   ———————————————————-

   Installing Oracle Security Option (ASO), formerly known as Oracle Networking
   Option – ANO, may be problematic.

   If the following entries are present in the SQLNET.ORA file on the database
   server, Automatic Instance Registration will not occur:

   SQLNET.ENCRYPTION_CLIENT=REQUIRED
   SQLNET.ENCRYPTION_SERVER=REQUIRED

   If you either disable encryption or change these values to ACCEPTED or
   REQUESTED, instance registration will work correctly:

   SQLNET.ENCRYPTION_CLIENT=REQUESTED
   SQLNET.ENCRYPTION_SERVER=REQUESTED

   The other workaround is to use static service handlers instead of Service
   Registration.

   As of Oracle9i, this issue still exists.

   Refer Bug:887753.

   2.4 SDU and TDU are overwritten by dynamically registered instances
   ——————————————————————-

   Dedicated service handlers registered dynamically with a TNS listener will
   always use the default maximum SDU size (usually 2048 bytes).

   The SDU parameters are configured in the LISTENER.ORA file in the sid_desc
   section. When the Listener is started, the service handler that is started
   based on the SID_DESC section will reflect the specified SDU size.

   The problem is that with Service Registration, there is no place to set the
   SDU size for dedicated service handlers, so the default value (2048) is
   always used.

   There are a couple of workarounds for this issue:

   1) Disable Service Registration and connect using the static service handlers.

   2) Use Shared Server/MTS. You can set SDU in the DISPATCHERS or
   MTS_DISPATCHERS parameter.

   DISPATCHERS=”(description=(SDU=8192)(address=(protocol=tcp)\
   (host=)))(dispatchers=1)”

   When the Dispatchers are started, they will reflect the specified SDU size.

   Refer Bug:1113588

   IMPORTANT NOTE: Beginning with version 9.2.0.4, it is possible to workaround
   the SDU issue mentioned above by setting the following sqlnet.ora file parameter:

   DEFAULT_SDU_SIZE

   Example:

   DEFAULT_SDU_SIZE=32767

   Please refer to Note:1113588.8 New SQLNET.ORA parameter DEFAULT_SDU_SIZE

   2.5 Dynamically registered instances do not work with prespawned servers
   ————————————————————————

   Dedicated service handlers registered dynamically with a TNS listener will not
   use prespawned servers.

   The PRESPAWN parameters are configured in the LISTENER.ORA file in the
   SID_DESC section. Prespawned Dedicated Server processes are pre-started by
   the Listener before any incoming connection requests.

   The problem is that with Service Registration, there is no place to configure
   prespawn.

   The solution is to ensure that the dynamic registration uses a different
   service name than the static registration. Clients can then select the static
   prespawned service if desired to attach to the prespawned dedicated servers.

   Starting from Oracle9i, pre-started dedicated server processes are no longer
   supported. Instead, configure Shared Server to improve scalability and system
   resource usage.

   2.6 Environment picked up from the registered instance, not the Listener
   ————————————————————————

   A server process that has been spawned by the Listener will inherit TNS_ADMIN
   from the Listener. If this server process itself needs to create a new
   connection, such as when opening a database link, the value of TNS_ADMIN is
   checked in order to locate Net configuration files. If the Net
   configuration files are not found, or incorrect files read, a TNS-12154 error
   could result.

   At instance startup, PMON picks up the TNS_ADMIN environment variable. When
   PMON subsequently registers the instance with the Listener, the value of
   TNS_ADMIN is passed to the Listener causing PMON’s TNS_ADMIN value to
   overwrite the value that the Listener had. If TNS_ADMIN is not set when PMON
   starts, after registration the Listener’s TNS_ADMIN value is cleared.

   This is only a problem if the user that starts the database and Listener do
   not have the same value of TNS_ADMIN set in their environment.

   The way to resolve this is to either disable Instance Registration or ensure
   that the users that start the database and Listener have the same value for
   TNS_ADMIN.

   2.7 ORA-00119 Error starting a 9.x instance with LOCAL_LISTENER defined
   ———————————————————————–
   A common method if disabling instance registration is to set the
   LOCAL_LISTENER parameter to a dummy alias or address. Having the parameter
   set keep the listener from contacting the listener and therefore disable the
   default registration from occurring.

   In version 8.x, LOCAL_LISTENER could be set to a dummy alias and the instance
   would whether the alias was resolvable or not.

   In version 9.x, the LOCAL_LISTENER must be resolvable or the instance will fail
   to start, returning the following errors:

   ORA-00119: invalid specification for system parameter local_listener
   ORA-00132: syntax error or unresolved network name ‘DUMMY’
   ORA-01078: failure in processing system parameters

   To resolve these errors, either use an actual address in the LOCAL_LISTENER
   parameter or set up an alias in the tnsnames.ora file on the server to resolve
   the alias to an address.

   To enable instance registration to a non-default listener, this address should
   match the listener’s address.

   To disable default instance registration, this address should not match the
   listener’s address.

   3.0 How to disable automatic instance registration
   ————————————————–

   There are circumstances, for example to work around known issues, when it
   may be desirable to disable Automatic Service registration of an instance to
   prevent the Instance from registering with the Listener.

   By default, PMON registers with the local Listener at the default local
   address of TCP/IP port 1521. This behavior can only be disabled for Oracle8i
   versions 8.1.5 and 8.1.7.

   There are several ways to avoid this behavior:

   1) For versions 8.1.5 and 8.1.7 only, set the following INIT.ORA event to
   disable Automatic Service Registration:

   EVENT=”10258 TRACE NAME CONTEXT FOREVER, LEVEL 4″

   Note: Due to Bug:1278836, the event does not apply for 8.1.6.

   2) Do not run any Listeners on port 1521. PMON will not be able to locate the
   Listener if it is not either specified in the LOCAL_LISTENER parameter or
   located on this default port. If PMON cannot find the Listener, it cannot
   register with it.

   3) Use the INIT.ORA LOCAL_LISTENER parameter to define a dummy address.
   Automatic Service Registration will not occur if the LOCAL_LISTENER
   parameter is present. PMON will reject the invalid address and will not
   attempt to register with port 1521. In version 8.x, LOCAL_LISTENER can be
   set to either a dummy alias or an address other than the address that the
   listener is running on. In 9.x, LOCAL_LISTENER can still be set to an
   address, but if it is set to a dummy alias, the alias must be resolvable
   to an address other than the address that the listener is running on.

   For version 8.x, add the following parameter to the init.ora file:

   LOCAL_LISTENER=dummy
   -or-
   LOCAL_LISTENER=”(address=(protocol=tcp)(host=)(port=))”

   For version 9.x, run the following:

   SQL> ALTER SYSTEM SET LOCAL_LISTENER=dummy SCOPE=SPFILE;
   -or-
   SQL> ALTER SYSTEM SET LOCAL_LISTENER=”(address=(protocol=tcp)
   (host=)(port=))” SCOPE=SPF

   回复
6. admin says

   2010/08/20 at 12:05

   RAC Instance Status Shows Ready Zero Handlers For The Service

   Applies to:

   Oracle Net Services – Version: 10.1.0.2.0 to 10.2.0.1.0
   This problem can occur on any platform.
   TNS-12516

   Symptoms

   Connections to RAC database fail with error TNS-12516 TNS:listener could not find instance with matching protocol stack.

   Listener services shows service is registered and instance is ready but 0 handles.

   Service “ORCL” has 1 instance(s).
   Instance “ORCL1”, status READY, has 0 handler(s) for this service…

   Cause

   LOCAL_LISTENER and REMOTE_LISTENER parameters contain both the VIP and physical hostnames of the nodes in the RAC cluster.

   Solution

   Setup the spfile/pfile to have only the VIP addresses of the node. For example:

   LOCAL_LISTENER = ‘LOCAL_LISTENER’
   REMOTE_LISTENER = ‘REMOTE_LISTENERS’

   And in the TNSNAMES.ORA file

   LOCAL_LISTENER = (ADDRESS = (PROTOCOL = TCP)(HOST = VIP1)(PORT = 1521))

   REMOTE_LISTENERS = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = VIP1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = VIP2)(PORT = 1521)))

    

   回复
7. admin says

   2010/08/20 at 12:06

   Repeating ‘* Service_died * 0’ Messages In The 9i R2 Listener.Log File

   Applies to:

   Oracle Net Services – Version: 8.1.7.0.0 to 9.2.0.8.0
   This problem can occur on any platform.

   Symptoms

   Server: Oracle9i R2 (9.2.0.8.0) on IBM AIX Based Systems (64-bit) 5.3 – ML 07

   The following errors are constantly being reported in the listener.log file for the 9i R2 TNS Listener:

   29-JAN-2009 20:25:58 * service_died * 0
   29-JAN-2009 20:26:29 * service_died * 0

   Cause

   The ‘service_died * 0’ message being reported in the listener.log for the 9i R2 TNS Listener, which is runing on the default port, 1521 because of one or more Oracle10g (or higher) database instances attempting to register service handler information with the earlier (9.2.x) release TNS Listener.

   The details of the registration attempt can be seen in the SUPPORT level TNS Listener trace file for the 9i R2 TNS Listener.

   For example:

   ————-
   ….
   [29-JAN-2009 20:24:58:296] nsprecv: reading from transport…
   [29-JAN-2009 20:24:58:296] nttrd: entry
   [29-JAN-2009 20:24:58:296] nttrd: socket 14 had bytes read=104
   [29-JAN-2009 20:24:58:296] nttrd: exit
   [29-JAN-2009 20:24:58:296] nsprecv: 104 bytes from transport
   [29-JAN-2009 20:24:58:296] nsprecv: tlen=104, plen=104, type=1
   [29-JAN-2009 20:24:58:296] nsprecv: packet dump
   [29-JAN-2009 20:24:58:296] nsprecv: 00 68 00 00 01 00 00 00 |.h……|
   [29-JAN-2009 20:24:58:296] nsprecv: 01 39 01 2C 00 00 20 00 |.9.,….|
   [29-JAN-2009 20:24:58:296] nsprecv: 7F FF 7F 08 00 00 00 01 |……..|
   [29-JAN-2009 20:24:58:296] nsprecv: 00 2E 00 3A 00 00 00 00 |…:….|
   [29-JAN-2009 20:24:58:296] nsprecv: 41 41 00 00 00 00 00 00 |AA……|
   [29-JAN-2009 20:24:58:296] nsprecv: 00 00 00 00 00 00 00 00 |……..|
   [29-JAN-2009 20:24:58:296] nsprecv: 00 00 00 00 00 00 00 00 |……..|
   [29-JAN-2009 20:24:58:296] nsprecv: 00 00 28 43 4F 4E 4E 45 |..(CONNE|
   [29-JAN-2009 20:24:58:296] nsprecv: 43 54 5F 44 41 54 41 3D |CT_DATA=|
   [29-JAN-2009 20:24:58:296] nsprecv: 28 43 4F 4D 4D 41 4E 44 |(COMMAND|
   [29-JAN-2009 20:24:58:296] nsprecv: 3D 73 65 72 76 69 63 65 |=service|
   [29-JAN-2009 20:24:58:296] nsprecv: 5F 72 65 67 69 73 74 65 |_registe|
   [29-JAN-2009 20:24:58:296] nsprecv: 72 5F 4E 53 47 52 29 29 |r_NSGR))|
   [29-JAN-2009 20:24:58:296] nsprecv: normal exit
   [29-JAN-2009 20:24:58:296] nscon: got NSPTCN packet
   ….
   —A few packets later:
   ….
   ….
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 00 07 00 00 00 22 |…….”|
   [29-JAN-2009 20:24:58:307] nsprecv: 5A EC F8 00 00 00 05 00 |Z…….|
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 00 00 00 00 01 00 |……..|
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 00 00 00 00 01 10 |……..|
   [29-JAN-2009 20:24:58:307] nsprecv: 97 63 70 6F 72 61 64 73 |.cporadv|
   [29-JAN-2009 20:24:58:307] nsprecv: 30 37 31 58 44 42 00 07 |001XDB..|
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 00 14 83 40 48 07 |…..@H.|
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 00 22 74 EC 70 00 |…”t.p.|
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 00 00 00 00 00 00 |……..|
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 09 00 00 00 00 07 |……..|
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 00 14 83 40 70 00 |…..@p.|
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 04 00 00 00 00 00 |……..|
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 01 00 00 00 00 00 |……..|
   [29-JAN-2009 20:24:58:307] nsprecv: 00 00 01 10 97 60 70 6F |…..`po|
   [29-JAN-2009 20:24:58:308] nsprecv: 72 61 64 73 30 37 31 00 |radv001.|
   [29-JAN-2009 20:24:58:308] nsprecv: 00 00 00 0D 00 00 00 00 |……..|
   [29-JAN-2009 20:24:58:308] nsprecv: 07 00 00 00 22 74 EC 98 |….”t..|
   [29-JAN-2009 20:24:58:308] nsprecv: 00 00 00 04 00 00 00 00 |……..|
   [29-JAN-2009 20:24:58:308] nsprecv: 00 00 00 00 00 00 00 00 |……..|
   [29-JAN-2009 20:24:58:308] nsprecv: 00 00 00 01 10 97 60 D0 |……`.|
   [29-JAN-2009 20:24:58:308] nsprecv: 6F 72 61 64 73 30 37 31 |oradv001|
   [29-JAN-2009 20:24:58:308] nsprecv: 5F 58 50 54 00 00 |_XPT.. |
   [29-JAN-2009 20:24:58:308] nsprecv: normal exit
   [29-JAN-2009 20:24:58:308] nsrdr: got NSPTDA packet
   ….
   ————-

   Note 235562.1 Issues Affecting Automatic Service Registration

   Note 271179.1 Connections Via a 9i Listener to a 10g Instance Fail With ORA-12500

   Oracle® Database Net Services Administrator’s Guide 10g Release 2 (10.2)
   10 Configuring and Administering the Listener
   Listener Configuration Overview

   Solution

   Explicitly set the LOCAL_LISTENER initialization parameter for the Oracle10g instance(s) to point
   to the 10g TNS Listener’s address(es).

   For example: If your 10g TNS Listener is on the non-default port, 1522, you would use the
   following commands:

   ALTER SYSTEM SET LOCAL_LISTENER ='(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>,<ip address>)(PORT=1522))’ SCOPE=BOTH;

   i.e.:

   ALTER SYSTEM SET LOCAL_LISTENER ='(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.10)(PORT=1522))’ SCOPE=BOTH;

   — or ‘SCOPE=MEMORY’ if you are using a pfile instead of an spfile, and set the LOCAL_LISTENER
   initialization parameter in the pfile: (if applicable):

   LOCAL_LISTENER ='(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.10)(PORT=1522))’

   <where you would use the correct IP Address/hostname> for your 10g TNS Listener.

    

   回复