It’s almost that time again as another exciting new release of Toad draws closer !
 

We’ve decided that, rather than let you wait until Toad 9.7 is released before you see what’s new, we’ll give you a little preview now !
 

The main objectives of this release are to provide improved knowledge and education on the wealth of Toad features for users, so that you can really see Toad’s full value and realise you just can’t live without it. Additionally, we further strengthen support for Oracle database administration, and offer really cool new features for developers and DBAs, including data generation and workflow automation.
 

By the way, if you are still on a Toad for Oracle version prior to 9.6, then you may be missing out on valuable functionality. In Toad for Oracle 9.6, we moved the debugger and both Knowledge Xpert tools into the Base (Standard) Edition. So now is a really good time to upgrade if you haven’t already done so.
 

In this first blog posting (there will be two parts), I cover the new features that will most appeal to Oracle database administrators.
 

We’ve made some enhancements to the Database Browser so that it becomes your essential database administration console. Not only can you simultaneously navigate to every database you manage and drill-down into any one of them to administer database and schema objects, but you can now right-click on a database node and perform a variety of additional operations. These include Top Session Browser, Session Browser, Health Check, AWR Browser and many more, including a brand new one...Generate RMAN Scripts.

After speaking to many DBAs and asking them about how they use RMAN, we saw a clear need to provide some essential backup script templates to simplify the task of creating RMAN scripts, particularly for less experienced DBAs.
 
There are a number of RMAN templates available in Toad for Oracle 9.7, from which you can start building your library. You can add to, edit and reference these scripts from a library accessible through the “Toad Options” window. Toad Variable names, such as current database and current user, etc. can be built-in and will be resolved when the scripts run.

Once an RMAN script is selected for execution, it executes on the server and closes itself once completed.
 
Also note that the Toad Editor now supports RMAN language. See screenshot above.

A log file is also generated, which serves as a record of the exact backup.
 

DBAs love the StatsPack and AWR Browsers from previous Toad releases, but thought that it would add tremendous value if we offered some basic advice, once a potential issue has been identified.
 

In Toad for Oracle 9.7, we’ve added advisories, which are based on information collected between two adjacent AWR snapshots.  Note, to use this feature you must be licensed for OEM Diagnostics Pack (AWR).
  

The advisories are surfaced in two places:

Toad has always provided the means to view Oracle trace files (which can be generated using Toad’s Session Browser) using the TKProf Interface screen (Database menu | Diagnose), but once opened, you see the trace file contents displayed in “raw” format making it difficult to read and interpret.
 
Now with the new Trace File Browser, interpreting trace files is a breeze! In Toad for Oracle 9.7, the Trace File Browser is located immediately under the TKProf Interface menu item. Simply select and open your trace file (either directly from Windows or via FTP if on a UNIX server) and you will see the trace file information presented in a MUCH easier format with tabs showing statement details, wait summary and query summary, together with the execution plan, executions, fetches, waits and much more.

 

You can even send your reviewed trace file for workload replay testing using Quest’s Benchmark Factory for Databases (part of the Toad Development Suite and the Toad DBA Suite). I will discuss this more later in this blog posting.
 

Perhaps one of the most popular Toad features for developers and DBAs is Schema Compare and Sync. I’ve included it in the DBA-focused blog because the “Sync” functionality is included in the DB Admin Module add-on (formerly DBA Module). Whereas, Schema Compare is available in every Toad for Oracle Edition.
 

Before I elaborate, there are two ways to perform a Schema Compare using Toad:

The first method uses “brute force” to obtain the DDL information pertaining to every object in each compared schema.
 

The second method uses what are called Schema Definition (or schema snapshot) files. These files are encrypted, compressed files which contain all the information about every object in a schema at a particular point-in-time. The real beauty of using these files is that, not only are they an accurate record of what a schema looks like at a particular point-in-time, but when used to perform a schema compare using Toad, they take a fraction of the amount of time to process. What would take minutes to process using a data dictionary compare only takes seconds using this method.
 

Schema Definition files have been available in Toad for many years, but in this release, each Schema Definition file created is unique – with the file name, including a date/time stamp, so you can see exactly when it was created.
 

Since Schema Compare is bi-directional, it’s absolutely possible to undo (rollback) a previously applied change. This means that Toad for Oracle provides some capabilities of a basic change management tool. I must point out however, before you get too carried away, that it’s advisable to only use this feature to synchronize changes between schemas in a NON-production environment!
 
The Schema Compare window also looks different in this release. It’s now much easier to view all of the target schemas (live connection and schema definition files) against those with which you want to compare – in a single window. The synchronization script (see below figure) can be used to sync the entire schema, or even a single object.

 

Furthermore, this entire operation can be replayed over again using our new automation console: Toad AppDesigner, which will be discussed in a subsequent section of this blog. 
 

To clarify, with all Toad for Oracle Editions, you can perform a standard Schema Compare using the Oracle Data Dictionary. With the DB Admin Module add-on, you have the ability to use Schema Definition files (for performance and accuracy) and perform synchronizations. 
 

We’ve enhanced the database health check and security reporting tool for greater usability. The Report Output tab is now easier to navigate. By collapsing the main headers, you can view as much detail as you wish. There is also a new tab called “Diffs Since Last Run” which enables DBAs to quickly see if something has changed since the Health Check was last run – it will be flagged.
 
This is much more useful than the full report because there is so much information. Spotting a specific change – such as a database security breach – is now easier. As you can see in the figure below, two users have been granted DBA privileges as well as “Unlimited Tablespace” since the report was last run. This may have been overlooked within the main body of a full report.

Above, I mentioned the possibility of performing a workload replay test on an Oracle trace file. This is a popular method by which testers and DBAs can anticipate the impact that user load has on their production databases by simulating realistic load levels in a test environment.
 

Using Quest’s Benchmark Factory for Oracle – either standalone or with the Toad Development Suite and the Toad DBA Suite, an Oracle trace file can now be identified, interpreted and sent to Benchmark Factory for workload replay testing.
 

This integration is implemented in two places: (Database menu | Diagnose)

Once the workload replay script is generated, you have the option of immediately launching Benchmark Factory to run the test. The script can be opened directly from Benchmark Factory later if you wish.
 
Once Benchmark Factory launches, the workload replay script can simply be executed (which replays the trace file transactions with one connected user), or it can be copied to a scalability test – where additional “virtual users” can be added to simulate multiple database connections. Or, you can use a goal-based test – where you can specify a maximum TPS (transactions per second) or maximum user load based on a pre-defined response time.

More blogs and Technical Briefs on Benchmark Factory can be found here in Toad World.
 

If you have tried Toad’s Action Recall (highlighted in the last blog on Toad 9.6), then you’ve already experienced productivity gains as a result of automating the steps you take in Toad to perform specific, repeatable tasks – either as a DBA or a developer.
 

In order to fully capitalize on this concept and create automated workflows, the Action Palette (which is now called AppDesigner) is now a full window to enable you to better visualize workflows, enable more tasks to be automated and combine different tasks using conditional logic. 
 

As the name implies, AppDesigner enables Toad users to easily create mini-Toad applications based on tasks that would normally require multiple, repeatable steps in Toad. Toad AppDesigner can be used by any type of Toad user, but I’ve detailed a DBA specific use case below just to give you an idea.
 

Let’s imagine, as a DBA, you have to regularly run a database health check and generate an HTML schema script on all your databases, say, once-a-month. Even using Toad, this can become quite laborious! So imagine that, all you could simply select a Toad App (macro) which not only performs this procedure, but also drops you an e-mail to let you know it’s done.
 

OK, check this out!
First, open the Toad AppDesigner window (main toolbar )

In the left panel, you’ll notice an area, called Apps, where completed Toad Apps can be viewed and run. There is also an Execution Log area to keep a record of all Toad App executions and a Scheduled Items area to specifically itemize all those Toad Apps you have scheduled.
 

In the right panel, a series of tabs which lets you select App “components” which can be placed sequentially to form a continuous series of operations. These include:

I’ve used conditional logic to first TNS Ping a database instance (I could have compiled a list of database instances from which to read) and perform the steps as listed if the database is up. I send an e-mail if the server is down. If not, complete the steps and send an e-mail once complete.
 

The properties of each step can be defined by right-clicking an item in the list and selecting Properties or they can be defined from the corresponding window in Toad (e.g. DB Health Check).
 

To define the step’s properties from the task window, open the Actionable Window, complete all the necessary information, then press the  in the lower left corner of the window to store as an Action (or Window Snapshot).
 
To define the step’s properties from within the Toad AppDesigner window, right-click the step and select “Properties.” For example, here are my properties for the DB Health Check and e-mail steps:

Once you have all of the steps complete (you can change the running order if it’s wrong), you can test your Toad App to make sure all the steps run correctly using the Run Selected App button  on the main Toad AppDesigner toolbar.
 

After confirming that it all works, you can even schedule the Toad App to run at a time and frequency of your choosing, using the Toad App Schedule button .  This uses the Windows Task Scheduler to launch Toad on your desktop and run your chosen Toad App.
 

Toad Apps can be shared with others very simply, and the recipient can import a Toad App into their Toad AppDesigner window and use or modify it. This is done using the Import from file and Export to file buttons.
 

If you get the chance to try out Toad AppDesigner, please be sure to let us know what ideas or experiences you have. We hope it will be tremendous fun and help save you hours of work.
 

For a comprehensive list of new features and enhancements in Toad 9.7, please refer to the Release Notes.