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