620552 - [Oracle] Bugzilla can not connect to Oracle 11G RAC

Status: UNCONFIRMED

(Bugzilla :: Database, enhancement)

Description

[guoliang.wu]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101203 Firefox/3.6.13
Build Identifier: 3.6.3

We are using Bugzilla
 3.6.3 and attempted to cut it over from Oracle 9i to Oracle 11G RAC. 
 We hit a hard stop when Bugzilla, as implemented here, continued to 
 try to access the DB via SID instead of Service_Name.
 The Bugzilla Wiki doesn't have any obvious direction on this matter 
 that we could find.

Software error:

Can't connect to the database.
Error: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor (DBD ERROR: OCIServerAttach)
  Is your database installed and up and running?
  Do you have the correct username and password selected in localconfig?

------------config:
tnsnames.ora

CORPT.AEEO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SDEVORACLU-SCAN.aeeo.ca)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CORPT.AEEO)
    )
  )



Localconfig is using 

# The DNS name of the host that the database server runs on.
$db_host = 'SDEVORACLU-SCAN';

# The name of the database
$db_name = 'CORPT.AEEO';

# Who we connect to the database as.
$db_user = 'bugs';




Reproducible: Always

Steps to Reproduce:
1.change the tnsnames.ora to point to the Oracle 11G RAC service
2.change the localconfig file to have the $db_host to use the Oracle 11G RAC service name instead of the SID

Actual Results:  

Can't connect to the database.
Error: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor (DBD ERROR: OCIServerAttach)
  Is your database installed and up and running?
  Do you have the correct username and password selected in localconfig?

Expected Results:  
db connection ok

We have oracle 11G RAC database environment which contains 3 database server nodes, so we would like the Bugzilla point to the RAC service name not the actual one database node.

Comment 1

[Max Kanat-Alexander]

  Jochen: It appears that Xiaoou isn't at Oracle anymore, from an email that I got recently. Do you think that you would be able to do any Oracle code maintenance for Bugzilla?

Comment 2

[Jochen Wiedmann]

Max, I can do a Bugzilla installation and play with it, as I did in the past. However, I don't have any possibility to use another database than the preinstalled one. In other words, I have no possibility to reproduce the scenario mentioned here.

Apart from that, I think this is likely a pure connection problem. Can you give advice, how Bugzilla can log the DBI URL? If so, Guoliang can reproduce the problem with running the DBI shell. We should be able to defer the problem to a DBI user mailing list, which could give advice on how to correct either configuration or URL. If changing the configuration works, then we are done. Otherwise, there might come a need to build Bugzilla's DBI URL in a different manner.

Comment 3

[Jochen Wiedmann]

Sorry: "pure connection problem" => "pure configuration problem"

Comment 4

[Max Kanat-Alexander]

Hey Jochen. Bugzilla always builds the DBI string in the same way--the code for it currently is in Bugzilla::DB::Oracle, here:

  http://bzr.mozilla.org/bugzilla/trunk/annotate/head:/Bugzilla/DB/Oracle.pm#L55

Comment 5

[Jochen Wiedmann]

Mail from Guoliang:


Hi Max,Jochen

In the DBI coding you mentioned
http://bzr.mozilla.org/bugzilla/trunk/annotate/head:/Bugzilla/DB/Oracle.pm#L55

Has

68      6365        my $dsn = "dbi:Oracle:host=$host;sid=$dbname";
Maybe this is the root cause, becasue for 11G RAC, has to use the service name not the sid.
 
Thanks,
Guoliang

Comment 6

[Jochen Wiedmann]

Guoliang, please try to change the aformentioned line in a manner that allows to connect to the database. Let us know the result.

Max, my recommendation would be to allow the specification of a complete DBI URL in localconfig.pl

Comment 7

[Leo Albert Jackson Jr]

I will take a look at this on to see what I can help with here.
Regards,
Leo

Comment 9

[Sid]

Any solution on the above?


Thanks,
Sid