Hi,
Toad Product Manager John Pocknell shared some very useful information about how Toad manages Oracle databases in the cloud. The information also included how to get a free 2 month trial of an Oracle Cloud-based Database.
Two Month Free Trial
Oracle Cloud Services Free Trial
Start with creating an account…https://cloud.oracle.com/database . Click on the ‘Try for Free’ button.
Oracle Cloud Services Dashboard
Once you get your account established, use this link to view the dashboard:
https://myaccount.cloud.oracle.com/mycloud/faces/dashboard.jspx. John has already setup a cloud instance called ORCLOUD2. Click the ‘Create Service’ button to start creating your database in the cloud.
Using PuTTY to get VM Public and Private Keys
You will need a "VM Public Key”. This will be used to connect to the new cloud database thru SSH. Download and install PuTTY (a key generator).
Generating VM Public and Private Keys
You want to run the ‘puttygen.exe’ program that installed into the folder you installed PuTTY into. This program will have you move the mouse around in a blank area. Click ‘Save private key’ to save the private key generated.
Saving the Public Key
To save the public key, click "Conversions" off the main menu, then click "Export OpenSSH key". Use the same name but give the file an SSH extension. John used "Key.ssh". Now save the public by using copy/paste into a file called “Key.pub” using textpad or notepad. This will be your "VM Public Key". You can now close the puttygen program.
Return to the Oracle Cloud Dashboard and click ‘Create Service’.
Database Configuration
Click the Edit button next to the VM Public Key. Fill in the rest of the blanks, change the DB Name if you like, etc. John called his “ORCL TEST”. Click the “Create” button. This database may take hours to create. You can logout and come back later to make sure it is done.
New Database in the Cloud
Connecting to the Cloud
After your database has been created, you can review your databases and their detail. https://dbaas.oraclecloud.com/dbaas/faces/dbRunner.jspx to see a list of your databases.
New Database Information
Use this information to create a TNSNAMES.ora entry so you can connect to this database from your desktop Toad (or from other Oracle-based applications).
Edit your tnsnames.ora file found on your workstation where your Oracle client software is in the Oracle_Homenetworkadmin folder. You can use this entry and make your adjustments if you like:
ORCLOUD2 =
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 9999)))
(CONNECT_DATA = (SERVICE_NAME = PDB1.usdell91421.oraclecloud.internal)))
You can see the service name in the “Connect String” information from the above database information panel. John used port 9999. I’m not sure why 1521 wouldn’t work either but I’m following John’s lead here.
Now, start putty.exe and follow these directions:
a) enter the Public IP address that Oracle gave you where it says "Host Name (or IP address)" Leave everything else on that page (including port) as the default
b) On the left, under "Connection", click "Data". On the right, enter "oracle" (no quotes) as "Auto-login username".
c) On the left, expand the SSH node and click "Auth". On the right, use the "Browse" button to locate your private key file.
d) On the left, click "Tunnels". On the right, for "Source Port", enter the port that you've specified in your TNSNames entry (from my example above, 9999). For "Destination" enter the Public IP Address and port used on the server (from my example above, 129.144.13.1:1521). Then click "Add" so it's added to the listbox above.
e) If you want to save these settings, go back to the "Session" node on the LHS, then on the right, enter a name in the "Saved Sessions" edit box, and click "Save".
f) Press "Open" at the bottom to connect. At a window that resembles a DOS prompt enter:
Using username "oracle".
Authenticating with public key “rsa-key-20150825”
Authorized uses only. All activity may be monitored and reported.
[oracle@ORCLOUD2 ~]$
At this point, you have established a tunnel or connection to your database. Leave this PuTTY window open and your connect string from Toad should now work and connect you to the new database.
Toad in the Cloud
From here, you can do a variety of things with the Oracle12 Pluggable Database. Toad is an excellent tool to use to create a pluggable database, move a pluggable database to the Oracle Cloud, and to manage your Oracle based application.
Toad Database Browser
You can now use the Toad Database Browser to view and manage the new cloud instance. This example shows the Toad Database Browser connected to a local Oracle12 container database.
The information I’ve been working with walks you through creating a clone PDB database and dropping it (but saving database files). You can then use Toad’s FTP abilities to move these database files to the Oracle Cloud.
Toad Database Browser connected to Oracle Cloud
Create from XML File
Once you get the files on the Cloud environment, connect to that instance using your new connect string in Toad and using the Database Browser again, create a PDB using ‘Create from XML Files”.
Create Pluggable Database Final Setup
Oracle will do a bunch of checks, even suggest and insert tnsnames entries, database browser setup, and even start up the new PDB.
There isn’t any reason you couldn’t just connect to the cloud instance now and do a ‘Create from Seed’.
Toad Data Pump Export
You can now use Toad’s Import/Export features to export an entire schema with data from your host database and move the export file (using Toad’s FTP again) to the Oracle Cloud connection and again using Toad to import the schema tables/indexes and data.
You are ready to roll with your application now.
Additional Information
John Dorlon has a series of articles on this topic in Toadworld that you might find helpful.
How to connect to an Oracle Cloud DB
How to transfer files to an Oracle Cloud DB Server
How to move a PDB to Oracle Cloud
How to move data into an Oracle Cloud DB using Data Pump
How to see your private DB from an Oracle Cloud DB
One-Step Schema Copy to the Oracle Cloud using Data Pump
Summary
This article is based on information I received from Toad Product Manager John Pocknell (heck of a nice guy by the way…). Most of this article was about setting up an Oracle Cloud database and establishing a connection to it. From there, you can use Toad to create, move, and maintain this application environment, much like you can use Toad to maintain your current in-house databases and application code.
As always, I hope you find this information useful when working with Toad and the Oracle Database.
Dan Hotka
Author/Instructor/Oracle Expert
Start the discussion at forums.toadworld.com