Toad World Blog

Toad in the Cloud

May 29, 2018 4:45:41 PM by Dan Hotka

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

www.DanHotka.com

Dan@DanHotka.com

 

Tags: Toad for Oracle

Dan Hotka

Written by Dan Hotka

Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.

Dan's most recent book is Toad for Oracle Unleashed