Question How To Setup Dataserver For Oracle

Cecil

19+ years progress programming and still learning.
I've trying out DataServer for ORACLE (30 day trial) for the first time and the online manual is not being very helpful.

How do I configure DataServer? Do I need any Oracle products installed?

OE: 11.5.1 64bit for Windows.
OS: Windows 10.

Also I'll be connecting to an Oracle Database via VPN.
 

Cecil

19+ years progress programming and still learning.
Getting a little bit closer. I found a dirty old whitepaper for version 9 but I'm Guessing it should still work.

But i'm getting lost. Trying to figure it out.

Code:
[15/10/12@14:47:10.350+1300] P-019476 T-038292 1 ORA    -- ORACLE_HOME set to 'C:\Users\James\AppData\Local\Oracle\instantclient_12_1' (14875)
[15/10/12@14:47:32.339+1300] P-019476 T-038292 1 ORA    -- Login failure: ORA-12170: TNS:Connect timeout occurred (14879)
[15/10/12@14:47:32.339+1300] P-019476 T-038292 1 ORA    -- -Dsrv qt_debug: 0 (0x0 None) (6489)
[15/10/12@14:47:32.340+1300] P-019476 T-038292 1 ORA    --   Cursor limit: 84 (-c setting) (6490)
[15/10/12@14:47:32.340+1300] P-019476 T-038292 1 ORA    -- BROKER: The server could not open the foreign database. (1891)
[15/10/12@14:47:32.341+1300] P-019476 T-038292 1 ORA    -- Logoff from dataserver db as user READ_ONLY@10.11.1.2. (2690)
[15/10/12@14:47:32.342+1300] P-019476 T-038292 1 ORA    -- Remote ORACLE server end. (1898)
[15/10/12@15:06:57.291+1300] P-037184 T-006316 1 ORA    -- Remote ORACLE server begin. (1897)
[15/10/12@15:06:57.291+1300] P-037184 T-006316 1 ORA    -- Executable was compiled on Nov 24 2014 at 17:38:22 (5067)
[15/10/12@15:06:57.291+1300] P-037184 T-006316 1 ORA    -- Login to dataserver db as user X. (2689)
[15/10/12@15:06:57.304+1300] P-037184 T-006316 1 ORA    -- ORASRV version 17 (6440)
[15/10/12@15:06:57.306+1300] P-037184 T-006316 1 ORA    -- Using shared library C:\app\client\James\product\12.1.0\client_1\OCI.DLL (oci 8.1) (14874)
[15/10/12@15:06:57.306+1300] P-037184 T-006316 1 ORA    -- ORACLE_HOME set to 'C:\Users\James\AppData\Local\Oracle\instantclient_12_1' (14875)
[15/10/12@15:06:57.790+1300] P-037184 T-006316 1 ORA    -- Login failure: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA (14879)
[15/10/12@15:06:57.790+1300] P-037184 T-006316 1 ORA    -- -Dsrv qt_debug: 0 (0x0 None) (6489)
[15/10/12@15:06:57.790+1300] P-037184 T-006316 1 ORA    --   Cursor limit: 84 (-c setting) (6490)
[15/10/12@15:06:57.790+1300] P-037184 T-006316 1 ORA    -- BROKER: The server could not open the foreign database. (1891)
[15/10/12@15:06:57.791+1300] P-037184 T-006316 1 ORA    -- Logoff from dataserver db as user READ_ONLY@10.11.1.2. (2690)
[15/10/12@15:06:57.791+1300] P-037184 T-006316 1 ORA    -- Remote ORACLE server end. (1898)
 

Cringer

ProgressTalk.com Moderator
Staff member
We use the SQL Server flavour. No idea if you need to do this for ORACLE too, but have you set up the Windows DSN?
 

TheMadDBA

Active Member
You need to make sure that the Oracle connection info is correct. Can you connect from SQLPLUS to the Oracle database?

Are you planning on doing local connections or remote connections? I would suggest remote since you have much better control over the configuration.

I find the OE documentation rather useful, but if you don't have an Oracle background it can be a little tricky I suppose.
 

Chris Hughes

ProgressTalk.com Sponsor
I've done this once before with Oracle, plenty of times with SQL Server.
I created a Windows DSN as Cringer says. You must achieve this first and check that your test connection via the DSN works.

Regarding local or remote connections as MadDBA mentions, it depends how heavy you want the client install to be. With local connections you would need to install the Oracle ODBC clients on each PC etc and create the DSNs (although you can export Win registry keys to help).
With remote connections you can go via the Oracle Dataserver (via your OpenEdge server) therefore making the local installs easier as the DSN is just on the server.

There are pros and cons for local vs remote - deciding this first will at least help focus you with how to go forward.
 

Cecil

19+ years progress programming and still learning.
Do I need to be using a TNSNAMES.ORA file and if so were should it go?

I setup an ODBC System DSN and tested the connection and that worked without a problem. Sooooo I'm still not clear where I put the server host details?

This my connect string.

Code:
-DataService orabroker1 -S 5162 -H localhost -N tcp -U READ_ONLY@adb -P READ_ONLY -Dsrv svub,1
 

Cecil

19+ years progress programming and still learning.
You need a TNSNAMES.ORA in your Oracle client directory tree. Tnsnames.ora - Oracle FAQ

Once you have that set up you can verify by using running sqlplus to connect to the Oracle instance.


Thanks.
I did find KB article about needing the set the TNS_ADMIN environment variable and that is where I copied the TNSNAMES.ORA file too. Annoyingly this was not mentioned in the WhitePaper.

To help others who might come across the same problem I'm having, here are the environment variables I've set under the OpenEdge Explorer.

TNS_ADMIN=%ORACLE_HOME%\network\admin
ORACLE_HOME=C:\Users\James\AppData\Local\Oracle\instantclient_12_1
ORACLE_SID=adb
DSLOGDIR=@{WorkPath}


The next part I'm having trouble with (and out of my control) is the users name I've been given does not have SELECT access to series of ORACLE system tables on the database. I have contacted the ORACLE dba, however I am unsure if he understands or know how to allow SELECT access to the required system tables. DOH.
 

TheMadDBA

Active Member
If they don't understand they aren't an Oracle DBA :)

If they have reservations about allowing that level of access.. I usually had one user to create/maintain the dataserver and one or more users with much more limited rights to actually run the code.
 

Cecil

19+ years progress programming and still learning.
If they don't understand they aren't an Oracle DBA :)

If they have reservations about allowing that level of access.. I usually had one user to create/maintain the dataserver and one or more users with much more limited rights to actually run the code.

I dealing with the Japanese.... Staff members get allocated job positions and responsibilities but the majority of their own tech support is outsourced.

The impression I get is they are given a basic training to maintain their own internal systems but otherwise it's off loaded to their own support channels for additional tech. support.
 

TheMadDBA

Active Member
Not too shocking... just pointing out how basic that task is. I am sure we all have run into Progress developers/dbas that hold the position but not the skills.
 

Cecil

19+ years progress programming and still learning.
The Oracle "DBA" has just emailed me system tables in form of CSV files. Not knowing anything about Oracle, but apparently 'ReadOnly' users don't/can't have SELECT access to the system tables. Is this right?
 

TheMadDBA

Active Member
Not an Oracle restriction by any means. Maybe a policy on their side or misunderstanding.

One user to actually create/maintain the schema holder that can be turned off when not in use will usually resolve most policy issues. From a strict security/audit perspective the normal users should not need access to those system tables because they cross schemas/users.

There are user specific versions that Progress could/should be able to read that would make life so much easier in the vast majority of cases.
 

Cecil

19+ years progress programming and still learning.
I Suggested that they create a new special user for me and I have not heard anything since. Does creating users on Oracle effect the licensing?
 

TheMadDBA

Active Member
If they are using named user licensing it could in theory... usually this does not apply to system accounts unless they outnumber the "butts in seats" count. Most people use CPU based licenses or one of the free versions of Oracle.

Them knowing that this user would be transient in nature might help with any security concerns.
 

Cecil

19+ years progress programming and still learning.
I'm finally getting somewhere. Now I come across the following dialogue boxes with followed by an error. I found an KB article and removed the ORACLE_SID environment variable.

ScreenShot172.png
ScreenShot173.png
ScreenShot174.png
ScreenShot175.png
Also I get this message and don't quite understand what it's asking me to do???
ScreenShot176.png
 

TheMadDBA

Active Member
I doubt that the owner name should be READ_ONLY. That should match the schema name (user) that owns the objects you want to translate into the schema holder.

If there is a distributed database in the mix you should be connecting to that DB instead of the one you are connected to.
 

Cecil

19+ years progress programming and still learning.
From the SQL Plus command line which SQL statement can I user to list the 'objects'?
The problem I have is I'm flying blind, I don't have a real grip on whole ORACLE database. All I have really is a username & password and a Oracle database call 'adb'.
 

TheMadDBA

Active Member
Probably a good idea to get up to speed on Oracle. Especially if the DBAs aren't strong. What we would call a database in Progress is an instance in Oracle. An instance is a collection of user schemas that own objects (tables,views,etc.). The tables you are interested in are owned/connected to one of more of those schemas. Try and get the schema name from whoever is asking you to do this :)

I would advise you download SQL Developer: SQL Developer Downloads Release 4.0

It will give you a nice GUI interface to hide a lot of the guts from you. Otherwise you are left to query the system tables directly which is not for the faint of heart/new to Oracle.
 

Cecil

19+ years progress programming and still learning.
Thank for the update. I've now got a little bit further now. So the issue I was have was the Shema Ower as you said. I've selected some tables to load and it's "Loading ORACLE Definitions" But it just locked up. Doh!
ScreenShot177.png
 
Top