netxms with oracle 10+ backend

[victor]

Hi!

I assume you are not on Windows We don't have binary distribution for Oracle driver yet - if you need Oracle support, you have to built NetXMS from sources. I recommend using Oracle InstantClient instead of full client. Instruction is following:

1. Download instantclient-basic and instantclient-sdk archives from http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html and unpack both into same directory. I usually unpack them inside /opt - then you'll get something like /opt/instantclient_11_2
2. Configure NetXMS sources as usual, and add --with-oracle=/opt/instantclient_11_2
3. Do make and make install as usual
4. In netxmsd.conf, set DBServer as //oracle_server_ip//oracle_sid (DBName is ignored by oracle.ddr server).

Now you should be able to initialize database with nxdbmgr and start server.

Best regards,
Victor

[andrey--k]

checkinstall fails with next error:

make[3]: Entering directory `/opt/netxms-1.2.11'
if test "xyes" = "xyes"; then LD_LIBRARY_PATH="/usr/local/lib:" /usr/local/bin/nxmibc -d /usr/local/share/netxms/mibs -o /usr/local/share/netxms/mibs/netxms.mib; fi
/bin/bash: /usr/local/bin/nxmibc: No such file or directory

[victor]

What is checkinstall?

Best regards,
Victor

[andrey--k]

checkinstall - debian utility which does make install and creates deb package

[andrey--k]

just tried make install - finished without errors

[victor]

Looks like checkinstall cannot handle it correctly. make install supposed to install nxmibc and after that post installation hook is called which will compile MIBs using just installed nxmibc.

Best regards,
Victor

[Percy]

hi Victor,

i am using NetXMS 1.2.11 with Postgres as Backend database. it is  working Properly. now i have another test machine with Oracle as Backend Database but netxms is not able to connect to the Oracle Database. it shows the error message like

Unable to Connect to database : ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Machine Details
OS- Windows Server 2008 r2 64 Arch,
RAM - 16GB,
Database - Oracle 11g
NetXMS 1.2.11

Thanks
Percy

[victor]

Hi!

Seems that you've made a mistake in configuration. Please provide your netxmsd.conf and Oracle server parameters (address and service name). Also, what Oracle client you are using?

Best regards,
Victor

[Percy]

hi victor,
Sorry for the late reply . i have attached the netxmsd.conf, tnsnames.ora, Listener.ora file.
when i start Server Configuration Wizard to select the Data base Engine i selct the Oracle than Create new database become disable.
in case of Postgresql netxms create its database names Netxms_db and its required schema. why in case of oracle create new database become Disable.

thank you
Percy

---

netxmsd.conf

tnsnames.ora

listener.ora

[victor]

For Oracle you have to create database user manually (and this will create schema) and choose "existing database" option.

If you are using InstantClient you should specify Oracle connection string in DBServer parameter in form //server_ip/service_name

To do initialization manually, follow these steps:

1. Connect to Oracle server with sqlplus as sysdba:

C:\Source\NetXMS\x64\debug>sqlplus sys/admin@//192.168.64.3 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 19 11:41:37 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

2. Create user and grant necessary rights:

SQL> create user netxms identified by netxms;

User created.

SQL> grant create session to netxms;

Grant succeeded.

SQL> grant resource to netxms;

Grant succeeded.

SQL> grant create view to netxms;

Grant succeeded.

SQL>

3. Set correct values in netxmsd.conf. In my case it will be

DBDriver = oracle.ddr
DBServer = //192.168.64.3/XE

DBLogin = netxms
DBPassword = netxms

4. Initialize database:

C:\Source\NetXMS\x64\debug>nxdbmgr init ..\..\sql\dbinit_oracle.sql
NetXMS Database Manager Version 1.2.12 Build 7861

Initializing database...
Database initialized successfully

C:\Source\NetXMS\x64\debug>

Also, it seems that initialization script shipped with NetXMS installer may contain errors and database initialization will fail with "cannot insert NULL" error. In that case, please use attached initialization script.

Best regards,
Victor

---

dbinit_oracle.sql

[andrey--k]

Looks like missing dependencies or not included library:

Configuration Debian 7.4, Oracle 11_2 on separate server.

$nxdbmgr init /usr/share/netxms/sql/dbinit_oracle.sql
NetXMS Database Manager Version 1.2.12 Build 7861

Unable to load and initialize database driver "/usr/lib/netxms/dbdrv/oracle.ddr"

$ sudo cat /var/log/netxms

[19-Feb-2014 10:25:07.642] Log file opened
[19-Feb-2014 10:25:07.642] [DEBUG] Code page set to ASCII
[19-Feb-2014 10:25:07.643] [INFO ] Platform subagent "/usr/lib/netxms/linux.nsm" successfully loaded
[19-Feb-2014 10:25:07.643] [DEBUG] Debug callback set for DB library
[19-Feb-2014 10:25:07.650] [ERROR] Unable to load database driver module "oracle.ddr": libclntsh.so.11.1: cannot open shared object file: No such file or directory

[victor]

You have to install Oracle client manually because it's not an open source software and there are no package in Debian repository for it. I recommend using Instant Client (http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html) version 11.2.0.4.0. Recommended location - /opt/instantclient_11_2.

Best regards,
Victor

[andrey--k]

TO WIKI:
Debian 7
Oracle 11.2g on separate server:

#less /etc/netxmsd.conf

DBDriver = /usr/lib/netxms/dbdrv/oracle.ddr
DBServer = ServerIP/Hostname.DomainName #Here is service (full database name), not SID
DBName = netxms
DBLogin = netxms
DBPassword = PaSwD

download from oracle web:
instantclient-basic-linux.x64-11.2.0.4.0
unpak into /opt/instantclient_11_2

create file
less /etc/ld.so.conf.d/oracle.conf

/opt/instantclient_11_2

Reload the list of system-wide library paths:

ldconfig

on oracle:

-- USER SQL
CREATE USER netxms IDENTIFIED BY netxms
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
-- QUOTAS
ALTER USER netxms QUOTA UNLIMITED ON USERS;
-- ROLES
GRANT RESOURCE TO netxms ;
GRANT CONNECT TO netxms ;

on Debian:

nxdbmgr init /usr/share/netxms/sql/dbinit_oracle.sql

if next errors appeared:

Initializing database...
SQL query failed (ORA-01400: cannot insert NULL into ("NETXMS"."USERS"."FULL_NAME")):
INSERT INTO users (id,name,password,system_access,flags,full_name,                    description,grace_logins,auth_method,guid,                    cert_mapping_method,ce                          rt_mapping_data,                    auth_failures,last_passwd_change,min_passwd_length,                    disabled_until,last_login)    VALUES (0,'admin',            '3A                          445C0072CD69D9030CC6644020E5C4576051B1',            65535,8,'','Built-in system administrator account',5,0,            '00000000-0000-0000-0000-000000000000',0,'',0,0,0,0                          ,0          )
Database initialization failed
andrey@i0debnxms:/usr/share/netxms/sql$ sudo vi db
dbinit_db2.sql      dbinit_mysql.sql    dbinit_pgsql.sql    dbschema_db2.sql    dbschema_mysql.sql  dbschema_pgsql.sql
dbinit_mssql.sql    dbinit_oracle.sql    dbinit_sqlite.sql    dbschema_mssql.sql  dbschema_oracle.sql  dbschema_sqlite.sql
andrey@i0debnxms:/usr/share/netxms/sql$ sudo vi dbinit_oracle.sql
[sudo] password for andrey:
andrey@i0debnxms:/usr/share/netxms/sql$ nxdbmgr init /usr/share/netxms/sql/dbinit_oracle.sql
NetXMS Database Manager Version 1.2.12 Build 7861

Initializing database...
SQL query failed (ORA-01400: cannot insert NULL into ("NETXMS"."USERS"."CERT_MAPPING_DATA")):
INSERT INTO users (id,name,password,system_access,flags,full_name,                    description,grace_logins,auth_method,guid,                    cert_mapping_method,cert_mapping_data,                    auth_failures,last_passwd_change,min_passwd_length,                    disabled_until,last_login)    VALUES (0,'admin',            '3A445C0072CD69D9030CC6644020E5C4576051B1',            65535,8,'admin','Built-in system administrator account',5,0,            '00000000-0000-0000-0000-000000000000',0,'',0,0,0,0,0          )
Database initialization failed
andrey@i0debnxms:/usr/share/netxms/sql$ sudo vi dbinit_oracle.sql
andrey@i0debnxms:/usr/share/netxms/sql$ nxdbmgr init /usr/share/netxms/sql/dbinit_oracle.sql
NetXMS Database Manager Version 1.2.12 Build 7861

edit dbinit_oracle.sql
find and change from:

           65535,8,'','Built-in system administrator account',5,0,
           '00000000-0000-0000-0000-000000000000',0,'',0,0,0,0,0

to:

           65535,8,'admin','Built-in system administrator account',5,0,
           '00000000-0000-0000-0000-000000000000',0,' ',0,0,0,0,0

!!! here is changed admin as full username and whitespace as sertificate

before rerunning nxdbmgr init i have made drop and create user once more.

[andrey--k]

Created wiki page.
http://wiki.netxms.org/wiki/Oracle
Please if something is incorrect, please apply changes there.

[Percy]

hi Victor,

Great help...thanks a lot.
its working now

regards  !
Percy