The Cloud! You may have heard a few things about Cloud services and Oracle over the past few years. What I’m going to talk about today is our Database Schema Service offering.
It’s what it sounds like, pretty much. On a kick-butt Exadata box, you get your own schema in a database that we take care of for you. The data dictionary has been modified to make any cross-schema contamination impossible. So you just see you, no one else can see you, and the dictionary views only reference your account.
I have a new account up and running. It can be managed all through our web portal. You can create and manage users, your APEX installation, and consult your service dashboard to see what’s going on.
After signing up for your service you’ll receive an email to activate your account, and then another with your account details plus links to maintain your subscription.
The only real thing I needed to do to get started right away was to reset my main and sftp account passwords.
With that done, I can start shoving data and objects up into the service.
Uploading Local Data to My Cloud Service
Before I can move anything up, I need to define my connection. Everything you need to know to connect is available in your welcome email or in your service dashboard. I did have to reset my SFTP user password, but that takes two seconds.
Once you know the password, you can define your Cloud connection in SQL Developer.
DEFINING YOUR CONNECTION – FOR BROWSING (HTTPS) AND UPLOADING DATA (SFTP or OSS)
Identity Domain, Service Name, User Name, Password: You can find these details in the Welcome to Oracle Cloud email that you received for the OSS service.
By the way, you can always also use Application Express (APEX) to mess around with your schema objects…
But I’m the tools guy, specifically, Oracle SQL Developer.
So let’s go back to SQLDev.
I can browse my objects. I get a connection tree, just like with ‘normal’ connections:
Why can’t I edit anything with SQL Developer?
Well, we’re not connecting with SQL*Net or JDBC. Nope, we’re making REST calls over the HTTPS. So on the service side, I have some RESTful Services configured to help SQL Developer show this information.
We’ll be adding more services to allow for more work to be done via SQL Developer. Stay tuned!
Move the data up already!
Remember the Cart?
Well, with the Cart, I can drag ‘local’ database objects – really any Oracle database object reachable via standard JDBC connections, and copy them up to my Schema As A Service Cloud account.
Do you want the data? Do you want the DDL? Do you want both? Add any filters you want to apply. And that’s it. Just hit the deploy button.
There’s a ‘cloud’ button in the main Cart toolbar. Click it.
Then answer these questions:
Once you hit OK, you’ll see a progress dialog. It will give you feedback on the objects being exported to a local file on your machine – this is the ZIP file being referenced in the above dialog. It’s creating a series of DDL and SQL*Loader data streams, that will be uploaded to a staging FTP server. A daemon process will see your zip sitting there, and scan it for viruses and other bad, naughty things, and then process the scripts into your schema service.
You can see this via the Deployments item in the tree.
If I click on the deployment in the tree, you can get an update on your upload request. It will move from APPROVED to RUNNING to PROCESSED.
And then when we’re done…
According to the logs, my upload took only 2 minutes from start to finish. I created 2 tables and uploaded about 12,000 records.
Browse Your New Data
The ‘split editors’ trick works here too – so I can view my Schema As A Service table definition and its data in one panel.
New data, what to do with it?
Maybe you are familiar with the free customer evaluation instance of Oracle Application Express at apex.oracle.com? If you want to go from a ‘play’ space to a production ready space, the Schema As A Service offering is worth an evaluation.
The Cloud Service starts out with APEX already up and running. And in a few seconds I’ve modified the default application to let me play with my data. I could have just as easily uploaded my own local APEX app though…
This whole thing runs on REST
Perhaps you’ve heard of Oracle REST Data Services (ORDS?) It is powering the entire backbone of this Cloud service. RESTful endpoints are serving up the data to populate all of these screens – including the ones you see in SQL Developer. So, we’re using our own stuff to build you new stuff. Which in turn drives us to make our stuff even better. Which benefits you, whether you use our Cloud service or not.
Whew, way too many words today.
My next post will show how to use a command line call in SQL Developer to upload a cart to the Cloud service. I promise it will be MUCH shorter.
3 Comments
Using SQL Developer 4.1.5.21 I can connect to my Database Schema Service Connection and view my tables, however when attempting to perform a data upload I cannot drag and drop the tables to the cart.
When right clicking on a table no options are presented either. Am I connected in a READ-ONLY mode?
The user account used to login to my Database Schema Service has the “workspace administrator” and “developer privileges”, and is assigned to the “SQL Developer” Group.
You can use the cart to upload data from an on premises database to your schema service, but you can’t add schema service tables to a cart.
Able to access the Cloud service from the Web browser ( and able to use sql-worksheet, apex , Team Development etc)
But when tried to connect via SQL Developer 4.1.3 and 4.1.5 under cloud connection got this error. please help.
oracle.dbtools.raptor.cloud.auth.AuthenticationFailedException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at oracle.dbtools.raptor.cloud.auth.basic.BasicAuthenticationEngine.doPost(BasicAuthenticationEngine.java:162)
at oracle.dbtools.raptor.cloud.auth.basic.BasicAuthenticationEngine.authenticate(BasicAuthenticationEngine.java:63)
at oracle.dbtools.raptor.cloud.auth.basic.GUIBasicAuthenticationEngine.authenticate(GUIBasicAuthenticationEngine.java:26)
at oracle.dbtools.raptor.cloud.connection.ConnectionCreator.createConnection(ConnectionCreator.java:24)
at oracle.dbtools.raptor.cloud.connection.CConnections$C.getConnection(CConnections.java:39)
at oracle.dbtools.raptor.cloud.connection.CConnections.getConnection(CConnections.java:176)
at oracle.dbtools.raptor.cloud.navigator.CloudConnection.openConnectionImpl(CloudConnection.java:127)
at oracle.dbtools.raptor.cloud.navigator.CloudConnection.getConnection(CloudConnection.java:89)
at oracle.dbtools.raptor.cloud.navigator.ConnectionTreeNode$LoadTask.doWork(ConnectionTreeNode.java:70)
at oracle.dbtools.raptor.cloud.navigator.ConnectionTreeNode$LoadTask.doWork(ConnectionTreeNode.java:34)
at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:621)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1949)
at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:302)
at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:296)
at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1509)
at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:216)
at sun.security.ssl.Handshaker.processLoop(Handshaker.java:979)
at sun.security.ssl.Handshaker.process_record(Handshaker.java:914)
at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1062)
at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1375)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1403)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1387)
at org.apache.http.conn.ssl.SSLSocketFactory.createLayeredSocket(SSLSocketFactory.java:494)
at org.apache.http.impl.conn.DefaultClientConnectionOperator.updateSecureConnection(DefaultClientConnectionOperator.java:232)
at org.apache.http.impl.conn.AbstractPoolEntry.layerProtocol(AbstractPoolEntry.java:279)
at org.apache.http.impl.conn.AbstractPooledConnAdapter.layerProtocol(AbstractPooledConnAdapter.java:146)
at org.apache.http.impl.client.DefaultRequestDirector.establishRoute(DefaultRequestDirector.java:840)
at org.apache.http.impl.client.DefaultRequestDirector.tryConnect(DefaultRequestDirector.java:647)
at org.apache.http.impl.client.DefaultRequestDirector.execute(DefaultRequestDirector.java:479)
at org.apache.http.impl.client.AbstractHttpClient.execute(AbstractHttpClient.java:906)
at org.apache.http.impl.client.AbstractHttpClient.execute(AbstractHttpClient.java:1138)
at org.apache.http.impl.client.AbstractHttpClient.execute(AbstractHttpClient.java:1076)
at org.apache.http.impl.client.AbstractHttpClient.execute(AbstractHttpClient.java:1050)
at oracle.dbtools.raptor.cloud.connection.DefaultClient.executeRequest(DefaultClient.java:96)
at oracle.dbtools.raptor.cloud.auth.basic.BasicAuthenticationEngine.doPost(BasicAuthenticationEngine.java:153)
… 17 more
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:387)
at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:292)
at sun.security.validator.Validator.validate(Validator.java:260)
at sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:324)
at sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:229)
at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:124)
at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1491)
… 37 more
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)
at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)
at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:280)
at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:382)
… 43 more