奇怪的12514

显示全部楼层 · 2007-10-20 08:38:44

现在公司数据库都跑在MYSQL上。自己搭了个DATAGUARD玩玩。遇到奇怪的ORA12514问题。
server ：192.168.1.79
OS:centos 5
db

racle 10g r2 64bit
local:192.168.1.254
OS:xp
db

racle 10g r2
1、check serverlistener.ora ,tnsnames.ora
[oracle@centos79 admin]$ cat listener.ora
# listener.ora Network Configuration File: /usr/local/ora10/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/local/ora10/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.79)(PORT = 1521))
)
)

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.79)(PORT = 1522))
)
)
~
[oracle@centos79 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /usr/local/ora10/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = centos79)(PORT = 1522))

ORCL79 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.79)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL76 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.76)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

ORCL254 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
2、check local tnsnames.ora
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XP254 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVER_NAME = orcl)
)
)
CENTOS79 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.79)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
CENTOS76 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.76)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
2、tnsping server
监听都已经启动，tnsping能通，说明server上监听启动，1521端口也开放了。
3、通过DOS连接server报错
sqlplussystem/system@centos79
error

ra-12514

千问 · 2007-10-20 08:38:44

4、check SID
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl
SQL>
5、check the hosts file in server and local
local :
192.168.1.79centos79
192.168.1.254 bbb-7a9d379e51a
192.168.1.76centos76
server:
[oracle@centos79 admin]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail
127.0.0.1
centos79 localhost
::1
centos9 localhost9
192.168.1.79 centos79
192.168.1.254 bbb-7a9d379e51a
192.168.1.76 centos76
[oracle@centos79 admin]$

千问 · 2007-10-20 08:38:44

6、check iptabes
[root@centos79 admin]# /etc/init.d/iptables status
防火墙已停
[root@centos79 admin]#

千问 · 2007-10-20 08:38:44

7、connect tolocal from server
[oracle@centos79 ~]$ sqlplus system/system@orcl254
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 19 17:32:29 2009
Copyright (c) 1982, 2005, Oracle.All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
连接成功
[ 本帖最后由 zeromyth 于 2009-8-19 17:36 编辑 ]

千问 · 2007-10-20 08:38:44

怎么回事？：（

千问 · 2007-10-20 08:38:44

寻热心人士.

千问 · 2007-10-20 08:38:44

顶起来

千问 · 2007-10-20 08:38:44

一个linux一个windows? 10G?

千问 · 2007-10-20 08:38:44

.....11G才支持这样的搭配.

千问 · 2007-10-20 08:38:44

DG是两台centos，79和76
XP254 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVER_NAME = orcl)
)
)
这个net server_name配的是我本地local.
79连接我本地的ORACLE可以。
local连接79不行
[ 本帖最后由 zeromyth 于 2009-8-20 09:34 编辑 ]