正確理解tnsnames.ora中的service_name

lovehewenyu發表於2013-04-19

正確理解tnsnames.ora中的service_name

 

正解:tnsnames.ora中的service_name 其實應該是監聽程式監聽到

Services Summary...

Service "PLSExtProc" has 1 instance(s).

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

Service "test.test.doudou.com" has 1 instance(s).

  Instance "test", status READY, has 1 handler(s) for this service...

Service "testXDB.test.doudou.com" has 1 instance(s).

  Instance "test", status READY, has 1 handler(s) for this service...

Service "test_XPT.test.doudou.com" has 1 instance(s).

  Instance "test", status READY, has 1 handler(s) for this service...

The command completed successfully

 

 

場景回憶

檢視service_names,db_domain

SQL> show parameter service_names

NAME                                 TYPE        VALUE

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

service_names                        string      test

SQL> show parameter db_domain

 

NAME                                 TYPE        VALUE

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

db_domain                            string      test.doudou.com

tnsping 通,可以ping

[ora@dg-ss dbs]$ tnsping test

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 08-AUG-2008 17:23:19

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

Used parameter files:

/opt/oracle/product/10.2.0/db_2/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg-ss)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)))

OK (0 msec)

使用者登入確報錯 ORA-12514

[ora@dg-ss dbs]$ sqlplus doudou/123@test

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 8 17:23:23 2008

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

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

檢視監聽程式狀態

[ora@dg-ss dbs]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 08-AUG-2008 17:22:00

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg-ss)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date                08-AUG-2008 17:21:35

Uptime                    0 days 0 hr. 0 min. 24 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/product/10.2.0/db_2/network/admin/listener.ora

Listener Log File         /opt/oracle/product/10.2.0/db_2/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg-ss)(PORT=1521)))

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

Services Summary...

Service "PLSExtProc" has 1 instance(s).

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

Service "test.test.doudou.com" has 1 instance(s).

  Instance "test", status READY, has 1 handler(s) for this service...

Service "testXDB.test.doudou.com" has 1 instance(s).

  Instance "test", status READY, has 1 handler(s) for this service...

Service "test_XPT.test.doudou.com" has 1 instance(s).

  Instance "test", status READY, has 1 handler(s) for this service...

The command completed successfully

檢視tnsnames.ora檔案

[ora@dg-ss admin]$ cat tnsnames.ora

TEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dg-ss)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = test)

    )

  )

 

 

解決方法

TEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dg-ss)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = test.test.doudou.com)

    )

  )

使用者可以正常登入

[ora@dg-ss admin]$ sqlplus doudou/123@test

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 8 17:24:14 2008

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

 

總結:

通過這次簡單的錯誤,讓我意識到研究oracle不能人云亦云,必須通過實驗才能研究的更深,更明瞭。

    最後提醒自己一次tnsnames.ora中的service_name是監聽程式監聽到的service_name也可以認為是servcie_name=db_name.db_domain

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26442936/viewspace-758902/,如需轉載,請註明出處,否則將追究法律責任。

相關文章