[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,问题解决.
