建立資料庫連線失敗ORA-12514的分析解決過程

forrest_itpub發表於2016-12-04
資料庫
平臺：
OS：CentOS release 6.5 (Final)
DB：oracle 12.1.0

  • 1. 建立資料庫連線失敗
[laolv@centhost3 admin]$ sqlplus "sys/forrest@ORCLRMAN as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 3 17:51:59 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

  • 2. 檢視失敗原因
[laolv@centhost3 admin]$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener.  This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action: 
//  - Wait a moment and try to connect a second time.
//  - Check which services are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SERVICE_NAME parameter in the connect descriptor of the
//    net service name used specifies a service known by the listener.
//  - If an easy connect naming connect identifier was used, check that
//    the service name specified is a service known by the listener.
//  - Check for an event in the listener.log file.


  • 3. 檢查listener 狀態
[laolv@centhost3 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 03-DEC-2016 17:49:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                03-DEC-2016 17:30:28
Uptime                    0 days 0 hr. 18 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.147)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


  • 4. 定位解決過程
1）檢視listener知道的服務

點選(此處)摺疊或開啟

  1. [laolv@centhost3 admin]$ lsnrctl services

  3. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 03-DEC-2016 17:49:25

  5. Copyright (c) 1991, 2014, Oracle. All rights reserved.


  8. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
  9. The listener supports no services
  10. The command completed successfully
  11. [laolv@centhost3 admin]$ vi listener.ora
  12. [laolv@centhost3 admin]$ lsnrctl services LISTENER


  15. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 03-DEC-2016 17:50:54


  18. Copyright (c) 1991, 2014, Oracle. All rights reserved.


  21. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
  22. The listener supports no services
  23. The command completed successfully
  24. [laolv@centhost3 admin]$
2）對比另一臺裝有oracle資料庫的host2上的lsnrctl的資訊

點選(此處)摺疊或開啟

  1. [laolv@centhost2 admin]$ lsnrctl status
  2. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 03-DEC-2016 18:40:12
  3. Copyright (c) 1991, 2014, Oracle. All rights reserved.
  4. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
  5. STATUS of the LISTENER
  6. ------------------------
  7. Alias LISTENER
  8. Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
  9. Start Date 22-NOV-2016 06:09:22
  10. Uptime 11 days 12 hr. 30 min. 50 sec
  11. Trace Level off
  12. Security ON: Local OS Authentication
  13. SNMP OFF
  14. Listener Parameter File /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  15. Listener Log File /opt/oracle/diag/tnslsnr/centhost2/listener/alert/log.xml
  16. Listening Endpoints Summary...
  17.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  18.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  19.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=centhost2)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  20. Services Summary...
  21. Service "orcl" has 1 instance(s).
  22.   Instance "orcl", status READY, has 1 handler(s) for this service...
  23. Service "orclXDB" has 1 instance(s).
  24.   Instance "orcl", status READY, has 1 handler(s) for this service...
  25. Service "pdborcl" has 1 instance(s).
  26.   Instance "orcl", status READY, has 1 handler(s) for this service...
  27. The command completed successfully
  28. [laolv@centhost2 admin]$

3）是否是因為我先建立（且啟動了）的listener，後建立的DB例項，導致資料庫例項沒有被註冊到監聽
參考了http://blog.itpub.net/24492954/viewspace-765551/
新增了DataBase Service後，重啟：



點選(此處)摺疊或開啟

  1. [laolv@centhost3 admin]$ lsnrctl stop

  3. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 03-DEC-2016 19:01:36

  5. Copyright (c) 1991, 2014, Oracle. All rights reserved.

  7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
  8. The command completed successfully
  9. [laolv@centhost3 admin]$ lsnrctl start

  11. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 03-DEC-2016 19:01:42

  13. Copyright (c) 1991, 2014, Oracle. All rights reserved.

  15. Starting /opt/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

  17. TNSLSNR for Linux: Version 12.1.0.2.0 - Production
  18. System parameter file is /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  19. Log messages written to /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
  20. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.147)(PORT=1521)))
  21. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

  23. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
  24. STATUS of the LISTENER
  25. ------------------------
  26. Alias LISTENER
  27. Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
  28. Start Date 03-DEC-2016 19:01:42
  29. Uptime 0 days 0 hr. 0 min. 0 sec
  30. Trace Level off
  31. Security ON: Local OS Authentication
  32. SNMP OFF
  33. Listener Parameter File /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  34. Listener Log File /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
  35. Listening Endpoints Summary...
  36.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.147)(PORT=1521)))
  37.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  38. Services Summary...
  39. Service "ORCLRMAN" has 1 instance(s).
  40.   Instance "ORCLRMAN", status UNKNOWN, has 1 handler(s) for this service...
  41. The command completed successfully

4）透過alter system register來臨時註冊服務

點選(此處)摺疊或開啟

  1. [laolv@centhost3 admin]$ sqlplus / as sysdba

  3. SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 3 19:14:37 2016

  5. Copyright (c) 1982, 2014, Oracle. All rights reserved.
  6. Connected to:
  7. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

  10. SQL> show parameter service_names;

  12. NAME     TYPE    VALUE
  13. ------------------------------------ ----------- ------------------------------
  14. service_names     string    orclrman
  15. SQL> alter system register;


  18. System altered.


  21. SQL> exit
  22. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  23. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


  26. [laolv@centhost3 admin]$ lsnrctl status


  29. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 03-DEC-2016 19:16:27


  32. Copyright (c) 1991, 2014, Oracle. All rights reserved.


  35. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
  36. STATUS of the LISTENER
  37. ------------------------
  38. Alias LISTENER
  39. Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
  40. Start Date 03-DEC-2016 19:01:42
  41. Uptime 0 days 0 hr. 14 min. 45 sec
  42. Trace Level off
  43. Security ON: Local OS Authentication
  44. SNMP OFF
  45. Listener Parameter File /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  46. Listener Log File /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
  47. Listening Endpoints Summary...
  48.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.147)(PORT=1521)))
  49.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  50. Services Summary...
  51. Service "ORCLRMAN" has 1 instance(s).
  52.   Instance "ORCLRMAN", status UNKNOWN, has 1 handler(s) for this service...
  53. The command completed successfully
5）刪掉netmgr靜態註冊的Database Service。重啟監聽。再手動alter system register註冊
lsnrctl stop
lsnrctl start

點選(此處)摺疊或開啟

  1. [laolv@centhost3 admin]$ sqlplus / as sysdba

  3. SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 3 19:14:37 2016

  5. Copyright (c) 1982, 2014, Oracle. All rights reserved.
  6. Connected to:
  7. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

  10. SQL> show parameter service_names;

  12. NAME     TYPE    VALUE
  13. ------------------------------------ ----------- ------------------------------
  14. service_names     string    orclrman
  15. SQL> alter system register;


  18. System altered.


  21. SQL> exit
  22. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  23. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


  26. [laolv@centhost3 admin]$ lsnrctl status


  29. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 03-DEC-2016 19:16:27


  32. Copyright (c) 1991, 2014, Oracle. All rights reserved.


  35. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
  36. STATUS of the LISTENER
  37. ------------------------
  38. Alias LISTENER
  39. Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
  40. Start Date 03-DEC-2016 19:01:42
  41. Uptime 0 days 0 hr. 14 min. 45 sec
  42. Trace Level off
  43. Security ON: Local OS Authentication
  44. SNMP OFF
  45. Listener Parameter File /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  46. Listener Log File /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
  47. Listening Endpoints Summary...
  48.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.147)(PORT=1521)))
  49.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  50. Services Summary...
  51. Service "ORCLRMAN" has 1 instance(s).
  52.   Instance "ORCLRMAN", status UNKNOWN, has 1 handler(s) for this service...
  53. The command completed successfully

6）檢視日誌來定位
重啟tnslsnr之前，刪除log.xml

點選(此處)摺疊或開啟

  1. [laolv@centhost3 admin]$ cat /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
  2. <msg time='2016-12-03T19:32:21.387+08:00' org_id='oracle' comp_id='tnslsnr'
  3.  type='UNKNOWN' level='16' host_id='centhost3'
  4.  host_addr='192.168.1.147' version='1'>
  5.  <txt>System parameter file is /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  6.  </txt>
  7. </msg>
  8. <msg time='2016-12-03T19:32:21.388+08:00' org_id='oracle' comp_id='tnslsnr'
  9.  type='UNKNOWN' level='16' host_id='centhost3'
  10.  host_addr='192.168.1.147'>
  11.  <txt>Log messages written to /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
  12.  </txt>
  13. </msg>
  14. <msg time='2016-12-03T19:32:21.388+08:00' org_id='oracle' comp_id='tnslsnr'
  15.  type='UNKNOWN' level='16' host_id='centhost3'
  16.  host_addr='192.168.1.147'>
  17.  <txt>Trace information written to /opt/oracle/diag/tnslsnr/centhost3/listener/trace/ora_6744_139683398370752.trc
  18.  </txt>
  19. </msg>
  20. <msg time='2016-12-03T19:32:21.388+08:00' org_id='oracle' comp_id='tnslsnr'
  21.  type='UNKNOWN' level='16' host_id='centhost3'
  22.  host_addr='192.168.1.147'>
  23.  <txt>Trace level is currently 0
  24.  </txt>
  25. </msg>
  26. <msg time='2016-12-03T19:32:21.388+08:00' org_id='oracle' comp_id='tnslsnr'
  27.  type='UNKNOWN' level='16' host_id='centhost3'
  28.  host_addr='192.168.1.147'>
  29.  <txt>
  30.  </txt>
  31. </msg>
  32. <msg time='2016-12-03T19:32:21.389+08:00' org_id='oracle' comp_id='tnslsnr'
  33.  type='UNKNOWN' level='16' host_id='centhost3'
  34.  host_addr='192.168.1.147'>
  35.  <txt>Started with pid=6744
  36.  </txt>
  37. </msg>
  38. <msg time='2016-12-03T19:32:21.391+08:00' org_id='oracle' comp_id='tnslsnr'
  39.  type='UNKNOWN' level='16' host_id='centhost3'
  40.  host_addr='192.168.1.147'>
  41.  <txt>Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.147)(PORT=1521)))
  42.  </txt>
  43. </msg>
  44. <msg time='2016-12-03T19:32:21.392+08:00' org_id='oracle' comp_id='tnslsnr'
  45.  type='UNKNOWN' level='16' host_id='centhost3'
  46.  host_addr='192.168.1.147'>
  47.  <txt>Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  48.  </txt>
  49. </msg>
  50. <msg time='2016-12-03T19:32:21.402+08:00' org_id='oracle' comp_id='tnslsnr'
  51.  type='UNKNOWN' level='16' host_id='centhost3'
  52.  host_addr='192.168.1.147'>
  53.  <txt>
  54. TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
  55.  </txt>
  56. </msg>
  57. <msg time='2016-12-03T19:32:21.405+08:00' org_id='oracle' comp_id='tnslsnr'
  58.  type='UNKNOWN' level='16' host_id='centhost3'
  59.  host_addr='192.168.1.147'>
  60.  <txt>WARNING: Subscription for node down event still pending
  61.  </txt>
  62. </msg>
  63. <msg time='2016-12-03T19:32:21.405+08:00' org_id='oracle' comp_id='tnslsnr'
  64.  type='UNKNOWN' level='16' host_id='centhost3'
  65.  host_addr='192.168.1.147'>
  66.  <txt>03-DEC-2016 19:32:21 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=centhost3)(USER=laolv))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=202375680)) * status * 0
  67.  </txt>
  68. </msg>
  69. [laolv@centhost3 admin]$
重啟tnslsnr之前，刪除listener.log

點選(此處)摺疊或開啟

  1. [laolv@centhost3 alert]$ cat /opt/oracle/diag/tnslsnr/centhost3/listener/trace/listener.log
  2. Sat Dec 03 19:57:42 2016
  3. System parameter file is /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  4. Log messages written to /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
  5. Trace information written to /opt/oracle/diag/tnslsnr/centhost3/listener/trace/ora_7273_140042619459008.trc
  6. Trace level is currently 0

  8. Started with pid=7273
  9. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.147)(PORT=1521)))
  10. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

  12. TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
  13. WARNING: Subscription for node down event still pending
  14. 03-DEC-2016 19:57:42 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=centhost3)(USER=laolv))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=202375680)) * status * 0
  15. [laolv@centhost3 alert]$
並無進展

7)對比host2和host3
發現host2多一個sqlnet.ora檔案
[laolv@centhost2 admin]$ cat sqlnet.ora 
# sqlnet.ora Network Configuration File: /opt/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
新增sqlnet.ora到host3也不起作用，因為sqlnet.ora是為客戶端服務的/置於客戶機的，並不是為listener程式所用。

8)Oracle監聽器無法動態註冊例項的服務
參考 
http://blog.csdn.net/weiwangsisoftstone/article/details/38082487 
http://www.itpub.net/thread-1921723-1-1.html 帖子：個人懷疑計算機名為localhost時只能動態註冊到127.0.0.1的ip上面，所以導致上面監聽配置檔案中10.98.0.122時獲取不到動態註冊，為了驗證這個想法，個人修改了計算機名，再修改hosts檔案後重啟計算機後動態註冊已經有了，為了驗證這個問題，有時間個人再把計算機名重新改回localhost再驗證下.

在http://blog.csdn.net/weiwangsisoftstone/article/details/38082487中有檢測/etc/hosts,我懷疑是(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.147)(PORT = 1521))中的HOST應該使用機器名而不能使用IP


點選(此處)摺疊或開啟

  1. [laolv@centhost3 admin]$ cat listener.ora
  2. # listener.ora Network Configuration File: /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  3. # Generated by Oracle configuration tools.

  5. LISTENER =
  6.   (DESCRIPTION_LIST =
  7.     (DESCRIPTION =
  8.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.147)(PORT = 1521))
  9.     )
  10.     (DESCRIPTION =
  11.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  12.     )
  13.   )

  15. ADR_BASE_LISTENER = /opt/oracle

  17. [laolv@centhost3 admin]$ cat /etc/hosts
  18. 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
  19. ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
  20. 192.168.1.147 localhost
  21. 192.168.1.145 centos65
  22. 192.168.1.146 centhost2
  23. 192.168.1.147 centhost3

  25. （因為在我的另一臺裝有oracle的主機host2中，lsnrctl status返回的服務是READY的
  26. [laolv@centhost2 admin]$ cat listener.ora
  27. # listener.ora Network Configuration File: /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  28. # Generated by Oracle configuration tools.

  30. LISTENER =
  31.   (DESCRIPTION_LIST =
  32.     (DESCRIPTION =
  33.       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  34.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  35.     )
  36.   )

  38. [laolv@centhost2 admin]$ cat /etc/hosts
  39. 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
  40. ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
  41. 192.168.1.146 localhost
  42. 192.168.1.145 centos65
  43. 192.168.1.146 centhost2
  44. 192.168.1.147 centhost3
  45. 192.168.1.156 www.lvzhongqian.com
  46. ）

  48. [laolv@centhost3 admin]$ cat listener.ora
  49. # listener.ora Network Configuration File: /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  50. # Generated by Oracle configuration tools.

  52. LISTENER =
  53.   (DESCRIPTION_LIST =
  54.     (DESCRIPTION =
  55.       (ADDRESS = (PROTOCOL = TCP)(HOST = centhost3)(PORT = 1521))
  56.     )
  57.     (DESCRIPTION =
  58.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  59.     )
  60.   )

  62. ADR_BASE_LISTENER = /opt/oracle

lsnrctl stop
lsnrctl start
[laolv@centhost3 admin]$ lsnrctl status

點選(此處)摺疊或開啟

  1. 過幾分鐘（因為資料庫例項透過PMON程式向監聽器註冊service_name，也有一個週期的；大概是每3分鐘一個週期）
  2. [laolv@centhost3 admin]$ lsnrctl status

  4. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 03-DEC-2016 23:43:30

  6. Copyright (c) 1991, 2014, Oracle. All rights reserved.

  8. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=centhost3)(PORT=1521)))
  9. STATUS of the LISTENER
  10. ------------------------
  11. Alias LISTENER
  12. Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
  13. Start Date 03-DEC-2016 23:42:01
  14. Uptime 0 days 0 hr. 1 min. 29 sec
  15. Trace Level off
  16. Security ON: Local OS Authentication
  17. SNMP OFF
  18. Listener Parameter File /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  19. Listener Log File /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
  20. Listening Endpoints Summary...
  21.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centhost3)(PORT=1521)))
  22.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  23.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=centhost3)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/orclrman/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  24. Services Summary...
  25. Service "orclrman" has 1 instance(s).
  26.   Instance "orclrman", status READY, has 1 handler(s) for this service...
  27. Service "orclrmanXDB" has 1 instance(s).
  28.   Instance "orclrman", status READY, has 1 handler(s) for this service...
  29. The command completed successfully
  30. [laolv@centhost3 alert]$ hostname
  31. centhost3

-------------------------------------------------------點到此位置，以下為可選內容------------------------------------------------------------------

9)分析host=centos3行，host=192.168.1.147 不行的原因
參考“監聽器trace file中”，我設定成host=192.168.1.147，檢視listener.log和trace日誌、tracsst分析trace後得到的資訊，依舊沒定位出原因。
·反向思維：既然知道“host=centos3行”，那就設定成host=centos3,lsnrctl reload一下，檢視trace日誌和使用tracsst分析一下trace日誌。

tailf ora_20228**.trc:
2016-12-04 11:24:53.717415 : nlstdtp_trace_pfile: -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
2016-12-04 11:24:53.717545 : nlstdtp_trace_pfile:Successful parameter table load
2016-12-04 11:24:53.717608 : nlstddp_dump_ptable: -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
2016-12-04 11:24:53.717656 : nlstddp_dump_ptable:  LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = centhost3)(PORT = 1521))) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))))
2016-12-04 11:24:53.717715 : nlstddp_dump_ptable:  ADR_BASE_LISTENER = /opt/oracle
2016-12-04 11:24:53.717767 : nlstddp_dump_ptable:  TRACE_LEVEL_LISTENER = 16
2016-12-04 11:24:53.717819 : nlstddp_dump_ptable:  TRACE_TIMESTAMP_LISTENER = true
2016-12-04 11:24:53.717909 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION ENDS ---
2016-12-04 11:24:53.718027 : nsglldsec:entry
2016-12-04 11:24:53.718274 : nsglldsec:exit
TNSLSNR for Linux: Version 12.1.0.2.0 - Production on 04-DEC-2016 11:24:53

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

2016-12-04 11:24:53.719602 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION FOLLOWS ---
2016-12-04 11:24:53.719663 : nlstddt_do_alter_trace:New trace stream is /opt/oracle/diag/tnslsnr/centhost3/listener/trace/ora_20228_139696976258496.trc
2016-12-04 11:24:53.719722 : nlstddt_do_alter_trace:New trace level is 16
2016-12-04 11:24:53.719772 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION ENDS ---
2016-12-04 11:24:53.720559 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION FOLLOWS ---
2016-12-04 11:24:53.720693 : nlstddl_do_alter_log:Existing log stream is identical to desired "/opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml"; ignoring alter
2016-12-04 11:24:53.720750 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION ENDS ---

reload後還是The listener supports no services （alter system register後也是The listener supports no services）
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-DEC-2016 09:58:53
Uptime                    0 days 1 hr. 35 min. 17 sec
Trace Level               support
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
Listener Trace File       /opt/oracle/diag/tnslsnr/centhost3/listener/trace/ora_20228_139696976258496.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.147)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

·那就重啟：
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
The command completed successfully
LSNRCTL> start
Starting /opt/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
Trace information written to /opt/oracle/diag/tnslsnr/centhost3/listener/trace/ora_22421_140508226909632.trc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centhost3)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-DEC-2016 11:37:04
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               support
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
Listener Trace File       /opt/oracle/diag/tnslsnr/centhost3/listener/trace/ora_22421_140508226909632.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centhost3)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.147)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-DEC-2016 11:37:04
Uptime                    0 days 0 hr. 0 min. 55 sec
Trace Level               support
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/centhost3/listener/alert/log.xml
Listener Trace File       /opt/oracle/diag/tnslsnr/centhost3/listener/trace/ora_22421_140508226909632.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centhost3)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=centhost3)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/orclrman/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orclrman" has 1 instance(s).
  Instance "orclrman", status READY, has 1 handler(s) for this service...
Service "orclrmanXDB" has 1 instance(s).
  Instance "orclrman", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> 

取listener.log、ora_22421_140508226909632.trc、 ora_22421_140508226909632.trm到本地再分析
[laolv@centhost3 trc]$ trcasst ora_22421_140508226909632.trc > ora_22421_140508226909632.trcasst
 host=192.168.1.147 ,host=centhost3 的前後trcasst 分析資訊


2016-12-04 11:37:16.104003 : nsglgrDoRegister:Creating new instance: "orclrman".
2016-12-04 11:37:16.104032 : nscontrol:entry
2016-12-04 11:37:16.104046 : nttctl:entry
2016-12-04 11:37:16.104059 : nscontrol:cmd=7, lcl=0x0
2016-12-04 11:37:16.104070 : nscontrol:normal exit
2016-12-04 11:37:16.104102 : nsglgrDoRegister:inst loads: ld1:0 mld1:10240 ld2:1 mld2:472
2016-12-04 11:37:16.104115 : nsglgrDoRegister:instance flags - req:0 cur:16
2016-12-04 11:37:16.104162 : nsglgrDoRegister:Creating new service: "orclrmanXDB".
2016-12-04 11:37:16.104182 : nsglgrDoRegister:service:orclrmanXDB flag:3 goodness:0 delta:1
2016-12-04 11:37:16.104219 : nsglgrDoRegister:Creating new service: "orclrman".
2016-12-04 11:37:16.104233 : nsglgrDoRegister:service:orclrman flag:2 goodness:0 delta:1
2016-12-04 11:37:16.104260 : nsglegln:entry
2016-12-04 11:37:16.104280 : snlinGetAddrInfo:entry
2016-12-04 11:37:16.104400 : snlinGetAddrInfo:getaddrinfo() failed with error -2
2016-12-04 11:37:16.104419 : snlinGetAddrInfo:exit
2016-12-04 11:37:16.104430 : snlinGetAddrInfo:entry
2016-12-04 11:37:16.104517 : snlinGetAddrInfo:exit
2016-12-04 11:37:16.104540 : snlinGetAddrInfo:entry
……
2016-12-04 11:37:16.104996 : snlinFreeAddrInfo:entry
2016-12-04 11:37:16.105015 : snlinFreeAddrInfo:exit
2016-12-04 11:37:16.105030 : nsglegln:exit
2016-12-04 11:37:16.105044 : nsglgrDoRegister:Creating new endpoint: "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=centhost3)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/orclrman/xdb_wallet))(Presentation=HTTP)(Session=RAW))".
2016-12-04 11:37:16.105056 : nladini:entry
2016-12-04 11:37:16.105094 : nladini:exit
2016-12-04 11:37:16.105112 : nladget:entry
…
2016-12-04 11:37:16.106259 : nsglvldyl:Dynamic loading of valid node list successful
2016-12-04 11:37:16.106270 : nsglvldyl:exit
2016-12-04 11:37:16.106282 : nsgllsn:LSNDSC==>(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=centhost3)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/orclrman/xdb_wallet))(Presentation=HTTP)(Session=RAW))
2016-12-04 11:37:16.106293 : nsglhcrt:entry
2016-12-04 11:37:16.106349 : nsglhcrt:exit
2016-12-04 11:37:16.106366 : nsglbgetRSPidx:entry
2016-12-04 11:37:16.106377 : nladini:entry
……
2016-12-04 11:37:16.106572 : nsc2addr:entry
2016-12-04 11:37:16.106583 : nsc2addr:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=centhost3)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/orclrman/xdb_wallet))(Presentation=HTTP)(Session=RAW))
2016-12-04 11:37:16.106641 : ntzini:entry
2016-12-04 11:37:16.106699 : ntzSetupConnection:entry


ora_22421_140508226909632.trcasst檔案：
  *************************************************************************
    *                        Trace Assistant                                *
    *************************************************************************
<--- Received 204 bytes - Connect packet  timestamp=016-12-04 11:37:04.990220
Current NS version number is: 315.
Lowest NS version number can accommodate is: 300.
Maximum SDU size: 8192
Maximum TDU size: 4096
NT protocol characteristics:
Test for more data
Spawner is running
Hang on to Listener connection
Test operation
Full duplex I/O
Urgent data support
Generate SIGURG signal
Handoff connection to another
Line turnaround value: 0
Connect data length: 134
Connect data offset: 70
Connect data maximum size: 2048
Disable NA services on this connection
No NA services are linked in
Disable NA services on this connection
No NA services are linked in
    (ADDRESS=(PROTOCOL=beq)(PROGRAM=/opt/oracle/product/12.1.0/dbhome_1/bi
    n/tnslsnr)(ENVS=)(ARGV0=)(ARGS='LISTENER,-inherit')(DETACH=yes))

---> Send 2686976 bytes - Accept packet  timestamp=016-12-04 11:37:05.214508
Accepted NS version number is: 315.
Accepted maximum SDU size: 8192
Accepted maximum TDU size: 4096
Connect data length: 480
Connect data offset: 41
Native Services wanted
Disable NA services on this connection
No NA services are linked in
Disable NA services on this connection
No NA services are linked in
Packet data is in the following data packet


---> Send 490 bytes - Data packet  timestamp=016-12-04 11:37:05.215613
    TNSLSNR for Linux: Version 12.1.0.2.0 - Production
    System parameter file is /opt/oracle/product/12.1.0/dbhome_1/network/a
    dmin/listener.ora
    Log messages written to /opt/oracle/diag/tnslsnr/centhost3/listener/al
    ert/log.xml
    Trace information written to /opt/oracle/diag/tnslsnr/centhost3/listen
    er/trace/ora_22421_140508226909632.trc
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centhost3)(POR
    T=1521)))
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

<--- Received 210 bytes - Connect packet  timestamp=016-12-04 11:37:05.225394
Current NS version number is: 315.
Lowest NS version number can accommodate is: 300.
Global options for the connection: 
Don't care
Maximum SDU size: 8192
Maximum TDU size: 2097152
NT protocol characteristics:
Test for more data
Test operation
Full duplex I/O
Urgent data support
Generate SIGURG signal
Generate SIGPIPE signal
Generate SIGIO signal
Handoff connection to another
Line turnaround value: 0
Connect data length: 140
Connect data offset: 70
Connect data maximum size: 2040
Disable NA services on this connection
No NA services are linked in
Disable NA services on this connection
No NA services are linked in
    (DESCRIPTION=(CONNECT_DATA=(CID=(PROGRAM=)(HOST=centhost3)(USER=laolv)
    )(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=202375680)))


<--- Received 116 bytes - Connect packet  timestamp=016-12-04 11:37:16.088274
Current NS version number is: 315.
Lowest NS version number can accommodate is: 300.
Maximum SDU size: 8192
Maximum TDU size: 2097152
NT protocol characteristics:
Test for more data
Test operation
Full duplex I/O
Urgent data support
Generate SIGURG signal
Generate SIGPIPE signal
Generate SIGIO signal
Handoff connection to another
Line turnaround value: 0
Connect data length: 46
Connect data offset: 70
Connect data maximum size: 0
    (CONNECT_DATA=(COMMAND=service_register_NSGR))

我認為：pmon向監聽器註冊服務時，是基於hostname來尋找監聽器的，而不是IP（可能會把IP錯認為hostname）


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

相關文章