TL/DR: – we switched out the old SQL Worksheet for the one you see in SQL Developer Web (AKA Database Actions).
In November of 2021, we released a new service in the Oracle Cloud (OCI), Database Tools. It allows you to create database connections, and then use those connections in your applications, OR use them to run queries in a OCI Console webpage.
Here’s what it looked like on November 16, 2021:
The features we launched in 2021 were pretty impressive even for a v1, and included:
- Define Oracle Database connections
- Store the database password in the OCI Vault
- Create Bastions to access the DB VCN as needed
- Automatically Launch SQLcl in CloudShell with said connection
- Open a SQL Worksheet in OCI Console with said connection
The SQL Worksheet was also pretty feature rich, and offered:
- the ability to change connections or create new ones
- Run SQL, get results to a GRID
- Run a script, get script output
- Save results to a local file or in the object store
- SQL insight assistance
- SQL History
You can read my feature-deep-dive demo here.
Footnote: we added support for MySQL connections recently as well. Have you heard about Heatwave?
And THIS is what that looks like as of today.
2023 Features:
- Browser to navigate your schemas
- Object Search
- Create/Edit/Open objects from said schemas
- Load data from CSV, Excel, JSON, etc to new or existing tables
- Visual Explain Plan & AutoTrace
- Resizable SQL worksheet editor/results
- Data Grid – Single Record View, Column Sorting, Hide/Re-Order Columns
It’s a lot of stuff, and you’ve probably seen me talk about these features via SQL Developer Web in OCI we have hosted for our Autonomous Database Cloud Services.
Or, you’ve probably seen me talk about this for your on-premises Oracle Databases via Oracle REST Data Services.
But this is different
This doesn’t require an ORDS install. This doesn’t require an Autonomous Database (although we support that). This SQL Worksheet can be setup for ANY Oracle Database you have running in OCI.
By the way, it supports our new managed MySQL instances as well!
A Quick Demo
Connecting to my BASE database service
In order to connect to something, I need to create said ‘something,’ and in this case the something is a 19c EE BASE service in our Montreal region, called ‘brouepub.’
Now, I already created two different database connections (SYS and JEFF), I’m going to show you what using it looks like, first. THEN I’ll show you how I set it up to be used.
But Jeff, GUIs are for wimps, give us the mighty command-line!
Ok, you’re in charge. Let’s go!
By the way, this isn’t new, but it’s still very cool!
Defining my connections
The Database Tools service is listed in the OCI Console under the Developer service category –
If you haven’t already setup a Vault, you’ll need one. This is the ‘Secret Service’ we use to keep your encrypted database passwords, SAFE.
If you’ve done your homework or already have Vault, then you can get started by clicking the ‘Create Connection’ button.
On the pop-out slider, the first big question is, what are we going to name your connection? This name is VERY important. It’s the only thing you’ll have to distinguish your connection. So probably some combination of a database user and database service name.
After that, you’ll use our menu selection to find the particular Oracle Database you want to connect to.
Do you want to use an Autonomous Database? No, how about the Base service – that’s both the VM and BareMetal varieties? No again? How about our Exadata on OPC (aka ExaCS?)
Or maybe you have a database that our control plane doesn’t know about! Maybe you have a Kubernetes cluster or a compute node that you’ve put an Oracle Database onto.
Just choose the ‘Enter database info’ radio option! If you have a connection string, we’ll know how to get there! Think, EZCONNECT strings.
But what about the networking?
Yeah, that’s the ‘hard’ part, but we do our best to make it easier.
If we were to finish off our connection properties by scrolling down the connection dialog, we’d see this –
Ah, private endpoints (PEs), my ‘nemesis.’ I say nemisis because networking is one of my many IT weakness. More or less, our database is on a particular OCI virtual network (VCN), and the Database Tools service needs a path to this VCN. Creating a PE allows us to do that!
If you click the Create PE button, you’ll see something like this –
If there’s a place to ‘screw this up’ so you can’t connect, it’s HERE. The INGRESS Rule on your private subnet, for port 1521.
This is basically saying, hey, allow for TCP activity on port 1521 (the database listener!) from this IP address, which is the CIDR Block for our Subnet.
Your prize for making it this far – more info and pictures!
The SQL worksheet is FULL FEATURED.
So for example, we can use it to –
1. Load Data
2. Execution Plans
I’ve talked about the SSB schema before, and it’s accompanying execution plans. Well, what’s that look like with our new plan viewer?
Well, it looks like THIS:
1 Comment
I know networking is your nemesis, but here goes. We have a working environment that puts SSL termination from the public in via LB (normal) and somehow does the same from the DB private endpoint (requiring SSL) to the private subnets that hold the computes, none of which can do SSL.
Terminating SSL on the load balancers is standard, easy, but getting the REST return traffic from the private endpoint to the private LB to feed the computes is undocumented, but working and I just can’t figure out how.
(public IP’s masked for security)
Public Load Balancer (traffic comes from DNS URL) – XXX-XXX-LB xxx.xxx.xx.xxx terminates SSL
sends traffic through Test-Retail-Pub-SN xxx. xx.xx.xxxx/27
LB routes to one of two computes in private Test-Retail-App-SN 172.30.24.160/27
JavaServices compute (172.30.24.187) if needs ORDS, sends request to ORDS compute 172.30.24.162
ORDS compute sends request to ATP-S DB in private Test-Retail-DB-SN 172.30.24.192/27 with a private endpoint of 172.30.24.194
Up to this point, this is very simple
Now, since Private endpoint returns SSL by mandate:
Traffic from private endpoint 172.30.24.194 is somehow sending all traffic to private LB 172.30.24.185 in the test-retail-app-sn 172.30.24.160/27 <——THIS IS WHAT IS UNKNOWN, HOW IS THIS ROUTING BEING ACCOMPLISHED
LB 172.30.24.185 terminates SSL and routes traffic to the JavaServices compute 172.30.24.187 or to the ORDS compute 172.30.24.194 based on the URL of the REST return. – this is also very standard
This is working but how it got working is not documented, I need to understand the main point above:
Traffic from private endpoint 172.30.24.194 is somehow sending all traffic to private LB 172.30.24.185 in the test-retail-app-sn 172.30.24.160/27 <——THIS IS WHAT IS UNKNOWN, HOW IS THIS ROUTING BEING ACCOMPLISHED.
(Yes I have an SR asking this question for the past week, no joy)
Thanks for the help.