在本機安裝的VMWARE虛機上，無法透過服務名連線資料庫。

連線時報錯：

[oracle@yangtk2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 01-SEP-2008 17:36:03

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                01-SEP-2008 17:01:31
Uptime                    0 days 0 hr. 34 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/oracle/product/11.1/network/admin/listener.ora
Listener Log File         /data/oracle/diag/tnslsnr/yangtk2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.25.1.104)(PORT=1521)))
The listener supports no services
The command completed successfully

檢查listener.ora和tnsnames.ora都未發現異常：

[oracle@yangtk2 admin]$ more listener.ora
# listener.ora Network Configuration File: /data/oracle/product/11.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.25.1.104)(PORT = 1521))
    )
)
[oracle@yangtk2 admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /data/oracle/product/11.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA11G_S =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.25.1.104)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g_s.ytk-thinkpad)
    )
)

ORA11G_P =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.25.1.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g_p.ytk-thinkpad)
    )
)

而且以前這個配置沒有任何問題，就是這次啟動虛機後才出現的問題。

查詢了metalink，雖然上面記載了很多ORA-12514錯誤，但是沒有發現什麼有幫助的線索。

由於這個錯誤以前並沒有出現，所以懷疑是最近修改了什麼東西導致了這個問題。而啟動之後做了唯一的修改就是修改了主機的IP地址。

透過ifconfig修改了主機IP地址，莫非是修改錯誤導致了問題，不過現在透過IP地址可以正常的登陸主機，而且tnsping也沒有任何的問題：

[oracle@yangtk2 admin]$ tnsping ora11g_s

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 01-SEP-2008 17:54:31

Used parameter files:
/data/oracle/product/11.1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.25.1.104)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora11g_s.ytk-thinkpad)))
OK (0 msec)

最後檢查listner的日誌：

[oracle@yangtk2 admin]$ tail -20 /data/oracle/diag/tnslsnr/yangtk2/listener/alert/log.xml

type='UNKNOWN' level='16' host_id='yangtk2.ytk-thinkpad'
host_addr='172.25.4.70'>
01-SEP-2008 17:54:31 * ping * 0

type='UNKNOWN' level='16' host_id='yangtk2.ytk-thinkpad'
host_addr='172.25.4.70'>
WARNING: Subscription for node down event still pending

type='UNKNOWN' level='16' host_id='yangtk2.ytk-thinkpad'
host_addr='172.25.4.70'>
01-SEP-2008 17:58:24 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=yangtk2.ytk-thinkpad)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * status * 0

發現日誌中的IP地址並沒有改變，看來是修改IP的時候遺漏了什麼位置。

檢查HOSTS檔案，發現裡面的配置忘了進行修改：

[oracle@yangtk2 admin]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
172.25.4.70 yangtk2.ytk-thinkpad
127.0.0.1 localhost.localdomain localhost

將hosts檔案中的ip也修改為當前的IP後，問題解決。

[oracle@yangtk2 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 01-SEP-2008 18:01:00

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@yangtk2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 01-SEP-2008 18:01:10

Starting /data/oracle/product/11.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /data/oracle/product/11.1/network/admin/listener.ora
Log messages written to /data/oracle/diag/tnslsnr/yangtk2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.25.1.104)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                01-SEP-2008 18:01:10
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/oracle/product/11.1/network/admin/listener.ora
Listener Log File         /data/oracle/diag/tnslsnr/yangtk2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.25.1.104)(PORT=1521)))
The listener supports no services
The command completed successfully

稍等一會，透過sqlplus連線資料庫：

[oracle@yangtk2 ~]$ sqlplus sys/test@ora11g_s as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Sep 1 18:03:16 2008

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

連線資料庫報錯ORA-12514

相關文章