[20180428]DNS與ORA-12154錯誤.txt

[20180428]DNS與ORA-12154錯誤.txt

--//今天建立並測試DNS,使用名字代替IP登入資料庫,遇到ORA-12154錯誤,做一個記錄:

1.環境:
--//首先我建立dns使用dnsmasq,很簡單僅僅需要把IP與機器名加入/etc/hosts,啟動dnsmasq就ok了.

--//環境:最後一列表示主機名:
192.168.100.78 DNS伺服器與資料庫   gxqyydg4
192.168.100.33 client端            icaredg

--//2臺機器修改/etc/resolv.conf,加入:
nameserver 192.168.100.78

--//192.168.100.33 client端       icaredg 執行:
# host icaredg 192.168.100.78
Using domain server:
Name: 192.168.100.78
Address: 192.168.100.78#53
Aliases:

Host icaredg not found: 5(REFUSED)
--//client Ip不做解析.

# host gxqyydg4
gxqyydg4 has address 192.168.100.78
Host gxqyydg4 not found: 5(REFUSED)
Host gxqyydg4 not found: 5(REFUSED)

# host 192.168.100.78
78.100.168.192.in-addr.arpa domain name pointer gxqyydg4.com.

2.測試:
--//192.168.100.33 client端:
$ sqlplus -S -l scott/book@gxqyydg4:1521/book
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

--//出現ora-12154錯誤!!
--//使用strace跟蹤重複測試:
$ strace -f -o /tmp/xx1.txt sqlplus -S -l scott/book@gxqyydg4:1521/book
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

--//檢查跟蹤檔案發現如下內容:
16336 ioctl(7, FIONREAD, [38])          = 0
16336 recvfrom(7, " m\201\205\0\1\0\0\0\0\0\0\10gxqyydg4\vlocaldomai"..., 1024, 0, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.100.78")}, [16]) = 38
16336 close(7)                          = 0
16336 socket(PF_INET, SOCK_DGRAM, IPPROTO_IP) = 7
16336 connect(7, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.100.78")}, 28) = 0
16336 fcntl(7, F_GETFL)                 = 0x2 (flags O_RDWR)
16336 fcntl(7, F_SETFL, O_RDWR|O_NONBLOCK) = 0
16336 poll([{fd=7, events=POLLOUT}], 1, 0) = 1 ([{fd=7, revents=POLLOUT}])
16336 sendto(7, " m\1\0\0\1\0\0\0\0\0\0\10gxqyydg4\vlocaldomai"..., 38, MSG_NOSIGNAL, NULL, 0) = 38
16336 poll([{fd=7, events=POLLIN}], 1, 5000) = 1 ([{fd=7, revents=POLLIN}])
16336 ioctl(7, FIONREAD, [38])          = 0
16336 recvfrom(7, " m\201\205\0\1\0\0\0\0\0\0\10gxqyydg4\vlocaldomai"..., 1024, 0, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.100.78")}, [16]) = 38
16336 close(7)                          = 0
16336 open("/u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb", O_RDONLY) = 7
16336 fcntl(7, F_SETFD, FD_CLOEXEC)     = 0
16336 lseek(7, 0, SEEK_SET)             = 0
16336 read(7, "\25\23\"\1\23\3\t\t\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 256) = 256"
16336 lseek(7, 512, SEEK_SET)           = 512
16336 read(7, "Q\32C5V[\340u\374\205\200\224y\352\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
16336 lseek(7, 1536, SEEK_SET)          = 1536
16336 read(7, "`\32m\32{\32\222\32\232\32\365\32\0\33\f\33)\33>\33$\34-\0346\34@\34L\34T\34"..., 512) = 512
16336 lseek(7, 221696, SEEK_SET)        = 221696
16336 read(7, "\v\0B/\0\0J\0C/\0\0q\0D/\0\0\232\0v/\0\0\321\0w/\0\0\350\0"..., 512) = 512
16336 close(7)                          = 0
16336 lseek(4, 512, SEEK_SET)           = 512
16336 read(4, "\245\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
16336 lseek(4, 1024, SEEK_SET)          = 1024
16336 read(4, "\26\0*\0R\0h\0\201\0\236\0\350\0\374\0\n\1#\1?\1M\1\251\1\307\1\346\1\3\2"..., 512) = 512
16336 lseek(4, 5120, SEEK_SET)          = 5120
16336 read(4, "\r\0\351\0\0\0V\0\352\0\0\0\220\0\353\0\0\0\240\0\356\0\0\0\320\0\357\0\0\0\344\0"..., 512) = 512
16336 fstat(1, {st_mode=S_IFCHR|0620, st_rdev=makedev(136, 4), ...}) = 0
16336 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b50e4535000
16336 write(1, "ERROR:\n", 7)           = 7
16336 write(1, "ORA-12154: TNS:could not resolve"..., 66) = 66
16336 write(1, "\n", 1)                 = 1
16336 write(1, "\n", 1)                 = 1
16336 write(1, "SP2-0751: ", 10)        = 10
16336 lseek(4, 512, SEEK_SET)           = 512
16336 read(4, "\245\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
16336 lseek(4, 1024, SEEK_SET)          = 1024
16336 read(4, "\26\0*\0R\0h\0\201\0\236\0\350\0\374\0\n\1#\1?\1M\1\251\1\307\1\346\1\3\2"..., 512) = 512
16336 lseek(4, 18432, SEEK_SET)         = 18432
16336 read(4, "\10\0\351\2\0\0008\0\352\2\0\0s\0\353\2\0\0\232\0\355\2\0\0\332\0\356\2\0\0\7\1"..., 512) = 512
16336 write(1, "Unable to connect to Oracle.  Ex"..., 47) = 47

--//視乎解析正常的,但是發現
16336 sendto(7, " m\1\0\0\1\0\0\0\0\0\0\10gxqyydg4\vlocaldomai"..., 38, MSG_NOSIGNAL, NULL, 0) = 38

--//很明顯被解析成了gxqyydg4.localdomai,而實際上192.168.100.78的全名是 gxqyydg4.com

--//在192.168.100.78 DNS伺服器與資料庫 執行:
# grep 100.78 /etc/hosts
192.168.100.78   gxqyydg4.com gxqyydg4

--//可以發現全名實際上是gxqyydg4.com.

3.如何解決這個問題呢?
--//一個解決方法就是在主機名後面加入一個.(點),執行如下:
$ sqlplus -S  -L  scott/book@gxqyydg4.:1521/book <<< 'select sysdate from dual;'

SYSDATE
-------------------
2018-04-28 09:52:46

--//另外的解決方法加入修改192.168.100.33的客戶端加入/etc/resolv.conf,內容如下,加入search com.
# cat /etc/resolv.conf
search localdomain
search com
nameserver 192.168.100.78

$ sqlplus -S  -L  scott/book@gxqyydg4:1521/book <<< 'select sysdate from dual;'

SYSDATE
-------------------
2018-04-28 09:54:07

$ sqlplus -S  -L  scott/book@gxqyydg4.com:1521/book <<< 'select sysdate from dual;'

SYSDATE
-------------------
2018-04-28 09:54:12

--//OK,問題解決.