Oracle Exadata Express Service – Kicking the Tires (Part 4 – SQLcl Connectivity)

In the last post we looked round connecting to our Exadata Express Instance via a browser to play with App Builder and SQL Workshop.

How about if we want to connect via SQLcl (you can use SQLPlus if you like but SQLcl is sooo much more productive!)?

Well if you recall from the earlier post there is an option in the Service Console to enable client access

2017-06-04_23-37-34.png

So, let’s click that link

Enable Client Access

Clicking the ‘Enable Client Access’ button is a tad underwhelming (I’m not sure what I expected), but the Service Console now has some new options available.

2017-06-05_00-14-20.png

 

In order to connect via SQLcl we need to download some client credentials (think of it like private keys in SSH).

We can also download drivers for other languages (e.g. NodeJS), also there are handy links for downloading SQLcl, SQL Developer etc.

So clicking on the Download Client Credentials asks me to enter a password:

2017-06-05_00-18-42.png

It took me a few attempts to create a password with sufficient complexity (even using 1Password), once I had a valid password it took 20 seconds or so to download the credentials (I assume in the background it’s generating a private / public key)

2017-06-05_00-20-46.png

There is a very handy link at the end of the Instructions for step-by-step walkthroughs of connecting via the various clients. In essence it boils down to:

2017-06-05_00-30-58.png

For SQLcl, I just needed to move the downloaded zipfile somewhere SQLcl will be able to access it – note you do not need to unzip the file.


bash-3.2$ ls -al
total 48
drwxr-xr-x 3 jes staff 102 9 May 21:22 .
drwxr-xr-x 3 jes staff 102 9 May 21:22 ..
-rw-r--r--@ 1 jes staff 22421 9 May 21:22 client_credentials.zip

Now fire up SQLcl, using nolog mode.


bash-3.2$ sql /nolog

SQLcl: Release 4.1.0 Release Candidate on Tue May 9 21:32:12 2016

Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL>

Now following the instructions we just need to tell SQLcl about the cloudconfig:


SQL> set cloudconfig client_credentials.zip
***** JCE NOT INSTALLED ****
***** CAN NOT CONNECT TO PDB Service without it ****
 Current Java: /Library/Java/JavaVirtualMachines/jdk1.8.0_131.jdk/Contents/Home/jre
 Follow instructions on http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html
SQL>

Hmm ok, that didn’t work too well.

Ah! Looking back at the requirements up above, I didn’t install the Java Cryptography Extension. Ok let’s do that by visiting the helpful link in the error message:

2017-06-05_00-33-18.png

When you download this zipfile you’ll find it contains 3 files:

  • local_policy.jar
  • README.txt
  • US_export_policy.jar

If you read the README.txt (everyone does that right?) – you’ll see you need to copy those 2 jar files to the location of your JRE (overwriting the originals after taking a backup incase you want to revert!).

In my case, on a Mac I needed to copy them to-

/Library/Java/JavaVirtualMachines/jdk1.8.0_131.jdk/Contents/Home/jre/lib/security

Once I’d done that I needed to quit out of SQLcl and reconnect so it picked up the new jar files, then I could retry the cloudconfig setting


SQL> set cloudconfig client_credentials.zip
Using temp directory:/var/folders/2l/lnw1_vds343f1byjvzlr70y80000gn/T/oracle_cloud_config5160868892063171139
SQL>

Curiously it doesn’t prompt me for my password here (the one I used when I Downloaded Client Credentials (even though the help says it should). Now according to the Help I should be able to just connect

and…here’s where some problems started….this implies I should use the syntax

conn <username>/<password>@<servicename>

So I tried all types of combinations of my email address (assuming that was the right username), also for servicename I used –

  • aeexa01
  • aeexa01-<Identity Domain>
  • aeexa01-<Identity Domain>.
  • aeexa01-aeprod.db.em2.oraclecloudapps.com

but almost everything I tried resulted in an error similar to


SQL> conn <MY USERNAME>@aeexa01
Password? (**********?) ****************
USER = <MY USERNAME>
URL = jdbc:oracle:thin:@aeexa01
Error Message = IO Error: Invalid connection string format, a valid format is: "host:port:sid"
USER =<MY USERNAME>
Error Message = IO Error: Invalid connection string format, a valid format is: "host:port:sid"

Warning: You are no longer connected to ORACLE.
SQL>

Question Everything

So, I fell back on an old technique I always have in these situations, step away from the computer for a bit, have a coffee, do something else entirely and then come back to it.

The first thing I tried (I’m still not sure what made me do it), was trying the exact same connection details shown in the help, i.e.

conn pdb_admin/welcome1@dbaccess

and i received this


SQL> conn pdb_admin/welcome1@dbaccess
USER = pdb_admin
URL = jdbc:oracle:thin:@(description= (address=(protocol=tcps)(port=1522)(host=dbaccess.em2.oraclecloudapps.com)) (connect_data=(service_name=xz2kksm1oe.nldc1.oraclecloud.com)) (security=(ssl_server_cert_dn="CN=dbaccess.em2.oraclecloudapps.com,O=Oracle Corporation,L=Redwood Shores,ST=California,C=US")) )
Error Message = ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.
SQL>

check out the “the account is locked” message!

How could this be? My instance is not named dbaccess so am I connecting to someone elses instance?

Remember I told you earlier you didn’t need to unzip that zip file? Well that’s exactly what I did to see what was in it.


bash-3.2$ ls -al
total 112
drwxr-xr-x 9 jes staff 306 9 May 07:54 .
drwxr-xr-x 4 jes staff 136 9 May 07:53 ..
-rw-------@ 1 jes staff 7045 9 May 22:20 cwallet.sso
-rw-------@ 1 jes staff 7000 9 May 22:20 ewallet.p12
-rw-------@ 1 jes staff 3181 9 May 22:20 keystore.jks
-rw-r-----@ 1 jes staff 134 9 May 22:20 sqlnet.ora
-rw-r-----@ 1 jes staff 334 9 May 22:20 tnsnames.ora
-rw-------@ 1 jes staff 3797 9 May 22:20 truststore.jks
bash-3.2$

Ah, there’s a tnsnames.ora in there, let’s look at it –


bash-3.2$ more tnsnames.ora
dbaccess = (description=
(address=(protocol=tcps)(port=1522)(host=dbaccess.em2.oraclecloudapps.com))
(connect_data=(service_name=<SOME RANDOM STRING>.nldc1.oraclecloud.com))
(security=(ssl_server_cert_dn="CN=dbaccess.em2.oraclecloudapps.com,O=Oracle Corporation,L=Redwood Shores,ST=California,C=US"))
)

Ah, so that explains it, looks like the default configuration gives a name of dbaccess.

But what about the PDB_ADMIN user that we used? Well lets take a look back of the Service Console page –

Notice the PDB_ADMIN user is mentioned there. Now let’s set a password

Now lets try connecting again –


SQL> conn pdb_admin@dbaccess
Password? (**********?) ***********
Connected.
SQL>

Success!

Let’s try some basic queries –


SQL> select object_name, object_type from user_objects;
no rows selected

hmmm, not very exciting, let’s check which users are available


SQL> select username from all_users order by username;

USERNAME
---------------------------------------------------------------------------------------------------------------
ANONYMOUS
APEX_050000
APEX_050100
APEX_INSTANCE_ADMIN_USER
APEX_LISTENER
APEX_PUBLIC_USER
APEX_REST_PUBLIC_USER
APPQOSSYS
AUDSYS
C##CLOUD$SERVICE
C##CLOUD_OPS
C##DBPOD
C##DV_ACCT_ADMIN
C##DV_OWNER
C##PDBSERVICE$ADMIN
C##SERVICE_CONFIG
C9_OPS
CTXSYS
DBSFWUSER
DBSNMP
DIP
DVF
DVSYS
FLOWS_FILES
GGSYS
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
LBACSYS
OJVMSYS
OLAPSYS
ORACLE_OCM
ORDS_METADATA
ORDS_PUBLIC_USER
OUTLN
PDB_ADMIN
PDB_MON
REMOTE_SCHEDULER_AGENT
SYS
SYS$UMF
SYSBACKUP
SYSDG
SYSKM
SYSRAC
SYSTEM
WMSYS
XDB
XS$NULL
XZ2KKSM1O

49 rows selected.

&nbsp;

SQL>

So quite a lot of default users setup. Notice also the schema named XZ2KKSM1OE which is the same default one I noticed in SQL Workshop in a previous post.

So let’s set the password for that account and trying logging in –


SQL> alter user XZ2KKSM1OE identified by <MY_NEW_PASSWORD>
2 /

User XZ2KKSM1OE altered.

SQL> conn XZ2KKSM1OE@dbaccess
Password? (**********?) ************
Connected.

Great!


SQL> select object_name, object_type from user_objects;
OBJECT_NAME                 OBJECT_TYPE
DEPT                              TABLE
SYS_C0013892                      INDEX
EMP                               TABLE
<rest of output ommitted>

So, you see how easy it is to connect from your local machine to Exadata Express via SQLcl (that is, once you know all the quirks!)

One thought on “Oracle Exadata Express Service – Kicking the Tires (Part 4 – SQLcl Connectivity)

  1. Pingback: Oracle Exadata Express Service – Kicking the Tires (Part 5 – SQL Developer Connectivity) | Johns Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s