Asterisk to oracle 11 via unixodbc - any oracle help!

Asterisk Asterisk Support
1

DOES ANYONE HAVE ANY EXPERIENCE OF ASTERISK TO ORACLE

For the last 6 months trying to move my asterisk call logging from mysql to an Oracle DB. All existing questions raised on this forum do no show an answer, and have been through every asterisk/oracle resource I can find.

I have created the odbc/odbcinst ini files, and can connect to the database via isql without any problems, and retrieve data.

When connecting via asterisk, the message log states:

cdr_odbc.c: cdr_odbc: Unable to connect to datasource: ora11db

On the server where asterisk is located is a working 11db, which I can also connect to via other remote tools.

I have made the following configuration:
modules.conf - made sure that cdr_mysql is not loaded, and cdr_odbc is loaded.

The TNS connection via the listener is call ora11db, and also the DNS name in odbc.ini

Checking via the CLI, CDR shows the corect DNS, username and password.

There are configurations in cdr_odbc too.

Does anyone have a working CDR to unixodbc on Fedora ? I would be interested to compare configurations

thanks

2

Has no-one any experience of Asterisk to Oracle DB ???

3

Hello,

my cdr_odbc.conf:
[global]
dsn=asterisk
username=asterisk
password=asterisk
loguniqueid=yes
dispositionstring=yes
table=a_cdr
usegmtime=no

res_odbc.conf:
[asterisk]
enabled => yes
dsn => asterisk
username => asterisk
password => asterisk
pre-connect => yes

You must delete the config files res_pgsql.conf & res_mysql.conf.
Let me know if it works!

4

duplicate post removed

5

since my original post, i have got a little further. All my configs are and turned on unix odbc debug and discovered that it looks like a permissions issue between unixodbc and oracle. using verbose loggin in asterisk, cdr_odbc gets ll tne config details, but the sql_connect fails with an IM004 error.

Looking through oracle errors this looks like unixodbc cannot access/read some part of the oracle environment.

I’m using an oracle 11 db on the same server, so trying to use the existing oracle so libraries. I also tried installing the oracle instant client, but has the same errors.

So still looks like a oracle / unixodbc permission problem. However ISQL connects to the database without any problems.

6

I can’t say anything to oracle 11, we run the 10g express edtion.

By default, asterisk is running with the user “asterisk” i think.
Can you connect with “sqlplus user/passwort@sid” with the user “asterisk”?

You must set the environment variables for your asterisk user.
http://www.oracle.com/technology/software/products/database/xe/files/install.102/b25144/toc.htm
(Point 4.3)

7

i had asterisk in the relevant groups, but the asterisk user did not have the environment variables set. So just added the oracle environment to the .bash_profile, and can now sqlplus from the asterisk user.

just working on something else at the moment, so will have to test a little later

8

unix odbc log reports : [ODBC][3950][__handles.c][421]
Exit:[SQL_SUCCESS]
Environment = 0x8b58e08
[ODBC][3950][SQLSetEnvAttr.c][178]
Entry:
Environment = 0x8b58e08
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = (nil)
[ODBC][3950][SQLSetEnvAttr.c][345]
Exit:[SQL_SUCCESS]
[ODBC][3950][SQLAllocHandle.c][345]
Entry:
Handle Type = 2
Input Handle = 0x8b58e08
[ODBC][3950][SQLAllocHandle.c][463]
Exit:[SQL_SUCCESS]
Output Handle = 0x8b59398
[ODBC][3950][SQLSetConnectAttr.c][311]
Entry:
Connection = 0x8b59398
Attribute = SQL_ATTR_LOGIN_TIMEOUT
Value = 0xa
StrLen = (nil)
[ODBC][3950][SQLSetConnectAttr.c][493]
Exit:[SQL_SUCCESS]
[ODBC][3950][SQLConnect.c][3244]
Entry:
Connection = 0x8b59398
Server Name = [ora11db][length = 7 (SQL_NTS)]
User Name = [www][length = 3 (SQL_NTS)]
Authentication = [*********][length = 9 (SQL_NTS)]
[ODBC][3950][SQLConnect.c][1190]Error: IM004
[ODBC][3950][SQLFreeHandle.c][268]
Entry:
Handle Type = 2
Input Handle = 0x8b59398
[ODBC][3950][SQLFreeHandle.c][317]
Exit:[SQL_SUCCESS]
[ODBC][3950][SQLFreeHandle.c][203]
Entry:
Handle Type = 1
Input Handle = 0x8b58e08

which is still the IM004 error which appears to be caused by not being able to load the correct library or find the correct path.

my odbcinst.ini entry is :
[OracleODBCdriver]
Description = Oracle ODBC driver for Oracle
Driver = /u01/app/oracle/product/11.1.0/db_1/lib/libsqora.so.11.1
Setup = /u01/app/oracle/product/11.1.0/db_1/lib/libsqora.so.11.1
DontDLClose = 1
FileUsage = 1
UsageCount = 1

which points to the oracle 11 library

thanks

9

okay,
please post your odbc.ini from the /etc/ directory and your system environment variables from the asterisk user.

For test purposes, copy the files libnnz*.so, libsqora.so., libclntsh.so. from the oracle lib directory to /usr/lib.

10

moved files to /usr/lib.

odbc.ini entry is :
[ora11db]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = OracleODBCdriver
DSN = ora11db
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = ora11db
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = www
Password = xxxxxx

asterisk env’s are :

ORACLE_HOME_LISTNER=/u01/app/oracle/product/11.1.0/db_1/network/admin
ORACLE_BASE=/u01/app/oracle
TNS_ADMIN=/u01/app/oracle/product/11.1.0/db_1/network/admin
PATH=/u01/app/oracle/product/11.1.0/db_1/bin:/usr/sbin:/usr/kerberos/bin:/usr/lib/ccache:/usr/local/bin:/bin:/usr/bin:/home/asterisk/bin
CLASSPATH=/u01/app/oracle/product/11.1.0/db_1/JRE:/u01/app/oracle/product/11.1.0/db_1/jlib:/u01/app/oracle/product/11.1.0/db_1/rdbms/jlib
ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
LD_LIBRARY_PATH=/usr/lib

LD library path was changed after copying shared libraries

thanks

11

you don’t need to change the LD library path. (/usr/bin is normally included)

this is my odbc.ini:

[Asterisk]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 10g ODBC driver
DSN = XE
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = 172.21.0.143
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
UserID =

DSN should point to your SID/Instance, i think you take your hostname?
ServerName = Hostname (or IP)
I don’t use any password field.

12

have tried everything again. Changed odbc connections as per your config, and still the same errors.

For each change of configs, I have performed a reboot just to be sure it’s a clean asterisk. Also after each test, checked isql which is still working.

So got to a sticking point again. I am still unsure whether the oracle library libsqora.so.11.1 is the correct one. I am using befora 8, and there was a unixodbc patch last week, but that has made no difference either.

Will continue experimenting when I get chance, but have to use my production/working asterisk to do the testing, availability is limited.

thanks for you help so far - at least I know that oracle does work, and will carry on with the battle of trying to get it to work

13

Im running fedora 8 with oracle 10g express edition and this is working with cdr, extensions, sip buddies, voicemail users in realtime.

For test, download the free oracle 10g express edition.
You can ask me for future questions.

have a good weekend,
Thomas

14

Loaded the 10g xe client - at this point don’t want to move db back to 10g.

Pointed the environment to the xe client libraries, changed asterisk configs, and still getting the same errors. Isql still works ok.

My C is only ‘basic’, but going to do some more work on trying to debug the SqlConnect more in the cdr_odbc.c . I know which statement is failing on the Init function.

Just a little busy with other things at the moment, so will have a look next week.

Thanks for your help and support so far.

15

Hi

Do I need to install the oracle client if asterisk and oracle run on same server?

/Jesper Vels

16

I never got asterisk working with oracle.

Still using MYSQL = no-one has updated this thread since I was last looking at it.

I do know that if asterisk and oracle is on the same server, that you shouldn’t need a client.

Everyone I spoke to was using the client to talk from an asterisk server to a database server.

Sorry, but cannot offer any help, as I was not able to resolve this problem. I currently use Fedora 8, and in the process of moving/creating a new service in fedora 9, and am going to try again to see if I can get asterisk talking to oracle.

17

I’ve gotten Oracle integration working.

  • Install InstantClient.

  • Install unixODBC.

  • (x64 only) Apply patch as described in sourceforge.net tracker item 2260937 to unixODBC.

  • Configure connection to Oracle. Example:
    odbc.ini:
    [PRD11R1]
    Application Attributes = T
    Attributes = W
    BatchAutocommitMode = IfAllSuccessful
    BindAsFLOAT = F
    CloseCursor = F
    DisableDPM = F
    DisableMTS = T
    Driver = Oracle
    DSN = PRD11R1
    EXECSchemaOpt =
    EXECSyntax = T
    Failover = T
    FailoverDelay = 10
    FailoverRetryCount = 10
    FetchBufferSize = 64000
    ForceWCHAR = F
    Lobs = T
    Longs = T
    MetadataIdDefault = F
    QueryTimeout = T
    ResultSets = T
    ServerName = prd11r1.invantive.local
    SQLGetData extensions = F
    Translation DLL =
    Translation Option = 0
    DisableRULEHint = T
    UserID =

  • odbcinst.ini:
    [Oracle]
    Description = Oracle
    Driver = /usr/lib/libsqora.so.11.1
    Setup =
    FileUsage =
    CPTimeout =
    CPReuse =

[ODBC]
Trace=Yes
TraceFile=/tmp/sql.log
ForceTrace=No
Pooling=No

  • /etc/asterisk/modules.conf: load => cdr_odbc.so
  • cdr_odbc.conf:
    ;
    ; cdr_odbc.conf
    ;

[global]
dsn=PRD11R1
username=usr_asterisk
password=xyz
loguniqueid=yes
dispositionstring=yes
table=bubs_asterisk_cdr_r
;usegmtime=no ; set to “yes” to log in GMT

  • res_odbc.conf:
    ;;; odbc setup file

; ENV is a global set of environmental variables that will get set.
; Note that all environmental variables can be seen by all connections,
; so you can’t have different values for different connections.
[ENV]
ORACLE_HOME=>/var/opt/oracle
TNS_ADMIN=>/var/opt/oracle/network/admin
NLS_LANG=>AMERICAN_AMERICA.AL32UTF8
NLS_NCHAR=>AL32UTF8

[PRD11R1]
enabled=>yes
dsn=>PRD11R1
username=>usr_asterisk
password=>xyz
pre-connect=>yes

18

You might be interested in connecting Asterisk AGI with Oracle like databases. Please visit the following URL:
forums.digium.com/viewtopic.php? … ght=oracle

19

[quote=“iasgoscouk”]I never got asterisk working with oracle.

Still using MYSQL = no-one has updated this thread since I was last looking at it.

I do know that if asterisk and oracle is on the same server, that you shouldn’t need a client.

Everyone I spoke to was using the client to talk from an asterisk server to a database server.

Sorry, but cannot offer any help, as I was not able to resolve this problem. I currently use Fedora 8, and in the process of moving/creating a new service in fedora 9, and am going to try again to see if I can get asterisk talking to oracle.[/quote]

… Hi there,

I’m in the same situation. I spent the last week trying to set up Asterisk 1.4.24.1 on a Virtual CentOS 5.0 machine with Oracle 10g in a remote server, but i miserably failed.

I installed the oracle-odbc instant client… but no luck at all.

Asterisk-RealTime-MySQL-odbc runs nice and smoothly…

Have you got lucky lately ??

20

You may try you luck with Easysoft Oracle ODBC drivers. But the purpose wont be served for long, as sooner or later you will be inclined to take advantage of Multithreaded Oracle Connection Pooling. But let me tell you what I did. I have written a Multithreaded TCP server which serves the request from Asterisk CDR, AGI, API, AMI, ARA (Real Time) components etc and connected the TCP server with Oracle 11g/10g using Oracle OCI Libraries. In this way I am able to use Oracle mutlithreaded OCI libraries with connection pooling. So the response is as fast as that of MySQL with additional benefit I am able to put all the business logic relating to calls (IVR, VoiceLogger, Dialer, IP PBX) in the time tested Oracle Stored Procedures with J2EE support. Secondly I connected Pocket Sphinx (Automatic Speech Recognition Engine) with my TCP server while taking advantage of storing Sphinx Linguistic Model, Language Model, Grammar, Transcription, Speech Corpus, Sound Files for Sphinx training all in the Oracle Database, taking advantage of deciding (in run time) about the sphinx related data just according to the requirement.