Oracle Cloud Free: Connect to Your 21c Database From an OCI Client

Welcome back.  In my earlier blog post we created an account on Oracle Cloud Free Tier, and we created an Oracle 21c database as part of the Always Free option.  Each database comes with one superuser named ADMIN, and we created a developer account named SADIE.

Database users on Oracle Cloud can be configured with REST or not.  REST-enabled users can connect to the database through the Oracle Cloud web-based interfaces like SQL Developer Web.  Users without REST enabled can only connect to these databases using traditional client software such as SQL*Plus installed on your laptop.

Connecting from your own application or from a client-server utility like SQL*Plus on your laptop requires special attention and is low-to-medium difficulty.  I'll walk you through the process in this blog post.

You will need Oracle Client software, and since everything on Oracle Cloud is secured you'll need a copy of your database wallet in order to connect to the database.  Don't bother getting these things now as my steps below will tell you when and where to get stuff.

Oracle Cloud Free Tier supports database connections from clients compatible with Oracle 18.2 and higher.  You don't need a 21c client to connect to a 21c database.  In the below steps I walk you through the process of obtaining and installing the client software.

First, let's install Oracle Instant Client on your computer.  If you already have it installed, skip this step.  Official instructions are found on Oracle's web site (ref).

  1. Go to https://www.oracle.com/database/technologies/instant-client/downloads.html.
  2. Select your platform such as "Instant Client for Microsoft Windows (x64)".
  3. Download "Basic Package" to a scratch space on your computer.
  4. Also download "SQL Plus Package".
  5. Also download "Tools Package" if you will need data loading tools.
  6. Unzip the "Basic Package" file to c:\oracle and it will create a subdirectory like instantclient_19_9.
  7. Unzip the other two packages to the same location (c:\oracle) and they will add files to the same subdirectory (instantclient_19_9).
  8. Update your computer's environment variable PATH to include the instant client directory (c:\oracle\instantclient_19_9).
  9. Create a sub-directory named network, and a sub-directory under that called admin.  This lower level is known as your TNS Admin directory.  For example:

    mkdir c:\oracle\instantclient_19_9\network\admin

Second, test the Oracle Instant Client software.  The software comes ready to run on Windows.  It does not need to be compiled.
  1. Go to your Oracle Client software directory (i.e., c:\oracle\instantclient_19_9).
  2. Run the sqlplus executable.  
  3. If it prompts for a user name, then it is working and you can press Ctrl-C to stop here.  
  4. If your computer throws an error about missing the Visual Studio runtime, see here to get the software.

At this point we are done installing and configuring Oracle Instant Client software.

Tip: Oracle Cloud does not support pings and will not reply to them, so you will not be able to verify the host addresses found in your tnsnames.ora by pinging them.  All you can do is try a database connection using a tool like SQL*Plus.  Database connection requests hit a specific port number where the database listener is running.  If the hostname is bad, then will hang for a while.  If it is valid, then you'll get some type of response immediately.

A word of caution before we proceed.  The below steps will overwrite any existing files with the same names.  I strongly recommend making a copy of your TNS_ADMIN folder before proceeding. 

Third, you need your database's TNS address and wallet.  You can get these from the Oracle Cloud database page, which you first saw immediately after creating the database.  Here are the steps:

  1. Log into your Oracle Cloud Free Tier account.
  2. Go to your database's summary page.
  3. Click the button for DB Connection to download a copy of your wallet.  The zip file also includes TNS files required for remote connections
  4. You will be prompted to set a wallet password.
  5. After downloading the wallet file you may unzip the contents to TNS_ADMIN directory you defined earlier (c:\oracle\instantclient_19_9\network\admin).
  6. You'll notice 8 files with names like wallet, store, sqlnet, and tnsnames.  There's also a README file, but it only contains the date that the wallet was created and the date that your SSL certificate in the wallet will expire.  The download does not include any directions or instructions.
  7. Edit file sqlnet.ora and edit the wallet location parameter to match the path where you have unzipped the files.  If you are using Microsoft Windows, then you need double slashes such as c:\\oracle\\instantclient_19_9\\network\\admin.  This use of double-slashes is only required within file sqlnet.ora.  Do not use double slashes when setting environment variables.
  8. Set environment variable TNS_ADMIN to the same path but without double slashes, such as C:\oracle\instantclient_19_9\network\admin.
  9. View the file tnsnames.ora and copy or write down the first alias for your database.  It is the string that comes before the first equal sign.

Finally, we're ready to connect to our Oracle Cloud database from an OCI client on our Windows laptop.

  1. Call the SQL*Plus executable.  On Microsoft Windows, just double-click on the file sqlplus.exe.
  2. SQL*Plus will open a command window.
  3. You will be prompted for a username.  You must follow the username with @yourtnsalias such as ADMIN@MYATP1.  After entering the information press [Enter] to continue.
  4. You will be prompted for a password.  Input the password you set for the given database user, but do not enter the wallet password.  When done press [Enter] to continue.
Below is a complete example:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 4 16:54:26 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Enter user-name: ADMIN@remi9898atp1_low
Enter password:
Last Successful login time: Thu Feb 04 2021 16:53:15 -05:00

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.2.0.0.0

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
feqw1pod1

SQL>


Comments

Post a Comment

Popular posts from this blog

Using DBGen to Generate TPC-H Test Data

Image
In this post I describe the Data Base Generator (DBGen) utility in terms of what it is, how to install it, and how to use it.   This post assumes a Linux operating system.   There are different build procedures for DBGen on Windows, but the concepts carry over. DBGen is used to create a TPC-H schema (i.e., it provides the CREATE TABLE statements) and to generate the data.   DBGen is used in official TPC-H benchmarking, but it can also be used in informal TPC-H-like benchmarking.   The schema and data is compatible with most 3 rd party benchmarking tools like HammerDB, so even if you plan to run a TPC-H-like test using HammerDB you may still use DBGen to create the schema and data. The  CREATE TABLE   statements are ANSI standard SQL and can be run in any DBMS without editing.   There are no SQL statements for indexes or foreign key constraints provided by DBGen since those types of objects are not required.   The data is created as delimited...
Read more

Oracle 21c Caching Solutions

This blog post reviews all of the caching solutions available in Oracle Database 21c.  I dive into several of the caching solutions, but I do try to keep things relatively short-and-sweet here.  I have other blogs dedicated to each caching solution. I don't mean the Oracle SGA or PGA.   I mean optional features you can enable to enhance performance. The content of this blog post is applicable to Oracle 12g, 18c, 19c, and 21c.   Some of the caching solutions were available in Oracle 11g, but I did not regression test my examples back that far. There have been no new caching solutions in Oracle 12g R2 through 21c.   One solution in particular, the In-Memory Column Store, has received some major enhancements.   I briefly describe it here, but the deep dive is in another blog post. Summary of Oracle Caching Solutions Oracle 21c includes six caching features as listed in my table below.   I've listed them in order from newest on top to oldest at t...
Read more

TPC-like Database Benchmarking Tools

Image
  This post describes five popular tools for benchmarking relational databases: HammerDB, Quest Benchmark Factory, SwingBench, pgbench, and Oracle RAT.  Previously, I described TPC and TPC-like benchmarks for relational databases.   Recall a benchmark is "like" a TPC benchmark if it follows some of the rules but not all of them.   The tools described in this post follow some of the TPC's rules so we call them "TPC-like".   Publishing results is generally not allowed by commercial DBMS vendors, and use of the benchmark trademark is also restricted. Ok then, let's look at the most commonly used benchmarking tools for relational databases. HammerDB is my #1 choice for benchmarking relational databases.   HammerDB supports every major DBMS on Linux and Windows including bare metal, VM, and cloud.   This means you can use one tool for all environments.   You can even compare performance across platforms or DBMS, such as comparing Oracle on Linux...
Read more