Toad for Oracle on cloud

1-click AWS Deployment 1-click Azure Deployment

Overview

Toad solutions are desktop tools that give DBAs, developers and analysts a proactive, automated approach to developing and managing databases, so organizations can spend more time on strategic initiatives and less time on mundane, repetitive tasks. Quest offers several Toad editions, which support databases such as Oracle, SQL Server, DB2, MySQL, SAP Solutions, and more, as well as offerings built specifically for database developers, DBAs and business analysts. Used by millions, Toad for Oracle is the flagship brand in the Toad portfolio, and the leading database development and optimization toolset on the market. Toad Data Point is an analyst toolset, which connects to nearly any data source and is purpose-built for an organization’s data provisioning and reporting requirements. Toad solutions help organizations maximize their investments in data and database technologies by empowering database professionals to automate processes, minimize risks and cut project timelines in half.

Whether your Oracle database is on premises or in the cloud, Toad® for Oracle is the only solution of its kind that will simplify your workflow, help reduce code defects and improve code quality and performance while supporting team collaboration. 

Automation Designer. Automate complex and repetitive tasks like schema compare, script execution, … 2 Code Review. Ensure consistent code quality standards to reduce problems in production … 3 Code Tester. Reduce production code defects with automated unit test creation and reporting. 4 Collaboration. Share standards, best practices, code, 

OverView :

You’re under pressure to take on more database responsibilities and work faster than ever before. This, combined with declining resources, puts your critical databases and cloud native applications at risk. But what if you could use a single toolset to speed Oracle SQL development and database administration while improving performance? 

With Toad® for Oracle, you’ll reduce costs and risks by easily ensuring the health and performance of your Oracle Cloud Platform and on-premises databases. Toad is the only developer tool of its kind that will simplify your workflow, help reduce code defects and improve code quality and performance while supporting team collaboration. On the operations side, you can automate administration tasks and proactively manage your databases while embracing performance optimization and risk mitigation. You can also use Toad for Oracle – Sensitive Data Protection to define, search for and protect sensitive data across all your Oracle databases quickly and easily. 

Using Oracle Database Exadata Express Cloud Service with Toad for Oracle:

Oracle Database Exadata Express Cloud Service is a managed service for Oracle Database 12c R2 Enterprise Edition running on Oracle Exadata. Exadata Express is a lightweight version of the full-featured Oracle Exadata, suitable for small- to medium-scale data. Exadata Express provides functionality for application development, data analytics, and database optimization. Exadata Express opens a pluggable database by default with the provision to create multiple schemas. Some of the salient application development features are JSON document storage, schema-less documents storage, and access to the Exadata Express service with Simple Oracle Document Access API, Oracle REST Data Services (ORDS), Oracle Application Express 5 and 5.1, and SQL Developer 4. In three articles we shall learn to use Oracle Database Exadata Express Cloud Service with Toad for Oracle. The three articles have the following sections.

Setting the Environment

Creating an Exadata Instance

Setting Admin Password

Enabling Client Access

Creating a Schema

Downloading Client Credentials

Setting Permissions on Credentials Files

Creating a SQL Plus Connection

Creating a Connection

Creating Tables

Comparing Tables

Comparing Schemas

Finding Data Duplicates

Using Schema Browser

Exporting dataset to HTML

Running a Query

Finding Data

Exporting Result set to Excel

Creating a Report

Modifying a Report

Deleting Exadata Instance

Setting the Environment

Download and install Toad for Oracle. Create an Oracle Cloud account. Download Oracle Instant Client version 12.2.0.1.0 Basic Package zip file instantclient-basic-windows.x64-12.2.0.1.0.zip and SQL*Plus Package zip file instantclient-sqlplus-windows.x64-12.2.0.1.0.zip from http://www.oracle.com/technetwork/topics/winx64soft-089540.html. Create a directory C:instantclient_12_2 and extract the contents of the zip files to the directory. The subdirectories are not to be kept when the zip files are extracted. Only the files and the vc14 directory are to be extracted to C:instantclient_12_2 such that all the extracted files and the vc14 directory are at the top level within the C:instantclient_12_2 directory, as shown in Figure 1. The sqlplus.exe application and the other SQL*Plus files are also directly in the C:instantclient_12_2 directory.

Figure 1. Directory Structure

We also need to modify the PATH environment variable to add the C:instantclient_12_2 directory path.

Creating an Exadata Instance

Login to the Oracle Cloud Dashboard with the URL of the format https://myservices.us.oraclecloud.com/mycloud/<user>/faces/dashboard.jspx that is sent to a user when a new account is created. Click on Create Instance as shown in Figure 2.

Figure 2. Create Instance

In the Create Instance dialog click on Create for the Exadata Express service as shown in Figure 3.

Figure 3. Exadata Express>Create

In the Instance Details page specify an Instance Name (oraexadata), select the default Plan Exadata Express Cloud Service, and select a Database Shape as shown in Figure 4. The other fields to be filled are Email, User Name, First Name and Last Name.

Figure 4. Instance Details

Click on Create to create an instance as shown in Figure 5.

Figure 5. Instance Details>Create

In Confirmation click on Create as shown in Figure 6.

Figure 6. Confirmation>Create

The oraexadata instance starts to get created, as indicated by the message in Figure 7.

Figure 7. Instance oraexadata starts to get created

An Exadata Express service instance gets created and listed on the Dashboard, as shown in Figure 8.

Figure 8. Exadata Express Instance in Dashboard

The Service Instances section on the instance Overview page lists the detail about the oraexadata instance. Click on the Oracle Service Console link as shown in Figure 9 to open the service console.

Figure 9. Oracle Service Console link

The Oracle Service Console page gets displayed, as shown in Figure 10.

Figure 10. Oracle Service Console

Setting Admin Password

The default admin user for the Exadata Express is PDB_ADMIN. To set the password for the admin user click on Manage in the Service Console as shown in Figure 11.

Figure 11. Manage

In Manage click on the Admin Password link as shown in Figure 12.

Figure 12. Admin Password

In the Admin Password dialog specify a New Password and a Confirm Password and click on Set Password as shown in Figure 13.

Figure 13. Admin Password

Success dialog indicates that the password has been set as shown in Figure 14. Click on Close.

Figure 14. PDB_ADMIN Password Set

Enabling Client Access

By default client access to the database is not enabled. To enable access click on Client Access as shown in Figure 15.

Figure 15. Client Access

In the Client Access dialog click on Enable Client Access as shown in Figure 16.

Figure 16. Client Access>Enable Client Access

The client access gets enabled as indicated by the Enabled label in Figure 17.

Figure 17. Client Access Enabled

Creating a Schema

One of the benefits of using Exadata Express is the provision to create multiple schemas. To create a schema click on Database Schema in Manage as shown in Figure 18.

Figure 18. Manage>Database Schema

In Create Database Schema specify Schema name (ORCL) and specify Password and Confirm Password for the schema. Click on Create Database Schema as shown in Figure 19.

Figure 19. Create Database Schema

The database schema ORCL gets created as indicated in the Success message dialog shown in Figure 20. Click on Close.

Figure 20. Schema ORCL created

Downloading Client Credentials

To download client credentials click on Client Credentials as shown in Figure 21.

Figure 21. Client Credentials

In Client Credentials specify Password and Confirm password and click on Download as shown in Figure 22.

Figure 22. Client Credentials>Download

A client_credentials.zip file gets downloaded. Create a directory structure C:instantclient_12_2networkadmin and extract the files in the client_credentials.zip to the C:instantclient_12_2networkadmin directory as shown in Figure 23.

Figure 23. Credentials Files

The credentials files include the tnsnames.ora and sqlnet.ora configuration files. None of the files is to be modified. The tnsnames.ora file should not even be opened and reformatted, as the file is generated in a specific syntax which, if modified, makes the file unusable. Add an environment variable TNS_ADMIN with value as directory path C:instantclient_12_2networkadmin.

Setting Permissions on Credentials Files

We need to modify permissions on the credentials files to make the files accessible for the OS user that is logged into the local machine. The tnsnames.ora and sqlnet.ora files’ permissions are not to be set. As an example, to modify the permissions of the ewallet file right-click on the file and select Properties. In the ewallet Properties dialog select the Security tab and select the Users group and click on Edit as shown in Figure 24.

Figure 24. ewallet Properties>Security>Edit

In the Permissions for ewallet select Allow in Permissions for Users and click on Apply as shown in Figure 25.

Figure 25. Setting Permissions for ewallet

Creating a SQL* Plus Connection

The tnsnames.ora file consists of a net service entry dbaccess for the Oracle Database running on Exadata Express. Run the following command to connect to Oracle Database using SQL*Plus. If the C:instantclient_12_2 directory has been added to PATH, the command may be run from any directory; but if the directory has not been added to PATH, change directory (cd) to the C:instantclient_12_2 directory to run the command.

sqlplus PDB_ADMIN@dbaccess

Specify a password for PDB_ADMIN user at the command prompt. A connection gets established with Oracle Database 12c.

C:instantclient_12_2>sqlplus PDB_ADMIN@dbaccess

SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 1 19:19:05 2018

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

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

The pluggable database is already running, which may be verified with the startup command.

SQL> startup

ORA-65019: pluggable database YHTPGDZTLFV already open

SQL>

The output from the preceding SQL*Plus commands is shown in Figure 26.

Figure 26. SQL*Plus Commands output

Toad for Oracle is unrivaled for ensuring the greatest possible productivity in development and administration of Oracle databases.Save time and reduce risks with the leading database development and optimization software, as ranked by IDC.

Toad for Oracle on cloud for AWS

 

Features

Major Features of Toad for Oracle:

Simplified database development
Design, build, edit, debug and manage projects with Toad Data Modeler, and built-in Database Browser, SQL Editor, Debugger and Project Manager.

Code quality assurance
Collaborate with team members and standardize code formatting while preserving code integrity and preventing overwrites with included Team Coding and version control.

Automated code testing
Automate the creation and execution of functional code tests to ensure application quality and enable faster delivery of quality code with Code Tester.

Automated code analysis
Automate code reviews using industry best practices and predefined and/or customizable rulesets in categories such as readability, correctness, program structure, naming conventions and maintainability, with Code Analysis.

Automated performance optimization
Pinpoint database bottlenecks with Spotlight database diagnostics and resolve issues quickly or validate SQL, PL/SQL and indexes to ensure high performance through the only automated SQL tuning toolset, SQL Optimizer.

13 new features in Toad® for Oracle 13.2:

1.  New unit testing built on utPLSQL

A GUI front-end for the popular open source unit testing suite, utPLSQL. Create and run unit tests, and review test results.

Toad for Oracle 13.2 screen shot of GUI ront-end for the popular open source unit testing suite, utPLSQL.

 

2. Sensitive Data Protection

You can now define default audit, encryption, and redaction policy options, as well as custom user-defined severity categories, to individual sensitive data rules. Once defined, those policies can then be applied to a group of columns by selecting the columns in question and selecting “Apply Default Policies”. This allows you to set up policies on multiple columns at once rather than individually.

Main Menu: View -> Toad Options -> Sensitive Data

Screen shot showing edit tool for the sensitive data rule.

 

Main Menu: Database -> Report -> Sensitive Data Protection Search

 Screen shot showing how to apply default policies to columns identified by Sensitive Data Protection search.

 

3. Updated Create/Alter DB Object windows

These windows have an updated look. Here’s a screen shot of the new Alter Table window (there are about 40 others!)

Main Menu: Database -> Create -> Table

 Screen shot of the new Alter Table window in Toad for Oracle 13.2.

 

4. Updated Analyze Objects window

Analyze Objects has a new, easy-to-use workflow, and is now supported in the Automation Designer.

Main Menu: Database -> Optimize -> Analyze Objects

 Screen shot of Analyze Objects new, easy-to-use workflow that is now supported in the Automation Designer in Toad for Oracle 13.2.

 

5. Export/Import connections

  • Now includes password (protected by master password)

6. Support/Compliance

  • Windows 10 support
  • Oracle 19c support
  • FIPS compliance

7. XMLType

  • It is no longer necessary to manually include .GetCLOBVal() in your queries with XMLTYPE. This now happens automatically behind the scenes.
  • XMLTYPE data is now editable in the popup editor (just include ROWID in your query).

8. Data Compare window

  • Supports BLOB, CLOB, NCLOB, XMLTYPE
  • A differences report can now be created (also in Compare Multiple Tables)

9. Export Dataset

  • Now Faster!
  • Enable/disable auto file name (based on table exporting from) by right-click in the “filename” box.
  • Can now specify “where” clause for tables being exported.

10. Team Coding

  • All VCS and Team Coding settings can now be backed up/restored to the server using new toolbar buttons on the Team Coding Configuration window.

11. Code Analysis

  • Several new and updated analysis rules.

12. Sensitive Data Protection

  • Added regular expression rules for common identification names and formats used throughout the European Union.
  • Added an “examples” box and “test” button to the rules window, so you can test rules as you are adding them.
  • Added a “severity” field to the rules window to allow for user-defined rule management and organization.

13. Automation Designer/Task Scheduler

  • New, easy migration of scheduled Toad actions from prior Toad version to 13.2.

 

AWS

Installation Instructions For Windows

A) Click the Windows “Start” button and select “All Programs” and then point to Toad for Oracle

B) RDP Connection: To connect to the operating system,

1) Connect to virtual machine using following RDP credentials :

  • Hostname: PublicDNS  / IP of machine
  • Port : 3389

Username: To connect to the operating system, use RDP and the username is Administrator.
Password : Please Click here  to know how to  get password .

C) Other Information:

1.Default installation path: will be on your root folder “C:\Program Files\Dell\Toad for Oracle 12.10 Freeware”
2.Default ports:

  • Windows Machines:  RDP Port – 3389
  • Http: 80
  • Https: 443

Configure custom inbound and outbound rules using this link

Installation Step by Step Screenshots

 

 

 

 

 

Videos

Toad for Oracle on cloud