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
So, let’s click that link
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.
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:
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)
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:
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&nbsp;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:
When you download this zipfile you’ll find it contains 3 files:
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-
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
So I tried all types of combinations of my email address (assuming that was the right username), also for servicename I used –
- aeexa01-<Identity Domain>
- aeexa01-<Identity Domain>.
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>
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.
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>
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. 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.
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!)