Toad's DB Health Check - Is There a Doctor in the House?

Michael Lumbard — Mon, 05 Oct 2009 14:42:00 GMT

As DBAs it seems that we are always faced with issues of Database Performance. As gate keepers to our multiple database environments many of our Service Level Agreements (SLA's) can be directly tied into the health of our databases. Working with Quest's Toad solution we can now take a comprehensive look at the health of our databases with the DB Health Check. Note: This Toad feature is only available in the commercial version of Toad with the optional DB Admin Module.

The Database Health Check performs a series of checks on a selected database and displays the results. The Health Check includes a "Vulnerability Assessment" to help find places where your database could be vulnerable to hackers and a differences report to see what has changed since Health Check was last run. You can save the results to a color-coded RTF or HTML file, or to a plain text file. You can also send the results through email in any of these formats.

To run a health check

From the Database menu, select Diagnose - DB Health Check.

In the left pane, select the databases you want to check. You can multi-select using the CTRL or Shift keys.

In the Checks and Options tab on the right side of the window, select the pre-defined health checks you want to run. (See Health Check - Check and Options for more details about each of these.)

Select from over 91 check points to verify the health of your database:

Toad’s DB Health Check also includes the Vulnerability Assessment Check List:

The "Vulnerability Assessment" helps to locate places where your database could be vulnerable to hackers.

Below are examples of the rules that are being applied within the DB Health Check.

List Hidden Users
List Nested Roles
List Roles not granted to a Role or User
List Grants on V$Views granted directly to users with admin option
List of Grants on Sys Tables granted directly to the users
Check for DBA, Resource, Connect Roles
List of DB Links which are Inaccessible
List of Users with non-existent Default Tablespaces
Verify Sys.Aud$ is not in the System Tablespace
List Powerful Roles granted directly to the Users
List Powerful System Privileges granted directly to the Users
List Tables with Long or Long Raw Data Types
List Tables with no Primary Key
List Table with no Unique key or Index

The Schemas tab on the Database Health Check window provides you with a location to select the schemas you want to examine:

Report Output presents the results in an interactive tree as shown here:

The health check results can drilled into for further detailed information:

The 'Diffs Since Last Run' tab can display the differences between successive DB Health Checks:

Results can be sent via email. The email sent will include the entire Health Check Report. You can send the email as either HTML or plain text. To have email sent when you run a health check you must first set up the email pages. From the Database Health Check window, click the Checks and Options tab. You must also have the global Email Options set up with your SMTP server, and recipient addresses. You can also schedule the execution of your DB Health Check to run from the command line or via the Oracle scheduler.

Note: Health checks require access to the DBA views and to some of the V$ views. A list of V$ views required is located in the V$ Tables Required topic in Toad's help system.

Toad's Export File Browser - Developers and DBAs Secret Weapon

Michael Lumbard — Mon, 13 Jul 2009 13:54:00 GMT

Have you ever had a need to recover data? Have you ever looked at data or maybe scripts and thought something is not correct? When working with databases there are times when we may have a loss of data or files. Has this ever happened and there is no backup information to rely on?

During my time as both a Developer and DBA I have worked with numerous Oracle export file (dmp files). Often I could be looking at 15 to 20 or even more of these files. I would go through a very manual process to confirm which file is the exact one needed for my particular problem. Often I talk with users who have this specific type of problem or need. If you are like me, there have been many occurrences when I wished I had a quick and easy way to recover information. I will get asked “wouldn’t it be great if Toad had this functionality?”.

Well – guess what? For many of us working with Toad we have had the solution all along. Let me introduce you to Toad’s Export File Browser.

QUEST SOLUTION - INTRODUCING TOAD’S EXPORT FILE BROWSER

Toad’s Export File Browser is a great solution in being able to easily browse through export files and view file information contained within these files. Toad’s Export File Browser will assist users in quickly being able to confirm the exact information they need to recover a number of database critical needs.

When working with databases there are times when we may have a loss of data, files, and so on. Recovery of this data can be very time consuming. If we take this a step further - let’s say we are working on a project and coming up to quarter or year end processes. I think you can all understand that in this situation time is critical.

When working with Toad’s Export File Browsers users can look at some critical database items that could include: Schemas, DDL, Data, Code, Functions, Object Type Bodies, Packages, Package bodies, Procedures, and Triggers

To access Toad’s Export File Browser screen, select DATABASE | EXPORT | EXPORT FILE BROWSER. This screen will allow you to work with your export files and assist in recovering mission critical information.

Figure 1: TOAD’S EXPORT FILE BROWSER MAIN SCREEN

SELECTING AN EXISTING EXPORT FILE

Once we have accessed the main screen we need to be able to view our export files. To do this, click on the SAVE FOLDER icon at the top left hand side of the screen. Now we will be able to view all of the export files that have been created. This screen allows the user to confirm information such as File Name, Version, Export Mode, File Size, File Date, and Export User.

Figure 2: TOAD’S EXPORT FILE BROWSER – SELECTING A FILE SCREEN

DATA GRID OPTIONS

After selecting the SAVE FOLDER icon we will be able to view existing export files. As we select one of our saved export files we will be able to view the details of this file. In reviewing this information we will see a global view of the export file and the familiar Toad option of being able to drill down on the needed information on the right side of the screen. What is great is the user can view both the DDL on the right and also the DATA for the selected export file (see below). At this point if we right click on the data grid there are a number of options such as: View Cell Data, Filter Data, Go to Row #, Get Row Count, Size Columns, Save As, and Print Grid

Figure 3: TOAD’S EXPORT FILE BROWSER – DATA GRID OPTIONS SCREEN

DATA GRID SAVE OPTION

Right click and choose the Save option. This shows a number of ways that data can be saved. Some of these ways including: Delimited Text, Excel, HTML, Insert Statement, and SQL Loader.

For our discussion I have chosen the OW_CUSTOMERS table (left side of screen) and selected the SAVE AS Option of INSERT STATEMENT (right side of screen). Also there are a couple of other save options to address – Output and Table Options.

Output Options:

Do we want our data information to be saved to a FILE or to a CLIPBOARD? For our example we have chosen a clipboard. Remember when working with Toad the clipboard selection can refer to the TOAD EDITOR screen (cut and paste).

Table Options:

Here, you also have some additional options to Include Null Text, SQL Statements, Schema Name, and Lower Case Column Names. For our example I have selected Include Schema Name. We are asked to either give our insert script a schema name or leave blank. As you can see I have left this information blank. I want to create a generic script so that I could use this script across different schemas if needed. This can be very valuable when working with Test or Development environments.

Finally we need to give our insert script the name of the table where we wish to insert the data. If you noticed I gave a table name of OW_QUEST. If you remember we are using the data from a table called OW_CUSTOMERS. The reason for this is I want to copy my OW_CUSTOMERS data into a new temporary table called OW_QUEST,- click ok (see Figure 4).

DATA GRID SAVE OPTION

Figure 4: TOAD’S EXPORT FILE BROWSER - DATA GRID SAVE OPTION | INSERT STATEMENT

We can view the generated insert statement from our Export File Using the Export File Browser (Figure 5).

EXPORT FILE BROWSER DATA INSERT STATEMENT

Figure 5: TOAD’S EDITOR SCREEN – EXPORT FILE BROWSER DATA INSERT STATEMENT

Now we have the option to execute the newly created script or save the script and execute as needed.

DATABASE COMPARE MODE

A great option in TOAD’S Export File Browser is the option to use the DB Compare feature. Here we can compare an export file with objects in the database. This is a cursory compare and will not indicate deep, data level difference.

For any selected nodes and databases, check boxes will indicate if the object is in the file, the database, or both.

To compare a export file to a database:

Click the DB Compare Icon (left side of screen).
On the right hand side of the screen, select a connection from the connection drop down to compare to the file.
Now on the left side, select one or more nodes to compare to the selected database.

As a DBA you can use this compare feature to give you a quick and accurate solution to view your Export files compared to your database as opposed to reviewing your numerous export logs.

Figure 6: TOAD’S EXPORT FILE BROWSER – DB COMPARE MODE SCREEN

SUMMARY

When there is a need to quickly recover database information, Toad’s Export File Browser is an efficient way to meet this challenge. It allows the users to select from any number of existing Export Files and provides a number of key features to assist with the recovering of the exact data information needed.

Michael Lumbard's blog

Toad's DB Health Check - Is There a Doctor in the House?

Toad's Export File Browser - Developers and DBAs Secret Weapon