Hi,
I have RAC database servers (2 nodes). Each node runs 1 respective instance of a common database “dw” oracle 10g 10.2.0.4 . OS: RHEL 5.2. Call these as Node1 and Node2.
Node1 runs Instance named dw1 of database “dw” and Node2 runs dw2.
IP 10.188.6.131 refers to Node1.com
IP 10.188.6.132 refers to Node2.com
IP 10.188.6.139 refers to Node1-vip.unify.com
IP 10.188.6.140 refers to Node2-vip.unify.com
I have another application server running informatica. It is now installed with the 10g client. OS: RHEL 5.2. Call this Server2.
I have updated the tnsnames.ora in Server2 with proper entries found in listener.ora of Node1 for the database “dw”. I have updated the hosts file of Server2 with proper entry for the Node1.
When I try to connect to database “dw” from Server2, I encounter below error.
[oracle@Server2 admin]$ sqlplus system@dw as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jan 24 23:37:13 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist
[oracle@Server2 admin]$ sqlplus system@dw1 as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jan 24 23:37:34 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist
Tnsnames.ora from Server2.
DW =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.6.140)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.6.139)(PORT = 1522))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dw)
)
)
DW1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Node1-vip.unify.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dw)
(INSTANCE_NAME = dw1)
)
)
Listener.ora from Node1
[oraprod03@Node1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/dw/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
DW1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Node1-vip.unify.com)(PORT = 1522)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.6.131)(PORT = 1522)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
)
)
Hosts file from Server2
10.188.6.139 Node1-vip.unify.com Node1-vip
10.188.6.140 Node2-vip.unify.com Node2-vip
I tried logging into dw database from Node1 and was successful.
[oraprod03@Node1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jan 24 23:18:13 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select name from v$database;
NAME
---------
DW
SQL> set linesize 121
SQL> col name format a30
col value format a30
SQL> SQL>
SQL> SELECT name, value
FROM gv$parameter
WHERE (name LIKE '%link%')
OR (name IN ('global_names', 'dblink_encrypt_login')); 2 3 4
NAME VALUE
------------------------------ ------------------------------
global_names FALSE
open_links 4
open_links_per_instance 4
global_names FALSE
open_links 4
open_links_per_instance 4
6 rows selected.
SQL> SELECT name, value
FROM gv$parameter
WHERE name IN ('db_name', 'db_domain');
2 3
NAME VALUE
------------------------------ ------------------------------
db_domain
db_name dw
db_domain
db_name dw
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string +DWB_DATA/dw/datafile, +DW_DAT
A/dw/datafile
db_name string dw
db_unique_name string dwA
global_names boolean FALSE
instance_name string dw1
lock_name_space string
log_file_name_convert string +DWB_DATA/dw/onlinelog, +DW_DA
TA/dw/onlinelog
service_names string dw
Lsnrctl status output from Node1
[oraprod03@Node1 admin]$ lsnrctl status|grep dw
Service "dw" has 1 instance(s).
Instance "dw1", status READY, has 1 handler(s) for this service...
Lsnrctl services output from Node1
[oraprod03@Node1 admin]$ lsnrctl services|grep dw
Service "dw" has 1 instance(s).
Instance "dw1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1520 refused:0 state:ready
LOCAL SERVER
Ping from Server2 to Node1
[oracle@Server2 admin]$ ping Node1-vip.unify.com
PING Node1-vip.unify.com (10.188.6.139) 56(84) bytes of data.
64 bytes from sgi Node1-vip.unify.com (10.188.6.139): icmp_seq=0 ttl=64 time=0.911 ms
64 bytes from Node1-vip.unify.com (10.188.6.139): icmp_seq=1 ttl=64 time=0.181 ms
64 bytes from Node1-vip.unify.com (10.188.6.139): icmp_seq=2 ttl=64 time=0.171 ms
64 bytes from Node1-vip.unify.com (10.188.6.139): icmp_seq=3 ttl=64 time=0.172 ms
64 bytes from Node1-vip.unify.com (10.188.6.139): icmp_seq=4 ttl=64 time=0.176 ms
--- Node1-vip.unify.com ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4000ms
rtt min/avg/max/mdev = 0.171/0.322/0.911/0.294 ms, pipe 2
Tnsping from Server2
[oracle@Server2 admin]$ tnsping dw
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 25-JAN-2010 00:05:11
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/oracle/db/10.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.6.140)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.6.139)(PORT = 1522)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dw)))
OK (0 msec)
[oracle@Server2 admin]$ tnsping dw1
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 25-JAN-2010 00:05:16
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/oracle/db/10.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Node1-vip.unify.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dw) (INSTANCE_NAME = dw1)))
OK (0 msec)
I donno what is wrong. Can anyone help?
Regards,
Sam