Configure OTRS for Oracle12

Moderator: crythias

Post Reply
claud10r
Znuny newbie
Posts: 5
Joined: 02 Mar 2018, 11:38
Znuny Version: 6.0.6

Configure OTRS for Oracle12

Post by claud10r »

Hello,
I've been experiencing some problems in the configuration of OTRS for Oracle 12.

What I did:
- edited /otrs/scripts/apache2-perl-startup.pl: added use lib PATH_TO_PERL , uncommented use DBD::Oracle (); and use Kernel::System::DB::oracle;
- edited /Kernel/Config.pm: added connection parameters to Oracle, commented the mysql ones.

Now, if I use mySql there are no problems, I can login into the web-interface and everything works fine, but whenever I edit Kernel/Config.pm to use Oracle something strange happens.
If I run otrs.Console.pl Maint::Database::Check it says "connection successful", but if I try to use the web-interface i get the following error:

ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach)

Full-log:
Backend ERROR: OTRS-CGI-11 Perl: 5.16.3 OS: linux Time: Thu Mar 15 15:10:42 2018

Message: ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach)

RemoteAddress: ---
RequestURI: /otrs/index.pl?

Traceback (31481):
Module: Kernel::System::Web::InterfaceAgent::Run Line: 184
Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler Line: 40
Module: (eval) (v1.99) Line: 207
Module: ModPerl::RegistryCooker::run (v1.99) Line: 207
Module: ModPerl::RegistryCooker::default_handler (v1.99) Line: 173
Module: ModPerl::Registry::handler (v1.99) Line: 32
And it writes the same thing in /var/log/httpd/error_log

( I had the same problem running the CloneDB package to migrate mysql db to oracle, but I managed to resolve that editing "Kernel/System/CloneDB/Driver/oracle.pm", changing the rule for the DSN

if ( !defined $Param{TargetDatabaseDSN} ) {
$Param{TargetDatabaseDSN} =
"DBI:Oracle:sid=$Param{TargetDatabaseSID};host=$Param{TargetDatabaseHost};port=$Param{TargetDatabasePort};";
}


So CloneDB successfully migrated my DB from mysql to oracle)

Do you have any clue to help me?
Thanks in advance

EDIT:
./bin/otrs.Console.pl Maint::Database::Check
says "connection successful" only when run from otrs user
If I try to use
su -c "/opt/otrs/bin/otrs.Console.pl Maint::Database::Check" otrs
as root, I get the following error
Trying to connect to database '---' with user 'otrs'...
Error: install_driver(Oracle) failed: Can't locate DBD/Oracle.pm in @INC (@INC contains: /opt/otrs/Custom /opt/otrs/Kernel/cpan-lib /opt/otrs /root/perl5/lib/perl5 /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at (eval 37) line 3.
Perhaps the DBD::Oracle perl module hasn't been fully installed,
or perhaps the capitalisation of 'Oracle' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, SQLite, Sponge, mysql.
at /opt/otrs/Kernel/System/DB.pm line 204.
wurzel
Znuny guru
Posts: 3224
Joined: 08 Jul 2010, 22:25
Znuny Version: x.x.x
Real Name: Florian

Re: Configure OTRS for Oracle12

Post by wurzel »

Hi,

you need a oracle client (sqlplus connect must work)

you need oracle perl driver

you need to have correct DSN

and then your otrs.Console.pl Maint::Database::Check

should return OK and you can use it.


Good luck.

Florian
OTRS 8 SILVER (Prod)
OTRS 8 auf Debian 11 (Test)
Znuny 7.x latest version testing auf Debian 11

-- Ich beantworte keine Forums-Fragen PN - No PN please

I won't answer to unfriendly users any more. A greeting and regards are just polite.
claud10r
Znuny newbie
Posts: 5
Joined: 02 Mar 2018, 11:38
Znuny Version: 6.0.6

Re: Configure OTRS for Oracle12

Post by claud10r »

wurzel wrote:Hi,

you need a oracle client (sqlplus connect must work)

you need oracle perl driver

you need to have correct DSN

and then your otrs.Console.pl Maint::Database::Check

should return OK and you can use it.

Good luck.

Florian
I have all of this, I can connect to the DB with any client using the same data in tnsnames.ora
+ Maint::DB::Check already returns ok when run by otrs user - problem is it gives me error when run by every other user, root included

EDIT: Just tried, sqlplus works fine
wurzel
Znuny guru
Posts: 3224
Joined: 08 Jul 2010, 22:25
Znuny Version: x.x.x
Real Name: Florian

Re: Configure OTRS for Oracle12

Post by wurzel »

*edited*
OTRS 8 SILVER (Prod)
OTRS 8 auf Debian 11 (Test)
Znuny 7.x latest version testing auf Debian 11

-- Ich beantworte keine Forums-Fragen PN - No PN please

I won't answer to unfriendly users any more. A greeting and regards are just polite.
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Configure OTRS for Oracle12

Post by root »

Hi,

Talk to to your Oracle DBA to set up the client system properly. I guess you don't populate the Oracle environment variables like ORACLE_HOME for all users.

What's the location of DBD/Oracle.pm (use locate or find to determine)?

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
wurzel
Znuny guru
Posts: 3224
Joined: 08 Jul 2010, 22:25
Znuny Version: x.x.x
Real Name: Florian

Re: Configure OTRS for Oracle12

Post by wurzel »

Hi,

once I got the connection successful with the otrs user - it usually worked fine.

AFAIK apache and/or perl need the variables, too.


Flo
OTRS 8 SILVER (Prod)
OTRS 8 auf Debian 11 (Test)
Znuny 7.x latest version testing auf Debian 11

-- Ich beantworte keine Forums-Fragen PN - No PN please

I won't answer to unfriendly users any more. A greeting and regards are just polite.
claud10r
Znuny newbie
Posts: 5
Joined: 02 Mar 2018, 11:38
Znuny Version: 6.0.6

Re: Configure OTRS for Oracle12

Post by claud10r »

root wrote:Hi,

Talk to to your Oracle DBA to set up the client system properly. I guess you don't populate the Oracle environment variables like ORACLE_HOME for all users.

What's the location of DBD/Oracle.pm (use locate or find to determine)?

- Roy
/opt/otrs/perl5/lib/perl5/x86_64-linux-thread-multi/DBD

I managed to have "connection successful" even with root editing PERL5LIB variable, still can't login through the web-interface because of the

ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach)

error :(

I already checked for every user (otrs, root, apache) and Oracle variables are the same for everyone
TNS_ADMIN, ORACLE_HOME, LD_LIBRARY_PATH
with the same values
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Configure OTRS for Oracle12

Post by root »

claud10r wrote: /opt/otrs/perl5/lib/perl5/x86_64-linux-thread-multi/DBD
That's definitely the wrong location for a system wide installation. I recommend using professional help for this issue.

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
claud10r
Znuny newbie
Posts: 5
Joined: 02 Mar 2018, 11:38
Znuny Version: 6.0.6

Re: Configure OTRS for Oracle12

Post by claud10r »

UPDATE: I did it.
For some reason, adding TNS_ADMIN=*path to tnsnames.ora* to /etc/sysconfig/httpd made it work.
I had previously tried to add SetEnv TNS_ADMIN *path* to httpd.conf but it didn't work. However, now it works. Thanks everyone for your help.
wurzel
Znuny guru
Posts: 3224
Joined: 08 Jul 2010, 22:25
Znuny Version: x.x.x
Real Name: Florian

Re: Configure OTRS for Oracle12

Post by wurzel »

Hi,

Grats! It would be nice if you share your config files.

:)

Cheers
Florian
OTRS 8 SILVER (Prod)
OTRS 8 auf Debian 11 (Test)
Znuny 7.x latest version testing auf Debian 11

-- Ich beantworte keine Forums-Fragen PN - No PN please

I won't answer to unfriendly users any more. A greeting and regards are just polite.
claud10r
Znuny newbie
Posts: 5
Joined: 02 Mar 2018, 11:38
Znuny Version: 6.0.6

Re: Configure OTRS for Oracle12

Post by claud10r »

wurzel wrote:Hi,

Grats! It would be nice if you share your config files.

:)

Cheers
Florian
which ones?
wurzel
Znuny guru
Posts: 3224
Joined: 08 Jul 2010, 22:25
Znuny Version: x.x.x
Real Name: Florian

Re: Configure OTRS for Oracle12

Post by wurzel »

Hi,

all files, you set Oracle Variables and your apache config.
And/or all steps you did to get it running ;-)

Flo
OTRS 8 SILVER (Prod)
OTRS 8 auf Debian 11 (Test)
Znuny 7.x latest version testing auf Debian 11

-- Ich beantworte keine Forums-Fragen PN - No PN please

I won't answer to unfriendly users any more. A greeting and regards are just polite.
Post Reply