如何跟踪Oracle动态服务注册

如何trace Oracle PMON进程动态注册过程?这个问题我想到2个答案,对PMON做event trace或者采用Oracle Network Server因该都可以达到目的。
让我们来实践一下!

Oracle Network Server Trace模式

1. 启用Oracle SqlNet服务器端trace,这需要我们修改sqlnet.ora配置文件

[maclean@rh2 ~]$ echo "TRACE_LEVEL_SERVER = 16
> TRACE_FILE_SERVER = SERVER
> TRACE_DIRECTORY_SERVER= /home/maclean/ntrc"  > $ORACLE_HOME/network/admin/sqlnet.ora
[maclean@rh2 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = SERVER
TRACE_DIRECTORY_SERVER= /home/maclean/ntrc

2. 触发trace

SQL> conn / as sysdba
Connected.

SQL>  select spid from V$process, V$session where audsid=userenv('SESSIONID') and paddr=addr;
SPID
------------
4264

SQL> alter system register;
System altered.

/*在之前指定的TRACE_DIRECTORY_SERVER目录下将出现形如server_$spid.trc的trace文件*/

[maclean@rh2 ntrc]$ cat server_4290.trc|grep nsprecv|grep -A 2 -B 2 reg
[20-AUG-2010 10:42:53:896] nsprecv: 00 00 00 00 00 15 61 6C  |......al|
[20-AUG-2010 10:42:53:896] nsprecv: 74 65 72 20 73 79 73 74  |ter.syst|
[20-AUG-2010 10:42:53:896] nsprecv: 65 6D 20 72 65 67 69 73  |em.regis|
[20-AUG-2010 10:42:53:896] nsprecv: 74 65 72 01 00 00 00 01  |ter.....|
[20-AUG-2010 10:42:53:896] nsprecv: 00 00 00 00 00 00 00 00  |........|

/*分析该trace文件后可以匹配到以上动态注册语句*/

/*记得将server端sqlnet trace设置disable掉*/

[maclean@rh2 ntrc]$ echo "" > $ORACLE_HOME/network/admin/sqlnet.ora

PMON TRACE模式
1.找出PMON的进程号

SQL> select spid ,program from v$process where program like '%PMON%';

SPID         PROGRAM
------------ ------------------------------------------------
4050         oracle@rh2 (PMON)

2.执行10257事件trace

SQL> oradebug setospid 4050;
Oracle pid: 2, Unix process pid: 4050, image: oracle@rh2 (PMON)

SQL> oradebug event 10257 trace name context forever,level 16;
Statement processed.

SQL> alter system register;
System altered.

SQL> oradebug event 10257 trace name context off;
Statement processed.

SQL> oradebug tracefile_name;
/s01/10gdb/admin/YOUYUS/bdump/youyus_pmon_4050.trc

cat /s01/10gdb/admin/YOUYUS/bdump/youyus_pmon_4050.trc
.......................
kmmlrl: register now
kmmgdnu: cXDB
         goodness=0, delta=1,
         flags=0x5:unblocked/not overloaded, update=0x6:G/D/-
kmmgdnu: YOUYUS
         goodness=0, delta=1,
         flags=0x4:unblocked/not overloaded, update=0x6:G/D/-
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0

/*可以看到注册了YOUYUS和cXDB2个service的过程*/

若无法正常注册可以按照以下步骤检查:
1. 检查Oracle Net命名方式是否正确设置,该参数由sqlnet.ora配置文件中的NAMES.DIRECTORY_PATH指定。
2. 确认SQL NET配置文件如sqlnet.ora,tnsnames.ora等的位置正确配载。
3. 检查是否设置了TNS_ADMIN环境变量,该变量将影响配置文件的正确路径。
4. 确认LOCAL_LISTENER或REMOTE_LISTENER中指定的service可以被正常tnsping通,若以上参数未指定值则尝试步骤6。
5. 确认主机名可以被正确解析为ip地址,如以下示例:

C:\Users\weisly>nslookup www.oracle.com
服务器:  dir-605
Address:  192.168.0.1

非权威应答:
名称:    a398.g.akamai.net.0.1.cn.akamaitech.net
Addresses:  117.104.136.98
          117.104.136.128
Aliases:  www.oracle.com
          www.oracle.com.edgesuite.net
          a398.g.akamai.net

6. 修改LOCAL_LISTENER为非服务别名解析的形式,这样可以排除配置文件可能造成的影响,从而确认问题是否由配置文件设置不当而引起:

SQL> conn / as sysdba
Connected.

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh2)(PORT=1521)))';
System altered.

SQL> alter system set REMOTE_LISTENER=' (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = rh1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = rh2)(PORT = 1521)))';
System altered.

7. 将主机名替换为ip地址并测试是否解决问题。
8. 以IPC协议替代常见的TCP协议测试,绕过TCP和主机名等因素可能造成的问题,以确认问题是否由这些因素引起:

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=KEY1)))';
System altered.

/*其中KEY值需要和listener.ora中指定的一致。*/

SQL> host
[maclean@rh2 admin]$ lsnrctl status KEY1

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-AUG-2010 11:30:06
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=KEY1)))
STATUS of the LISTENER
------------------------
Alias                     KEY1
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                20-AUG-2010 11:29:15
Uptime                    0 days 0 hr. 0 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /s01/10gdb/network/admin/listener.ora
Listener Log File         /s01/10gdb/network/log/key1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=KEY1)))
Services Summary...
Service "YOUYUS" has 1 instance(s).
  Instance "YOUYUS", status READY, has 1 handler(s) for this service...
Service "YOUYUS_XPT" has 1 instance(s).
  Instance "YOUYUS", status READY, has 1 handler(s) for this service...
Service "cXDB" has 1 instance(s).
  Instance "YOUYUS", status READY, has 10 handler(s) for this service...
The command completed successfully

实在不行的话,我们还是提SR吧!

Comments

  1. 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

    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

    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

    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

    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

    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

    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.

     

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号