Johannes Ahrends' Blog

Using Toad DBA Suite for Oracle with Oracle Enterprise Manager (Part 3)

Johannes Ahrends — Wed, 18 Mar 2009 12:09:00 GMT

By Johannes Ahrends and John Pocknell

In the previous blog, we compared and contrasted how database Performance Management operations are performed in Oracle’s Enterprise Manager (OEM) and Quest’s Toad DBA Suite for Oracle and how Toad is an excellent complement to OEM because it simplifies and automates many tasks which would take longer in OEM.

In the final blog, we contrast Change Management operations between OEM (including OEM Database Control for Oracle 10g and above) and Toad DBA Suite for Oracle.

Change Management

For this particular comparison with Toad DBA Suite, you will need in addition to OEM:

Diagnostics Pack – offers automatic performance diagnostics and monitoring.
http://www.oracle.com/technology/products/oem/pdf/diagnostic-pack-11g-datasheet.pdf
Tuning Pack – offers automatic SQL tuning for applications.
http://www.oracle.com/technology/products/oem/pdf/tuning-pack-11g-datasheet.pdf
Change Management Pack – compare schema changes before and after an application upgrade.
http://www.oracle.com/technology/products/oem/pdf/change-management-pack-11g-datasheet.pdf
Real Application Testing – workload capture/replay together with SQL performance analysis.
http://www.oracle.com/technology/products/database/oracle11g/pdf/real-application-testing-datasheet.pdf

Toad DBA Suite already includes a number of features specifically targeted towards change management including: configuration changes, schema changes and database workload replay testing.

Example 1

Let’s start with a simple example where you need to compare a development schema with a test schema to see if they are synchronized.

Synchronizing schemas is essential when performing testing or when a new iteration of changes needs to be carried out by development, to ensure nothing gets lost and code continues to work as it was intended.

In Toad DBA Suite:

Note: Toad DBA Suite does not require the use of the OEM Change Management pack

Compare Schemas (Select Database | Compare | Schemas from Toad menu) is a built in task which enables selections of source and (multiple) target schemas all in the same window.

There is an option to run the compare using the Oracle Data Dictionary or using “Def Files” (a Toad offline snapshot). Def files are encrypted and can be used as baselines since they will contain all the object definitions in the schema and will prevent unauthorized access to the underlying objects and dramatically reduce the time taken to run future comparisons. Def files can also be used as restore points to get you back to a point-in-time if something goes wrong.

The Schema Compare window contains multiple tabs which include the setup, object specific options, results and synchronization script. Everything is done from this one window.

Compare Schemas showing a sync script for a selected object

Compare Schemas will even report on two tables which are structurally the same but have a different number or rows of data.

Upon completion of the compare, if it’s realized that the source and target were inadvertently transposed, it’s a simple matter of a right click in the Schemas tab to switch the source and target and update the synchronization script dynamically.

Sync script is dynamically reversible without re-processing

An alternative method DBAs can use to deploy changes from development to production is where changes are driven through data models.

Toad DBA Suite includes a data modeling component called Toad Data Modeler which can perform both conceptual and physical data modeling for most database platforms. One of its principal features is to compare two versions of the same physical data model – one with pending changes and the other a snapshot of production (after reverse engineering) and create an Alter script.

The DBA can review this script and apply the changes to production.

In OEM:

OEM’s Change Management Pack is designed to enable DBAs to compare databases (on the same or different servers). Performing a schema compare (especially if the schemas are on the same database), involves many steps.

You first need to create 2 baselines (called “Dictionary Baselines“) of each of the schemas to be compared on each database. This process uses a wizard and involves a number of steps which need to be repeated for each baseline created.

You then create a “Dictionary Comparison” and select each of the 2 “Dictionary Baselines” to compare. This process also uses a wizard to help simplify the steps.

After completion, a comparison screen shows you the detailed differences between the 2 schemas.

Change Management pack lets you synchronize a single object or the whole schema and changes can also be implemented to multiple targets.

An alternative to using OEM to perform this task would be to use SQL Developer. SQL Developer’s Database Diff feature still uses the OEM Change Management pack, but the steps are fewer and easier to follow; especially for a simple schema compare operation.

Example 1 Summary:

Toad DBA Suite

Simple to use workflow with only one window.
Visibility of each step of the process at all times through tabular interface.
Enables optional encryption of schema information.
Fast processing (Definition Files make this even faster).
Source and target schemas are switchable.
Schema compare only (Database Compare and Data Compare are separate functions).
No integral impact analysis.
No integral data copy option.
Synchronization possible for schema or selected object.

OEM

Aimed for a full database compare (no compare data feature).
More steps required to perform the same compare.
Requires the DBA to be in multiple screens.
Slower processing.
Synchronization possible for schema or selected object.
Generates an impact report before deployment.
Data can be copied from source to target.
Requires the Change Management Pack.

Example 2

For this example, we will look at something which is becoming of more interest to DBAs – Database Workload Replay testing.

In order to assess the impact on a production database of impending or predicted changes in the future such as increased activity or workload (application changes or more end-users), patch releases, database upgrades, hardware upgrades, etc, the DBA has to be sure that the database can accommodate these planned changes in order to maintain application response times and possibly service levels.

In Toad DBA Suite:

Toad DBA Suite uses a component called Benchmark Factory for Databases, a database load-testing/scalability tool which can be used on Oracle 7.3.4 to 11g. Benchmark Factory for Databases can be used in one of 3 ways:

Database Workload Replay – workload capture using a number of different methods including Oracle trace files and subsequent playback on a test database to replicate the execution of production transactions.
Database Server benchmarking - based on industry standards such as TPC-C, TPC-H, etc and is typically used to compare database platforms and plan for patch/database upgrades, hardware upgrades, etc.
Application Scalability testing – to help development predict likely performance in a production environment of PL/SQL code, SQL statements, scripts, etc.

For the purposes of this comparison, we will look at method 1 and the use of Oracle Trace files as the capture mechanism.

The generation of Oracle Trace files is initiated in Toad DBA Suite and can be specified to be generated in one of 4 ways:

a.      Basic

b.      Waits

c.       Binds

d.      Waits + Binds

Trace files can also be created using command line (SQL*Plus) which offers additional options.

Toad’s Trace File Browser can then be used to evaluate each trace file down to SQL statement, execution plan level, waits, etc.

From the Trace File Browser, it’s simply a matter of clicking the “send trace file to Benchmark Factory” button () and clicking “Yes” to open.

The Benchmark Factory Workload Replay script is ready to run. This will simply replay the transactions recorded in the trace file for a single user. For scalability purposes, we can use Benchmark Factory to specify a number of “Virtual Users” all of which will make their own separate connection to the Test database and execute the transactions in the trace file. This can be done by clicking on “Oracle Workload Replay” in the navigator and entering the required number of users and the number of executions of each transaction.

Toad DBA Suite’s Workload Replay screen

In order to determine thresholds (for SLA purposes for example), you can copy the transactions in the Workload Replay to an SLA (Goal) or Mixed Workload replay test (see right-click menu above). With these options, you can specify a required SLA such as SQL response time or (in the case of a Mix Test) you can use multiple trace files:

Workload Replay of a group of Oracle Trace files

In an SLA test, workload replay will continue until the SLA requirement is “broken” (e.g. SQL response time is too low) whereupon the test will automatically stop and report what userload was achieved.

Toad DBA Suite’s Benchmark Factory component can also import Oracle trace files either as multiple session specific files or directly from the server and there are many filtering options available.

In OEM:

Oracle recently introduced a new Manageability option with their Database 11g called Real Application Testing (RAT) and is available only with Oracle Enterprise Edition. RAT can be used to capture, analyze and replay database transactions. During replay, RAT can maintain the original transaction concurrency, timing and dependencies.

RAT is quite complex, so for the purposes of simplicity, I will just give you an overview of how it works.

RAT consists of 2 main components:

Database Replay – captures production workload and replays it on a different system (e.g. Test)
SQL Performance Analyzer – identifies SQL execution plan changes and performance regression (see Example 3)

Database Replay in RAT:

There are 4 main steps in RAT:

Workload capture (from the production system)
1. Capture is available from Oracle version 9i and up (according to Oracle’s website)
2. Variety of workload filters inc User, Program, Module, Session ID, time period, etc
Workload pre-processing
1. Transforms the captured data into a replayable format
Workload replay
1. Any timing and concurrency characteristics are preserved
2. Transaction commit order is maintained
3. Maintains the same number of user connections
Analysis and reporting
1. Analysis can be performed via AWR, ASH and ADDM
2. AWR diff report can compare original performance to replayed performance

RAT Workload Replay results screen

Example 2 Summary:

Toad DBA Suite

The use of Oracle Trace files is very easy with options on how they are created and a browser (Trace File Browser) to see exactly what was captured. Transactional latencies are faithfully reproduced and dependencies are reasonably well reproduced by Oracle, but extensive use of bind variables may require some interaction.
Works with any version of Oracle from 7.3.4. and upwards.
Workflow is logical with the workload replay being initiated straight from Toad’s Trace File Browser.
Everything is executed from the client including monitoring of the test environment using the Spotlight on Oracle component.
Workload replay is modifiable in the sense that the userload can be modified, number of SQL executions can be changed and also transactional latencies can be altered.
Workload replay can be defined as a goal-based test or a mixed-user test – where a bias (or weighting) can be applied to one particular session to amplify the stress.
SQL statements can be optimized on-the-fly, or after the workload testing by using SQL Optimizer’s SQL Inspector (part of the SQL Scanner component) to capture the workload SQL from the SGA.
Toad DBA Suite’s Benchmark Factory component can also be used to perform industry standard tests or ad-hoc scalability testing.

OEM

The use of RAT is theoretically more accurate since it captures all client requests directed to the database. Values such as SCN, wall-clock time, binds are faithfully reproduced and the original transaction concurrency, timing and dependencies retained.
Cannot artificially scale the userload beyond that which is captured, therefore there is no opportunity to perform a “what-if” analysis.
RAT cannot be used for SLA testing.
RAT requires Oracle 11g, but will work with captured sessions on Oracle 9i and higher.
RAT executes on the server, so loading is higher although Oracle optimizes this to minimize impact.
Analysis can be performed via AWR, ASH and ADDM. An AWR diff report can compare original performance to replayed performance.
There is no opportunity to see if specific performance SLAs can be met.
OEM does not provide any way of using of industry standard tests such as TPC-C or TPC-H for standard benchmarking or for ad-hoc scalability testing.

Example 3

In Example 2, we used production transactions and replayed them in test environment to simulate what might happen when making a planned change.

In this final example, I would like to show how it is possible to assess the impact of a planned change on the database and also determine whether performance regression has taken place after the change has been made. Performance regression may well result after planned changes such as a different indexing strategy, database parameter changes, Oracle patch or a database upgrade.

Impact analysis and performance regression testing through comparison of “before” and “after” is vital to understanding these effects before they are implemented into production.

In Toad DBA Suite:

Quest has two approaches to enabling the DBA to assess what happens when a change is planned:

Static - by comparing the execution plans for groups of SQL statements before and after the change on the database without actually executing them. In addition, with index changes, performance differences can be reported “before the fact” without actually building any “real” indexes.
Dynamic – SQL transactions (workload) are played in a test environment in order to assess whether performance regresses.

In Toad DBA Suite’s SQL Optimizer component, the Impact Analyzer enables you to:

Compare execution plan changes to identify potential performance variations. For scenarios such as:
1. Migrations }
2. Upgrades } by comparing 2 instances
3. Deployments }
4. Index changes
5. Simulated configuration change via ALTER SESSION
Stores SQL statements and execution plans in a (local) SQL repository for impact analysis.

The first thing you must do is decide which SQL statements you want to assess and then add them to the SQL Repository. You then create an Analysis Group into which these SQL statements will be associated. A simple to use wizard takes you through these steps.

This initial process then creates a baseline snapshot against which execution plan changes can be compared.

So let’s imagine that we want to look at the effects of an index change on these SQL statements.

SQL Optimizer’s Impact Analyzer

Here you can see my Analyzer Group with six SQL statements which are being sent for index analysis using another SQL Optimizer component called Global Indexing. Global Indexing analyses all the SQL statements and offers suggested indexes which may collectively improve the execution of all these SQL statements. Since SQL Optimizer uses virtual indexes, simply for the purposes of extracting an execution plan for comparison, nothing is built on the database.

In order to perform the impact analysis, you select a proposed index and push it to the Impact Analyzer. Impact Analyzer asks you to confirm the Analysis Group (created earlier)

Impact Analyzer then creates a second snapshot based on the virtual index I just created and copies the executions plans for the SQL statements into the SQL Repository.

Adding a 2^nd snapshot (based on a proposed index) into the SQL Repository for execution plan comparison

Now, using the stored SQL statements and their corresponding execution plans (with and without an index) you can compare the baseline snapshot with the Global Indexing snapshot in order to assess the impact. All of this is done without building the index or executing any SQL!

SQL Optimizer’s Impact Analyzer comparing the execution plans for a proposed new index against a set of baseline execution plans stored in the SQL Repository

This same process can be used for other scenarios too. For example, SQL Optimizer’s SQL Inspector component allows the DBA to set up a scan of the Oracle SGA and capture SQL statements from there. (This can be scheduled). SQL statements captured using the SQL Optimizer’s SQL Scanner component (including scripts, PL/SQL code, etc) can also be saved into the SQL Repository.

For regression testing, Toad DBA Suite’s Benchmark Factory for Oracle component can generate reports showing transaction performance versus userload to show whether performance regressed compared to before.

Benchmark Factory’s Results screen showing Transaction Time vs User Load

In addition, the Spotlight on Oracle component can be used to determine, in real-time, where performance bottlenecks, as a result of performance regression, may be occurring.

In OEM:

Oracle’s approach is slightly different since it cannot perform a static impact analysis. SQL statements have to be executed first in order to have something which can be compared.

Real Application Testing (RAT) contains a component called SQL Performance Analyzer, which uses a similar approach to Toad DBA Suite and enables you to perform impact analysis of changes such as schema, database configuration, patches, etc with the major difference that, for index analysis, you would have to perform the actual impact analysis on a test server since Oracle must build the recommended indexes first.

SQL Performance Analyzer uses its RAT Replay technology and captures SQL execution performance stats in what are called SQL Tuning Sets (or STS). To be completely effective, you must use SQL Performance Analyzer in conjunction with OEM and the Management Packs listed at the top of this blog.

The basic idea is that you capture workload (with all the transactions) via STS from production while the application is running and is being accessed by users. You then post the STS to your test database and execute everything in order to establish a baseline.

You then manually implement all the planned changes you are intending to apply to production (patches, hardware, upgrades, etc) and re-execute all the SQL.

You then compare and analyze the performance against the baseline and a report shows which SQL statements regressed as a result of the changes.

SQL Performance Analyzer report showing whether performance regression has taken place

SQL Performance Analyzer also lets you compare execution plans and execute SQL statements and time them.

SQL Tuning Advisor is used to help implement any alternative SQL statements.

This cycle can be repeated over the life of the application as further changes are made

Example 3 Summary:

Toad DBA Suite

Specifically for index changes, Quest’s approach is to perform the impact analysis “before the fact” through the use of Virtual Indexes which means you do not have to build the index on the database first.
Execution plans (which can be displayed in a variety of ways, inc flow chart) can be compared side-by-side making interpretation easier.
Graphical comparisons following a static test (i.e. no SQL is executed) using Impact Analysis for performance regression helps to make an informed decision about the merits of a planned change.
Graphical comparisons following a dynamic test (i.e. SQL is executed) using Benchmark Factory for performance regression are not possible.
SQL Optimizer’s SQL Repository allows you to store baselines and snapshots containing execution plans and SQL statements for future comparisons.

OEM

Specifically for index changes, Oracle’s approach is to perform regression testing “after the fact” which means building indexes on a test server and executing the SQL first then performing the tests.
Execution plans can be compared side-by-side making interpretation easier.
SQL statements can be executed and timed.
Graphical comparisons of performance metrics following regression testing helps to make an informed decision about the merits of a planned change.
SQL Plan Management lets you retain execution plans for future comparisons.

Summary

For change management tasks, Toad DBA Suite offers a significantly lower cost solution and provides greater depth in some areas (e.g. impact analysis) than is currently available in Oracle OEM, across a wider range of platforms and in a way which is simple to use thereby minimizing the amount of interpretation the DBA has to do.

Schema change management is much simpler than OEM owing to its tried-and-tested Schema Compare & Sync technology which does not rely on OEM’s Change Management pack. If you require a full database compare, OEM’s Change Management pack includes non-schema database objects. In Toad, Database and Schema Compare are 2 separate operations with Database Compare providing comparison for non-schema database objects but also other items not included in OEM (such as Initialization Parameters and Directories). However, Toad DBA Suite offers the DBA two possible ways to generate a schema sync script depending whether or not their company uses data modeling technology. Toad DBA Suite’s Toad Data Modeler component is a full data modeling solution which can generate a deployment script simply by comparing the development data model with a reverse-engineered model of the production schema.

Toad DBA Suite’s SQL Optimizer technology is far more advanced in terms of its SQL re-writing and Virtual Index technology as well as its ability to perform what-if analysis compared to OEM’s more “black-box” approach to tuning.

Toad DBA Suite’s Benchmark Factory workload replay technology is simpler to user, being based on established Oracle trace file technology and is integrated with Toad, but may involve additional work if extensive use of bind variables are used.

Oracle’s RAT technology essentially recreates the whole production environment and so is a better representation, but there is no opportunity to make adjustments to userload or perform any other form of “what if” analysis.

Here’s a quick comparison of the differences:

Toad DBA Suite for Oracle

Task oriented workflow starting in Toad as the core application with contextual links to related components.
Lightweight, standalone schema comparison with sync script creation which out-performs OEM Change Management pack in terms of speed and ease-of-use.
Lightweight, standalone data modeling environment with on-the-fly reverse engineering from Toad together with Alter Script generation based on model version differences.
Assessing the potential impact of new indexes and determining the effect of proposed indexes on the execution plan changes of other SQL statement is easy to use and requires the minimum of interpretation owing to the clear graphical reporting available.
Oracle Database Workload Replay is limited to the use of Oracle trace files which can get somewhat complex and cumbersome. However, this approach is very simple and quick to use and satisfies many user requirements especially when using earlier database platforms.
Toad DBA Suite’s Benchmark Factory component lets the DBA perform “what-if” analyses by letting them change such things as userload, latencies, etc.
Toad DBA Suite runs completely on the client PC and therefore does not consume resources on the server (except in the case of Spotlight – which are minimal and are configurable) and Benchmark Factory for Databases which should only be used to generate load on a non-production server)

Oracle OEM

Workflow is initiated from the OEM Home Page and uses hyperlinks and is not therefore contextually connected to other tasks the DBA might be performing, such as the impact analysis of a proposed new index.
OEM’s Change Management Pack is designed for database comparison therefore schema compare workflow requires you to follow the same process. Workflow is improved if using SQL Developer.
No data modeling capability.
The impact of a proposed new index (or indexes) requires it (them) to be built on the database first in order to assess the execution plans. The only way to minimize disruption in production is to try them out in test and use RAT and SQL Performance Analyzer.
Database Workload Replay using RAT enables a more representative replay of application sessions since it mirrors full application transactional concurrency, synchronization and dependencies which Oracle trace files don’t. However, this is a more complex, expensive process better suited to patches and upgrades and is platform limited.
RAT does not allow “what-if” analyses. It simply replays what was captured.
OEM’s Management Packs and RAT all run on the server and therefore consume server resources.

Caveats

Quest relies on information from a variety of sources in preparing this document. While every attempt is made to ensure its accuracy, errors or omissions can occur.

In addition, Oracle might make changes to its offering after this document is published, potentially affecting the comparison to Quest’s offering.

If you missed Parts 1 and 2 of this blog where we contrasted Database Administration and Database Performance Management tasks using Toad DBA Suite for Oracle and OEM, be sure to check them out.

Using Toad DBA Suite for Oracle with Oracle Enterprise Manager (Part 2)

Johannes Ahrends — Mon, 02 Mar 2009 14:04:00 GMT

By Johannes Ahrends and John Pocknell

In the previous blog, we compared and contrasted how Database Maintenance operations are performed in Oracle’s Enterprise Manager (OEM) and Quest’s Toad DBA Suite for Oracle and how Toad is an excellent complement to OEM because it simplifies and automates many tasks which would take longer in OEM.

In this second part, we contrast Performance Management operations between OEM (including OEM Database Control for Oracle 10g and above) and Toad DBA Suite for Oracle.

Performance Management

Since OEM Database Control enables DBAs to manage the performance of many aspects of an Oracle database including SQL execution, configuration, storage, etc, for the purposes of this blog, we will just focus on contrasting application performance management. For comparison with Toad DBA Suite for Oracle, you will need, in addition to OEM, 2 Management Packs:

Diagnostics Pack – offers automatic performance diagnostics and monitoring. This pack is required for any kind of performance monitoring. http://www.oracle.com/technology/products/oem/pdf/diagnostic-pack-11g-datasheet.pdf
Tuning Pack – offers automatic SQL tuning for applications http://www.oracle.com/technology/products/oem/pdf/tuning-pack-11g-datasheet.pdf

Quest Software offers enterprise solutions which address the wider aspects of database performance management but which are beyond the scope of this blog.

Example 1

Let’s start with a simple example where you are contacted by an end user who complains: “My application doesn’t respond”

Possible cause? Blocking – waiting locks. A very common problem in Oracle databases.

In Toad DBA Suite’s Spotlight on Oracle component, you see a color change in the overview screen “changes per second” which will be either orange or red.

Click on the “changes per second” flow.
A Lock Wait alarm pop-up opens. Click on the “Blocking Locks” drill down.
The detail screen provides additional information together with information about which session is holding the lock and which one is requesting it.
Click on one of the sessions and you see the actual DML statement the session wants to execute including explain plan if possible.

In OEM :

Home page
Within your database and in “alerts” you see “waits by wait class” as warning.
Click on the message and you see (if you sent the “view data to last 1 hour”) a chart. But no further detail.
Recommendation on that page: “Run ADDM to get more performance analysis about your system.” Why?

But you might find it more interesting to go to the performance tab. It shows you that there is a problem with “active sessions”. Drill down and voila “here is the session detail”.

But nothing about the blocking session.

You can drill down to find the actual SQL statement including an explain plan (on several pages) but only if you explicitly go the menu “Hang Analysis” you see that there are two sessions with a blocking waiting lock. Again, drilling down gives some more details.

But how long does it take to come to the right conclusion? :

“My application doesn’t respond = Blocking lock from user a SQL statement x”.

The significant differences between Spotlight on Oracle and OEM, in this example, is in the ability of Spotlight to evaluate what is happening in the Oracle database in a real-time, unambiguous way; be able to drill-down in context with the notified problem in order to obtain more information; cross-reference what you have found with other views such as user sessions and then (with the help of contextual advice, if necessary) arrive at the correct conclusion. This is achieved through a combination of Spotlight’s excellent visual dashboard, efficient navigation and the persistence of context even through to problem resolution.

It is simply not possible to achieve the same feeling of real-time, smooth navigation and root cause analysis as efficiently by using OEM.

Example 1 Summary

Spotlight on Oracle

Progressive workflow starting with a top-down view of all managed databases.
Dynamic color change on a monitored metric notifies the DBA of a problem prompting an immediate response.
If the DBA misses the event, Spotlight’s History Browser enables the DBA to “playback” the event.
Clicking on a color change presents choices including access to more granular information.
Detail screen enables DBA to determine root cause and launch the appropriate solution to resolve the problem.
For more information on using Spotlight to diagnose Oracle bottlenecks and optimize databases, see http://www.toadworld.com/Experts/GuyHarrisonsImprovingOraclePerformance/ResolvingOracleContention/tabid/257/Default.aspx or http://www.toadworld.com/Knowledge/DatabaseKnowledge/GuyHarrisonsImprovingOraclePerformance/SQLTuning/tabid/398/Default.aspx

OEM

Top down view for the connected database only. For other databases, separate connections need to be made.
Alerts identify things like excessive Waits will draw DBAs attention to a problem. SQL related issues are less obvious.
OEM stores longer history than Spotlight does so the DBA can review performance trend over a longer period of time. (Note: Quest Software offers an enterprise performance management solution called Foglight Performance Analysis (beyond the scope of this article) which provides a long-term historical workload analysis and advisories which you can find out about here.

Example 2

Now let’s assume that you suspect that your performance problem is SQL statement related, but you are not sure which one it is within the application.

Again, with Toad DBA Suite’s Spotlight on Oracle component, you are able to drill down into the root cause from the home page. However, rather than expect you to copy and paste the SQL statement you’ve identified into an editor so you can begin figuring out what to do, Spotlight provides a launch point to another component of the Toad DBA Suite – SQL Optimizer for Oracle.

Spotlight on Oracle’s Top Sessions screen isolating the most CPU intensive SQL showing the launch button to SQL Optimizer for Oracle

As you can see in the Top Sessions drill down screen (above), the SQL statement which was the cause of the performance bottleneck is immediately isolated and a button on the screen enables you to launch SQL Optimizer to resolve the problem.

SQL Optimizer’s unique artificial intelligence optimization engine automatically transforms the original SQL statement into multiple, semantically equivalent re-writes, then takes all the re-writes and re-writes those. It continues doing this until there are no possibilities left. This process takes seconds.

SQL Optimizer then automatically discards all those re-writes which have the same execution plan leaving a number of possible improvements. Information such as plan cost enables the DBA to make an informed decision as to which SQL statement to execute in order to get a measure of actual performance improvement compared to the original statement as you can see below:

SQL Optimizer’s Tuning Lab showing a completed SQL tuning task

This workflow here is also extremely productive especially where you have a dynamically changing landscape and SQL statements are being continuously being executed, and you need a quick and decisive way to identify, diagnose and resolve but at the same time maintain control over how the best probable solution is implemented.

An alternate way of performing SQL optimization in the SQL Optimizer is to use the Batch Optimizer. The back-end process is still the same as described above, but multiple SQL statements and code can be fed-in to the Batch Optimizer (rather like a conveyor belt) for downstream optimization. This process frees the DBA from becoming involved with the optimization of each statement so that they only have to look at the results and decide which optimized statement to use in each case.

OEM together with the SQL Tuning Advisor takes an entirely different approach.

The first significant difference (especially on a production database) is that the problematic SQL statement has to have actually been executed first. Here are some other key differences:

You can only send to the SQL Tuning Advisor SQL statements which have been captured by OEM (for example Top Activity or from AWR). You cannot enter your own SQL, therefore not helpful to DBAs who already know what they want to tune.
SQL Tuning Advisor does not rewrite your code. It will offer suggestions for code changes which then need to be manually implemented by the DBA. It does not find all the rewrites, test the SQL or figure out which is the best alternative.
SQL Tuning Advisor’s more advanced plan analysis mode, creates what is called a SQL Profile, which collects additional information for the query by the optimizer. This analysis is not possible in the normal mode. Such a SQL Profile helps generate a better execution plan than the normal optimization. Additional tasks like checking for advanced predicate selectivity, correlation between columns, join skews, and complex predicates such as functions, help in profiling the SQL statement. Once a statement is profiled and stored, it can be used at will.
OEM also has a feature called SQL Repair Advisor which a DBA can run in the rare event that a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If the DBA decides to implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.

OEM’s Top Activity screen showing link to SQL Tuning Advisor

Example 2 Summary

Toad DBA Suite

SQL Optimizer works contextually with the identified problem and optimizes the SQL based on an automated optimizing engine generating every possible semantically equivalent re-write.
The DBA is offered choices and can select the most appropriate solution.
SQL Optimizer’s approach means that a junior or experienced user can use the tool. It can be used by developers and DBAs, and it fixes the root cause of many SQL performance problems - poorly written SQL.
SQL Optimizer is designed to simultaneously optimize multiple SQL statements using its Batch Optimizer to reduce the user’s workload.
If SQL Optimizer is initiated from Toad, the best solution can be implemented directly back to the SQL statement or PL/SQL code in Toad’s Editor.
In tests, the re-written statement performed better than the original.
SQL Optimizer can accept SQL statements for optimization at any time for “what-if” analysis.

OEM

Since there is no re-writing technology, there is no concept of offering possible alternatives – just one.
There is more dependency on the DBA to interpret the data and implement the best solution. This is not a solution for the developer.
While OEM’s approach on deploying objects like SQL Profiles influences an execution plan, you are creating an object in the database that needs maintenance and that most likely only DBAs have the rights to deploy.
OEM is not designed to simultaneously tune multiple SQL statements.
In tests, there is no guarantee that the offered suggestion actually improves the SQL performance.
SQL Tuning Advisor cannot accept SQL statements for tuning, the statement can only be captured once it has executed.

Example 3

In this example, you know you have performance problems in the application and you want to statically scan the database and/or the application code to see which SQL statements are the most likely ones contributing to poor performance.

The problem is that database applications typically contain thousands of SQL statements that may need to be optimized for better performance. You would therefore have to find and extract each SQL statement manually — a very tedious and time-consuming task. Once you have found the SQL statements, you need to analyze the execution plan of each SQL statement to see if the execution plan represents a potential performance problem.

SQL Optimizer’s SQL Scanner component relieves you of this tedious task:

The SQL Scanner extracts SQL statements embedded in database objects, captured from the SGA, or stored in application source code and binary files. It retrieves and analyzes the execution plans for the extracted SQL statements. It then categorizes them according to the complexity of the execution plan and determines whether it has the characteristics that typically cause performance problems.

The SQL Scanner allows you to quickly review SQL statements in existing code and detect potential problems. With this approach, you can be proactive in the detection of performance problems and identify the SQL statements that need to be optimized without actually executing them.

Once the problematic SQL statements have been identified, you can determine the best solution by

Sending a SQL statement to the Tuning Lab for optimization.
Sending a SQL statement to the Tuning Lab for index generation.
Sending a SQL statement to the Batch Optimizer for optimization. (see screenshot below)
Sending a group of SQL statements to Global Indexing for index generation.
Saving a group of SQL statements to the SQL Repository for further analysis.

SQL Scanner screen showing collected SQL statements from various sources

OEM does not have the ability to scan code directly in source code or in external files. The SQL has to have actually executed in the database first. OEM can look for executed SQL statements in either of the following areas:

Cursor Cache
AWR Snapshot within a specified time period
AWR Baseline
SQL Tuning Set

The other disadvantage with this approach is it’s practically impossible to tell whether an executed SQL statement is part of a script, PL/SQL object, etc so locating it will be a problem.

OEM’s SQL Search screen

Example 3 Summary

Toad DBA Suite for Oracle

Potentially problematic SQL statements can be statically scanned in a variety of sources and do not have to be executed first.
A classification system enables the DBA to prioritize what needs to be tuned and what does not.

OEM

OEM does not have the capability to make tuning assessments on SQL unless it has been executed first.
Determining where the executed SQL statement came from is difficult.

Example 4

In this final example, we need to assess whether something other than a SQL re-write (i.e. an index) would offer a better alternative. It may well be that we need to collectively improve the execution performance of more than one SQL statement, which is particularly challenging for a DBA.

Toad DBA Suite for Oracle’s SQL Optimizer component makes this task easy, without having to unnecessarily stress the database, through the use of Virtual Index Simulation.

Clicking the Indexing tab allows the DBA to add a single SQL statement or SQL script and determine the most effective index (or group of indexes) which would improve the performance of a single SQL statement or a group of SQL statements (using Global Indexing).

Global Indexing performance comparison on a group of SQL statements using a Virtual Index

An execution plan comparison can be performed without the proposed index being built. If full execution statistics are required before the DBA makes a decision, an index creation script is generated, run (to collect the stats) then the index is dropped afterwards. The script is saved for possible future use.

OEM has something similar to this called the SQL Access Advisor. This uses the concept of a workload which would typically include a group of SQL statements, but the critical difference here is that all the SQL statements must have already executed in the database.

The workload source can be selected from one of the following:

Current and Recent SQL Activity (stored in cache)
Existing SQL Tuning Set (from SQL Tuning Advisor)
A hypothetical workload based on selected tables and schemas

SQL Access Advisor showing indexing recommendations

Recommendations can be based on indexes, materialized views or partitioning. Once the submitted job is complete, graphs show workload I/O cost improvements and query execution time improvement. Where indexes are recommended, the DDL script is generated which can be scheduled or run.

Although the concept is good on the basis of recommending a strategy based on a workload (SQL transactions), the fact is that these recommendations can only be made after execution on the database with no opportunity to do a “what-if” analysis.

Example 4 Summary

Toad DBA Suite for Oracle

Global Indexing proposes the most suitable index sets to collectively improve the performance of a group of SQL statements without having to (permanently) build the index and execute the SQL first.
What-if analysis enables a confident assessment of whether the index will improve performance with impacting the database.

OEM

OEM’s Tuning Pack offers one suggestion for an alternative index which must be created in the database before its effect can be determined.
OEM’s SQL Access Advisor recommends a course of action (possibly an index) to collectively improve a group of SQL statements.
The absence of what-if analysis means the recommended index might have a negative impact on performance.

Summary

This blog set out to contrast the differences between Toad DBA Suite for Oracle and OEM in the context of Performance Management. It is worth noting that there are certain tasks for which Toad DBA Suite for Oracle was not designed, such as database configuration advisories, longer historical collection, etc.

However, Quest Software does offer Enterprise Database solutions which address these areas which you can find out about here.

It is also worth mentioning that there are additional features in Toad DBA Suite for Oracle which I did not have space to include in this (already long) blog such as Trace File Browser, StatsPack and AWR Browsers, Session Browser, Database Health Check, etc which can help augment Spotlight in helping to pinpoint a performance related problem.

For performance management tasks, Toad DBA Suite for Oracle enhances the DBAs productivity by starting with the big picture (across all managed databases) and allows the DBA to access successive layers until the root cause of a performance slow-down is presented. From that point, the DBA is able to directly access the appropriate component with which to resolve and implement a solution.

OEM is still able to present the big picture, but only shows one database at a time. The other key difference is interpretation of the information. The various screens which OEM presents requires the DBA to have more than a reasonable level of experience in order to understand and correctly interpret what’s presented in order to determine the exact cause of a problem. Implementation of the most appropriate solution also requires experience.

Toad DBA Suite for Oracle, therefore, is a worthy complement to OEM by providing the DBA with faster and more effective methods of problem identification and resolution with minimal impact to production, where time and accuracy matter and where the DBA sometimes just requires a “second opinion” when it comes to SQL tuning. OEM enables the DBA to take a longer term view of performance management with particular assistance around database configuration and advisories.

Caveats

Quest relies on information from a variety of sources in preparing this document. While every attempt is made to ensure its accuracy, errors or omissions can occur.

In addition, Oracle might make changes to its offering after this document is published, potentially affecting the comparison to Quest’s offering.

Look for Part 3 of this blog where we’ll be contrasting Change Management tasks using Toad DBA Suite for Oracle and OEM.

Using Toad DBA Suite for Oracle with Oracle Enterprise Manager (Part 1)

Johannes Ahrends — Tue, 03 Feb 2009 17:36:00 GMT

By Johannes Ahrends and John Pocknell

This 3-part blog will help you differentiate between what Oracle Enterprise Manager (OEM) offers the DBA compared to Toad DBA Suite for Oracle and how the same tasks are approached using each.

Toad DBA Suite for Oracle provides a complete solution for DBAs using Oracle Standard Edition, or in smaller shops where OEM is not being used or as a complementary solution for DBAs using Oracle Database Enterprise Edition and OEM.

With the Toad DBA Suite for Oracle, you can improve operational efficiency to lower costs by formalizing database management through a systematic approach that assures maintenance, performance and change. The Toad DBA Suite for Oracle allows you to simplify and automate common database administration tasks and become more proactive in solving database-related performance issues before end users are impacted.

Toad DBA Suite for Oracle is comprised of the following components:

Toad for Oracle Xpert Edition (includes Quest SQL Optimizer for Oracle)
DB Admin Module
Benchmark Factory for Databases (Oracle Edition)
Toad Data Modeler
Spotlight on Oracle (seat-based license)

The Toad DBA Suite focuses on the database only and addresses three key areas of database administration:

Maintenance – managing users, database health checks (including security as it relates to user Oracle accounts and permissions), task automation and scheduling, etc.
Performance Management – real-time performance diagnostics, database and SQL optimization
Change Management – configuration changes, schema changes and database workload replay testing

Before we continue, it’s worth clarifying what I mean by OEM (for purpose of comparison in this and the following two parts of this blog).

The full Oracle OEM Grid Control works to combine all aspects of database, application and infrastructure management in one tool. This leads to several distinct menu structures which make administration of only database-related issues (which most customers are interested in) very complicated.

From database startup and shutdown to conversion of a database to RAC, there are tons of hypertext links in OEM Grid Control, which causes problems.

OEM Grid Control is based on Oracle 10g and mainly on 10g Release 1. If you want to install it you first need to install Grid Control 10.2.0.1 which is the management server and agent in version 10.2 and the database in version 10.1.0.4. After the upgrade to 10.2.0.4, both the management server and the agent will be 10.2.0.4 aware (note that this is the final version of Enterprise Manager so still no 11g agent). And the database runs on 10.1.0.5 (which to my knowledge is not supported).

In this blog, we will focus specifically on OEM or OEM Database Control (for Oracle 10g and above) which hereafter will be referred to as OEM.

Now let’s compare some typical tasks using OEM and Toad DBA Suite:

Database Maintenance

OEM is built to manage databases over a web interface. So each function ends up as a hypertext link on a new webpage.

So let’s assume, as a DBA, you want to create a table similar to an existing one:

In Toad DBA Suite :

Open Schema Browser
Click the table tab
Right-click table
Select Create Like
Notice the warning regarding the use of user-named constraints

In OEM :

Targets (new page)
Databases (new page)
Instance (new page)
Administration (new page)
Schema Tables (new page)
Search (popup page)
Select Schema
Then you select the original table and use “Actions create like” GO.

After specifying the table name you might want to create a duplicate table. But that’s not possible because you are using the same names for the constraints (no warning, nothing). The execution (OK button) gives the error messages “ORA-02264 name already used by an existing constraint”.

Now let’s compare how you would look at overviews of your managed databases.

It is very common to have more than one database running on the same host. So you might want to have an overview about the structures like datafiles, memory layout, or top sessions.

With Toad DBA Suite this is combined in the Database Browser:

Open Database Browser (main toolbar)
Click on your host and use the tabs on the right-hand side. All the above details are shown in various tabs. One screen for ALL databases.
Access to both database objects and schema objects is provided in the navigator on the left-hand side meaning that you no longer need to use Schema Browser as well.

In OEM you first have to select the database again.

Going to “CPU” gives you some information about the top sessions but not about the layout.
The memory layout can be found at databases -> administration -> memory.
The tablespace layout under databases -> administration -> tablespaces.

Yes, everything is available, but keep in mind that you are working with a Web browser so navigation always launches new screens.

These are just a few examples which show that:

Yes, OEM provides a lot of the same information as Toad
The navigation is very complex and it costs you lot of time to find the root cause of a problem
OEM has database maintenance features which are not available in Toad:
1. Recovery Manager (RMAN) repository (catalog) management
2. Administration of Data Guard (Standby database)
3. Database cloning
4. Streams and advanced replication management
5. Convert to cluster databases
6. Administration of flashback recovery area and archiving
7. Summary Advisor (for management of materialized views)
Toad offers several database maintenance functions that OEM does not have (click the links for more information on each function):
1. All features related to data (browse, compare, generate, subset, export/import, report, etc)
2. Manage user accounts without scripts.
3. Editor (yes there is an interface to iSQL*Plus but that product is no longer supported).
4. Compare and synchronize schemas (this is available in OEM but only with the add-on Change Management Pack).
5. Compare objects (users, tables, PL/SQL, tablespaces, etc) including objects on other ODBC compatible DB platforms
6. DBMS Flashback (performs a flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN). Uses the DBMS_FLASHBACK package).
7. Flashback data archive (as this is an Oracle 11g feature it’s not available in the full OEM Grid Control).
8. Task scheduling – although OEM can enable the DBA to schedule tasks on the server, Toad allows you to schedule many tasks using the Windows Task Scheduler – including schema compare, database health check, script execution, and reporting
9. Task automation – Toad’s AppDesigner lets you sequence a group of tasks such as those listed above and have them run against multiple managed databases rather than have to repeat them independently for each database, resulting in a huge amount of time savings on repetitive tasks.
10. Database health check (including database security vulnerabilities).
11. Database and schema reporting.
12. Pre-defined RMAN script templates.
13. Script management.
14. And my all time favorite: log switch frequency map.

Caveats

Quest relies on information from a variety of sources in preparing this document. While every attempt is made to ensure its accuracy, errors or omissions can occur.

In addition, Oracle might make changes to its offering after this document is published, potentially affecting the comparison to Quest’s offering.

Summary

For database maintenance tasks, Toad dramatically increase the DBAs productivity by providing a single, collective view of all managed databases (instead of one at a time) with the ability to drill-down and perform those tasks in a consistent, efficient way. Task automation and scheduling allow many tasks, which are often repetitive, to be executed without the DBAs direct involvement, thus freeing them up to perform other important tasks or processes.

OEM still lets the DBA perform some of these tasks, but productivity is significantly slower due to navigational inefficiencies. Plus, OEM offers no opportunity to perform multiple tasks against multiple databases.

Look for Part 2 of this blog where we’ll be contrasting Performance Management tasks using Toad DBA Suite for Oracle and OEM.

Automatic RMAN Backup with Oracle Scheduler

Johannes Ahrends — Thu, 08 Jan 2009 16:56:00 GMT

Is German your native language? Read this blog in German on Johannes' tech articles page.

Since version 10g, Oracle provides a new job scheduling mechanism named scheduler. There are several advantages over the old dbms_job function but as often the documentation and the various functions is more confusing than helpful. In 11g we have 7 (!) different functions (Scheduler Chains, Scheduler Programs, Scheduler Schedules, Scheduler Jobs, Scheduler Job Classes, Scheduler Windows, Scheduler Window Groups) so you might say “let’s better go with the unix cron!”.

But in most of the cases the daily usage is much simpler: I normally only use Scheduler Programs and Scheduler Jobs so I removed the others from my Toad Schema Browser menu.

Picture 1: Schema Browser Configuration

With these two functions I’m able to create Jobs which can be executed inside the database (PL/SQL procedure) or outside the database as shown in the following example.

RMAN Scheduling

The Oracle scheduler is able to call operating system commands on the server. Unfortunately it is very complicate to tell the Oracle scheduler to execute a command with a script (e.g. rman rcvfile=…). Therefore I prefer to create a small shell scripts on Unix or Linux systems which only needs one variable (ORACLE_SID) and includes the RMAN commands.

A script can look like this:

#!/bin/sh
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin
export PATH

if [ $# -ne 1 ]
then
    echo "Usage: $0 [ORACLE_SID]"
    exit
fi
ORACLE_SID=$1
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK
shift
rman target /                             
BACKUP DATABASE;                                     
BACKUP ARCHIVELOG ALL NOT BACKED UP 2 TIMES;         
DELETE ARCHIVELOG ALL COMPLETED AFTER 'SYSDATE - 3';
EOF

Script 1: Shell Script for RMAN-Backup

I normally store this script named rman.sh into the /usr/local/bin directory. Now I’m able to use Toad to create a scheduler job.

First I create a scheduler program named FULLDB_PROG which mainly includes the name of the executable (/usr/local/bin/rman.sh) and some variables (in our case the ORACLE_SID):

Figure 2: Scheduler Program Info

Figure 3: Scheduler Program Arguments

Now I’m creating the scheduler job named Backup_JOB. Don’t forget to enable the job by checking the Enable button.

Figure 4: Scheduler Job Basic Info

In the tab Scheduler Info the start date (midnight) for the first execution is specified and the execution interval (once per day).

Figure 5: Scheduler Job Schedule Info

Useful Tip: because the time syntax for the scheduler is not easy you might want to use the right mouse button and see some examples like “Weekdays at 7 am and 3 pm” to change them accordingly.

In the tab “Program Info“ I now add the name for the scheduler program (FULLDB_PROG)

Figure 6: Scheduler Job Program Info

You can verify if the execution of your script was successful in the “Run Log” tab of the scheduler job.

Figure 7: Scheduler Job Run Log

Being Successful with Toad and RMAN

Johannes Ahrends — Wed, 07 Jan 2009 20:51:00 GMT

Is German your native language? Read this blog in German on Johannes' tech articles page.

A lot of companies with Oracle databases have been using RMAN as their main backup utility for years now. But others are still relying on the classical way by copying data files or even using Oracle export as a backup method. There are several reasons for this, but often it’s just only a lack of knowledge about RMAN which prevents DBAs from using this very flexible tool.

Like other Oracle tools, RMAN was designed as an all-in-one tool which is capable of backing up and restoring nearly everything you want. But this means that for 90% of all databases you only need 10% of the functionality RMAN offers.

Why should you prefer RMAN verses copy or export?

First of all Export (and Data Pump export as well) shouldn’t be named a backup tool at all because it is primarily designed for exchanging data between Oracle databases. It can help recover objects from logical failures like DROP, TRUNCATE, DELETE, … but only to a specific point in time (the time the export was executed) and only with time exposure. For a full recovery you first have to create a basic database including SYSTEM, SYSAUX, UNDO and TEMP tablespaces. And then it will probably take more than 24 hours to import the data (even with databases of less than 100 GB in size). That’s often not acceptable for a production environment.

Since Oracle Version 6 it is possible to copy data files in an online database. To do so it is necessary to switch the database into archive log mode. For sure a database can be backed up with OS tools if it is offline and then archiving is not necessary but in case of a failure the database can only be restored to the latest backup. New transactions will be lost. So this practice is not suitable for production environments.

In addition to the archive log mode it was always necessary to set the database or a data file into a backup mode (BEGIN BACKUP). This is the only way to guarantee that in case of an error the restore and recovery is possible. But this doesn’t imply that the backup is consistent. You have to backup all archived redo logs to assure a successful recovery of your database to any point in time.

Why RMAN?

Here are some reasons:

RMAN is Oracle’s recommended tool for backup and recovery of databases
RMAN is easy to use (I’ll explain later)
Only tablespaces which are necessary for a recovery are backed up (so no TEMP-Tablespace)
Only used blocks are backed up (this can reduce the backup size dramatically)
Backup creates backup sets including the data file backup, the archived redo logs, the control files, and the spfile.
Restore and Recovery is simple
Since Oracle 11g the data recovery advisor helps you to find the most effective recovery method without wasting time.
The database control files or a repository keeps track of all backups if they are successful or not
RMAN can control the archived redo logs (e.g. automatically deletes obsolete files)

There are several other reasons why you should use RMAN. The possibility of on the fly compression and encryption. Oracle only allows standard compression with the enterprise edition and an enhanced compression and encryption is only available by buying additional options (advanced compression and advanced security).

How RMAN works

The following example is for backing up databases without an optional catalog. This catalog is mainly used in large environments with hundreds or thousands of Oracle database. This central repository allows you to maintain the backup of all your databases. But in case of a failure you have to rely on this repository so a second backup strategy is necessary and some other pitfalls (e.g. catalog version vs. RMAN version) limits the usage of a catalog for smaller environments.

First of all you should – if it isn’t already - switch the database into archive log mode.

Since Oracle 10g there is a default directory for archiving if you are using flash recovery. In this case the parameter log_archive_dest_10 points to the destination named with db_recovery_file_dest. So in case you switch on archiving (ALTER DATABASE ARCHIVE LOG) corresponding subdirectories are created automatically.

Before you backup the database with RMAN the first time you should set some configuration parameters in the control files. This simplifies the daily backup routine because the parameters are stored in the control files of the database.

Figure 1: Calling RMAN with Database Browser

Since Toad 9.7 the Database Browser has an addition menu named “Generate RMAN Script”. From this menu you can call several predefined scripts and you can add your own scripts as necessary.

The “Basic Script to setup RMAN for backups“ allows you to set the configuration parameters.

Calling this script opens an additional parameter window to set or change some basic variables like log file location, backup location, etc.

Figure2: Parameter windows

The parameter DB (Database), PWD (Password), and USER are predefined with the corresponding values of the database you choose in the database browser. RMAN can only work with the privilege SYSDBA or SYOPER, so SYS is often used as the default user for RMAN:

RMAN_BACKUP_LOCATION is the backup directory on the server. RMAN will substitute the variable %d with the name of the database. So in the example the backup directory will be /u02/oradata/PROD11G/.

The RMAN_LOG_LOCATION is a directory on the client to store some logging information. Even if this spooling of RMAN is not enabled all changes will be stored in some V$-Tables on the server.

Now you will see the new RMAN-Editor which allows you to execute RMAN commands from the client.

This is the default configuration file:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/oradata/%d/%F.bck';

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 
'/u02/oradata/%d/%d-%Y-%M-%D-%p-%s.bck'

Script 1: RMAN-Configuration script

Explanation:

CONFIGURE CONTROLFILE AUTOBACKUP ON

This enables automatic control file and spfile backup with every database backup. By default this is disabled but because in our case only the control file contains information about the backup sets it’s necessary to have the control file backup with every database backup.

CONFIGURE DEFAULT DEVICE TYPE TO DISK

This sets the default backup method to disk instead of backing up directly to tape (DEVICE TYPE SBT_TAPE).

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/oradata/%d/%F.bck'

This parameter tells RMAN were to store the control file and spfile backups. The variable %F should be used all the time because it’s a combination of the Database ID (DBID) the date and a sequence number. If you have to do a full restore and recovery of the database (missing controlfiles) the DBID is necessary in a first step to specify the database.

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u02/oradata/%d/%d-%Y-%M-%D-%p-%s.bck'

This parameter tells RMAN were to store the database backup. It is a combination of the variable RMAN_BACKUP_LOCATION and some other RMAN variables. In our example these are:

%d the database name
%Y Year
%M Month
%D Day of the month
%p Backup Piece
%s Backup Set

The parameter s and p are necessary to identify the backup set (s) and uniquely store every piece of the backup.

There are several other parameters available but I think these are the most common used.

In Toad you can now execute this script with the “Execute statement“ button. As mentioned previously, this script needs to run only once because all parameters are stored in the control files. With V$RMAN_CONFIGURATION you can validate the parameters.

Figure 3: RMAN-Configuration

Now the prerequisites for backing up the database, tablespaces, or data files (full or incremental) are done and we can backup the database for example with the following command:

BACKUP DATABASE PLUS ARCHIVELOG;

You see, this is really an easy command!

For the daily backup you might want to add some other tasks to your backup procedure. So the default script “Hot Backup of Database“ might help you.

Default Script:

SPOOL LOG TO c:\temp\RMANDBHotBackup_PROD11G.log;

CONNECT TARGET SYS/<7,0,0,0,93,159,93,197,18,136,72,121,80,107,196,243,85,234,12,108>
@PROD11G;

CROSSCHECK ARCHIVELOG ALL;

BACKUP DATABASE;

BACKUP ARCHIVELOG ALL NOT BACKED UP 2 TIMES;

DELETE ARCHIVELOG ALL COMPLETED AFTER 'SYSDATE - 3';

Script 2: Backup Script

Explanation:

CROSSCHECK ARCHIVELOG ALL

This is a verification routine to check if the archive logs we want to backup are available. Sometimes a DBA has to delete archive logs without backing them up (for example if due to an unforeseen event the database produces tons of archives). Missing archive logs are now marked.

BACKUP DATABASE

This is the minimal command to backup the database including the control files and spfile.

BACKUP ARCHIVELOG ALL NOT BACKED UP 2 TIMES

Now we are backing up all archived logs generated in the meantime. To reduce the backup size and enabling the storage of archived logs on disk for a longer period of time the “NOT BACKED UP 2 TIMES” checks how often the archived redo logs are already backed up.

DELETE ARCHIVELOG ALL COMPLETED AFTER 'SYSDATE - 3'

Because we normally backing up our database once a day we can remove all archive logs from disk if they are older than 3 days. If you have plenty of space you might want to change this parameter to 7 days.

This is only one example how to backup a database. In the Toad options menu you will find several predefined RMAN Templates which you can change as needed and you can add your own templates as shows in the example with a tape backup script.

Figure 4: RMAN Templates

Please Note: We anticipate being able to provide further support for RMAN in the next version of Toad for Oracle, which will be released later this year, to provide DBAs with better visibility of (and reporting on) what backups they have carried out. We would very much welcome any feedback you have on this article and suggestions you have as to what else you would like us to provide.

Unicode Migration

Johannes Ahrends — Mon, 03 Nov 2008 17:24:00 GMT

Migrating to a database that supports Unicode is a challenge. You are safe if you are currently using US7ASCII for you Oracle database because Oracle provides a mechanism with ALTER DATABASE SET CHARACTERSET UTF8 which allows you by just bouncing the database to switch to this new character set. But what if you are currently on a different character set? In Europe we have a bunch of WE8 (Western European) or EE8 (Eastern European) character sets and as they are not a strict subset of Unicode the ALTER Database does not work. So the only chance is to export the whole database, create a new one (with UTF8 or similar) and import it again.

Is it that easy? No! The reason is that in most databases the so called length semantics hasn’t been used while creating a table. Since Oracle9i the correct syntax for creating is:

CREATE TABLE test1
(custid   NUMBER(10),
lastname VARCHAR2(6 CHAR))

Have you used this syntax? No of course not! We all used the old version:

CREATE TABLE test1
(custid   NUMBER(10),
lastname VARCHAR2(6))

But what is the difference? The default behavior for an Oracle database is to use “BYTE” as length semantic. And this can cause huge problems while migrating to Unicode.

Let’s assume your current database is working with WE8MSWIN1252 (common on western European Windows Databases). Entering the following name into the customer table is safe:

INSERT INTO customer (custid, lastname)
VALUES
(1,’Müller’);
COMMIT;

If you try to enter the same row in an Oracle database with AL32UTF8 you get the following error message:

ERROR at line 2:
ORA-12899: value too large for column "DEMO"."TEST1"."LASTNAME" (actual: 7, maximum: 6)

The reason is that a German umlaut takes two bytes in Unicode as well as most special characters for other languages.

When using Oracle export/import or datapump the situation doesn’t change because both utilities are using the exported format while importing into an new database.

The only chance is to first create the objects and then do the import.

Toad has a very nice switch under options -> data types which is called “Include Byte/Char spec when creating DDL scripts from 9i databases”. This behavior is not limited to 9i but all existing Oracle versions you can switch off the “BYTE or CHAR” specification for all scripts.

Now you can generate the schema script (under menu export) for all schemas you want to recreate and execute a user export or full export of the database.

On your target you first create the user (maybe with CREATE LIKE…) using Toad and load your generated schema script. Before you execute it ,please add one command to the script:

ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';

This will switch the actual session into a mode where the default length semantic is now CHAR instead of BYTE. This works for a session as well as for the database but there are several known bugs if you switch it for the database so better only use it for the actual session.

The test table is now created like:

CREATE TABLE test1
(custid   NUMBER(10),
lastname VARCHAR2(6 CHAR));

which allows you to insert the lastname “Müller” now.

After all objects are successfully created you can run the import for the table data but don’t forget to switch on “IGNORE ERRORS” as the “CREATE TABLE” scripts are included in the export file.