Hello,

We had a working nickname until two days ago which goes and hits an oracle database(target) from our DB2 Federation Server.

Oracle team has moved the single database to a HA pair to have primary and standby databases.The nickname worked fine when it was a single database.But after Primary and Standy have been introduced it stopped working.

So Steps I did:

1)Changed the tnsnames.ora file to have entries for both Primary and Standby
2)Droped the Server and nickname
2)Recreated the server for both Primary and Standby

4)Now, the real problem comes when trying to create the nickname.I am getting some error that neither info center nor google show much.Pls help me with this.Below are the details:

tnsnames,ora file before oracle database was HA pair

aaa.ax.xxx.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = abccom.ax.xxx.com)
(PROTOCOL = TCP)
(Host = abc4.ax.xxx.com)
(Port = 1521)
)
)
(CONNECT_DATA =
(SID = SDS)
(GLOBAL_NAME = aaa.ax.xxx.com )
)
)



After , the oracle db has been moved to HA pair,I modified the tnsnames.ora file to add like below

aaa.ax.xxx.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcpcom.dx.deere.com)
(PROTOCOL = TCP)
(Host = abc4.ax.xxx.com)
(Port = 1521)
)
(ADDRESS =
(COMMUNITY =abccom.ax.xxx.com)
(PROTOCOL = TCP)
(Host = abc3.ax.xxx.com)
(Port = 1521)
)
)
(CONNECT_DATA =
(SID = SDS)
(GLOBAL_NAME = aaa.ax.xxx.com )
)
)


The tnsping works, sqlplus works when I query the oracle table but when I am trying to create a nickname its failing with this error:

"DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL30080N A communication error "NET8: 1033" occurred sending or receiving
data from the remote database. SQLSTATE=08001"

Help needed !!!!

Firstly, this has nothing to do with DB2. ORA-1033 means that an Oracle instance is not available. Talk to your Oracle guys - they need to give you a proper tnsnames.ora.

If they have no clue, tell them that, without the FAILOVER specified, only one address at random is chosen from those in ADDRESS_LIST. I guess it just so happens that in your case the standby database listener is selection, and obviously you cannot connect to it while it is in standby mode.

The tnsnames.ora entry that I added was given by the oracle team..But.how does the tnsping and sqlplus work and not the nickname...Correct me if I am wrong, but in either of the cases, meaning when using tnsping,sqlplus does it not look at tnsnames.ora file at all?

I think you should ask that in an Oracle forum. That would make an entertaining theoretical discussion. If you just need to fix the problem, try adding (FAILOVER=on) as the first item in ADDRESS_LIST

Itried to add FAILOVER=ON ,but result is the same.I get the same error.

aaa.ax.xxx.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(FAILOVER=ON)
(COMMUNITY = tcpcom.dx.deere.com)
(PROTOCOL = TCP)
(Host = abc4.ax.xxx.com)
(Port = 1521)
)
(ADDRESS =
(COMMUNITY =abccom.ax.xxx.com)
(PROTOCOL = TCP)
(Host = abc3.ax.xxx.com)
(Port = 1521)
)
)
(CONNECT_DATA =
(SID = SDS)
(GLOBAL_NAME = aaa.ax.xxx.com )
)
)

as the first item in address_list, not in address

I tried that too...same issue

aaa.ax.xxx.com =
(DESCRIPTION =
(ADDRESS_LIST =
(FAILOVER=ON)
(ADDRESS =
(COMMUNITY = tcpcom.dx.deere.com)
(PROTOCOL = TCP)
(Host = abc4.ax.xxx.com)
(Port = 1521)
)
(ADDRESS =
(COMMUNITY =abccom.ax.xxx.com)
(PROTOCOL = TCP)
(Host = abc3.ax.xxx.com)
(Port = 1521)
)
)
(CONNECT_DATA =
(SID = SDS)
(GLOBAL_NAME = aaa.ax.xxx.com )
)
)

any ideas guys??