CMOD 8.5 on RHEL 6.2 with remote Oracle 11g database

[paulogg]

Hello, All.
I have a cust request to install CMOD 8.5 (LS + OS) on Linux Red Hat 6.2 with an Oracle 11g remote database.
Due to security and compliance issues, having the CMOD database in a corporate Oracle database server environment is a mandatory request.
I know there are a few customers out there running CMOD in production with a scenario like this.
Since this is not officially supported by IBM, just wanted to know if anybody from this forum ever had the chance to successfully configure CMOD 8.5 with a remote Oracle database.
And if so, what's the procedure to follow?

Thanks in advance,
Best regards,

PSGG

[kbsiva]

I  am not sure what do you mean by remote database ? Can you connect via sqlplus or oracle client to those database from Linux ? IBM does support CMod with oracle , check the infocenter please.

[Alessandro Perucchi]

Hello Paulogg,

we did that with one of our customer. I won't advise it, because everything has been done in CMOD, so CMOD will handle automatically everything for you (tablespace creation, load balancing of tables in database, etc...).
And to be honest every customer I know wants to have their Oracle database in a centralized server... for me it's just to cut down the licenses costs from Oracle, since they are so expensive, and there are no real reasons beyond that. At least I haven't heard a single good reason (and not even for security, but that's another topic...).

You might face difficulties with the upgrade to CMOD 9.0.X we have a PMR open with such configuration. Since it's new, I don't have yet feedback.

You will face performance problems, since library and database are not in the same box. For small things, that's OK, but if you have a lot of activities, then you will suffer lag and some kind of degradation of performance.

Well back to the subject. So since you are going that road because of that mandatory request (*sigh*):

So to have a remote database with Oracle, follow the standard instructions and when you define your instance in the tnsnames.ora, just point your entry to the database to a remote server, and have your ars.dbfs empty.
that's more or less all.
Be careful to handle the Oracle Timezone bug problem correctly, otherwise you won't be able to start the LS.
If you want to handle all the filespace yourself (as all the Oracle DBA wants to do it), you will need to create the tablespace user-exit.
And for that you will need to use at least V8.5.0.3, because an enhancement was made to be able also to code in the userexit a drop table/tablespace/index.

Sincerely yours,
Alessandro

[swat]

Hello,

I am in the similar situation now where I have to do setup for CMOD(9.0.2) using remote oracle database.
just want to know the experiences/problems faced, Also who can help in writing the security exit for tablespace any sample?

Thanks,
Swat

[Alessandro Perucchi]

Hello,

just for info this is the technote from IBM:

http://www-01.ibm.com/support/docview.wss?uid=swg27019582

I don't have a sample here, but you can use the skeleton given by IBM in /opt/ibm/ondemand/V9.0/exits/arsutbl.c

And use the arscsxit.h in order to know what represent each parameter.

You also need to make a Oracle connection each time, and for that you will need to link with the Oracle library.

Unfortunately I don't have that much experience with coding and Oracle to be able to help correctly. But maybe you have some Oracle guru on your side.

Sincerely yours,
Alessandro

[SV]

All,
If any of you completed setting up CMOD with remote ORACLE db server?
if so, 1)Can you share all the CMOD configuration parameters that need to be taken care, and what values are assigned to them.

2) Do I absolutely need table creation exit with for having Oracle on remote server? Any working sample of tablespace creation routine available? IBM Document is not enough for me.

[Alessandro Perucchi]

All,
If any of you completed setting up CMOD with remote ORACLE db server?
if so, 1)Can you share all the CMOD configuration parameters that need to be taken care, and what values are assigned to them.

2) Do I absolutely need table creation exit with for having Oracle on remote server? Any working sample of tablespace creation routine available? IBM Document is not enough for me.

Hello SV,

Well, local or remote Oracle DB instance for CMOD that's the same... CMOD will need to be able to read the tnsnames.ora.

The value that you need to put in the file ars.ini for the SRVR_INSTANCE entry is the entry in the tnsnames.ora:

in tnsnames.ora (for example)

Code: [Select]

MYORCLDB=
 (DESCRIPTION=
   (ADDRESS = (PROTOCOL = TCP) (HOST = host.example.com) (PORT = 1521)
   (CONNECT_DATA=(SID=ODDB))
)

and in the ars.ini

Code: [Select]

[@SRV@_CMODTST]
HOST=
PROTOCOL=2
PORT=0
SRVR_INSTANCE=MYORCLDB
SRVR_INSTANCE_OWNER=ondemand
SRVR_OD_CFG=/opt/ibm/ondemand/V9.0/config/ars.cfg
SRVR_DB_CFG=/opt/ibm/ondemand/V9.0/config/ars.dbfs
SRVR_SM_CFG=/opt/ibm/ondemand/V9.0/config/ars.cache

ars.dbfs must be empty

For the ars.cfg, then you need to have to fill the following entries:

Code: [Select]

...

#
# Database for OnDemand to use (Library Server Only)
#
ARS_DB_ENGINE=ORACLE


###########################################
# ORACLE Parameters (Library Server Only) #
###########################################
ARS_ORACLE_HOME=/oracle/product/11.2.0.2
...

Or you can read this document from IBM http://www-01.ibm.com/support/docview.wss?uid=swg27019582

Or simply read the official documentation of CMOD in the knowledge center http://www.ibm.com/support/knowledgecenter/SSEPCD_9.5.0/com.ibm.ondemand.installmp.doc/dodlx093.htm

You don't need to create the user tablespace/table exit. But that's an advise. Sample for tablespace are provided in the "exits" directory in the base installation directory of CMOD.
What do you need exactly for the tablespace user exit?

[danielito]

Hello from Switzerland  first post here 
I allready opened a Ticket since I was not able to solve this problem. with the oracle DB connection. 

--- Do I have to set up a stash File for the 9.0 version or is it still an option ?
--- Do I have to compile the exits to run the Server ?

I guess I did the exits stuff on the Version 8.5 when the Server and DB was running

I am trying to Setup the new 9.0 CMOD and ODWEK on a new Server RedHat
Linux version 3.10.0-327.13.1.el7.x86_64 (mockbuild@x86-019.build.eng.bos.redhat.com) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-4)

CMOD      ProdNo: CIV7WML
ODWEK    ProdNo: CIV83ML
FixPack    ProdNo: 9-0-0-7-Fix-PackLinux

OD User: xxxxu
OD Group: YYYU

xxxxu:yyyu

I have a Oracle DB Clone from the old System 8.5 : VDXXXX04

The Installation from the CMOD Server and FixPack under root is no Problem but I can't connect to
the Oracle DB or even Start the Server.
I am always getting the same Errors even if I do the Installation exactly like the old Running System

/bin $ ./arsdb -u VDXXXX04 -I
arsdb:  ARS4013E Unable to determine the database engine

/bin $ ./arsdb -u -I VDXXXX04
arsdb:  ARS4014E Unable to load >ORACLE<

/bin $ ./arssockd -S -I VDXXXX04
ARS0013E DB Error: STARTUP -- SQLSTATE=1.  ARS_ORIGINAL_CODEPAGE is not defined in ars.cfg.  Run arsdb -u -I VDXXXX04 to determine setting of ARS_ORIGINAL_CODEPAGE, SQLCODE=0, File=arssrvr.c, Line=7248

If I have the same ARS_ORIGINAL_CODEPAGE in the cfg I don't get an error but the Server is not starting

Setup
normal Installation
./odlinux.bin -i console
rpm -Uhv gsk*
rpm -qa | egrep "gskcrypt64|gskssl64"
rpm -Uhv gsk*
rpm -Uhv gsk*6.rpm
./odweklinux.bin -i console

Fix
./odlinux.bin -i console
./odweklinux.bin -i console

cd /opt/ibm/ondemand/V9.0/config
chown xxxxu:yyyu ars.cache ars.cfg ars.dbfs ars.ini
chmod 444 arsload.cfg

vi arsload.cfg/root:root
set PASSWD=xxxxx

vi ars.cache/xxxxu:yyyu

/xx/xx/cache/cache1

vi ars.cfg/xxxxu:yyyu

ARS_NUM_LICENSE=5
ARS_LANGUAGE=ENU
ARS_ORIGINAL_CODEPAGE=1208
# ARS_ORIGINAL_CODEPAGE=923
# ARS_ORIGINAL_CODEPAGE=819
ARS_NUM_DBSRVR=10
ARS_TMP=/tmp
ARS_PRINT_PATH=/tmp
ARS_DB_ENGINE=ORACLE
ARS_ORACLE_USERID=xxxxu
ARS_ORACLE_PASSWD=xxxxu_xxxxu
ARS_ORACLE_HOME=/oracle/product/client/11.2.0.2.0_000
ARS_STORAGE_MANAGER=CACHE_ONLY


vi ars.ini/xxxxu:yyyu

[@SRV@_VDXXXX04]
HOST=                                 (localhost or realhost dosen't work too)
PROTOCOL=2
PORT=1503
SRVR_INSTANCE=VDXXXX04
SRVR_INSTANCE_OWNER=xxxxu
SRVR_OD_CFG=/opt/ibm/ondemand/V9.0/config/ars.cfg
SRVR_DB_CFG=/opt/ibm/ondemand/V9.0/config/ars.dbfs
SRVR_SM_CFG=/opt/ibm/ondemand/V9.0/config/ars.cache

[@SRV@_DD]
PROTOCOL=1

tnsnames.ora

VDXXXX04.DDD.DD=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=vdxxxx04.ddd.dd)(PORT=25XXX)))(CONNECT_DATA=(SERVICE_NAME=VDXXXX04.dddd)))

cd /opt/ibm/ondemand/V9.0/bin
chown xxxxu:yyyu arslog arsprt arsrdprt                      (chown xxxxu:yyyu ars* dosen't work too)


Do I still need this Links
I did also the Link to Oracle like I did under 8.5

cd /opt/ibm/ondemand/V9.0/lib64                                  (two links from Oracle Bug: BUG:10399685 dosen't work too)

ln -sf  /opt/releases/oracle/product/client/11.2.0.2.0_000//lib/libclntsh.so.11.1

now I am almost helpless ....


kind regards Daniel

[Justin Derrick]

Major issue...

ARS_ORIGINAL_CODEPAGE=1208
# ARS_ORIGINAL_CODEPAGE=923
# ARS_ORIGINAL_CODEPAGE=819

You're not allowed to change this number after you set it.  If you've changed this number from the first time you used it, the results are unpredictable, and will likely result in a corrupted database.

-JD.

[danielito]

Major issue...

ARS_ORIGINAL_CODEPAGE=1208
# ARS_ORIGINAL_CODEPAGE=923
# ARS_ORIGINAL_CODEPAGE=819

You're not allowed to change this number after you set it.  If you've changed this number from the first time you used it, the results are unpredictable, and will likely result in a corrupted database.


-JD.

Hmm ok
I have the Oracle Clone DB from my old 8.5 Server and there is the ARS_ORIGINAL_CODEPAGE=1208 allredy in the cfg File.

If I am setting up the 9.0 with this Cloned DB do I have to have an empty "ARS_ORIGINAL_CODEPAGE=" entry in the ars.cfg
and run the /arsdb comands first before I even try to start the Server  ?

or If I have the Clone do I need to set the CODEPAGE to '0'

/opt/ibm/ondemand/V9.0/bin $ ./arsdb -I VDXXXX04 -efv
ARS1607E The OnDemand stash file >/opt/ibm/ondemand/V9.5/config/ars.stash< either does not exist or is not valid.  Return Code=4.arsdb:ARS4023E Unable to connect to ORACLE VDXXXX04 database

even If I have a stash File I getting the same Error 

cheers dani

[Justin Derrick]

I don't have the information to tell you what the correct setting is -- just that the documentation clearly states that you can't change this parameter once it's set.  If you're restoring a fresh database backup from v8.5 to a new system with v9.0, then set it (correctly) once, and make a note in the file to never change it.

-JD.

[danielito]

I don't have the information to tell you what the correct setting is -- just that the documentation clearly states that you can't change this parameter once it's set.  If you're restoring a fresh database backup from v8.5 to a new system with v9.0, then set it (correctly) once, and make a note in the file to never change it.

-JD.

Hi JD

I did now the same Installation on the new Server with the 8.5 and the clone DB. I was also able to set up the Trace File
and I saw that a Directory was missing for the Cache. After the mkdir from this missing directory the Server was running
and I could also open all the Files from the Cache.

Now on the 9.0 Installation with the same configuration I can't the Server start anymore and also not the ORACLE DB

28625:140105016907584 04/03/2017 17:22:23:678585 INFO arssys.c(111)ArcDBP_LoadDBMS:CONSOLE message msg num=215 msg timestamp=2017-04-03 17:22:23.669428 msg userid=ARSSOCKD msg severity=4 msg text=ARS0215I Unable to load the OnDemand Oracle dynamic load library (arsorcle).  Check to make sure that Oracle is installed and that the Oracle shared library (libclntsh.a (AIX), libclntsh.so (SunOS), libclntsh.sl (HPUX), oci.dll (Windows) is in the shared library path msg appl=N msg logid=0
28625:140105016907584 04/03/2017 17:22:23:678589 FLOW arsmsg.c(156)ArcMSGP_Console:Enter

and there Is noe ERROR in teh Trace File just INFO but I guess this Is the problem ......

[Alessandro Perucchi]

Ok...

I also live in Switzerland, and I can tell you, codepage are a nightmare, and you DO NOT want to play with it, especially if you are new in the field.
I've had with my customers horror stories about that aspect... What I will tell you now, if the experience I have with that topic and CMOD from the last 17 years.

If the documentation says that once set, one must NOT change this setting, then, it means that one must NOT change this settings if he doesn't know what it means and the impact behind the scene.

Codepage in any systems are a NIGHTMARE, and especially in systems were you need to convert from one to another to another to another... And CMOD in that regard can be quite difficult to debug when you enter that field... and if you don't know anything, then the nightmare becomes HELL.

That said, what is important for CMOD is the following:

1) Database codepage.
    When you create your database, you must specify a codepage (Oracle, DB2, SQL Server, mySQL, mariaDB, ...)
2) When you created the DB with CMOD
    It was before V8.5.0.3 or after? I mean before or after the introduction of the field ARS_ORIGINAL_CODEPAGE
    If it was before, CMOD was writting in the database with DB codepage that you had setup at that time.
    If it was after, CMOD writes normally in UTF-8 in the database, independently of the database codepage.

Meaning, if you have a database that was created with codepage WIN1252 with CMOD 8.3 in Windows using Oracle.
Then your codepage will be 1252, and during the migration you will need to use ARS_ORIGINAL_CODEPAGE=1252 (for example).
that way CMOD will be able to continue to write with this codepage.

If you do a clone of your database, and you don't look at the original codepage, and choose to use the UTF-8 (which was not the original), then you are nearly certainly in trouble.
Because you might have migrated the database, or thinking that you have cloned correctly your database, but in fact you have corrupted it, because CMOD doesn't write things in the Database as one will think it will write in it for international chars. Normally you would need to import each rows separately and taking care of the codepage at that level, not the db level.

So my advice, especially if you are new to CMOD is the following:

- Don't play with codepages and CMOD, you will loose
- Clone your original database with EXACTLY the same codepage it was set originally
- If CMOD tells you to use ARS_ORIGINAL_CODEPAGE=923, then STICK with it. Don't play around, again you will loose

Once that work, then if you are good in database and codepages, you can try to play around, and see how to convert from your current codepage to a new one... but that is a huge work, and it means to check every bits and bytes of your database to ensure that everything works as expected (old data, new data, etc...), every interface to CMOD, every client with CMOD... you will need to test everything to ensure that what you did didn't break anything.

I hope I could give you a small view on that creepy aspect of CMOD... and that I could give an answer to your question.

[danielito]

Hi Alessandro it's me Daniel 
I was able to resolve all the problems that I had and now 2 Linux Servers are running with the V9.5 and Oracle

I have still some issues with the hardcoded parts in the scripts but I guess I will solve this problems too

Gruess Dänu