RAC环境中的listener问题（兼OAST）

        

   今天在做oast里的rac elevator时，发现之前的一个检查测试报错，查看一下输出。

lxsfrac04%oracle>more oce_nxora1.out

/opt/oast/home/oast_cluster/oastoltp4/nxhome/ora+.ksh ........................................................

=== Tue Apr  1 16:24:06 CST 2008 === xn @oastoltp04 iteration 1 of 1: +/opt/oast/home/oast_cluster/oastoltp4/nxhom

e/ora+.ksh+ ===

04

=== Tue Apr  1 16:24:06 CST 2008 === starting Oracle instance @oastoltp1... sys/change_on_install ===

sqlplus sys/change_on_install@oastoltp1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 1 16:24:06 2008

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

 

ERROR:

ORA-12505: TNS:listener does not currently know of SID given in connect

descriptor

 

Enter user-name: SP2-0306: Invalid option.

Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]

where   ::= [/][@] | /

Enter user-name: Enter password:

ERROR:

ORA-01005: null password given; logon denied

 

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

 

…………………………………………………………………………

   尝试一下手工连接

lxsfrac04%oracle>sqlplus sys/change_on_install@oastoltp1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 1 16:26:02 2008

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

ERROR:

ORA-12505: TNS:listener does not currently know of SID given in connect

descriptor

Enter user-name: ^C

手工连接和脚本的输出结果一致。

 

lxsfrac04%oracle>tnsping oastoltp1

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 01-APR-2008 16:26:17

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

 

Used parameter files:

/oracle/10g/network/admin/sqlnet.ora

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = lxsfrac04)(PORT = 1521))) (CONNECT_DATA = (SID = oastoltp1)))

OK (20 msec)

 

lxsfrac04%oracle>lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 01-APR-2008 16:26:37

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Solaris: Version 10.2.0.1.0 - Production

Start Date                01-APR-2008 13:30:57

Uptime                    0 days 2 hr. 55 min. 40 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/10g/network/admin/listener.ora

Listener Log File         /oracle/10g/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lxsfrac04)(PORT=1521)))

The listener supports no services

The command completed successfully

查看一下listener文件（实际上这个listener文件是由脚本自动创建的，不会有问题）

lxsfrac04%oracle>more  /oracle/10g/network/admin/listener.ora

 

LISTENER_lxsfrac04 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

        (ADDRESS = (PROTOCOL = TCP)(HOST = lxsfrac04)(PORT = 1521))

      )

    )

  )

 

SID_LIST_LISTENER_lxsfrac04 =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /oracle/10g)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = oastoltp)

      (ORACLE_HOME =/oracle/10g)

      (SID_NAME = oastoltp1)

    )

  )

 

LISTENER_lxsfrac03 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

        (ADDRESS = (PROTOCOL = TCP)(HOST = lxsfrac03)(PORT = 1521))

      )

    )

  )

 

SID_LIST_LISTENER_lxsfrac03 =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /oracle/10g)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = oastoltp)

      (ORACLE_HOME =/oracle/10g)

      (SID_NAME = oastoltp2)

    )

  )

STARTUP_WAIT_TIME_LISTENER=0

CONNECT_TIMEOUT_LISTENER=10

TRACE_LEVEL_LISTENER=OFF

TRACE_DIRECTORY_LISTENER=/oracle/10g/network/trace

TRACE File_LISTENER=listener.trc

    察看当前的lisner进程

lxsfrac04%oracle>ps -ef|grep lsn

  oracle 11504     1   0 13:30:58 ?           0:00 /oracle/10g/bin/tnslsnr LISTENER -inherit

  oracle  2608 22144   0 16:27:17 pts/3       0:00 grep lsn

 

 

用crs_stat来查看listener资源（因为是在rac环境里，所以要用crs_stat来查看）

root@lxsfrac04 # crs_stat -t

Name           Type           Target    State     Host       

------------------------------------------------------------

ora....03.lsnr application    OFFLINE   OFFLINE              

ora....c03.gsd application    ONLINE    ONLINE    lxsfrac03  

ora....c03.ons application    ONLINE    ONLINE    lxsfrac03  

ora....c03.vip application    ONLINE    ONLINE    lxsfrac03  

ora....04.lsnr application    OFFLINE   OFFLINE              

ora....c04.gsd application    ONLINE    ONLINE    lxsfrac04  

ora....c04.ons application    ONLINE    ONLINE    lxsfrac04  

ora....c04.vip application    ONLINE    ONLINE    lxsfrac04  

ora.test.db    application    ONLINE    OFFLINE              

ora....t1.inst application    ONLINE    OFFLINE              

ora....t2.inst application    ONLINE    OFFLINE   

 

看见listener处在offline 状态， 手工启动        

root@lxsfrac04 # crs_start -all

Attempting to start `ora.test.db` on member `lxsfrac03`

Attempting to start `ora.lxsfrac04.LISTENER_LXSFRAC04.lsnr` on member `lxsfrac04`

Attempting to start `ora.lxsfrac03.LISTENER_LXSFRAC03.lsnr` on member `lxsfrac03`

Start of `ora.lxsfrac04.LISTENER_LXSFRAC04.lsnr` on member `lxsfrac04` succeeded.

Start of `ora.lxsfrac03.LISTENER_LXSFRAC03.lsnr` on member `lxsfrac03` succeeded.

Attempting to start `ora.test.test1.inst` on member `lxsfrac04`

Attempting to start `ora.test.test2.inst` on member `lxsfrac03`

Start of `ora.test.db` on member `lxsfrac03` failed.

Attempting to start `ora.test.db` on member `lxsfrac04`

^C

root@lxsfrac04 # crs_stat -t

Name           Type           Target    State     Host       

------------------------------------------------------------

ora....03.lsnr application    ONLINE    ONLINE    lxsfrac03  

ora....c03.gsd application    ONLINE    ONLINE    lxsfrac03  

ora....c03.ons application    ONLINE    ONLINE    lxsfrac03  

ora....c03.vip application    ONLINE    ONLINE    lxsfrac03  

ora....04.lsnr application    ONLINE    ONLINE    lxsfrac04  

ora....c04.gsd application    ONLINE    ONLINE    lxsfrac04  

ora....c04.ons application    ONLINE    ONLINE    lxsfrac04  

ora....c04.vip application    ONLINE    ONLINE    lxsfrac04  

ora.test.db    application    ONLINE    OFFLINE              

ora....t1.inst application    ONLINE    OFFLINE              

ora....t2.inst application    ONLINE    OFFLINE      

       

root@lxsfrac04 # ps -ef|grep lsn

  oracle  7034     1   0 16:29:37 ?           0:00 /oracle/10g/bin/tnslsnr LISTENER_LXSFRAC04 -inherit

root  7720 17233   0 16:29:55 pts/7       0:00 grep lsn

 

未启动crs的listener资源时listener状态输出

lxsfrac04%oracle>lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 01-APR-2008 16:10:49

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

 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                   LISTENER

Version                 TNSLSNR for Solaris: Version 10.2.0.1.0 - Production

Start Date              01-APR-2008 13:30:57

Uptime                  0 days 2 hr. 39 min. 52 sec

Trace Level             off

Security                ON: Local OS Authentication

SNMP                    OFF

Listener Parameter File  /oracle/10g/network/admin/listener.ora

Listener Log File        /oracle/10g/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lxsfrac04)(PORT=1521)))

The listener supports no services

The command completed successfully

 

启动crs的listener资源后listener状态输出

lxsfrac04%oracle>lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 01-APR-2008 16:30:17

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                   LISTENER_LXSFRAC04

Version                 TNSLSNR for Solaris: Version 10.2.0.1.0 - Production

Start Date              01-APR-2008 16:29:36

Uptime                  0 days 0 hr. 0 min. 41 sec

Trace Level             off

Security                ON: Local OS Authentication

SNMP                    OFF

Listener Parameter File  /oracle/10g/network/admin/listener.ora

Listener Log File        /oracle/10g/network/log/listener_lxsfrac04.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lxsfrac04)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "oastoltp" has 1 instance(s).

  Instance "oastoltp1", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

 

察看集群环境下另一台主机的listener状态

lxsfrac03%oracle>lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 01-APR-2008 16:33:39

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   Solaris Error: 146: Connection refused

 

lxsfrac03%oracle>lsnrctl start

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 01-APR-2008 16:33:44

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

Starting /oracle/10g/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 10.2.0.1.0 - Production

System parameter file is /oracle/10g/network/admin/listener.ora

Log messages written to /oracle/10g/network/log/listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lxsfrac03)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                 TNSLSNR for Solaris: Version 10.2.0.1.0 - Production

Start Date               01-APR-2008 16:33:44

Uptime                   0 days 0 hr. 0 min. 0 sec

Trace Level              off

Security                 ON: Local OS Authentication

SNMP                     OFF

Listener Parameter File   /oracle/10g/network/admin/listener.ora

Listener Log File         /oracle/10g/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lxsfrac03)(PORT=1521)))

The listener supports no services

The command completed successfully

 

查看listener文件，发现未变化，为什么上面的listener输出会有问题呢？

lxsfrac03%oracle>more /oracle/10g/network/admin/listener.ora

LISTENER_lxsfrac04 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

        (ADDRESS = (PROTOCOL = TCP)(HOST = lxsfrac04)(PORT = 1521))

      )

    )

  )

 

SID_LIST_LISTENER_lxsfrac04 =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /oracle/10g)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = oastoltp)

      (ORACLE_HOME =/oracle/10g)

      (SID_NAME = oastoltp1)

    )

  )

 

LISTENER_lxsfrac03 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

        (ADDRESS = (PROTOCOL = TCP)(HOST = lxsfrac03)(PORT = 1521))

      )

    )

  )

 

SID_LIST_LISTENER_lxsfrac03 =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /oracle/10g)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = oastoltp)

      (ORACLE_HOME =/oracle/10g)

      (SID_NAME = oastoltp2)

    )

  )

STARTUP_WAIT_TIME_LISTENER=0

CONNECT_TIMEOUT_LISTENER=10

TRACE_LEVEL_LISTENER=OFF

TRACE_DIRECTORY_LISTENER=/oracle/10g/network/trace

TRACE File_LISTENER=listener.trc

LOG_DIRECTORY_LISTENER=/oracle/10g/network/log

LOG_FILE_LISTENER=listener.log

 

在本节点察看crs资源,listener资源的确已启动

lxsfrac03%oracle>/oracle/crs/bin/crs_stat -t

Name           Type           Target    State     Host       

------------------------------------------------------------

ora....03.lsnr application    ONLINE    ONLINE    lxsfrac03  

ora....c03.gsd application    ONLINE    ONLINE    lxsfrac03  

ora....c03.ons application    ONLINE    ONLINE    lxsfrac03  

ora....c03.vip application    ONLINE    ONLINE    lxsfrac03  

ora....04.lsnr application    ONLINE    ONLINE    lxsfrac04  

ora....c04.gsd application    ONLINE    ONLINE    lxsfrac04  

ora....c04.ons application    ONLINE    ONLINE    lxsfrac04  

ora....c04.vip application    ONLINE    ONLINE    lxsfrac04   

ora.test.db    application    ONLINE    OFFLINE              

ora....t1.inst application    ONLINE    OFFLINE              

ora....t2.inst application    ONLINE    OFFLINE 

            

lxsfrac03%oracle>ps -ef|grep lsn

  oracle  8685     1   0 16:33:45 ?           0:00 /oracle/10g/bin/tnslsnr LISTENER -inherit

  oracle 13669  8242   0 16:36:23 pts/1       0:00 grep lsn

从上面的进程可以看出，listener启动不正确。关闭它。

lxsfrac03%oracle>lsnrctl stop

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 01-APR-2008 16:39:24

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

The command completed successfully

 

lxsfrac03%oracle>/oracle/crs/bin/crs_stat -t

Name           Type           Target    State     Host       

------------------------------------------------------------

ora....03.lsnr application    ONLINE    ONLINE    lxsfrac03  

ora....c03.gsd application    ONLINE    ONLINE    lxsfrac03  

ora....c03.ons application    ONLINE    ONLINE    lxsfrac03  

ora....c03.vip application    ONLINE    ONLINE    lxsfrac03  

ora....04.lsnr application    ONLINE    ONLINE    lxsfrac04   

ora....c04.gsd application    ONLINE    ONLINE    lxsfrac04  

ora....c04.ons application    ONLINE    ONLINE    lxsfrac04  

ora....c04.vip application    ONLINE    ONLINE    lxsfrac04  

ora.test.db    application    ONLINE    OFFLINE              

ora....t1.inst application    ONLINE    OFFLINE              

ora....t2.inst application    ONLINE    OFFLINE  

           

lxsfrac03%oracle>lsnrctl status

 

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 01-APR-2008 16:39:53

 

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

 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                   LISTENER_LXSFRAC03

Version                 TNSLSNR for Solaris: Version 10.2.0.1.0 - Production

Start Date                01-APR-2008 16:39:33

Uptime                    0 days 0 hr. 0 min. 19 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/10g/network/admin/listener.ora

Listener Log File         /oracle/10g/network/log/listener_lxsfrac03.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lxsfrac03)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "oastoltp" has 1 instance(s).

  Instance "oastoltp2", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

 

lxsfrac03%oracle>ps -ef|grep lsn

  oracle 27979  8242   0 16:59:46 pts/1       0:00 grep lsn

  oracle 19717     1   0 16:39:34 ?           0:00 /oracle/10g/bin/tnslsnr LISTENER_LXSFRAC03 -inherit

 

这次启动终于正确。

总结：

虽说是rac环境里listener的一点小问题，但也看出我对oracle的知识有太多的生疏。

1） tnsping的问题。

Tnsping ping的是tnsname,读的是$oracle_home/network/admin/tnsname.ora文件。

sqlplus sys/change_on_install@oastoltp1 as sysdba

完成这个解析需要oastoltp1实例和oastoltp1 listener已启动。

---------listener.ora,sqlnet.ora,tnsnames.ora的关系以及手工配置-------

　　1.sqlplus / as sysdba 这是典型的操作系统认证，不需要listener进程
　　2.sqlplus sys/oracle 这种连接方式只能连接本机数据库，同样不需要listener进程
　　3.sqlplus 这种方式需要listener进程处于可用状态。最普遍的通过网络连接。
　　以上连接方式使用sys用户或者其他通过密码文件验证的用户都不需要数据库处于可用状态，操作系统认证也不需要数据库可用，普通用户因为是数据库认证，所以数据库必需处于open状态。

---------listener.ora,sqlnet.ora,tnsnames.ora的关系以及手工配置-------

 

2）                    在rac环境里,靠crs来管理listener,不能直接用lsnrctl start来启动listener(因为存在多个listener),可用

      lsnrctl listener_name start 来启动。
     另外，理解用ps –ef|grep lsn来查看listener的名字及类型

     认真理解lsnrctl status的输出含义

    3）如何用crs命令察看rac环境里的资源信息。