MIM 2016: Understanding Oracle DB and the MIM Oracle Connector

Get Oracle Server Ready

For the purpose of this blog post, I am going to create an Oracle Server and DB. Go to the Oracle website, download and install a free copy. I installed my Oracle on Windows 7, desktop version.

Versions

Initially I installed the latest and greatest version 12 but discovered that the MIM Connector would not work. That is because version 12 is not supported. So I removed v12 and installed v11. I advise you stay with v11.

After installing there are some key files on the DB server

SQLNET.ora – This has to do with the how authentication will work. Leave this file unchanged

TNSNAMES.ora – This has to do with how services and process (client connections) will connect with the Oracle server/DB. There is a Listener section in that file, by default it is set to HOST = LOCALHOST, this specifies that the Oracle DB is only listening for TNS connections locally. Unless you plan to install MIM on the same Oracle server, change it to HOST = 0.0.0.0, this way, Oracle is listening on all ips for a TNS connection. Restart the Oracle services after this change.

There is a section in the TNSNAMES.ora that specifies client connection. Perhaps a good way to understand why we have this section is how you make a connection from PowerShell or from SQLPlus command line. For these command line connections you must specify

  • Username
  • Password
  • DataSource in the format OracleServername:Port/Servicename e.g TLKPC3:1521/MyOrcl

The name of the Service can be found in the Oracle Server TNSNames.ora file which is generated during Server installation.

So the connection section in TNSNAMES.ora captures the  Datasource format that you will use in the command line section. The Connection section has

  • Oracle Servername
  • Port
  • Service name

Get MIM Connector Ready

On the MIM server, install the Oracle client. I would advise you install the full client not the instant client. I installed the v11 client, the v12 is not MIM supported. After installing, check if the TNSNAMES.ora file is installed. In my case, it was not. Create the file in the $Oracle_Home\Network\Admin folder. Add this to the file

Add a friendly name e.g “MYMIMOracle” =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle Server Name)(PORT = 1521)))

(CONNECT_DATA =

(SERVICE_NAME = Get the name from the server tnsnames.ora)

)

)

So for the example I would have

TLKMIMORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = TLKWIN7CLIENT)(PORT = 1521)))

(CONNECT_DATA =

(SERVICE_NAME = TLKORCL)

)

)

Save the file

Test the connection on the MIM server

Open a command prompt and enter

Sqlplus myoracleid/myoraclepwd@OracleServername:Port/Servicename

e.g

sqlplus mimorclid/mymimpwd@tlkwin7client:1521/TLKORCL

You will get a successful connection response.

Tables in Oracle

Let’s discuss tables in Oracle and the way you specify them. In Msft SQL we simply specify the name of the table. In Oracle, we have to specify the name of the DB or Schema where the table resides else Oracle will look for the table in the System DB which is the DB installed with Oracle.

So If I installed DB called HRUsers and a table called TLKHRUsersInfo, I would specify

HRUsers.TLKHRUsersInfo as the name of the table when doing a select or query. If I put just TLKHRUsersInfo, Oracle will assume its System.TLKHRUsersInfo and the query will fail.

Configure MIM connector page

Advertisements