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 12c R2 but discovered that the MIM Connector with V12 client would not work. That is because version 12 is not supported for this connector. For V12c R2 use the Generic SQL Connector which uses the Oracle 12 ODBC client, see this blogpost I did on how to connect to V12c R2. The Generic SQL Connector is supported with all 64-bit ODBC drivers. If your DB is V12 R1, you can use the V11 client and that will work.
So I removed v12c R2 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
tlktechidentitythoughts 