Oracle 11g New Features


Database Replay
Partitioning Updates
SQL Performance Analyzer (SPA)
SQL Plan Management
Memory Management
SQL Access Advisor
Automatic Health Monitor

SKIP LOCKED Tables
READ ONLY Tables
Virtual Columns
Result Cache
PL/SQL Function Result Cache
Dataguard Enhancements
Default Value Alter Table
Sequence Assignment
Continue Statement
IGNORE_ROW_ON_DUPKEY_INDEX Hint
Improvements in MERGE Trigger Firing Order
Invisible Indexes
Query Rewrite More Often
Dynamic Cursor and REF CURSOR Interchangeability
Pivot Table
Transaction Management with LogMiner and Flashback Data Archive
Backup and Recovery (Recovery Advisor)
Duplicate Database from Backup (11g R2 Only)
Undrop a Tablespace (11gR2 Only)
Set NEWNAME Flexibility (11gR2 Only)
Backup (RMAN) TO DESTINATION Clause (11gR2 Only)
ADR Home
New Alert Log File
LOBs and Secure Files
Datapump
Online Index Rebuild
Different Tablespaces for Temporary Tables
SQL*Plus Error Logging
Shrink Temporary Tablespace
Scheduler Email Notification (Release 11gR2 Only)



New features in Fault Management:
* Automatic Diagnostic Repository (ADR) - When critical errors are detected, they automatically create an “incident”. Information relating to the incident is automatically captured, the DBA is notified and certain health checks are run automatically. This information can be packaged to be sent to Oracle support (see following).
* Incident Packaging Service (IPS) - This wraps up all information about an incident, requests further tests and information if necessary, and allows you to send the whole package to Oracle Support.
* Feature Based Patching - All one-off patches will be classified as to which feature they affect. This allows you to easily identify which patches are necessary for the features you are using. EM will allow you to subscribe to a feature based patching service, so EM automatically scans for available patches for the features you are using.

New features in Performance and Resource Management:
* Automatic SQL Tuning - The 10g automatic tuning advisor makes tuning suggestions in the form of SQL profiles that will improve performance. You can tell 11g to automatically apply SQL profiles for statements where the suggested profile give 3-times better performance that the existing statement. The performance comparisons are done by a new administrative task during a user-specified maintenance window.
* Access Advisor - The 11g Access Advisor gives partitioning advice, including advice on the new interval partitioning. Interval partitioning is an automated version of range partitioning, where new equally-sized partitions are automatically created when needed. Both range and interval partitions can exist for a single table, and range partitioned tables can be converted to interval partitioned tables.
* Automatic Memory Tuning - Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter. You literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set.
* Resource Manager - The 11g Resource Manager can manage I/O, not just CPU. You can set the priority associated with specific files, file types or ASM disk groups.
* ADDM - The ADDM in 11g can give advice on the whole RAC (database level), not just at the instance level. Directives have been added to ADDM so it can ignore issues you are not concerned about. For example, if you know you need more memory and are sick of being told it, you can ask ADDM not to report those messages anymore.
* AWR Baselines - The AWR baselines of 10g have been extended to allow automatic creation of baselines for use in other features. A rolling week baseline is created by default.
* Adaptive Metric Baselines - Notification thresholds in 10g were based on a fixed point. In 11g, notification thresholds can be associated with a baseline, so the notification thresholds vary throughout the day in line with the baseline.


Enhancements in Oracle 11g PL/SQL
   1. DML triggers are up to 25% faster. This especially impacts row level triggers doing updates against other tables (think Audit trigger).
   2. Fine Grained Depenancy Tracking (FGDT?). This means that when you add a column to a table, or a cursor to a package spec, you don't invalidate objects that are dependant on them. Sweet!
   3. Native Compilation no longer requires a C compiler to compile your PL/SQL. Your code goes directly to a shared library. I will definately talk more about this later.
   4. New Data Type: simple_integer. Always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER.
   5. Intra-unit inlining. In C, you can write a macro that gets inlined when called. Now any stored procedure is eligible for inlining if Oracle thinks it will improve performance. No change to your code is required. Now you have no reason for not making everything a subroutine!
   6. SQL and PL/SQL result caching. Yep, you read that right. Now there is a result cache that can store actual results from queries and stored procedures. Later calls are almost instaneous. Another SWEET on this one.
   7. Compound triggers. How would you like a trigger that is a before, after, row and statement all in one? And it maintains its statements between each? Now you have it.
   8. Dynamic SQL. DBMS_SQL is here to stay. It's faster and is being enhanced. DBMS_SQL and NDS can now accept CLOBs (no more 32k limit on NDS). A ref cursor can become a DBMS_SQL cursor and vice versa. DBMS_SQL now supprts user defined types and bulk operations. Sweet!
   9. FGAC for UTL_SMTP, UTL_TCP and UTL_HTTP. You can define security on ports and URLs.
  10. Support for SUPER references in Oracle Object Type methods. Sweet again!
  11. Read only tables
  12. Specify Trigger firing order.
  13. Compiler warning for a wehn others with no raise.
  14. Still no identity column!
  15. Continue statement is added. Personally, I find the continue to be too much like a goto.
  16. You can now used named notation when calling a stored procedure from SQL.



Database Replay
The new Database Replay tool works like a DVR inside the database. Using a unique approach, it faithfully captures all database activity beneath the level of SQL in a binary format and then replays it either in the same database or in a different one (which is exactly what you would want to do prior to making a database change). You can also customize the capture process to include certain types of activity, or perhaps exclude some.
Database Replay delivers half of what Oracle calls Oracle Database 11g's Real Application Testing (RAT) option; the other half is provided by another tool, SQL Performance Analyzer. The main difference between these two tools is the scope involved: whereas Database Replay applies to the capture and replay of all (subject to some filtering) activities in a database, SQL Performance Analyzer allows you to capture specific SQL statements and replay them. (You can't see or access specific SQLs captured in Database Replay, while in SQL Performance Analyzer you can.) The latter offers a significant advantage for SQL tuning because you can tweak the SQL statement issued by an application and assess its impact. (SQL Performance Analyzer is covered in a forthcoming installment in this series.)
For example, you can capture selectively—for specific users, programs, and so on—and you can specify a time period when the workload is captured, you can replay specific workloads that cause you problems, not the entire database.
For instance, you notice that the month-end interest calculation program is causing issues and you suspect that changing a parameter will ease the process. All you have to do is capture the workload for the duration the month-end program runs, make the change in parameter on a test system, and then replay the capture files on that test system. If the performance improves, you have your solution. If not, well, it's only a test system. You didn't impede the operation of the production database.

Here is the Step-by-Step Guide:

http://www.oracle.com/technetwork/articles/sql/11g-replay-099279.html



Partitioning Upgrades
Oracle Database 10g made a few important improvements to partitioned tables and indexes (e.g. hash-partitioned global indexes), but Oracle Database 11g dramatically expands the scope of partitioning with several new composite partitioning options: Range Within Range, List Within Range, List Within Hash, and List Within List. And that’s not all:

Interval Partitioning. One of the more intriguing new partitioning options, interval partitioning is a special version of range partitioning that requires the partition key be limited to a single column with a datatype of either NUMBER or DATE. Range partitions of a fixed duration can be specified just like in a regular range partition table based on this partition key. However, the table can also be partitioned dynamically based on which date values fall into a calculated interval (e.g. month, week, quarter, or even year). This enables Oracle Database 11g to create future new partitions automatically based on the interval specified without any future DBA intervention.
Partitioning On Virtual Columns. The concept of a virtual column – a column whose value is simply the result of an expression, but which is not stored physically in the database – is a powerful new construct in Oracle Database 11g. It’s now possible to partition a table based on a virtual column value, and this leads to enormous flexibility when creating a partitioned table. For example, it’s no longer necessary to store the date value that represents the starting week date for a table that is range-partitioned on week number; the value of week number can be simply calculated as a virtual column instead.
Partitioning By Reference. Another welcome partitioning enhancement is the ability to partition a table that contains only detail transactions based on those detail transactions’ relationships to entries in another partitioned table that contains only master transactions. The relationship between a set of invoice line items (detail entries) that corresponds directly to a single invoice (the master entry) is a typical business example. Oracle Database 11g will automatically place the detail table’s data into appropriate subpartitions based on the foreign key constraint that establishes and enforces the relationship between master and detail rows in the two tables. This eliminates the need to explicitly establish different partitions for both tables because the partitioning in the master table drives the partitioning of the detail table.
Transportable Partitions. Finally, Oracle Database 11g makes it possible to transport a partitioned table’s individual partitions between a source and a target database. This means it’s now possible to create a tablespace version of one or more selected partitions of a partitioned table, thus archiving that partitioned portion of the table to another database server.

Here is the Step-by-Step Guide:
http://www.oracle.com/technetwork/articles/sql/11g-partitioning-084209.html



SQL Performance Analyzer (SPA)
As we saw before, the Database Replay it's a great tool for capturing the real workload in your database and replay them at will. Database Replay is part of what Oracle calls the Real Application Testing option, with the emphasis on the word "real" the workload replayed is actually what occurred in your database.

In Database Replay, the entire captured workload is replayed against the database. But what if you don't want to do that? For example, you may be interested in understanding how SQL execution plans and therefore SQL performance might be affected by a change, because they can severely affect application performance and availability. In addition, Database Replay replays only what has been captured; not anything else. You may be interested to know the impact of parameter changes on some SQLs that has not been executed in production yet.

This area is where the other important component of the Real Application Testing family - SQL Performance Analyzer (SPA) - shines. SPA allows you to play out some specific SQL or your entire SQL workload against various types of changes such as initialization parameter changes, optimizer statistics refresh, and database upgrades, and then produces a comparison report to help you assess their impact. In this installment, you will learn how to use this tool to answer that important question.

A Sample Problem
Let's go on a test drive. First, let's define the problem you are trying to solve.

The problem is a typical one: Oracle is not using an index, and you want to know why not. To answer that question, I turned to the classic paper by the Oracle guru Tim Gorman, "Searching for Intelligent Life in Oracle's CBO." (You will find versions of this paper in many forms all over the Web.)
One of Tim's suggestions is to change the value of the parameter optimizer_index_cost_adj from the default 100 to something smaller. The paper also gives a formula to calculate what the value should be. Based on that formula, I calculated the value to be 10, in my case. But here comes a difficult question: Is that change going to be beneficial for every SQL statement?

In releases prior to Oracle Database 11g, I have to capture all SQL statements, run them through tracing, and get execution plans—a task not only enormously time consuming but error prone as well. With the new release, I don't need to do that; instead, I have the very simple and effective SQL Performance Analyzer.

Full Example here:
http://www.oracle.com/technetwork/articles/sql/11g-spa-092706.html



SQL Plan Management
How many times you have seen this situation: A query has the best possible plan but suddenly something happens to throw the plan off? The "something" could be that someone analyzed the table again or some optimizer influencing parameters such as star_transformation is changed—the list of possibilities is endless. Out of frustration you may clamp down on any changes on the database, meaning no database stats collection, no parameter changes, and so on.
But that's easier said than done. What happens when the data pattern changes? Take, for instance, the example shown in the section on Adaptive Cursors. The CUSTOMERS table is now filled with customers from New York; so the STATE_CODE is mostly "NY". So when a query with a predicate as shown below is executed:
where state_code = 'NY'

the index scanning does not occur; instead the system does a full table scan. When the predicate is:
where state_code = 'CT'
...

the index is used since it will return few rows. However, what happens if the pattern changes—say, suddenly there are a lot more customers from Connecticut (state_code = 'CT'); so much more so that the percentage of CT now jumps to 70%. In that case the CT queries should use full table scans. But as you have stopped collecting optimizer stats, the optimizer will not know about the change in pattern and will continue to derive an index scan path which is inefficient. What can you do?
What if Oracle used the optimal plan but reevaluated that plan when underlying factors such as stats collection or database parameters change, at which point it used the new plan if and only if the new plan is better? That would be splendid, wouldn't it? Well, it's possible in Oracle Database 11g. Let's see how.

SQL Plan Baselining
In Oracle Database 11g, when an already calculated optimizer plan needs to be updated because of changes in the underlying factors, it does not go into effect immediately. Rather Oracle evaluates the new plan and implements it in only if improves on the one already there. In addition, tools and interfaces are available to see the history of the plans calculated for each query and how they compare.
The life cycle starts with Oracle identifying a statement as one that is executed more than once, or "repeatable". Once a repeatable statement is identified, its plan is captured and stored as a SQL Plan Baseline, in the database in a logical construct known as SQL Management Base (SMB). When a new plan is calculated for this query for whatever reason, the new plan is also stored in the SMB. So the SMB stores each plan for the query, how it was generated, and so on.
The plans are not stored in SMB automatically. If that were the case, the SMB would hold plans of every type of query and become huge. Instead, you can and should control how many queries go into the SMB. There are two ways to do that: making all repeatable queries baselined in SMB automatically, or manually loading the queries that should be baselined
Let's look at the simple case first: you can make the SQL Plan Management feature capture SQL Plan Baselines for all repeatable queries automatically by setting a database parameter optimizer_capture_sql_plan_baselines, which is by default FALSE, to TRUE. Fortunately, this is a dynamic parameter.
SQL> alter system optimizer_capture_sql_plan_baselines = true;

After this statement is executed, the execution plans for all repeatable statements are stored as SQL Plan Baselines in the SMB. The SQL Plan Baselines are stored in the view called DBA_SQL_PLAN_BASELINES. You can also see it in the Enterprise Manager. To examine the baselined plans, bring up EM and click on the tab "Server" as shown in figure below:

 

Figure 1


From this page, click SQL Plan Control in the section Query Optimizer, which brings up the main SPM page shown below:

 

Figure 2


Click the SQL Plan Baseline tab, which brings up a screen similar to as shown below:

 

Figure 3


This is the main SQL Plan Baseline screen. At the top left corner, you will see the configuration parameters. It shows Capture SQL Plan Baselines as TRUE, which is what you enabled with the ALTER SYSTEM command. Below that is the Use SQL Plan Baselines set toTRUE (the default). It indicates that SQL Plan Baselines are to be used for a query if one is available.
Whenever a new plan is generated for the query, the old plan is retained in the history in the SMB. However, it also means that the SMB will be crowded with plan histories. A parameter controls how many weeks the plans are retained for, which is shown in the text box against Plan Retention (Weeks). In this screen it shows as set to 53 weeks. If a SQL Plan Baseline has not been used for 53 weeks it will be purged automatically.
The middle part of the screen has a search box where you can search for SQL statements. Enter a search string here and press Go, you will see the SQL statements and associated plans as shown in the figure above. Each baselined plan has a lot of status information associated with it. Let's see what they are: The same information and more is also available in the view DBA_SQL_PLAN_BASELINES: 
If you click the plan name, it will show you the plan details. Here is an output:

 

Figure 4


In the details you can see the explain plan of the query, along with the other relevant details such as whether the plan is accepted, enabled, fixed, and so on. Another important attribute is "Origin", which shows AUTO-CAPTURE—meaning the plan was captured automatically by the system because optimizer_capture_sql_plan_baselines was set to TRUE.
Click Return to get back to the list of plans as shown in the previous figure. Now select a plan whose status is not accepted and clickEvolve to see if the plan should be examined for a potentially better plan. The following screen comes up.

 

Figure 5


The important point to note in this screen is the Verify Performance radio button. If you want to examine the plans and compare its performance to that of the existing SQL Plan Baseline for the query, you should select that. Click OK. This shows the report of the comparison:
-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  PLAN_LIST  = SYS_SQL_PLAN_b5429522ee05ab0e
               SYS_SQL_PLAN_b5429522e53beeec
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_b5429522e53beeec
-----------------------------------
  It is already an accepted plan.

Plan: SYS_SQL_PLAN_b5429522ee05ab0e
-----------------------------------
  Plan was verified: Time used 3.9 seconds.
  Failed performance criterion: Compound improvement ratio <= 1.4.

                      Baseline Plan      Test Plan     Improv. Ratio
                            -------------          ---------           -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):            3396            440              7.72
  CPU Time(ms):                1990            408              4.88
  Buffer Gets:                 7048           5140              1.37
  Disk Reads:                  4732             53             89.28
  Direct Writes:                  0              0
  Fetches:                     4732             25            189.28
  Executions:                     1              1

This is a pretty good comparison report that shows how the plans compare. If a specific plan is shown to perform better, then the optimizer will use it. If the new plan does not show an appreciable performance improvement, then it should not be accepted and be used. SQL Performance Management allows you to see first hand how the plans compare and use the ones that are truly better.
You can change the accepted status of a plan manually by executing the DBMS_SPM package:
declare
   ctr binary_integer;
begin
   ctr := dbms_spm.alter_sql_plan_baseline (
      sql_handle      => 'SYS_SQL_e0b19f65b5429522',
      plan_name       => 'SYS_SQL_PLAN_b5429522ee05ab0e',
      attribute_name  => 'ACCEPTED',
      attribute_value => 'NO'
   );
end;

You can disable a SQL Plan Baseline so that it does not get used by the optimizer. Later you can re-enable the plan so that it gets used again. To disable, use this:
declare
   ctr binary_integer;
begin
   ctr := dbms_spm.alter_sql_plan_baseline (
      sql_handle      => 'SYS_SQL_e0b19f65b5429522',
      plan_name       => 'SYS_SQL_PLAN_b5429522ee05ab0e',
      attribute_name  => 'ENABLED',
      attribute_value => 'NO'
   );
end;

When a specific SQL statement's plan is fixed by a baseline, the explain plan shows it clearly. At the end of the plan you will see a line that confirms that the plan has been fixed by a baseline.



Memory Management
Deep down, an Oracle Database Instance is a collection of processes such as PMON, SMON, and memory areas such as System Global Area (SGA) and Program Global Area (PGA). Within the SGA, there are several sub areas such as database cache, the large pool and so on. How do you decide how big each area should be? Memory in the host is bounded at an upper limit, of which some parts must go to the Operating System. Deciding how much to allocate where could be tough.

In Oracle Database 10g, the issue is greatly simplified by setting a value for SGA_TARGET, which allocates a specified value for the overall SGA memory area. The sub-areas such as cache and shared pool are subsequently auto-tuned.

However, in 10g some components, such as db_cache_keep_size, are not; you still have to manually tune them. The memory area PGA is entirely outside the SGA, so the former is not touched by the Automatic SGA Management feature at all. So, you still have to make a few decisions, such as the sizes of SGA and of PGA.

What happens if you allocate too much to PGA, starving the SGA? Well, you waste memory while making performance suffer due to undersized SGA. But what if the boundary between PGA and SGA were fluid and the memory allowed to flow freely between them as and when needed? That would be a very desirable feature indeed.

In Oracle Database 11g, that precise functionality is provided. Instead of setting SGA_TARGET and PGA_AGGREGATE_TARGET, you specify MEMORY_TARGET. From the Enterprise Manager Database homepage, choose Advisor Central > Memory Advisor and then click Enable to enable Automatic Memory Management. Finally click OK. You will need to restart the database, as the parameter for Max Memory Target is a static one.

You can also do this via the command line:
SQL> alter system set memory_max_target = 1G scope=spfile;
SQL> alter system set memory_target = 1G scope = spfile;
 
In UNIX-based systems, the max memory should be less than the size of the /dev/shm shared memory file system. This functionality has been implemented in Linux, Solaris, HPUX, AIX and Windows.

[oracle@oradba3 dbs]$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      36316784  19456684  15015264  57% /
/dev/hda1               101086      9632     86235  11% /boot
none                    517448    131448    386000  26% /dev/shm

This example shows that you have only about 500MB available, so you will use 404MB as the MEMORY_TARGET.

This allocation keeps changing as the apps demands from the database. This relieves you from managing memory, which may be a best-case guess anyway.
When the demand for the memory goes up and down, the memory areas are shrunk and expanded. You can check it from the view V$MEMORY_DYNAMIC_COMPONENT.

set linesize 150
select substr(component,1,20) component, current_size, min_size, max_size,
       user_specified_size user_spec, oper_count, last_oper_type,
       last_oper_time
from v$memory_dynamic_components
where current_size != 0;

Here is the output:
COMPONENT            CURRENT_SIZE   MIN_SIZE   MAX_SIZE  USER_SPEC OPER_COUNT LAST_OPER_TYP LAST_OPER_TIME
-------------------- ------------ ---------- ---------- ---------- ---------- ------------- ------------------
shared pool             721420288  654311424  738197504          0         17 GROW          27/SEP/13 22:01:19
large pool               16777216   16777216   16777216          0          0 STATIC
java pool                16777216   16777216   16777216          0          0 STATIC
streams pool             16777216   16777216   16777216          0          0 STATIC
SGA Target             1224736768 1224736768 1224736768          0          0 STATIC
DEFAULT buffer cache    436207616  419430400  503316480          0         17 SHRINK        27/SEP/13 22:01:19
PGA Target              637534208  637534208  637534208          0          0 STATIC

Another very useful view is v$memory_resize_ops, which stores the information about the resize operations that occurred.
select start_time, end_time, status,
       substr(component,1,20) Component,
       oper_type Op, oper_mode,
       substr(parameter,1,20) Parameter,
       initial_size, target_size, final_size
from v$memory_resize_ops
where final_size != 0
order by 1,2;


SQL Access Advisor

Oracle Database 10g offers an avalanche of helpers—or "advisors"—which help you decide the best course of action. One example is SQL Tuning Advisor, which provides recommendations on query tuning, lengthening the overall optimization process a bit in the process.
But consider this tuning scenario: Say an index will definitely help a query but the query is executed only once. So even though the query will benefit from it, the cost of creating the index will outweigh that benefit. To analyze the scenario in that manner, you would need to know how often the query is accessed and why.
Another advisor—SQL Access Advisor—performs this type of analysis. In addition to analyzing indexes, materialized views, and so on as it does in Oracle Database 10g, in Oracle Database 11g SQL Access Advisor also analyzes tables and queries to identify possible partitioning strategies—a great help when designing optimal schema. In Oracle Database 11g SQL Access Advisor can now provide recommendations with respect to the entire workload, including considering the cost of creation and maintaining access structures.
In this installment you will see how a typical problem is solved by the new SQL Access Advisor. (Note: For the purposes of this demo a single statement will illustrate this functionality; however, Oracle recommends that SQL Access Advisor be used to help tune your entire workload, not just one SQL statement.)

The Problem

Here's a typical problem. The SQL statement below is issued by the application. The query seems resource intensive and slow.
select store_id, guest_id, count(1) cnt
from res r, trans t
where r.res_id between 2 and 40
and t.res_id = r.res_id
group by store_id, guest_id;

This SQL touches two tables, RES and TRANS; the latter is a child table of the former. You have been asked to find solutions to improve query performance—and SQL Access Advisor is the perfect tool for the job.

You can interact with the advisor either via command line or Oracle Enterprise Manager Database Control, but using the GUI provides somewhat better value by letting you visualize the solution and reducing many tasks to simple pointing and clicking. To solve the problem in the SQL using SQL Access Advisor in Enterprise Manager, follow the steps below.

  1. The first task is, of course, to fire up Enterprise Manager. On the Database Homepage, scroll down to the bottom of the page where you will see several hyperlinks, as shown in the figure below:

     

    Figure 1

  2. From this menu, click on Advisor Central, which brings up a screen similar to that below. Only the top portion of the screen is shown.

     

    Figure 2

  3. Click on SQL Advisors, which brings up a screen similar to that below:

     

    Figure 3

  4. In this screen, you can schedule a SQL Access Advisor session and specify its options. The advisor must gather some SQL statements to work with. The simplest option is to get them from the shared pool, via Current and Recent SQL Activity. Choosing this option allows you to get all SQL statements cached in the shared pool for analysis.
    However, in some cases you may not want all the statements in the shared pool; just a specific set of them. To do so, you need to create a "SQL Tuning Set" on a different screen and then refer to the set name here, in this screen.
    Alternatively, perhaps you want to run a synthetic workload based on a theoretical scenario you anticipate to occur. These types of SQL statements will not be present in the shared pool, as they have not executed yet. Rather, you need to create these statements and store them in a special table. In the third option ( Create a Hypothetical Workload...), you need to supply the name of this table along with the schema name.
    For the purpose of this article, assume you want to take the SQLs from the shared pool. So, choose the first option as shown in the screen, which is default.
  5. However, you may not want all the statements, just some key ones. For instance, you may want to analyze the SQL executed by the user SCOTT, which is the application user. All other users probably issue ad hoc SQL statements and you want to exclude them from your analysis. In that case, click on the "+" sign just before Filter Options as shown in the figure below.

     

    Figure 4

  6. In this screen, enter SCOTT in the text box where it asks to enter the users, and choose the radio button Include only SQL...(default). Similarly, you can exclude some users. For instance, you may want to capture all activity in the database except the users SYS, SYSTEM ,and SYSMAN. You will enter these users in the text box and click the button Exclude all SQL statements... .
  7. You can filter on tables accessed in statements, on Module Ids, Actions, and even specific strings in the SQL statements. The idea is to confirm that only the statements of interest are analyzed. Choosing a small subset of the entire SQL cache makes the analysis faster. In this example, we assumed there is only one statement issued by the user SCOTT. If that is not the case you can put additional filtering conditions to reduce the analyzed set to only one SQL, the one mentioned in the original problem statement.
  8. Click Next. This brings up a screen shown below (only top portion shown):

     

    Figure 5

  9. In this screen you can specify what types of recommendations should be searched. For instance, in this case, we want the advisor to look at potential indexes, materialized views, and partitioning, so check all the boxes next to these terms. For Advisor Mode, you have a choice; the default, Limited Mode, acts on only high-cost SQL statements. It will be faster, of course, and will yield better bang for the buck. For analysis of all SQL, use the Comprehensive Mode. (In this example the choice of modes is irrelevent because you have only one SQL.)
  10. The bottom half of the screen shows advanced options such as how the SQL statements should be prioritized, the tablespaces used, and so on. You can leave the defaults as marked (more on those later).Click Next, which bring up the scheduling screen. Choose Run Immediately and click Next.
  11. Click Submit. This creates a Scheduler job. You can click on the job hyperlink shown in this screen, at the top of the page. The job will be shown as Running.
  12. Click Refresh repeatedly until you see the value under the column Last Run Status change to SUCCEEDED.
  13. Now go back to the Database Homepage and click on Advisor Central as you did in Step 1. Now you will see the SQL Access Advisor row as shown in the figure below:

     

    Figure 6

  14. This screen indicates that the SQL Access Advisor task is COMPLETED. Now click on the button View Result. The screen is shown below:

     

    Figure 7

  15. This screen says it all! SQL Access Advisor analyzed the SQL statement and found some solutions that can improve query performance tenfold. To see what specific recommendations were made, click on the Recommendations tab, which brings up a details screen as shown below.

     

    Figure 8

  16. This screen has a lot of good information, at a slightly higher level. For instance, for the statement with ID = 1 there are two recommended actions, under column Actions. The following column, Action Types, shows the types of actions, indicated by colored squares. From the icon guide just below it, you can see that the actions were on indexes and partitions. Together they improve performance by several orders of magnitude.
    To see exactly what SQL statement can be improved, click on the ID, which brings up the screen below. Of course, this analysis had only one statement so only one showed up here. If you had more than one, you would see them all.

     

    Figure 9

  17. On the screen above note the column Recommendation ID. Click on the hyperlink, which brings up the detailed recommendations as shown below:

     

    Figure 10

  18. The screen offers a very clear description of the solutions. It has two recommendations: to create the table as partitioned and to use an index. Then it finds that the index is already present so it advises to retain the index.
    If you click on PARTITION TABLE under the column Action, you will see the actual script Oracle will generate to make this a partitioned table. Before you click, however, fill in the tablespace name in the text box. This allows SQL Access Advisor to use that tablespace while building this script:
    Rem 
    Rem Repartitioning table "SCOTT"."TRANS"
    Rem 
    
    SET SERVEROUTPUT ON
    SET ECHO ON
    
    Rem 
    Rem Creating new partitioned table
    Rem 
    CREATE TABLE "SCOTT"."TRANS1" 
    (    "TRANS_ID" NUMBER, 
        "RES_ID" NUMBER, 
        "TRANS_DATE" DATE, 
        "AMT" NUMBER, 
        "STORE_ID" NUMBER(3,0)
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    TABLESPACE "USERS" 
    PARTITION BY RANGE ("RES_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000)
    );
    
    begin
    dbms_stats.gather_table_stats('"SCOTT"', '"TRANS1"', NULL, dbms_stats.auto_sample_size);
    end;
    /
    
    Rem 
    Rem Copying constraints to new partitioned table
    Rem 
    ALTER TABLE "SCOTT"."TRANS1" MODIFY ("TRANS_ID" NOT NULL ENABLE);
    
    Rem 
    Rem Copying referential constraints to new partitioned table
    Rem 
    ALTER TABLE "SCOTT"."TRANS1" ADD CONSTRAINT "FK_TRANS_011" FOREIGN KEY ("RES_ID")
         REFERENCES "SCOTT"."RES" ("RES_ID") ENABLE;
    
    Rem 
    Rem Populating new partitioned table with data from original table
    Rem 
    INSERT /*+ APPEND */ INTO "SCOTT"."TRANS1"
    SELECT * FROM "SCOTT"."TRANS";
    COMMIT;
    
    Rem 
    Rem Renaming tables to give new partitioned table the original table name
    Rem 
    ALTER TABLE "SCOTT"."TRANS" RENAME TO "TRANS11";
    ALTER TABLE "SCOTT"."TRANS1" RENAME TO "TRANS";
    
  19.  The script actually builds a new table and then renames it to match the original table.
  20. The final tab, Details, show some interesting details on the task. Although they are not important for your analysis, they can offer valuable clues about how the advisor arrived at those conclusions, which can help your own thinking process. The screen has two parts, the first part being Workload and Task Options, shown below.

     

    Figure 11
  21.  The lower half of the screen shows a sort of run-log of the task. Sometimes the advisor cannot process all SQL statements. If some SQL statements are discarded, it shows up here, against the Invalid SQL String: Statements discarded count. If you are wondering how why only a few of the several SQL statements were analyzed, here lies the reason.

     

    Figure 12
Advanced Options
In Step 10 above, I made a reference to advanced settings. Let's see what these settings do.
Click on the plus sign just left of Advanced Options, which brings up a screen as shown below:

 

Figure 13


This screen allows you to enter the name of the tablespace where the index will be created, which schema it will be created on, and so on. For partitioning advice, you can specify the tablespace the partitions will go to and so on.
Perhaps the most important element is the checkbox Consider access structures creation costs recommendations. If you check this box, SQL Access Advisor will take into consideration the cost of the creation of the index itself. For example, should 10 new indexes potentially be involved, the associated cost may lead SQL Access Advisor to not advise their creation.
You can also specify the maximum size of the index in this screen.

Differences vs. SQL Tuning Advisor
In the introduction I only briefly described how this tool differs from SQL Tuning Advisor, so let's go into more detail here. A quick demo will best explain the differences.
From the SQL Advisors screen, choose SQL Tuning Advisor and run it. After it is completed, here is part of the screen that shows the results:

 

Figure 14


Now if you click View to examine the recommendations, it brings up a screen as shown below:

 

Figure 15


Examine the recommendation carefully: it is to create an index on TRANS on the column RES_ID. SQL Access Advisor, however, did not make that specific recommendation. Instead it recommended to partition the table, the reason being: Based on the access patterns and the data available, SQL Access Advisor determined that partitioning is more efficient than building an index on the column. This is a more "real world" recommendation than that provided by SQL Tuning Advisor.
The recommendations made by SQL Tuning Advisor map to one of four objectives: These recommendations are with respect to only a single statement, not the entire workload. Therefore SQL Tuning Advisor should be used on high-load or business critical queries occasionally. Note that this advisor only recommends indexes that offer significant improvement in performance, compared to SQL Access Advisor, which may have more lenient standards. And of course, the former does not have a partition advisor in it.


Use Cases
SQL Access Advisor is useful for tuning schemas, not just queries. As a best practice, you can use this strategy in developing an effective SQL tuning plan:
  1. Search for high-cost SQL statements, or better yet, evaluate the entire workload.
  2. Put suspect statements into a SQL Tuning Set.
  3. Analyze them using both SQL Tuning Advisor and SQL Access Advisor.
  4. Get the results of the analysis; note the recommendations.
  5. Plug the recommendations into SQL Performance Analyzer (see this installment).
  6. Examine the before-and-after changes in SQL Performance Analyzer and arrive at the best solution.
  7. Repeat the tasks until you get the optimal schema design.
  8. When you have the best schema design, you may want to lock the plan using SQL Plan Management baselines (described in this installment).



Automatic Health Monitor
How do you know if your database is humming along smoothly? Well, one way is to check "everything"—a rather time-consuming and error-prone process. In some shops dedicated DBAs perform the same task over and over to assess and report the health of the database, but most can't afford to hire full time staff for this effort. The alternative is to have the regular DBA staff perform health checks, but the result is usually not very encouraging. Dividing one's attention across too many things can lead to missing something potentially dangerous.

In Oracle Database 11g the effort has become somewhat simpler with the introduction of the Automatic Health Monitor. Similar to the Advisors introduced in Oracle Database 10g, the Automatic Health Monitor "checkers" monitor (automatically after a failure or on demand) various components, such as the datafiles and dictionary, to make sure they are not corrupted physically or logically. When the checkers find something, the information is reported and then may be fed to various recovery advisors. At a minimum, the new Incident Packaging Service (described later) allows you to make a bundle of all issues and supporting files for easier reporting to Oracle Support.

Like many other features of Oracle Database 11g, this process can be managed either from the command line or via the Oracle Enterprise Manager GUI. Here you'll see how it's done with the latter.
On the main Database page, scroll all the way down to the section marked Related Links as shown below.


From this list of hyperlinks, click on Advisor Central, which brings up the Advisors and Checkers screen. Click on the tab labeledCheckers. The top portion of the screen is shown below. 


This is a very important screen that shows the multiple checkers available as well as the automated checker runs that have been executed.
First, let's focus on the multiple checkers available.

DB Structure Integrity Check.
 This checker is best explained through an example. First click on DB Structure Integrity Checks, which brings up a small screen where you can name the run as "DB_Struct_Int1", as an example. The other input you make here is to time-limit the run, which you may ignore to effectively state that there is no limit.

After the health check runs successfully, the confirmation comes up as shown in the top portion of the screen, shown below:


The lower portion of the screen also shows the run that was just made. The name is what you entered earlier: DB_Struct_Int1. The important difference is the column Run Type, which shows "Manual" for this run, as opposed to "Reactive" for others. You can choose this run by ticking the radio button to the left and then clicking on the button labeled Details. The resulting screen shows the details of the run, such as what type of damage was detected and so on. 

In this case, a datafile was somehow corrupted and this checker will identify that. The information is then fed to the Data Recovery Advisor to take appropriate action. You can invoke this checker any time to check the datafile's integrity.

Your most popular checker will most likely be this one. On the screen that shows the past runs, choose any run of that type and click on the Details button, and you will see the screen as shown below:


This screen shows all the findings on this issue: datafile #7 has become corrupt. You can launch the Recovery Advisor if you wish to get advice on what needs to be done next. 

Data Block Integrity Checker. Data Block Integrity Checker is similar to DB Structure Integrity Check but checks only specific blocks rather than the entire file. As previously, you give it a name and other relevant details. Here is what the screen looks like:


Note that you have to enter the datafile number and the block number. (I entered 7 and 20 respectively.) After entering the details, pressOK. This starts the check process and the lower portion will reflect the status of the run as shown below: 


Again, if there were a problem with the block, the checker would have found it. Using the hyperlink you could navigate to the details page to learn about the issues.

Redo Integrity Check. This checker scans the contents of the redo and archive logs for accessibility and corruption.

Undo Segment Integrity Check.
 This check finds logical undo corruptions, which are sometimes identified during rollback operations. After locating an undo corruption, this check uses PMON and SMON to try to recover the corrupted transaction. If this recovery fails, then Automatic Health Monitor stores information about the corruption in V$CORRUPT_XID_LIST. Most undo corruptions can be resolved by forcing a commit.

Transaction Integrity Check.
 Transaction Integrity Check is almost identical to Undo Segment Check except that it checks only one specific transaction, which is passed to the check as an input parameter. After locating an undo corruption, this check uses PMON and SMON to try to recover the corrupted transaction. If this recovery fails, then Automatic Health Monitor stores information about the corruption in V$CORRUPT_XID_LIST. Most undo corruptions can be resolved by forcing a commit.

Dictionary Integrity Check.
 This check examines the integrity of core dictionary objects, such as tab$ and col$. It verifies the contents of dictionary entries for each dictionary object, that logical constraints on rows in the dictionary are enforced, and that parent-child relationships between dictionary objects are enforced.


Automatic Health Checks
Remember the Checkers main screen? Note the list of Checker Runs at the bottom of the page, which shows the various checker runs that have occurred and their Run Type. If you had run a checker manually, as you did earlier in this section, the Run Type would show "Manual". Checker runs listed as "Reactive" means they were run automatically when an error was detected somewhere. If the run finds something, it is recorded and you can access the findings by clicking on these hyperlinks. For instance, clicking on the first run, HM_RUN_140013, will show the details of that checker run: 


The screen clearly shows the cause of the failure. Actually there are at least two types of failure: a corrupt online redo log file as well as a datafile. The first thing you would want to do is to ask for advice by clicking the button Launch Recovery Advisor. After continuing through the wizard-based interface, you will come to a screen where the advisor directs you to perform a specific action: 


The action would be to run the SQL file reco_767216331.hm in the directory /home/oracle/diag/rdbms/odel11/ODEL11/hm. If you open the file, you will see the contents: 

begin
/*Clear the Log Group*/
execute immediate 'ALTER DATABASE CLEAR LOGFILE GROUP 3';
end;

The corrupt logfile belongs to a group that is not active so it will be fine to clear them—and that was the advice from the Recovery Advisor. If you decide to go ahead with the advice, press the Continue button and the recovery will continue. After that is completed, if you go back to Support Workbench, you will see that the redo corruption is gone but a new corruption in an archive log has been detected. 


As usual, you can launch the advisor to fix those errors.


Automatic Diagnostic Repository

When the checkers find something, they need to record it somewhere for further analysis and subsequent processing. All this metadata is recorded in a new facility called Automatic Diagnostic Repository that records all critical events, not just those detected by checkers. It's like the SYSTEM tablespace of the critical events in the database. Let's see how you can use it via the Enterprise Manager. 

From the Enterprise Manager main Database page, click on the tab named Software and Support and then click on Support Workbench, which brings up a screen similar to the one shown below.


This shows just a summary as reported by the checkers: ORA-603 errors have occurred. Click on the + sign to the left of the error and the details of those errors show up. 


If you click on the link corresponding to each incident id under the Incidents heading, you can view the incidents. For instance, take the example of the incident 14435. Clicking on the link brings up the details of the incident in a screen shown below: 


The top portion of the screen shows the details that are probably self-explanatory. The lower portion of the screen shows the supporting details of the incident such as trace files. These files are sent over to Oracle Support for analysis (but only if you use the Incident Packaging Service to package them and have Oracle Configuration Manager configured with proper credentials). Click on the eyeglass icon next to the first trace file, which brings up the trace file as shown below: 


Note how the lines in the file are parsed and presented in a very user-friendly manner. Enterprise Manager reads each line, identifies the dependent lines, and presents them properly indented. If you click on the hyperlinks in the file, you will see the raw section from the trace file. 

You can combine the incidents into one "envelope" to be sent to Oracle Support and that's what you should do next: package them. To do this, click inside the check box under Select and click on the button labeled Package. This brings up a screen similar to one shown below:


For now, ignore Custom Packaging. Click on Quick Packaging and press Continue, which brings up a screen such as the following: 


Enter all the details and press Next. The subsequent screens confirm what is being packaged, the manifest of the package, and so on. Here you see that all relevant trace files associated with the error have been identified and added to the package. This process saves you from the error-prone task of identifying the correct set of trace files for a particular error, and the tool also has the intelligence to gather trace files of related errors in the same package. This is important because trace files of all related errors are needed by Oracle to determine the root cause of the problem; the error that you decided to package may be a symptom and not the root cause. Finally, you can press Submit to send it to Oracle. Once you submit, the screen looks a bit different, as shown below: 


Note how there is an entry under the Packaged column now ("Yes"). This confirms that the incidents were packaged. Note one more section: 


This shows that the upload file was generated along with corresponding details like when it was generated, the main issue that is in the package, and so on. However the file was not uploaded to Oracle Support because you have either not installed or not configured the Configuration Manager yet. You will learn about that feature later; for now, if you click on the name, you will see more details about the package: 


The details are all self-explanatory. The incidents are all shown with hyperlinks, which, when clicked, will go to the incident page you have seen earlier. Clicking on the tab labeled Files brings you to the page that shows all the files contained in the package. A little eyeglass icon on the far right means that you can view the files by clicking on them. 


Click on the Activity Log tab now, which will show you the history of the package, when it was created, when it was sent to Oracle (if it was), and so on. 





SKIP LOCKED for locked tables
Oracle 11g introduced SKIP LOCKED clause to query the records from the table which are not locked in any other active session of the database. This looks quite similar to exclusive mode of locking. The SQL statement in the Example code queries the unlocked records from EMP table:
So this is our master table:

select empno, ename, job, sal from emp order by sal;


     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7900 JAMES      CLERK            950
      7876 ADAMS      CLERK           1100
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7934 MILLER     CLERK           1300
      7844 TURNER     SALESMAN        1500
      7499 ALLEN      SALESMAN        1600
      7782 CLARK      MANAGER         2450
      7698 BLAKE      MANAGER         2850
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7839 KING       PRESIDENT       5000

If from Session 1 I execute the following, I will be locking 5 rows with SAL higher than 1000:
SELECT empno, ename, job, sal FROM EMP where sal > 1000 and rownum <= 5 for update;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7499 ALLEN      SALESMAN        1600
      7521 WARD       SALESMAN        1250
      7566 JONES      MANAGER         2975
      7654 MARTIN     SALESMAN        1250
      7698 BLAKE      MANAGER         2850
 
Now if on Session 2 I execute this code:
SELECT empno, ename, job, sal FROM EMP where sal > 1000 and rownum <= 5 for update;

Usually that session will need to wait until the rows are commited or rollbacked from Session 1.
Instead of that, I can use the new option SKIP LOCKED. That option will search for the NEXT 5 items available for update instead of waiting for ever
SELECT empno, ename, job, sal FROM EMP where sal > 1000 and rownum <= 5 for update skip locked;


Read Only Tables
In Oracle 11g, a table can be set READ ONLY mode to restrict write operations on the table. A table can be altered to toggle over READ ONLY and READ WRITE modes. Examples:
SQL> ALTER TABLE EMP READ ONLY;
SQL> ALTER TABLE EMP READ WRITE;


Virtual Columns
Oracle 11g allows a user to create virtual columns in a table whose values are derived automatically from other actual columns of the same table. They show same behavior as other columns in the table in terms of indexing and statistics. Currently, Oracle does not support LOB and RAW values in virtual columns.
Example Syntax:

column [datatype] [GENERATED ALWAYS] AS (
)
[VIRTUAL] [( inline_constraint [,...] )]

Here, GENERATED ALWAYS and VIRTUAL are optional keywords, but included for more clarity.

A table ORDERS is created with ORDER_VAL_ANN as virtual column, whose value is derived from ORDER_VAL column of the ORDERS table. Example code :
CREATE TABLE ORDERS
(ORDER_ID NUMBER PRIMARY KEY,
ORDER_VAL NUMBER
ORDER_VAL_ANN AS (ORDER_VAL*12));


Result Cache in SQL and PL/SQL

Accessing memory is far quicker than accessing hard drives, and that will most likely be the case for next several years unless we see some major improvements in hard drive architecture. This fact gives rise to caching: the process of storing data in memory instead of disks. Caching is a common principle of Oracle database architecture, in which users are fed data from the buffer cache instead of the disks on which the database resides.

The advantage of caching is singularly visible in the case of relatively small tables that have static data—for example, reference tables such as STATES, PRODUCT_CODES, and so on. However, consider the case of a large table named CUSTOMERS that stores the customers of a company. The list is relatively static but not entirely so; the table changes rarely, when the customers are added or removed from the list.

Caching would probably provide some value here. But if you were to cache the table somehow, how would you make sure you get the correct data when something changes?

Oracle Database 11g has the answer: with the SQL Result Cache. Consider the following query. Run it to get the execution statistics and the response times:

SQL> set autot on explain stat

select state_code, count(*), min(times_purchased), avg(times_purchased)
from customers
group by state_code;

The results are:

ST   COUNT(*) MIN(TIMES_PURCHASED) AVG(TIMES_PURCHASED)
-- ---------- -------------------- --------------------
NJ          1                   15                   15
NY     994898                    0           15.0052086
CT       5099                    0           14.9466562
MO          1                   25                   25
FL          1                    3                    3
 
5 rows selected.
 
Elapsed: 00:00:02.57
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1577413243
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     5 |    30 |  1846  (25)| 00:00:23 |
|   1 |  HASH GROUP BY     |           |     5 |    30 |  1846  (25)| 00:00:23 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS |  1000K|  5859K|  1495   (7)| 00:00:18 |
--------------------------------------------------------------------------------
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5136  consistent gets
       5128  physical reads
          0  redo size
        760  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

Note a few things:
Since the table is pretty much unchanged, you can use a hint that stores the results of the query to be cached in the memory:

select /*+ result_cache */ state_code, count(*), min(times_purchased), avg(times_purchased)
from customers
group by state_code;

The query is identical to the first one except for the hint. The result (the second execution of this query):

ST   COUNT(*) MIN(TIMES_PURCHASED) AVG(TIMES_PURCHASED)
-- ---------- -------------------- --------------------
NJ          1                   15                   15
NY     994898                    0           15.0052086
CT       5099                    0           14.9466562
MO          1                   25                   25
FL          1                    3                    3
 
5 rows selected.
 
Elapsed: 00:00:00.01
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1577413243
 
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     5 |    30 |  1846  (25)| 00:00:23 |
|   1 |  RESULT CACHE       | gk69saf6h3ujx525twvvsnaytd |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     5 |    30 |  1846  (25)| 00:00:23 |
|   3 |    TABLE ACCESS FULL| CUSTOMERS                  |  1000K|  5859K|  1495   (7)| 00:00:18 |
--------------------------------------------------------------------------------------------------


Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=4; dependencies=(ARUP.CUSTOMERS); parameters=(nls);name="select /*+ result_cache */
        state_code,
        count(*),
        min(times_purchased),
        avg(times_purchased)
from customers
group by state_c"
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        760  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

Note several differences from the first case.
The savings in time is phenomenal: from 3 seconds virtually nothing! This is thanks to the fact that with the second query, where we used a cache, the results came back straight from database memory (the result cache), not after the execution of the query.

SQL Result Cache is another cache in the SGA, just like buffer cache or program global area. When you execute a query with the hint result_cache, Oracle performs the operation just like any other operation but the results are stored in the SQL Result Cache. Subsequent invocations of the same query do not actually go to the table(s) but get the results from the cache. The size of the cache is determined by several initialization parameters:

Parameter Description
result_cache_max_size Maximum size of the result cache (5M for 5 MB, for example). If you set this to 0, result caching will be completely turned off.
result_cache_max_result Specifies the percentage of result_cache_max_size that any single result can use
result_cache_mode If set to FORCE, all the queries are cached if they fit in the cache. The default is MANUAL, which indicates that only queries with the hint will be cached.
result_cache_remote_expiration Specifies the number of minutes that a cached result that accesses a remote object will remain valid. The default is 0.

Now, a logical question follows: What happens when a table row changes? Will the query get a new value or the old one? Well, let's see what happens. From another SQL*Plus session, update a row in the table:

SQL> update customers set times_purchased = 4 where state_code = 'FL';
 

but do not commit. On the original window where you ran the query the first time, run it again. The cached result is still used, because the change was not yet committed. The session that runs the query still looks at the most up-to-date version of the data and the cache is still valid.

Now, from the session where you did the update, issue a commit and run the query.

ST   COUNT(*) MIN(TIMES_PURCHASED) AVG(TIMES_PURCHASED)
-- ---------- -------------------- --------------------
NJ          1                   15                   15
NY     994898                    0           15.0052086
CT       5099                    0           14.9466562
MO          1                   25                   25
FL          1                    4                    4

Note that the data for FL updated to 4 automatically. A change in the underlying table merely invalidated the cache, which resulted in a dynamic refresh next time it was queried. You are guaranteed correct results whether or not you use the SQL Result Cache.


Subqueries
You can also use SQL Result Cache in sub-queries. Consider the following query:

select prod_subcategory, revenue
from (
   select /*+ result_cache */ p.prod_category,
      p.prod_subcategory,
      sum(s.amount_sold) revenue
   from products p, sales s
   where s.prod_id = p.prod_id
   and s.time_id between to_date('01-jan-1990','dd-mon-yyyy')
   and to_date('31-dec-2007','dd-mon-yyyy')
   group by rollup(p.prod_category, p.prod_subcategory)
)
where prod_category = 'software/other';

In the above query, the caching occurs in the sub-query in the inline view. So as long as the inner query remains the same, the outer query can change yet use the cache.

To check how much memory is used for the SQL Result Cache in the database, you can use a supplied package dbms_result_cache, as shown below:

SQL> set serveroutput on size 999999
SQL> execute dbms_result_cache.memory_report
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 2560K bytes (2560 blocks)
Maximum Result Size = 128K bytes (128 blocks)
[Memory]
Total Memory = 126736 bytes [0.041% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 121604 bytes [0.040% of the Shared Pool]
....... Overhead = 88836 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 21 blocks
........... Used Memory = 11 blocks
............... Dependencies = 4 blocks (4 count)
............... Results = 7 blocks
................... SQL     = 5 blocks (4 count)
................... Invalid = 2 blocks (2 count)
 

If you want to flush the cache (both result cache and function cache, described below) for some reason, you can use:

begin
   dbms_result_cache.flush;
end;

After you execute the above, when you run the original query against CUSTOMERS with the result_cache hint, you will see that the query again takes about three seconds to complete.

Of course, after the first execution, the results will be cached again and the subsequent executions will get the values from the result cache and hence they will execute much faster. If you want to invalidate the cache of only one table, not the entire cache, use the following:

begin
  dbms_result_cache.invalidate('ARUP','CUSTOMERS');
end;


PL/SQL Function Result Cache
Suppose you have a PL/SQL function instead of the SQL query that returns the values. It's a common practice to use a function to return a value to make the code modular. Consider a case of two tables: CUSTOMERS that store information on all customers along with the state_code. The other table TAX_RATE stores the tax rate of each state. To get the tax rate applicable to customers, you have to join the tables in a query. So, to make it simple, you decide to write a function shown below that accepts the customer ID as a parameter and returns the tax rate applicable based on state_code:

create or replace function get_tax_rate (p_cust_id       customers.cust_id%type)
return sales_tax_rate.tax_rate%type
is
        l_ret   sales_tax_rate.tax_rate%type;
begin
   select tax_rate into l_ret
      from sales_tax_rate t, customers c
      where c.cust_id = p_cust_id
        and t.state_code = c.state_code;
   -- simulate some time consuming
   -- processing by sleeping for 1 sec
   dbms_lock.sleep (1);
   return l_ret;
exception
  when NO_DATA_FOUND then
     return NULL;
  when others then
     raise;
end;
/

Execute the function a few times as shown below. Remember to set timing on to record the elapsed time in each case.

SQL> select get_tax_rate(1) from dual;
 
GET_TAX_RATE(1)
---------------
              6
 
1 row selected.
Elapsed: 00:00:01.23

SQL> select get_tax_rate(1) from dual;

GET_TAX_RATE(1)
---------------
              6
 
1 row selected.
Elapsed: 00:00:01.17

It consistently took pretty the same time for each execution. (I have deliberately placed a sleep statement to delay the processing inside the function; otherwise it would have returned too quickly.) If you examine the code you will notice that the function will most likely return the same value every time it is called. A customer does not change states that frequently and the tax rate for a state rarely changes, so for a given customer, the tax rate will be most likely the same on all executions. The rate changes if and only if the tax rate of a state changes or the customer moves out of the change. So, how about caching the results of this function?

Oracle Database 11g allows you to do exactly that. You can enable the results of a function to be cached as well, simply by placing a clause result_cache. But what about the scenario when the state actually changes the tax rate or the customer moves out of the state? The feature allows you to specify the dependence on the underlying tables so that any data change in those tables will trigger invalidation and subsequent rebuilding of the cache in the function. Here is the same function with the result cache code added (in bold):

create or replace function get_tax_rate(p_cust_id       customers.cust_id%type)
return sales_tax_rate.tax_rate%type
result_cache relies_on (sales_tax_rate, customers)
is
   l_ret   sales_tax_rate.tax_rate%type;
begin
   select tax_rate into l_ret
     from sales_tax_rate t, customers c
     where c.cust_id = p_cust_id
        and t.state_code = c.state_code;
   -- simulate some time consuming
   -- processing by sleeping for 1 sec
   dbms_lock.sleep (1);
   return l_ret;
exception
   when NO_DATA_FOUND then
     return NULL;
   when others then
     raise;
end;
/
                           
After this change, create and execute the function in the same way:

QL> select get_tax_rate(1) from dual;
 
GET_TAX_RATE(1)
---------------
              6
 
1 row selected.
 
Elapsed: 00:00:01.21

It took 1.21 seconds, as it did earlier with the non-cached way, but then look at the subsequent executions:

SQL> select get_tax_rate(1) from dual;
 
GET_TAX_RATE(1)
---------------
              6
 
1 row selected.
 
Elapsed: 00:00:00.01

The elapsed time was merely 0.01 seconds! What happened? The function executed normally the first time making the elapsed time 1.21 seconds. But the important difference this time was that it cached the results as it executed. The subsequent invocations didn't execute the function; it merely got the results from the cache. So, it didn't sleep for 1 second that was in the function code.

The cache was for the customer_id 1 only. What if you executed the function for a different customer?

SQL> select get_tax_rate(&n) from dual;
Enter value for n: 5
old   1: select get_tax_rate(&n) from dual
new   1: select get_tax_rate(5) from dual
 
GET_TAX_RATE(5)
       ---------------
              6
 
1 row selected.
 
Elapsed: 00:00:01.18
SQL> /
Enter value for n: 5
old   1: select get_tax_rate(&n) from dual
new   1: select get_tax_rate(5) from dual
 
GET_TAX_RATE(5)
       ---------------
              6
 
1 row selected.
 
Elapsed: 00:00:00.00
SQL> /
Enter value for n: 6
old   1: select get_tax_rate(&n) from dual
new   1: select get_tax_rate(6) from dual
 
GET_TAX_RATE(6)
       ---------------
              6
 
1 row selected.
 
Elapsed: 00:00:01.17

As you can see, the first time each parameter is executed, it a caches the result. The subsequent invocations retrieved the value from the cache. As you keep on executing the function for each customer, the cache builds up.

Notice the clause "relies on" in the function code. It tells the function that the cache depends on those two tables: customers and tax_rate. If the data in those tables change, the cache needs to be refreshed. The refresh happens automatically without your intervention. If the data does not change, the cache continues to provide the cached values as quickly as possible. (In Oracle Database 11g Release 2, the function knows the tables it gets the data from and automatically checks for the data change in them; the RELIES_ON clause is not necessary. The syntax still allows the table name but it is redundant.)

If you need to bypass the cache for some reason, you can call a procedure in the supplied package DBMS_RESULT_CACHE:

SQL> exec dbms_result_cache.bypass(true);
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.01

SQL> select get_tax_rate(&n) from dual;
Enter value for n: 6
old   1: select get_tax_rate(&n) from dual
new   1: select get_tax_rate(6) from dual
 
GET_TAX_RATE(6)
       ---------------
              6
 
1 row selected.
 
Elapsed: 00:00:01.18

The cache was not used, as you can see from the execution time.




DataGuard Enhancements
Oracle Database 11g adds plenty of enhancements to DataGuard:

Easier Creation of Standby Database with RMAN

Let's start at the beginning: the creation of a physical standby database. In Oracle Database 11g, that process has become a whole lot easier, with just one RMAN command that does it all. Previously, you could use the Grid Control wizard interface to build a Data Guard setup between two machines. But regardless of your experience in using SQL commands, you will find setting up a Data Guard environment in Oracle Database 11g a breeze. It's so simple that I can show you all the steps right here.

Suppose your primary database is called MYPROD running on a server called PRODSERVER. You want to set up the standby database on a server named DRSERVER. The name of the standby database instance should be MYDR. Here are the steps:

1- On PRODSERVER, first create a spfile if you don't already have one.
SQL> create spfile from pfile;
This step is not absolutely necessary but it makes the process easier. After database creation, restart the MYPROD database to use the spfile (again if needed).


2- While it is not necessary to create standby redo logs, it's a very good practice to do so. Standby redo logs enable the changes occurring in the primary database to be reflected in almost real time in the standby, a concept known as Real Time Apply (RTA). So, here we create the standby redo logs on the primary database (note carefully; the standby redo logs are created in the primary. RMAN will create them in standby):

SYS@MYPROD > alter database add standby logfile group 4 '/home/oracle/physt/sblog_g1m1.rdo' size 100m;
SYS@MYPROD > alter database add standby logfile group 5 '/home/oracle/physt/sblog_g2m1.rdo' size 100m;
SYS@MYPROD > alter database add standby logfile group 6 '/home/oracle/physt/sblog_g3m1.rdo' size 100m;
SYS@MYPROD > alter database add standby logfile group 7 '/home/oracle/prima/sblog_g1m1.rdo' size 100m;

This creates the four standby redo logs groups.


3- Create an entry for MYDR in the listener.ora file on DRSERVER server:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = MYDR)
      (ORACLE_HOME = /opt/oracle/product/11g/db1)
      (SID_NAME = MYDR)
    )
  )
 
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DRSERVER)(PORT = 1521))
  )
 
Reload the listener for it to take effect.


4- On PRODSERVER, create an entry for the MYDR database in the file tnsnames.ora under $ORACLE_HOME/network/admin:
MYDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DRSERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = MYDR)
    )
  )

 
5- On DRSERVER, in Oracle Home/dbs directory, create a file initodba11sb.ora containing just one line:
db_name=MYPROD
This will serve as the initialization file for the standby instance; the rest of the parameters will be populated automatically by the RMAN command you will see later.


6- On DRSERVER, go to the directory $ORACLE_BASE/admin. Create a directory called MYDR there and then a directory within MYDR called adump, to hold the audit files for the standby instance.


7- On PRODSERVER, under $ORACLE_HOME/dbs directory, you will find the password file for the instance, usually named orapwMYPROD. If that file is not present (most unlikely), create it. Then copy that file to DRSERVER under $ORACLE_HOME/dbs. Copy it to a new file orapwMYDR. This will make sure the sysdba connection passwords on primary database can be applied to standby as well.


8- On DRSERVER start the instance MYDR in NOMOUNT state:
$ sqlplus / as sysdba
SQL> startup nomount
 
This will start the instance but mount nothing.


9- Now that all initial preparation is completed, it's time to call the RMAN script that creates the standby database. On PRODSERVER, start RMAN and run the following script.
connect target sys/oracle123@MYPROD
connect auxiliary sys/oracle123@MYDR
 
run {
   allocate channel c1 type disk;
   allocate auxiliary channel s1 type disk;
 
   duplicate target database
        for standby
        from active database
        dorecover
        spfile
        parameter_value_convert 'MYPROD','MYDR'
        set db_unique_name='MYDR'
        set db_file_name_convert='/MYPROD/','/MYDR/'
        set log_file_name_convert='/MYPROD/','/MYDR/'
        set control_files='/oradata/MYDR/control01.ctl'
        set fal_client='MYDR'
        set fal_server='MYPROD'
        set standby_file_management='AUTO'
        set log_archive_config='dg_config=(MYPROD,MYDR)'
        set log_archive_dest_2='service=MYPROD LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=MYDR'
        set log_archive_dest_state_2='enable'
        set log_archive_format='MYDR_%t_%s_%r.arc'
   ;
  sql channel c1 "alter system archive log current";
  sql channel s1 "alter database recover managed standby database using current logfile disconnect";
}
 
This script creates the standby database, places the appropriate parameters in the spfile for the standby instance, creates the diagnostic destination for the standby database, and restarts the standby.
Because you copied the password file to the standby host, the password for SYS remains the same and hence the connection to the standby instance (with no mounted database, yet) is successful.
 
The duplicate target database command creates the standby database from the primary by first taking an image copy of the primary database via SQL*Net on the remote server. Once the copy is complete, it internally issues a command ( switch clone datafile all;), which brings up the standby database as a clone. The set commands in the script set the parameters for the SPFILE for the standby instance and the database comes up as a standby database. Again, an examination of the RMAN output gives you all the information on the behind-the-scene activities.
Note how easy building the physical standby database is? It's as simple as executing the script!


Active Data Guard
One of the traditional objections to building a Data Guard environment using physical standby database is the passiveness of the standby database. In Oracle Database 10g and below you could open the physical standby database for read-only activities (say, to offload some reporting), but only after stopping the recovery process. With these releases, If Data Guard is a part of your DR solution, you really can't afford to pause the recovery process for a long duration for fear of falling behind, so the physical standby database is essentially useless for any read-only activity.

With Oracle Database 11g, that situation changes: You can open the physical standby database in read-only mode and restart the recovery process. This means you can continue to be in sync with primary but can use the standby for reporting. (As in previous versions, you can take the backup from the standby as well.) Let's see how it is done.

First, cancel the managed standby recovery:
SQL> alter database recover managed standby database cancel;

Then, open the database as read only:
SQL> alter database open read only;
 
Up until this point, the process is identical to that in pre-11g versions. Now, the 11g feature shows its advantage: While the standby database is open in read-only mode, you can resume the managed recovery process.
SQL> alter database recover managed standby database disconnect;
 
Now the standby database has been placed in managed recovery mode applying the log files while it is open. How do you confirm that? It's quite simple; just check the maximum log sequence number on the primary and compare that to the standby. On the primary, do a log switch and check the maximum log sequence number:
SQL> alter system switch logfile;
SQL> select max(Sequence#) from v$log;
 
MAX(SEQUENCE#)
--------------
            79
The log switch occurred while the standby was opened in read only mode. Check the maximum log sequence in standby:

SQL> select max(Sequence#) from v$log;
 
MAX(SEQUENCE#)
--------------
            79


It's also 79, the same value in primary. It's the confirmation that the log application is still going on. Well, you might ask, this merely confirms that the logs are being applied; will the changes occurring on the primary be visible in this mode? Let's see. On the primary, create a table:
SQL> create table test2 (col1 number);
 
...then do a few log switches and wait until those logs are applied to standby. Then check the standby database:

SQL> desc test2
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 COL1                                               NUMBER

Presto! The table appears in standby, and is ready to be queried.

Remember, we could have used Real Time Apply in this case, which causes the changes made to the primary appear instantly on the standby, provided the network is available? RTA is not an absolute necessity for ADG but makes the ADG even more useful as you can expect to see the latest changes on the primary.

Security conscious readers might be little concerned however. The database is in read only mode, so nothing can be written to it. If the audit_trail parameter is set to DB on the primary (the default in Oracle Database 11g), it will be the same on standby as well, but the audit trails can't be written to the database since it's read only. So where do they go?

Note a line that shows up innocuously in alert log:

AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access

Aha! The audit trails don't stop; rather, they automatically switch to OS files when the database is open. When you activate the standby database, the audit_trail is automatically resets to DB.


Snapshot Standby Database.
Prior versions of Oracle Database supported two types of standby databases: the physical standby, which is an exact duplicate of the primary database and is updated via direct application of archived redo logs; and the logical standby, which contains the same logical information as the primary database, but whose data is organized and/or structured differently than on the primary database and which is updated via SQL Apply.
Oracle Database 11g adds a third standby database type, the snapshot standby database, that’s created by converting an existing physical standby database to this format. A snapshot standby database still accepts redo information from its primary, but unlike the first two standby types, it does not apply the redo to the database immediately; instead, the redo is only applied when the snapshot standby database is reconverted back into a physical standby. This means that the DBA could convert an existing physical standby database to a snapshot standby for testing purposes, allow developers or QA personnel to make changes to the snapshot standby, and then roll back those data created during testing and immediately reapply the valid production redo data, thus reverting the snapshot standby to a physical standby again. This is accomplished by creating a restore point in the database, using the Flashback database feature to flashback to that point and undo all the changes. Let's see how it is done:

First, start recovery on the standby, if not going on already:
SQL> alter database recover managed standby database disconnect;

Wait until the recovery picks up a few log files. Then stop the recovery.
SQL> alter database recover managed standby database cancel;
 
At this point, you may create the snapshot standby database. Remember, it enables Flashback logging, so if you haven't configured the flash recovery area, you will get a message like this:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_01/12/2008
00:23:14'.
ORA-38786: Flash recovery area is not enabled.

To avoid that, you should have already created flash recovery area. If you didn't, don't worry, you can create it now:
SQL> alter system set db_recovery_file_dest_size = 2G;
SQL> alter system set db_recovery_file_dest= '/db_recov';

Now that the formalities are completed, you can convert this standby database to snapshot standby using this simple command:
SQL> alter database convert to snapshot standby;

Now recycle the database:
SQL> shutdown immediate
ORA-01507: database not mounted
...
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Now the database is open for read/write operations:
SQL> select open_mode, database_role
  2  from v$database;
 
OPEN_MODE  DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY

You can do changes in this database now. This is a perfect place to replay the captured workload using Database Replay. You can then perform the system changes in this database and replay several times to see the impact of the changes. As this is a copy of the production database, the replay will be an accurate representation of the workload.

After your testing is completed, you would want to convert the snapshot standby database back to a regular physical standby database. Just follow the steps shown below:
SQL> connect / as sysdba
Connected.     
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
...
Database mounted.
SQL> alter database convert to physical standby;
 
Now shutdown, mount the database and start managed recovery.
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
...
Database mounted.

Start the managed recovery process:
SQL> alter database recover managed standby database disconnect;

Now the standby database is back in managed recovery mode. Needless to say, when the database was in snapshot standby mode, the archived logs from primary were not applied to it. They will be applied now and it may take some time before it completely catches up.

Snapshot standby database allows you to use the standby database to accurately predict changes to production database before they were made. But that's not it; there is another advantage as well. Remember, we could have used RTA in this case, which causes the changes made to the primary appear instantly on the standby, provided the network is available? Well, what if someone makes a mistake on the primary database, such as running a massive update or changing some code? In previous versions we deliberately use a delay in the standby database to stop these errors propagating to the standby. But that delay also means the standby can't be activated properly or be used as an active copy of production.

Not anymore. Since you can flashback the standby database, you need not keep the delay. If there is a problem, you can always flashback to a previous state.


Conversion from Physical to Logical Standby
You can now easily convert a physical standby database to a logical one. Here are the steps:

1. The standby database will need to get the data dictionary information from somewhere. The dictionary information should be put in the redo stream that comes from the primary. So, on the primary database, issue the following to build the LogMiner tables for dictionary:
SQL> begin
  2    dbms_logstdby.build;
  3  end;
  4  /
 
2. On the standby database, stop the managed recovery process:
SQL> alter database recover managed standby database cancel;

3. Now, issue the command in standby side to convert it to logical:
SQL> alter database recover to logical standby pro11sb;
 
If you didn't execute Step 1, the above command will wait since the dictionary information is not found. Don't worry; just execute the Step 1 at this point. If you have enabled RTA, the information will immediately appear on the standby database.
 
4. Issue a few log switches on primary to make sure the archived logs are created and sent over to the standby:
SQL> alter system switch logfile;
 
5. On the standby side, you can see that the alter database command has completed, after some time. Now the standby is a logical one. You will see the following line in alert log:
RFS[12]: Identified database type as 'logical standby'

6. Recycle the database:
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area 1071333376 bytes
...
Database mounted.
SQL> alter database open resetlogs;
 
 
7. Now that this is a logical standby database, you should start the SQL Apply process.
SQL> alter database start logical standby apply immediate;

The logical standby database is now fully operational! Once you convert the physical standby to a logical one, you can't convert it back to a physical one unless you use the special clause ("keep identity"), described in the section below.


Rolling Database Upgrades Support Physical Standby Databases.
Oracle Database 10g introduced the ability to utilize SQL Apply to perform rolling upgrades against a primary database and its logical standby database. During a rolling upgrade, the DBA first upgrades the logical standby database to the latest database version, and then performs a switchover to make the standby database the primary and vice versa. The original primary database is then upgraded to the new database version, and a switchover reverses the roles once again. This insures that the only interruption to database access is the time it takes to perform the switchovers. The good news is that Oracle Database 11g now allows a rolling database upgrade to be performed on a physical standby database by allowing the physical standby to be converted into a logical standby database before the upgrade begins. After the rolling upgrade is completed, the upgraded logical standby is simply reconverted back into a physical standby.

Redo Compression
Data Guard is premised on shipping the archived logs from the primary to the standby database server and applying them to the database. One of the key components of the time lag between the primary and standby is the time to transport the archived logs. This can be somewhat expedited If the redo stream is compressed.

In Oracle Database 11g you can compress the redo stream that goes across to the standby server via SQL*Net using a parameter compression set to true. This works only for the logs shipped during the gap resolution. Here is the command you can use to enable compression in the example shown in the beginning of this installment.

alter system set log_archive_dest_2 = 'service=pro11sb LGWR ASYNC
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=pro11sb compression=enable'


Net Timeout

The Data Guard environment works by sending the redo data to the standby server by connecting to the database instance there. If the instance does not respond in time, the log shipping service will wait for a specified timeout value and then give up. This timeout value can be set in Oracle Database, using a parameter called net_timeout. In maximum protection mode, the log shipping service will retry for 20 times before giving up.

But first you have to know who much delay is currently present in the log shipping. A new view v$redo_dest_resp_histogram shows that time in histograms of values:

SQL> desc v$redo_dest_resp_histogram
 Name                   Null?    Type
 ---------------------- -------  --------------
 DEST_ID                         NUMBER
 TIME                            VARCHAR2(20)
 DURATION                        NUMBER
 FREQUENCY                       NUMBER

The view shows you how many times the time was taken in the shipment in that given bucket. If you examine the view after a few days of operation, you will be able to get an idea of the timeout value to set. Then you can set the set the timeout value by issuing:

alter system set log_archive_dest_2 = 'service=pro11sb LGWR ASYNC
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=pro11sb compression=enable net_timeout=20'

Again, this is from the example shown above. Note the clause "net_timeout=20" in the parameter value.

Dynamically Alterable Parameters
In the process of operating a logical standby database environment, you will need to tune the process and tweak some parameter values. In Oracle Database 11g, most of these parameters can be updated online. You can find them by querying the view dba_logstdby_parameters.
col name format a30
col value format a10
col unit format a10
col setting a6
col setting format a6
col dynamic format a7
select *
from dba_logstdby_parameters
order by name;

NAME                           VALUE      UNIT       SETTIN DYNAMIC
------------------------------ ---------- ---------- ------ -------
APPLY_SERVERS                  5                     SYSTEM YES
EVENT_LOG_DEST                 DEST_EVENT            SYSTEM YES
                               S_TABLE
LOG_AUTO_DELETE                TRUE                  SYSTEM YES
LOG_AUTO_DEL_RETENTION_TARGET  1440       MINUTE     SYSTEM YES
MAX_EVENTS_RECORDED            10000                 SYSTEM YES
MAX_SERVERS                    9                     SYSTEM YES
MAX_SGA                        30         MEGABYTE   SYSTEM YES
PREPARE_SERVERS                1                     SYSTEM YES
PRESERVE_COMMIT_ORDER          TRUE                  SYSTEM NO
RECORD_APPLIED_DDL             FALSE                 SYSTEM YES
RECORD_SKIP_DDL                TRUE                  SYSTEM YES
RECORD_SKIP_ERRORS             TRUE                  SYSTEM YES
RECORD_UNSUPPORTED_OPERATIONS  FALSE                 SYSTEM YES

Note the column DYNAMIC, which shows the value is dynamic alterable or not. Almost all the parameters are dynamic. For instance, to modify the parameter APPLY_SERVERS without stopping standby, you can issue:

SQL> begin
  2     dbms_logstdby.apply_set('APPLY_SERVERS',2);
  3  end;
  4  /

This sets the value of apply_servers to 2, which can be done without shutting down the standby.

SQL Apply Event Table
In Oracle Database 10g, the events related to SQL Apply are written to the alert log, which is not very useful since you may want to write scripts to check them for alerts or reporting. In Oracle Database 11g, the events are by default written to a new table called LOGSTDBY$EVENTS in the SYSTEM schema. Here is a sample query:
select event_time, error
from system.logstdby$events
order by 1;

The output:

EVENT_TIME                    ERROR
----------------------------- -------------------------------------------------
13-JAN-08 11.24.14.296807 PM  ORA-16111: log mining and apply setting up
13-JAN-08 11.24.14.320487 PM  Apply LWM 2677727, HWM 2677727, SCN 2677727
14-JAN-08 07.22.10.057673 PM  APPLY_SET: APPLY_SERVERS changed to 2
14-JAN-08 07.22.11.034029 PM  APPLY_SERVERS changed to 2
14-JAN-08 07.45.15.579761 PM  APPLY_SET: EVENT_LOG_DEST changed to DEST_ALL
14-JAN-08 07.45.16.430027 PM  EVENT_LOG_DEST changed to DEST_ALL

It's very useful to have the events in a table for a lot of reasons; for one; it's easier to manipulate and report. But sometimes it's also useful to see them on alert log as well, especially if you have built on some monitoring tool to scan the alert log for errors and messages. You can set the logical standby database apply parameter "event_log_dest" to "DEST_ALL" to accomplish that:

begin
   dbms_logstdby.apply_set('EVENT_LOG_DEST','DEST_ALL');
end;

This can be done dynamically and now the events will go to both the table and the alert log. After this command, you can check alert log; it will have alt least these two lines, in addition to possibly a large number of
SQL Apply events:

LOGSTDBY: APPLY_SET: EVENT_LOG_DEST changed to DEST_ALL
LOGSTDBY status: EVENT_LOG_DEST changed to DEST_ALL





Default Value in Table ALTER command

In the Oracle version earlier than 11g, if a table has to be altered to add a column, the very next step was to update the existent rows to some default value.
Oracle 11g allows providing DEFAULT value for a column during table alteration. This has resolved the overhead of updating column with default value.
Example Syntax:
ALTER TABLE [TABLE NAME]
ADD [COLUMN] [DATA TYPE] [NOT NULL]
DEFAULT [DEFAULT VALUE]




Sequence Assignment
Prior to Oracle 11g, sequence assignment to a number variable could be done through a SELECT statement only. This was the gray area which could have degraded performance due to context switching from PL/SQL engine to SQL engine. Oracle 11g has transformed this feature of Sequence assignment to a PL/SQL construct.
Example code in older versions:
DECLARE
   v_ID NUMBER;
   x_current_value NUMBER;
BEGIN
   Select TEST_SEQ.NEXTVAL into v_ID from dual;
   Select TEST_SEQ.CURRVAL into x_current_value from dual;
END;
/

Example code in new version:
DECLARE
   v_ID NUMBER;
   x_current_value NUMBER;
BEGIN
   v_ID:= TEST_SEQ.NEXTVAL;
   x_current_value := TEST_SEQ.CURRVAL;
END;
/



CONTINUE Statement
In Oracle 11g PL/SQL, you can exit the current iteration of a loop using the new statements: CONTINUE or CONTINUE-WHEN. When a CONTINUE statement is encountered, the current iteration of the loop completes immediately and control passes to the next iteration of the loop, as in the following example:

Set serveroutput on
DECLARE
   x NUMBER := 0;
BEGIN
   LOOP -- After CONTINUE statement, control resumes here
      DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
      x := x + 1;
      IF x < 3 THEN
         CONTINUE;
      END IF;
      DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = ' || TO_CHAR(x));
      EXIT WHEN x = 5;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));
END;
/
 
When a CONTINUE-WHEN statement is encountered, the condition in the WHEN clause is evaluated. If the condition is true, the current iteration of the loop completes and control passes to the next iteration. The previous example can be altered as in the following code:

Set serveroutput on
DECLARE
   x NUMBER := 0;
BEGIN
   LOOP -- After CONTINUE statement, control resumes here
      DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
      x := x + 1;
      CONTINUE WHEN x < 3;
      DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = ' || TO_CHAR(x));
      EXIT WHEN x = 5;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));
END;
/



IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statements
In Oracle 11g, the INSERT statements which use other table to load the data can make use of the hint IGNORE_ROW_ON_DUPKEY_INDEX to avoid the unique key conflict with the existing row. It ignores the unique key violation during insertion. It is similar to handling of DUP_VAL_ON_INDEX exception, but comparatively it is slower than a single INSERT statement and carries the overhead of creating a PL/SQL block.
With the new IGNORE_ROW_ON_DUPKEY_INDEX hint, duplicate violation rows are automatically ignored.
The index must be Unique for the hint to be valid.

create table diego (id number constraint diego_pk primary key using index (create unique index diego_pk on diego(id)), name varchar2(20));
insert into diego values (1, 'Value 1');
insert into diego values (2, 'Value 2');
commit;

insert into diego values (1, 'Value 1');
insert into diego values (1, 'Value 1')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DIEGO_PK) violated

insert /*+ ignore_row_on_dupkey_index(diego,diego_pk) */ into diego 
select rownum, 'Value ' || to_char(rownum) from dual connect by level <= 5;

SCOTT@DB11G> select * from diego;

        ID NAME
---------- --------------------
         1 Value 1
         2 Value 2
         3 Value 3
         4 Value 4
         5 Value 5

The UPDATE statement is not allowed with this hint ...

 
 
Improvements of MERGE
We will use the following table in our example:
CREATE TABLE test1 AS SELECT *
  FROM USER_objects
  WHERE 1=2;

Optional Clauses
The MATCHED and NOT MATCHED clauses are now optional making all of the following examples valid.
 
-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status;
 

Conditional Operations
Conditional inserts and updates are now possible by using a WHERE clause on these statements.
 
-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID';

DELETE Clause
An optional DELETE WHERE clause can be added to the MATCHED clause to clean up after a merge operation. Only those rows in the destination table that match both the ON clause and the DELETE WHERE are deleted. Depending on which table the DELETE WHERE references, it can target the rows prior or post update. The following examples clarify this.

Create a source table with 5 rows as follows.
CREATE TABLE source AS
SELECT level AS id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 10
         ELSE 20
       END AS status,
       'Description of level ' || level AS description
FROM   dual
CONNECT BY level <= 5;

SELECT * FROM source;
          ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5
 
Create the destination table using a similar query, but this time with 10 rows.
CREATE TABLE destination AS
SELECT level AS id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 10
         ELSE 20
       END AS status,
       'Description of level ' || level AS description
FROM   dual
CONNECT BY level <= 10;
 
SELECT * FROM destination;
          ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

The following MERGE statement will update all the rows in the destination table that have a matching row in the source table. The additional DELETE WHERE clause will delete only those rows that were matched, already in the destination table, and meet the criteria of the DELETE WHERE clause.
MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET d.description = 'Updated'
    DELETE WHERE d.status = 10;

SELECT * FROM destination;
          ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Updated
         3         20 Updated
         5         20 Updated
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

Notice there are rows with a status of "10" that were not deleted. This is because there was no match between the source and destination for these rows, so the delete was not applicable.
 
The following example shows the DELETE WHERE can be made to match against values of the rows before the update operation, not after. In this case, all matching rows have their status changed to "10", but the DELETE WHERE references the source data, so the status is checked against the source, not the updated values.

ROLLBACK;
MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET  d.description = 'Updated',
                d.status = 10
    DELETE WHERE s.status = 10;
5 rows merged.

SELECT * FROM destination;
          ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         10 Updated
         3         10 Updated
         5         10 Updated
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

Notice, no extra rows were deleted compared to the previous example.

By switching the DELETE WHERE to reference the destination table, the extra updated rows can be deleted also.
ROLLBACK;
MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET  d.description = 'Updated',
                d.status = 10
    DELETE WHERE d.status = 10;
5 rows merged.

SELECT * FROM destination;
        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

 
Trigger Firing Order
Oracle allows more than one trigger to be created for the same timing point, but it has never guaranteed the execution order of those triggers. The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point. The following example creates a table with two triggers for the same timing point.

CREATE TABLE trigger_follows_test (
  id          NUMBER,
  description VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_1 - Executed');
END;
/

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_2
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_2 - Executed');
END;
/
If we insert into the test table, there is no guarantee of the execution order.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_follows_test VALUES (1, 'ONE');
TRIGGER_FOLLOWS_TEST_TRG_1 - Executed
TRIGGER_FOLLOWS_TEST_TRG_2 - Executed

We can specify that the TRIGGER_FOLLOWS_TEST_TRG_2 trigger should be executed before the TRIGGER_FOLLOWS_TEST_TRG_1 trigger by recreating the TRIGGER_FOLLOWS_TEST_TRG_1 trigger using the FOLLOWS clause.

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
FOLLOWS trigger_follows_test_trg_2
BEGIN
  DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_1 - Executed');
END;
/
Now the TRIGGER_FOLLOWS_TEST_TRG_1 trigger always follows the TRIGGER_FOLLOWS_TEST_TRG_2 trigger.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_follows_test VALUES (2, 'TWO');
TRIGGER_FOLLOWS_TEST_TRG_2 - Executed
TRIGGER_FOLLOWS_TEST_TRG_1 - Executed


Invisible Indexes
Do you often wonder if an index will be truly beneficial to your users' queries? It might be helping one query but hurting 10 others. Indexes definitely affect INSERT statements negatively and potentially deletes and updates as well, depending on whether the WHERE condition includes the column in the index.

A related question is, is the index being used at all and what happens to a query's performance if the index is dropped? Sure, you can drop the index and see the impact on the query, but that's easier said than done. What if the index actually did help the queries? You have to reinstate the index, and to do that, you will need to recreate. Until it is completely recreated, no one can use it. The recreation of the index is also an expensive process; it takes up a lot of database resources you would rather put to better use.

What if you had some kind of option to make an index sort of unusable for certain queries while not affecting the others? Prior to Oracle Database 11g, issuing ALTER INDEX ... UNUSABLE is not an option as it will make all DML on that table fail. But now you have precisely that option via invisible indexes. Simply stated, you can make an index "invisible" to the optimizer so that no query will use it. If a query wants to use the index, it has to explicitly specify it as a hint.

Here's an example. Suppose there is a table called RES and you created an index as shown below:
SQL> create index in_res_guest on res (guest_id);

After analyzing this table and index, if you
SQL> select * from res where guest_id = 101;

you'll find that the index is being used:

Execution Plan
----------------------------------------------------------
Plan hash value: 1519600902
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    28 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RES          |     1 |    28 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IN_RES_GUEST |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("GUEST_ID"=101)

Now make the index invisible:
SQL> alter index in_res_guest invisible;
 
The following now shows:
SQL> select * from res where guest_id = 101;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3824022422
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    28 |   140   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| RES  |     1 |    28 |   140   (2)| 00:00:02 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("GUEST_ID"=101)

that the index is not being used. To make the optimizer use the index again, you have to explicitly name the index in a hint:
SQL> select /*+ INDEX (res IN_RES_GUEST) */ res_id from res where guest_id = 101;

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    28 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RES          |     1 |    28 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IN_RES_GUEST |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Presto! The index is used by the optimizer again.

Alternatively, you can set a session-level parameter to use the invisible indexes:
SQL> alter session set optimizer_use_invisible_indexes = true;

This feature is very useful when you can't modify the code, as in third-party applications. When you create indexes, you can append the clause INVISIBLE at the end to build the index as invisible to the optimizer. You can also see the current setting for an index using the dictionary view USER_INDEXES.

SQL> select visibility
  2  from user_indexes
  3 where index_name = 'IN_RES_GUEST';

VISIBILITY
---------
INVISIBLE

Note that when you rebuild this index, the index will become visible. You have to explicitly make it invisible again.

So, to "what" exactly is this index invisible? Well, it's not invisible to the user. It's invisible to the optimizer only. Regular database operations such as inserts, updates, and deletes will continue to update the index. Be aware of that when you create invisible indexes; you will not see the performance gain due to the index while at the same time you may pay a price during DML operations.


 
Query Rewritten More Often
You should be already familiar with Query Rewrite functionality, introduced in Oracle Database a couple of releases ago. In summary, when a user writes a query that matches with the defining query of an MV, Oracle chooses to select from the MV instead of executing the query in the database. The term "matches" means either a partial result set of the query can be satisfied by the stored MV or that the query can be sped up using the existing data in the MV. In other words, Oracle rewrites the query (or portions of it) to select from the MV instead of the tables specified in the query. This eliminates the database doing the task of accessing the base tables and doing the computations and returns the data faster to the user. All these occur automatically without the user even knowing that such a MV existed and the query was rewritten.

Of course, the user must choose to accept such as a substitution in the query. The session parameter query_rewrite_enabled must be set to TRUE and query_rewrite_integrity should be either trusted or stale_tolerated based on the staleness of the MV (the parameter controls the level of data integrity enforcement that is provided by the kernel). The MV itself also must be available for query rewrite as well.

The query rewrite occurs when the users query similar to the defining query of the MV. In past versions, if the query was not similar, the query was not rewritten. But in Oracle Database 11g, the rules are more relaxed. Consider the MV shown below:

create materialized view mv4
        enable query rewrite
as
select   prod_id, cust_id, avg (rate) tot_qty
    from (select s.prod_id, cust_id, amount_sold / quantity_sold rate
            from sales s, products p
           where s.prod_id = p.prod_id) sq
group by prod_id, cust_id;

It uses an inline query, where the row source is actually another query (the clause in FROM is actually an inline query). If you write a query similar to the defining query of the MV, where you use the same inline view, now you will see rewrite occurring. Use AUTOTRACE to check the execution path.

SQL> alter session set query_rewrite_enabled = true;
SQL> alter session set query_rewrite_integrity = stale_tolerated;

SQL> set autotrace traceonly explain
SQL> select pid, cid, avg(item_rate) avg_item_rate
  2  from (select s.prod_id pid, cust_id cid, amount_sold/quantity_sold item_rate
  3  from sales s, products p
  4  where p.prod_id = s.prod_id)
  5  group by cid, pid;

Execution Plan
----------------------------------------------------------
Plan hash value: 3944983699
 
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |   287K|    10M|   226   (2)| 00:00:03 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV4  |   287K|    10M|   226   (2)| 00:00:03 |
-------------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement

Note the Id=1 above; the query was rewritten with the Materialized View MV4—the one we created earlier. The query rewrite took place even if the MV and the query used inline views (or subqueries).

Query rewrite also occurs for remote tables in Oracle Database 11g.
 
Dynamic Cursor and REF CURSOR Interchangeability
You know how useful a Native Dynamic Cursor can prove to be, especially if you don't know what exactly you are going to query prior to making the call. You may have also used Dynamic PL/SQL using DBMS_SQL. Both methods have their own attractiveness. But what if you started developing a program using one approach and later you want to switch to the other approach?

In Oracle Database 11g, that process is fairly simple. The supplied package DBMS_SQL has a new function, TO_REFCURSOR, which converts the DBMS_SQL dynamic cursor to a ref cursor. Here is an example of such a conversion:

create or replace procedure list_trans_by_store ( p_store_id number)
is
   type num_tab is table of number index by binary_integer;
   type type_refcur is ref cursor;
   c_ref_trans_cur type_refcur;
   c_trans_cur     number;
   trans_id        num_tab;
   trans_amt       num_tab;
   ret             integer;
   l_stmt          clob;
begin
   c_trans_cur := dbms_sql.open_cursor;
   l_stmt :=
       'select trans_id, trans_amt from trans where store_id = :store_id';
   dbms_sql.parse(c_trans_cur, l_stmt, dbms_sql.native);
   dbms_sql.bind_variable(c_trans_cur, 'store_id', p_store_id);
   ret := dbms_sql.execute(c_trans_cur);
   c_ref_trans_cur := dbms_sql.to_refcursor(c_trans_cur);
   fetch c_ref_trans_cur bulk collect into trans_id, trans_amt;
   for ctr in 1 .. trans_id.count loop
       dbms_output.put_line(trans_id(ctr) || ' ' || trans_amt(ctr));
   end loop;
   close c_ref_trans_cur;
end;
/

Suppose you want to write a generic procedure where you don't know the column list in the select clause at compile time. This is where the native dynamic SQL comes in handy; you can define a ref cursor for that. Now, to make it more interesting, suppose you don't know the bind variable as well, for which dbms_sql is more appropriate. How can you accomplish this complex requirement with minimal code? Simple: Just start with dbms_sql for the bind part and then convert it to ref cursor later for the other part.

Similarly, if you want to convert a Native Dynamic SQL to REF CURSOR, you will need to call another function, TO_CURSOR_NUMBER:
cur_handle := dbms_sql.to_cursor_number (c_ref_cur);

The ref cursor specified by the variable c_ref_cur must be opened prior to this call. After this call, the life of the ref cursor is over; it can be manipulated only as a dbms_sql cursor.

Suppose you know the binds at compile time but not the select list; you start with native dynamic sql with a ref cursor and later change it to dbms_sql to describe and fetch the columns from the cursor.



Pivot Table
As you know, relational tables are, well, tabular—that is, they are presented in a column-value pair. Consider the case of a table named CUSTOMERS.

desc customers
 Name                                      Type
 ----------------------------------------- ---------------------------
 CUST_ID                                   NUMBER(10)
 CUST_NAME                                 VARCHAR2(20)
 STATE_CODE                                VARCHAR2(2)
 TIMES_PURCHASED                           NUMBER(3)

When this table is selected:
select cust_id, state_code, times_purchased
from customers
order by cust_id;

The output is:
CUST_ID STATE_CODE TIMES_PURCHASED
------- ---------- ---------------
      1 CT          1
      2 NY         10
      3 NJ          2
      4 NY          4
and so on ...
                           
Note how the data is represented as rows of values: For each customer, the record shows the customer's home state and how many times the customer purchased something from the store. As the customer purchases more items from the store, the column times_purchased is updated.

Now consider a case where you want to have a report of the purchase frequency each state - that is, how many customers bought something only once, twice, thrice and so on, from each state. In regular SQL, you can issue the following statement:

select state_code, times_purchased, count(1) cnt
from customers
group by state_code, times_purchased;

Here is the output:

ST TIMES_PURCHASED        CNT
-- ---------------      ----------
CT               0         90
CT               1        165
CT               2        179
CT               3        173
CT               4        173
CT               5        152
and so on ...
                           
This is the information you want but it's a little hard to read. A better way to represent the same data may be through the use of crosstab reports, in which you can organized the data vertically and states horizontally, just like a spreadsheet:

Times_purchased
             CT           NY         NJ      ... 
and so on ...
1             0            1          0      ...
2            23          119         37      ...
3            17           45          1      ...
and so on ...
                           
Prior to Oracle Database 11g, you would do that via some sort of a decode function for each value and write each distinct value as a separate column. The technique is quite nonintuitive however.
Fortunately, you now have a great new feature called PIVOT for presenting any query in the crosstab format using a new operator, appropriately named pivot. Here is how you write the query:

select * from (
   select times_purchased, state_code
   from customers t
)
pivot
(
   count(state_code)
   for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased;

Here is the output:
TIMES_PURCHASED       'NY'       'CT'             'NJ'            FL'            'MO'
--------------- ---------- ----------      ----------       ---------      ----------
              0      16601         90               0               0               0
              1      33048        165               0               0               0
              2      33151        179               0               0               0
              3      32978        173               0               0               0
              4      33109        173                             1               0
... and so on ...

This shows the power of the pivot operator. The state_codes are presented on the header row, instead of a column.

The second line, "for state_code ...," limits the query to only those values. This line is necessary, so unfortunately you have to know the possible values beforehand. This restriction is relaxed in the XML format of the query

The column headers are the data from the table itself: the state codes. The abbreviations may be self explanatory but suppose you want to display the state names instead of abbreviations, ("Connecticut" instead of "CT")? In that case you have to make a little adjustment in the query, in the FOR clause as shown below:

select * from (
   select times_purchased as "Puchase Frequency", state_code
   from customers t
)
pivot
(
   count(state_code)
   for state_code in ('NY' as "New York",'CT' "Connecticut",'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
)
order by 1;

Puchase Frequency   New York   Connecticut  New Jersey    Florida   Missouri
-----------------  ----------  -----------  ---------- ---------- ----------
                0       16601           90           0          0          0
                1       33048          165           0          0          0
                2       33151          179           0          0          0
                3       32978          173           0          0          0
                4       33109          173           0          1          0
... 
                             
and so on ...
                           
The FOR clause can have aliases for the values there, which will become the column headers.

Transaction Management with LogMiner and Flashback Data Archive
LogMiner is an often ignored yet very powerful tool in the Oracle Database. It is used to extract DML statements from the redo log files—the original SQL that caused the transaction and even the SQL that can undo the transactions. (For an introduction to LogMiner and how it works, refer to my Oracle Magazine article "Mining for Clues.") Until now, this powerful tool was commonly under-appreciated due to the lack of a simpler interface. In Oracle Database 11g, however, Oracle Enterprise Manager has a graphical interface to extract transaction from the redo logs using LogMiner, which makes it extremely easy to use the tool to examine and rollback transactions. (Note: As in previous versions, you can continue to use the DBMS_LOGMNR package to perform command line-driven log mining if you wish.)

Oracle9i Database Release 2 introduced the proverbial time machine in the form of the Flashback Query, which allows you to select the pre-changed version of the data. For example, had you changed a value from 100 to 200 and committed, you can still select the value as of two minutes ago even if the change was committed. This technology used the pre-change data from the undo segments. In Oracle Database 10g, this facility was enhanced with the introduction of Flashback Versions Query, where you can even track the changes made to a row as long as the changes are still present in the undo segments.
However, there was a little problem: When the database is recycled, the undo data is cleaned out and the pre-change values disappear. Even if the database is not recycled, the data may be aged out of the undo segments to make room for new changes.
Since pre-11g flashback operations depend on the undo data, which is available only for a short duration, you can't really use it over an extended period of time or for more permanent recording such as for auditing. As a workaround, we resorted to writing triggers to make more permanent records of the changes to the database.
Well, don't despair. In Oracle Database 11g, Flashback Data Archive combines the best of both worlds: it offers the simplicity and power of the flashback queries but does not rely on transient storage like the undo. Rather, it records changes in a more permanent location, the Flashback Recovery Area.

More information Here:
http://www.oracle.com/technetwork/articles/sql/11g-transactionmanagement-092065.html


Backup and Recovery
Consider the error shown below:

SQL> conn scott/tiger
SQL> create table t (col1 number);
create table t (col1 number)
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/oradata/PRODB3/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Does it look familiar? Regardless of your experience as a DBA, you probably have seen this message more than once. This error occurs because the datafile in question is not available—it could be corrupt or perhaps someone removed the file while the database was running. In any case, you need to take some proactive action before the problem has a more widespread impact.

In Oracle Database 11g, the new Data Recovery Advisor makes this operation much easier. The advisor comes in two flavors: command line mode and as a screen in Oracle Enterprise Manager Database Control. Each flavor has its advantages for a given specific situation. For instance, the former option comes in handy when you want to automate the identification of such files via shell scripting and schedule recovery through a utility such as cron or at. The latter route is helpful for novice DBAs who might want the assurance of a GUI that guides them through the process.

Command Line Option
The command line option is executed through RMAN. First, start the RMAN process and connect to the target.

$ rman target=/
RMAN> list failure;

If there is no error, this command will come back with the message:
no failures found that match specification

If there is an error, a more explanatory message will follow:
using target database control file instead of recovery catalog
List of Database Failures
=========================
 
Failure ID Priority Status    Time Detected Summary
----------      --------     ---------     -------------       -------
142        HIGH     OPEN      15-JUL-07     One or more non-system datafiles are missing

This message shows that some datafiles are missing. As the datafiles belong to a tablespace other than SYSTEM, the database stays up with that tablespace being offline. This error is fairly critical, so the priority is set to HIGH. Each failure gets a Failure ID, which makes it easier to identify and address individual failures. For instance you can issue the following command to get the details of Failure 142.

RMAN> list failure 142 detail;

This command will show you the exact cause of the error.

Now comes the fun part: How do you rectify the error? Seasoned DBAs will probably ace this without further help but novice DBAs (and even experienced but tired ones) will welcome some guidance here. They can turn to Data Recovery Advisor for assistance:

RMAN> advise failure;

It responds with a detailed explanation of the error and how to correct it:

List of Database Failures
=========================
 
Failure ID Priority Status    Time Detected Summary
----------      --------     ---------     -------------       -------
142        HIGH     OPEN      15-JUL-07     One or more non-system datafiles are missing
 
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
 
Mandatory Manual Actions
========================
no manual actions available
 
Optional Manual Actions
=======================
1. If file /home/oracle/oradata/PRODB3/users01.dbf was unintentionally renamed or moved, restore it
 
Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4 
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /home/oracle/app/diag/rdbms/prodb3/PRODB3/hm/reco_3162589478.hm

This output has several important parts. First, the advisor analyzes the error. In this case, it's pretty obvious: the datafile is missing. Next, it suggests a strategy. In this case, this is fairly simple as well: restore and recover the file. (Please note that I have deliberately chosen a simple example to focus the attention on the usage of the tool, not to discuss the many cases where the database could fail and how they can be recovered. The dynamic performance view V$IR_MANUAL_CHECKLIST also shows this information.)

However, the most useful task Data Recovery Advisor does is shown in the very last line: it generates a script that can be used to repair the datafile or resolve the issue. The script does all the work; you don't have to write a single line of code.

Sometimes the advisor doesn't have all the information it needs. For instance, in this case, it does not know if someone moved the file to a different location or renamed it. In that case, it advises to move the file back to the original location and name (under Optional Manual Actions).

OK, so the script is prepared for you. Are you ready to execute it? I don't know about you, but I would verify what the script actually does first. So, I issue the following command to "preview" the actions the repair task will execute:

RMAN> repair failure preview;
 
Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/diag/rdbms/prodb3/PRODB3/hm/reco_741461097.hm
 
contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

This is good; the repair seems to be doing the same thing I would have done myself using RMAN. Now I can execute the actual repair by issuing:

RMAN> repair failure;
 
Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/diag/rdbms/prodb3/PRODB3/hm/reco_3162589478.hm
 
contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';
 
Do you really want to execute the above repair (enter YES or NO)?

Assuming I'm OK, I answer YES and the action goes on:

executing repair script
 
sql statement: alter database datafile 4 offline
 
Starting restore at 15-JUL-07
using channel ORA_DISK_1
 
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=5 STAMP=628025835 file name=/home/oracle/flasharea/PRODB3/datafile/o1_mf_users_39ocxbv3_.dbf
destination for restore of datafile 00004: /home/oracle/oradata/PRODB3/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/oradata/PRODB3/users01.dbf RECID=0 STAMP=0
Finished restore at 15-JUL-07
 
Starting recover at 15-JUL-07
using channel ORA_DISK_1
 
starting media recovery
 
archived log for thread 1 with sequence 51 is already on disk as file /home/oracle/flasharea/PRODB3/archivelog/2007_07_15/o1_mf_1_51_39ocxxdw_.arc
... 
                             
and so on ...
name=/home/oracle/flasharea/PRODB3/archivelog/2007_07_15/o1_mf_1_55_39ocy9ox_.arc thread=1 sequence=55
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-JUL-07
 
sql statement: alter database datafile 4 online
repair failure complete
 
RMAN>
                           
Note how RMAN prompts you before attempting to repair. In a scripting case, you may not want to do that; rather, you would want to just go ahead and repair it without an additional prompt. In such a case, just use repair failure noprompt at the RMAN prompt.
 
Proactive Health Checks
It helps you sleep better at night knowing that the database is healthy and has no bad blocks. But how can you ensure that? Bad blocks show themselves only when they are accessed so you want to identify them early and hopefully repair them using simple commands before the users get an error.

The tool dbverify can do the job but it might be a little inconvenient to use because it requires writing a script file contaning all datafiles and a lot of parameters. The output also needs scanning and interpretation. In Oracle Database 11g, a new command in RMAN, VALIDATE DATABASE, makes this operation trivial by checking database blocks for physical corruption. If corruption is detected, it logs into the Automatic Diagnostic Repository. RMAN then produces an output that is partially shown below:

RMAN> validate database;
 
Starting validate at 09-SEP-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=110 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/home/oracle/oradata/ODEL11/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/oradata/ODEL11/system01.dbf
input datafile file number=00003 name=/home/oracle/oradata/ODEL11/undotbs01.dbf
input datafile file number=00004 name=/home/oracle/oradata/ODEL11/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:02:18
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
----   ------    --------------        ------------       ---------------        ----------
1    OK     0              12852        94720           5420717  
  File Name: /home/oracle/oradata/ODEL11/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- --------------          ----------------
  Data       0              65435          
  Index      0              11898          
  Other      0              4535           
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
----   ------    --------------        ------------ -     --------------         ----------
2    OK     0              30753        115848          5420730  
  File Name: /home/oracle/oradata/ODEL11/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- --------------          ----------------
  Data       0              28042          
  Index      0              26924          
  Other      0              30129          
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
----   ------    --------------        ------------       ---------------        ----------
3    OK     0              5368         25600           5420730  
  File Name: /home/oracle/oradata/ODEL11/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- --------------      ----------------
  Data       0              0              
  Index      0              0              
  Other      0              20232          
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              2569         12256           4910970  

... 
                             
<snipped> ...
                           
Otherwise, in case of a failure you will see on parts of the above output:

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
----   ------    --------------        ------------       ---------------        ----------
7    FAILED 0              0            128             5556154  
  File Name: /home/oracle/oradata/ODEL11/test01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- --------------        ----------------
  Data       0              108            
  Index      0              0              
  Other      10             20             

You can also validate a specific tablespace:
RMAN> validate tablespace users;

Or, datafile:
RMAN> validate datafile 1;

Or, even a block in a datafile:
RMAN> validate datafile 4 block 56;

The VALIDATE command extends much beyond datafiles however. You can validate spfile, controlfilecopy, recovery files, Flash Recovery Area, and so on.

For the GUI Commands, please follow this link.

Backup Committed Undo? Why?
You already know what undo data is used for. When a transaction changes a block, the past image of the block is kept it the undo segments. The data is kept there even if the transaction is committed because some long running query that started before the block is changed can ask for the block that was changed and committed. This query should get the past image of the block—the pre-commit image, not the current one. Therefore undo data is kept undo segments even after the commit. The data is flushed out of the undo segment in course of time, to make room for the newly inserted undo data.

When the RMAN backup runs, it backs up all the data from the undo tablespace. But during recovery, the undo data related to committed transactions are no longer needed, since they are already in the redo log streams, or even in the datafiles (provided the dirty blocks have been cleaned out from buffer and written to the disk) and can be recovered from there. So, why bother backing up the committed undo data?

In Oracle Database 11g, RMAN does the smart thing: it bypasses backing up the committed undo data that is not required in recovery. The uncommitted undo data that is important for recovery is backed up as usual. This reduces the size and time of the backup (and the recovery as well).

In many databases, especially OLTP ones where the transaction are committed more frequently and the undo data stays longer in the undo segments, most of the undo data is actually committed. Thus RMAN has to backup only a few blocks from the undo tablespaces.

The best part is that you needn't do anything to achieve this optimization; Oracle does it by itself.


Duplicate Database from Backup (11g R2 Only)
You need to duplicate a database for various reasons – for example, setting up a Data Guard environment, establishing a staging or QA database from the production, or mopving the database to a new platform. The DUPLICATE command in RMAN makes that activity rather trivial. But where does RMAN duplicate the database from?

The most obvious choice is the main database itself. This is the most up-to-date version and has all the information needed to duplicate the database. But while this approach is convenient, it also puts some stress on the main database. Additionally, it requires a dedicated connection to the main database, which may not always be possible.

The other source of the production database is the database backup. This does not affect the production database, since we are going to the backup alone. Duplicating the database from its backup has been available since Oracle9i Database, but there was a catch: although the source of the duplicate was the backup, the process still needed a connection to the main database. So, there is a monkey wrench here: What if your main database is not available because it is down for maintenance? Or you are duplicating the database on a different server from which you can’t connect to the main database for some security or other logistical reasons?

Oracle Database 11g Release 2 solves that problem. In this version, you can perform a duplicate database task without needing a connection to the main database. All you need is the backup files. Let’s see how it is done through an example.

First of all, to demonstrate the concept, we need to take a backup from the main database. Let’s start by kicking off an RMAN job.
# $ORACLE_HOME/bin/rman target=/ rcvcat=rman_d112d1/rman_d112d1@d112d2
 
While a connection to a catalog database makes it simpler but is not absolutely necessary. I want to show you the steps with a catalog connection first.
RMAN> backup database plus archivelog format '/u01/oraback/%U.rmb';
 
The controlfile backup is also required. If you have configured the controlfile autobackup, the backup would contain the controlfile as well. If you want to be sure, or you have not configured controlfile autobackup, you can backup the controlfile explicitly.
RMAN> backup current controlfile format '/u01/oraback/%U.rmb';

These commands create the backup files in the directory /u01/oraback. Of course you don’t need to perform this step if you have a backup somewhere. Copy these backup files to the server where you want to create the duplicate copy.
# scp *.rmb oradba2:`pwd`

You need to know one piece of information before proceeding – the DBID of the source database. You can get that one of these three ways:
From the data dictionary
SQL> select dbid from v$database;

DBID
----------
1718629572
 
From the RMAN repository (catalog or the controlfile)
RMAN> list db_unique_name all;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
-------    -------     -----------------        ---------------         ------------------
2       D112D1   1718629572       PRIMARY          D112D1            
Querying the Recovery Catalog tables on the catalog database.

The DBID in this case is 1718629572; make a note of it.   (The DBID is not strictly required for the effort but you will see later why it may be important. )

You also need to know another very important fact: when the backup was completed. You can get that time from many sources, the RMAN logfile being the most obvious one. Otherwise just query the RMAN repository (catalog or the controlfile). Here is how:

# $ORACLE_HOME/bin/rman target=/ rcvcat=rman_d112d1/rman_d112d1@d112d2
 
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 9 12:25:36 2010
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: D112D1 (DBID=1718629572)
connected to recovery catalog database
 
RMAN> list backup of database;
 
List of Backup Sets
=================== 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
716     Full    2.44G      DISK        00:03:58     08/09/10 10:44:52
        BP Key: 720   Status: AVAILABLE  Compressed: NO  Tag: TAG20100809T104053
        Piece Name: /u01/oraback/22lktatm_1_1.rmb
  List of Datafiles in backup set 716
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  1       Full 13584379   08/09/10 10:40:55 +DATA/d112d1/datafile/system.256.696458617
… output truncated …

The NLS variable setting was required since we need to know the specific time, not just the date. From the output we know that the backup was taken on Aug. 9 at 10:44:53 AM.

The rest of the steps occur on the target host. Here the main database is named D112D1 and the duplicate database will be called STG.

Add a line in the file /etc/oratab to reflect the database instance you are going to copy:
STG:/opt/oracle/product/11.2.0/db1:N

Now set the Oracle SID as the duplicated database SID:
# . oraenv
ORACLE_SID = [STG] ?
The Oracle base for ORACLE_HOME=/opt/oracle/product/11.2.0/db1 is /opt/oracle

Copy the initialization parameter file from the main database. Edit it to reflect the new locations that might be appropriate such as audit dump destinations, datafile locations, etc. Create the password file as well.
# orapwd file=orapwSTG password=oracle entries=20

When the pfile and the password files are ready, start the instance with nomount option. It’s important to start just the instance since the duplication process will create the controlfile and mount it.
SQL> startup nomount
ORACLE instance started.
Total System Global Area  744910848 bytes
Fixed Size                  1339120 bytes
Variable Size             444596496 bytes
Database Buffers          293601280 bytes
Redo Buffers                5373952 bytes

While it’s not important, it may be easier to put the commands in a script and execute it from RMAN command line instead of giving each command line by line. Here are the contents of the script file:
connect auxiliary sys/oracle
connect catalog rman_d112d1/rman_d112d1@d112d2
duplicate database 'D112D1' DBID 1718629572 to 'STG'
until time "to_date('08/09/10 10:44:53','mm/dd/yy hh24:mi:ss')"
   db_file_name_convert = ("+DATA/D112D1","/u01/oradata/stg")
backup location '/u01/oraback' ;

The script is codetty self explanatory. The first two lines are for connection to the auxiliary instance (the database we are going to create as a duplicate of the main database) and the catalog connection.  The third line states that we are going to duplicate the database D112D1 to STG. The timestamp up to which the database should be recovered is shown here as well. The fifth line is there because of the difference of the database file locations between hosts. On the main database the datafiles are on ASM, on diskgroup DATA whereas the staging database will be created on the directory /u01/oradata. This means we have to perform a naming convention change. A datafile on main database named +DATA/somefile.dbf will be called /u01/oradata/somefile.dbf. Finally, we have provided the location where the backup files will be found.

Here we have used the timestamp Aug 9th 10:44:53, just a second after the backup is completed. Of course we could have used any other time here, as long as the archived logs are available. You could have also given SCN number instead of timestamp.

Let’s name this script file duplicate.rman. After creation, call this script from RMAN directly:
#$ORACLE_HOME/bin/rman @duplicate.rman

That’s it; the Staging Database STG is now up and running. You can connect to it now and select the table. Nowhere in this process did you have to connect to the main database. And only a few commands were needed.
In summary, as you can glean from the output, the command performs the following steps:
If you check the DBID of the database that was just created:

SQL> select dbid from v$database;
DBID
----------
 844813198


The DBID is different from the main database so it can be backed up independently and using the same catalog as well. Speaking of DBID, remember we used it during the duplication process even if it was not absolutely necessary? The reason for that is the possibility of two databases bearing the same name However in the recovery catalog there could be two databases with the name D111D1 (the source). How will the duplication process know which one to duplicate? This is where the DBID comes in to make the identification definitive.

On a similar note, if you have multiple backups, RMAN chooses which backup to duplicate from automatically based on the UNTIL TIME clause. Finally, here we have used the catalog database; but it is not required. If you don’t specify the catalog, you must use the “until time” clause, not “until SCN”.


Undrop a Tablespace (11gR2 Only)
Let’s say you were in the mood to clean up junk in the database, so off you went to drop all the small and large tablespaces created for the users who are probably long gone. While dropping those tablespaces, inadvertently you dropped a very critical tablespace. What are your options?

In the codevious versions the options were reduced to sum total of one. Here are the steps you would have followed:
Needless to say these are complex steps for anyone – except for probably seasoned DBAs in the habit of dropping tablespace often. Don’t you wish for a simple “undrop tablespace”, similar to the undrop table (flashback table) functionality?

In this version of the database you get your wish. Let’s see how it is done. To demonstrate, we will need a tablespace and put a table or two there to see the effect of the “undrop”:
SQL> create tablespace testts datafile '/u01/oradata/testts_01.dbf' size 1M;
SQL> conn arup/arup
SQL> create table test_tab1 (col1 number) tablespace testts;
SQL> insert into test_tab1 values (1);
SQL> commit;

After taking the backup, let’s create a second table in the same tablespace
SQL> create table testtab2 tablespace testts as select * from testtab;

Now drop the tablespace with the including contents clause, which will drop the tables as well.
SQL> drop tablespace testts including contents;

If you check the view ts_pitr_objects_to_be_dropped you will get no rows:

select owner, name, tablespace_name,
       to_char(creation_time, 'yyyy-mm-dd:hh24:mi:ss')
       from ts_pitr_objects_to_be_dropped
  where creation_time > sysdate -1
order by creation_time;

Now you need to undrop the tablespace. To do that, you have to know when the tablespace was dropped. One easy way is to check in the alert log. Here is an excerpt from the alert log:
Tue Aug 03 15:35:54 2010
drop tablespace testts
ORA-1549 signalled during: drop tablespace testts...
drop tablespace testts including contents
Completed: drop tablespace testts including contents

To recover the tablespace back into the database, we will use this timestamp, just in the nick of the time of the drop tablespace command.
RMAN> recover tablespace testts
 until time "to_date('08/03/2010 15:35:53','mm/dd/yyyy hh24:mi:ss')"
 auxiliary destination '/u01/oraux';

The auxiliary destination is where the files of the new database will be created. You can use any space, even bubble space you plan to use for something else here because the space is required only temporarily. (Here is the output of the RMAN command.)

That’s it; now the tablespace is available once again. Let’s see what the command actually does:
Creates a database instance called Dvlf. The instance name is deliberately spelled in such a way that it is least likely to clash with an existing instance name.
Identifies all the tablespaces that contain undo segments
Restores the necessary tablespaces (which includes the tablespace that was dropped, SYSTEM, SYSAUX and the undo tablespaces)
Transports the tablespace testts (the one that was dropped)
Plugs the tablespace back into the main database
When the tablespace is available, it is placed in offline mode. You have to make it online.

SQL> alter tablespace testts online;

Let’s make sure that we have got the data right as well:
SQL> conn arup/arup
SQL> select count(1) from test_tab1;  

The table TEST_TAB1 was brought back as expected; but what about TEST_TAB2?
SQL> select count(1) from test_tab2;  

It came back as well. How come? The table was created after the backup was taken. Shouldn’t it have been excluded?

No. The tablespace recovery recovered up to the last available redo entry. The backup of the tablespace was restored and the archived logs (and redo logs) were applied to make it consistent all the way up to the moment right before the failure since that’s what we out in the recovery clause.

If you check the above mentioned view now:

select owner, name, tablespace_name,
       to_char(creation_time, 'yyyy-mm-dd:hh24:mi:ss')
from ts_pitr_objects_to_be_dropped
where creation_time > sysdate -1
order by creation_time  ;

OWNER           NAME
------------------------------ ------------------------------
TABLESPACE_NAME TO_CHAR(CREATION_TI
------------------------------ -------------------
ARUP            TEST_TAB1
TESTTS          2010-08-03:15:31:16

ARUP            TEST_TAB2
TESTTS          2010-08-03:15:33:09

That’s it; the tablespace is now “undropped” and all the data is available. You accomplished that in just a few lines of RMAN command as opposed to making a complex plan of activity.

Another beauty of this approach is that you are not required to restore the tablespace to this very moment. Suppose you want to restore the tablespace to a specific point in time in the past. You can do that by using a different time in the until clause; and later you can recover it again to yet another point in time. This can be repeated as many times as you want. In codevious, once you recovered the tablespace to a point in time, you couldn’t recover it to another point earlier than that.

Remember in the codevious versions, you had to use an AUXNAME parameter for datafiles while doing the Tablespace Point in Time Recovery. This allowed you recover a tablespace but the datafile names were different; so the tablespace had to be plugged into the database. This process does not require an AUXNAME parameter. Note, however, that AUXNAME is not always necessary. It is needed when the datafile names are the same as the backup, typically in case of Image Copies.


Set NEWNAME Flexibility (11gR2 Only)
Suppose you are restoring datafiles from the backup, either on the same server or a different one such as staging. If the filesystem (or diskgroup) names are identifical, you won’t have to change anything. But that is hardly ever the case. In staging the filesystems may be different, or perhaps you are restoring a production database to an ASM diskgroup different from where it was originally created. In that case you have to let RMAN know the new name of the datafile. The way to do it is using the SET NEWNAME command. Here is an example, where your restored files are located on /u02 instead of /u01 where they were codeviously.

run
{
   set newname for datafile 1 to ‘/u02/oradata/system_01.dbf’;
   set newname for datafile 2 to ‘/u02/oradata/sysaux_01.dbf’;

   restore database;      …
}

Here there are just two datafiles, but what if you have hundreds or even thousands? It will not only be a herculean task to enter all that information but it will be error-prone as well. Instead of entering each datafile by name, now you can use a single set newname clause for a tablespace. Here is how you can do it:

run
{
 set newname for tablespace examples to '/u02/examples%b.dbf';
 …
 … rest of the commands come here …
}

If the tablespace has more than one datafile, they will all be uniquely created. You can use this clause for the entire database as well:

run
{  
   set newname for database to '/u02/oradata/%b';
}

The term %b specifies the base filename without the path, e.g. /u01/oradata/file1.dbf will be recodesented as file1.dbf in %b. This is very useful for cases where you are moving the files to a different directory. You can also use it for creating image copies where you will create the backup in a different location with the same names as the parent file which will make it easy for identification.

One caveat: Oracle Managed Files don’t have a specific basename; so this can’t be used for those. Here are some more examples of the placeholders.

%f is the absolute file number
%U is a system generated unique name similar to the %U in backup formats
%I is the Database ID
%N is the tablespace name
           
Using these placeholders you can use just one SET NEWNAME command for the entire database – making the process not only easy but more accurate as well.


Backup (RMAN) TO DESTINATION Clause (11gR2 Only)
Are you familiar with Oracle Managed Files (OMF), which are datafiles, logfiles and controlfiles managed by Oracle without your intervention? They are neatly organized in their own folders with names that probably mean nothing to you but everything to the Oracle database. Either you love it or hate it; there is no shade of emotions in between. There is plenty to love it for – this frees you from worrying about file names, locations and the related issues such as clashing of names. Since the locations are codedefined, e.g. DATAFILES for datafiles, ONLINELOGS for redo log files and so on, other tools can easily use it. If you are using ASM, you are using OMF – probably not something you knew.

You might want to extend the same structure to the RMAN backups as well, where all you have to define is a location and the files simply go there, all neatly organized. In this version of the database you can use a new clause in the BACKUP command to specify the location. Here is how you will use it:

RMAN> backup tablespace abcd_data to destination '/u01/oraback';

Note there is no format string like %U in the above command as we have been using in the backup commands earlier. Here is the output:

Starting backup at 08/09/10 16:42:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/d112d1/datafile/abcd_data.272.697114011
channel ORA_DISK_1: starting piece 1 at 08/09/10 16:42:17
channel ORA_DISK_1: finished piece 1 at 08/09/10 16:44:22
piece
handle=/u01/oraback/D112D1/backupset/2010_08_09/o1_mf_nnndf_TAG20100809T164216_660t194b_.
bkp tag=TAG20100809T164216 comment=NONE
channel ORA_DISK_1:
backup set complete, elapsed time: 00:02:05
Finished backup at 08/09/10 16:44:22

This clause creates backup files in an organized manner. The above command creates a directory D112D1 (the name of the instance), under which it creates a directory called backupset, under which another directory with the name as the date of the file creation. Finally the backuppiece is created with a system generated tag. When you use this to backup archived logs, that backuppiece goes under the subdirectory archivelogs and so on.

You an also use this clause in ALLOCATE CHANNEL command as well:
RMAN> run {
2> allocate channel c1 type disk to destination '/u01/oraback';
3> }

ADR Home
Since all the focus is on the diagnostic ability of the database, shouldn't Oracle Database store all the trace files, log files, and so on organized in a structured way? 

It does, in Oracle Database 11g. The Automatic Diagnostic Repository (ADR) files are located in directories under a common directory specified as the Diagnostic Destination (or ADR Base). This directory is set by an initialization parameter (diagnostic_dest). By default it is set to $ORACLE_BASE, but you could explicitly set to some exclusive directory. (This is not recommended however.) Under this directory, there is a subdirectory called diag under which you will find the subdirectories where the diagnostic files are stored. 

The ADR houses logs and traces of all components—ASM, CRS, listener, and so on—in addition to those of the database itself. This makes it convenient for you to look for a specific log at a single location.

Inside the ADR Base, there could be several ADR Homes, one for each component and instance. For example, if the server has two Oracle instances, there will be two ADR Homes. Here is the directory structure of the ADR Homes for the database instances.

Directory Name

Description

<Directory mentioned in the DIAGNOSTIC_DEST parameter>

 

→diag

 

  →rdbms

 

    →<Name of the Database>

 

       →<Name of the Instance>

 

          →alert

The alert log in XML format is stored here.

          →cdump

Core dumps are stored here, the equivalent of the core_dump_dest in earlier versions.

          →hm

The Health Monitor runs checks on many components, and it stores some files here.

          →incident

All incidents dumps are stored here.

            →<all incident directories exist here>

Each incident is stored in a different directory, which are all stored here.

          →incpkg

When you package incidents (learn about packaging in this article), certain supporting files are stored here. 

          →metadata 

Metadata about problems, incidents, packages and so on is kept here.

          →trace

User traces and background traces are kept here, along with the text version of the alert log.


For instance, if your database name is ODEL11 and the instance name is ODEL11 (in uppercase) as well, the path of the ADR Home is /home/oracle/diag/rdbms/odel11/ODEL11. You can now see the different subdirectories under this ADR Home:
$ ls
alert  cdump  hm  incident  incpkg  ir  lck  metadata  stage  sweep  trace

To support this new structure, the *_dest parameters in previous releases (background_dump_dest and user_dump_dest) are ignored. (core_dump_dest is not ignored; in fact Oracle recommends that you set it as core dumps can be very large.) You shouldn't set them at all and if you are upgrading from 10g to 11g, you should remove them from the initialization parameter file to avoid confusion later. 

The ADR directory structure for other components is similar. For instance, for ASM instance, the directory under "diag" is named asm, instead of rdbms. The rest of the directory structure remains the same. The name of the target in case of asm is +asm. For instance, here is how my ADR Home for ASM looks:

$ pwd
/home/oracle/diag/asm/+asm/+ASM
$ ls
alert  cdump  hm  incident  incpkg  ir  lck  metadata  stage  sweep  trace

For the listener, the directory under diag is called tnslsnr, under which another directory exists with the hostname, and then under that another directory with the listener name as the directory name. Under that you will see the other directories.


<Directory mentioned in the DIAGNOSTIC_DEST parameter> 
   → diag 
      → tnslsnr 
         → <hostname of the server> 
             → <name of the listener> 
               → alert 
               → trace ...

For instance, for a host named oradba3, and a listener named "listener" (the default name), the directory will look like /home/oracle/diag/tnslsnr/oradba3/listener. Under this directory all the others (alert, trace, metadata, and so on) are created. Like the alert log, the listener log file is also stored as XML entries, under the subdirectory alert. The usual text listener log file is still produced, under the directory trace.

A new view V$DIAG_INFO shows all the details about the ADR Homes. In my RDBMS home, it appears like this:

SQL> select * from v$diag_info;
 
 INST_ID NAME                           VALUE
    -------- ------------------------------                -----------------------------------------------------------------
       1 Diag Enabled                   TRUE
       1 ADR Base                       /home/oracle
       1 ADR Home                       /home/oracle/diag/rdbms/odel11/ODEL11
       1 Diag Trace                     /home/oracle/diag/rdbms/odel11/ODEL11/trace
       1 Diag Alert                     /home/oracle/diag/rdbms/odel11/ODEL11/alert
       1 Diag Incident                  /home/oracle/diag/rdbms/odel11/ODEL11/incident
       1 Diag Cdump                     /home/oracle/diag/rdbms/odel11/ODEL11/cdump
       1 Health Monitor                 /home/oracle/diag/rdbms/odel11/ODEL11/hm
       1 Default Trace File             /home/oracle/diag/rdbms/odel11/ODEL11/trace/ODEL11_ora_3908.trc
       1 Active Problem Count           3
       1 Active Incident Count          37
 
11 rows selected.

This shows the ADR information about this instance only. To see that for another instance, simply connect to that instance and select from v$diag_info. The columns are self-explanatory. The default trace file indicates the trace file for your current session. The Active Problem and Incident counts are for problems and incidents described earlier. 

You can access the files and perform other operations on the ADR in two ways. The easiest way is via Enterprise Manager as you saw earlier. The other option is to use a command line tool called asrci. Let's see how you can use the tool. From the UNIX (or Windows) command prompt, type "adrci":

$ adrci 
 
ADRCI: Release 11.1.0.6.0 - Beta on Sun Sep 23 23:22:24 2007
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
ADR base = ”/home/oracle”

As you learned earlier, there are several ADR Homes, one for each instance of the Oracle components. So, the first task is to show how many homes exist. The command is show homes.
adrci> show homes
ADR Homes: 
diag/rdbms/odel11/ODEL11
diag/rdbms/dbeng1/DBENG1
diag/clients/user_unknown/host_411310321_11
diag/tnslsnr/oradba3/listener

As you can see, there are several homes. To operate on a specific home, you should use set homepath command:
adrci> set homepath diag/rdbms/odel11/ODEL11

Once set, you can issue many commands at the prompt. The first command you may try is help, which will show all the available commands. Here is a brief excerpt of the output:
adrci> help
 
 HELP [topic]
   Available Topics:
        CREATE REPORT
        ECHO
        EXIT
        HELP
        HOST
        IPS
        ...
If you want to know more about a specific command, issue help <command>. For instance, if you want to get help on the usage of show incident commands, you will issue:
adrci> help show incident            
 
  Usage: SHOW INCIDENT [-p <predicate_string>] 
                       [-mode BASIC|BRIEF|DETAIL]
                       [-last <num> | -all] 
                       [-orderby (field1, field2, ...) [ASC|DSC]]
 
  Purpose: Show the incident information. By default, this command will
           only show the last 50 incidents which are not flood controlled.
 
  Options:
    [-p <predicate_string>]: The predicate string must be double-quoted.
 
    [-mode BASIC|BRIEF|DETAIL]: The different modes of showing incidents.
[... and so on ...]

This technique of decoupling of collecting and publishing stats can also be used with partitioned tables. Suppose you are loading a table partition by partition. You don't want to feed partial information to the optimizer; you rather want the stats of all partitions to be visible to the optimizer at the same time. But you also want to take advantage of the time right after the partition is loaded. So, you can collect the stats on a partition right after it is loaded but not publish it. After all partitions are analyzed, you can publish them all at once.


From the output you know the usage. Now to know how many incidents have been recorded, you can issue:

adrci> show incident -mode basic  

ADR Home = /home/oracle/diag/rdbms/odel11/ODEL11:
******************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- - --------------------------------------------------- ----------------------------------------
14556 ORA 600 [KSSRMP1] 2007-10-17 04:01:57.725620 -04:00
14555 ORA 600 [KSSRMP1] 2007-10-16 18:45:03.970884 -04:00
14435 ORA 603 2007-10-16 06:06:46.705430 -04:00
14427 ORA 603 2007-10-16 06:06:42.007937 -04:00
14419 ORA 603 2007-10-16 06:06:30.069050 -04:00
6001 ORA 4031 2007-08-28 14:50:01.355783 -04:00
5169 ORA 4031 2007-09-04 19:09:36.310123 -04:00
5121 ORA 4031 2007-09-03 14:40:14.575457 -04:00
5017 ORA 4031 2007-09-04 19:09:30.969226 -04:00
4993 ORA 4031 2007-09-04 19:09:33.179857 -04:00
4945 ORA 4031 2007-09-04 19:09:30.955524 -04:00
4913 ORA 4031 2007-09-04 19:09:31.641990 -04:00

This shows a list of all incidents. Now, you can get the details of a specific incident as shown below: 

adrci> show incident -mode detail -p "incident_id=14556"

ADR Home = /home/oracle/diag/rdbms/odel11/ODEL11:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 14556
STATUS ready
CREATE_TIME 2007-10-17 04:01:57.725620 -04:00
.
[... and so on ...]
.
INCIDENT_FILE /home/oracle/diag/rdbms/odel11/ODEL11/trace/ODEL11_mmon_14831.trc
OWNER_ID 1
INCIDENT_FILE /home/oracle/diag/rdbms/odel11/ODEL11/incident/incdir_14556/ODEL11_mmon_14831_i14556.trc
1 rows fetched

The information shown in the adcri command line is analgous to what you will see in the Enterprise Manager screens. The latter may, however, be simpler and much more user friendly. adcri is very helpful when you don't have access to EM Support Workbench for some reason. You can also use adcri to do things like tailing the alert log file or searching some log (listener, css, crs, alert, etc.) for specific patterns. adcri is also helpful if you want to work on ADR programmatically.



New Alert Log File


In Oracle Database 11g, the alert log is written in XML format. For the sake of compatibility with older tools, the traditional alert log is also available in the ADR Home under the trace directory. For instance, in my example shown above, the directory is /home/oracle/diag/rdbms/odel11/ODEL11/trace, where you can find the alert_ODEL11.log. However, the other alert logs are in XML format, and are located in the alert subdirectory under ADR Home. Let's see the files:
$ pwd
/home/oracle/diag/rdbms/odel11/ODEL11/alert
$ ls -ltr
total 60136
-rw-r-----  1 oracle oinstall 10485977 Sep 13 17:44 log_1.xml
-rw-r-----  1 oracle oinstall 10486008 Oct 16 06:35 log_2.xml
-rw-r-----  1 oracle oinstall 10485901 Oct 16 07:27 log_3.xml
-rw-r-----  1 oracle oinstall 10485866 Oct 16 08:12 log_4.xml
-rw-r-----  1 oracle oinstall 10486010 Oct 17 23:56 log_5.xml
-rw-r-----  1 oracle oinstall  9028631 Oct 21 20:07 log.xml

Note that there are several files: log_1.xml, log_2.xml, and so on. When the log.xml reaches a certain size, the file is renamed to log_?.xml and a new file is started. This prevents the alert log from becoming too large and unmanageable.


The new alert log is accessed via the adrci utility: the ADR command line tool, which you learned about in the previous section. From the adrci tool, issue:

adrci> show alert Choose the alert log from the following homes to view: 
1: diag/rdbms/odel11/ODEL11 
2: diag/clients/user_oracle/host_1967384410_11 
3: diag/clients/user_unknown/host_411310321_11 
4: diag/tnslsnr/oradba3/listener 
Q: to quit Please select option:

You can choose one from the menu or you can supply a specific home:
adrci> set homepath diag/rdbms/odel11/ODEL11
adrci> show alert 
 
ADR Home = /home/oracle/diag/rdbms/odel11/ODEL11:

[... and the whole alert log show up here ...]

Instead of selecting the entire alert log, you may want to specify only a few lines at the end, e.g. 10 lines (similar to the tail -10command in UNIX):
adrci> show alert -tail 10
2007-09-23 19:57:44.502000 -04:00
Errors in file /home/oracle/diag/rdbms/odel11/ODEL11/trace/ODEL11_arc1_20810.trc:
[... the rest of the 10 lines ...]

Perhaps the most frequent use of this will be to constantly display the last lines of the alert log, something similar to the tail -fcommand in UNIX.
adrci> show alert -tail -f

You can execute scripts from the adrci command line prompt. Here is an example of a Windows script that sets the home and displays the last 10 lines of the alert log:
C:\>type show_alert_10lines.cmd
set homepath diag\rdbms\lapdb11\lapdb11
show alert -tail 10

You can call this script as shown below:
adrci script=show_alert_10lines.cmd

A similar functionality is the exec parameter, which allows you to run commands directly from the command line:
adrci exec=”show homes; show catalog”

At the adrci prompt, you can also run a command using the "run" command or the "@" sign:
adrci>> @show_alert_10lines.cmd

One of the best things with the alert log being an XML file is that information is written in a structured way. Gone are the days when the alert log was a repository of unstructured data. The XML format makes the file viewable as a table in adrci. To see the fields of this "table", use the describe command:
adrci>>describe alert_ext
Name                          Type            NULL?
-----------------------------               ---------------        -----------
ORIGINATING_TIMESTAMP         timestamp
NORMALIZED_TIMESTAMP          timestamp
ORGANIZATION_ID               text(65)
COMPONENT_ID                  text(65)
HOST_ID                       text(65)
HOST_ADDRESS                  text(17)
MESSAGE_TYPE                  number
MESSAGE_LEVEL                 number
MESSAGE_ID                    text(65)
MESSAGE_GROUP                 text(65)
CLIENT_ID                     text(65)
MODULE_ID                     text(65)
PROCESS_ID                    text(33)
THREAD_ID                     text(65)
USER_ID                       text(65)
INSTANCE_ID                   text(65)
DETAILED_LOCATION             text(161)
UPSTREAM_COMP_ID              text(101)
DOWNSTREAM_COMP_ID            text(101)
EXECUTION_CONTEXT_ID          text(101)
EXECUTION_CONTEXT_SEQUENCE    number
ERROR_INSTANCE_ID             number
ERROR_INSTANCE_SEQUENCE       number
MESSAGE_TEXT                  text(2049)
MESSAGE_ARGUMENTS             text(129)
SUPPLEMENTAL_ATTRIBUTES       text(129)
SUPPLEMENTAL_DETAILS          text(129)
PARTITION                     number
RECORD_ID                     number
FILENAME                      text(513)
PROBLEM_KEY                   text(65)

Now that the information is structured, you can search with precision. Suppose you want to search for lines in the alert logs that match a specific value in a field. Here is an example:
adrci>> show alert -p "module_id='DBMS_SCHEDULER'"

This shows all the lines written by processes with the module id dbms_scheduler. You can also use the inequality operator (not containing DBMS_SCHEDULER):
adrci>>show alert -p "module_id != 'DBMS_SCHEDULER'"

Likewise you can use the pattern-matching operators:
adrci>>show alert -p "module_id like '%SCHEDULER'"

The spool command works just like its namesake command in SQL*Plus. You can spool the output to a file:
adrci>> spool a
adrci>> show alert -tail 50
adrci>> spool off

It creates a file (a.ado) containing the last 50 lines of the alert log. A great use of this option is to extract specific types of messages from the alert log. If you want to extract the Streams related statements from the alert log, you would use:
adrci> show alert -p "message_text like '%STREAM%'"

You can see all the trace files generated in the ADR base directory from the adrci command prompt as well.
adrci>> show tracefile 

The above command shows a list of all the trace files generated in the ADR directory. To show specific types of trace files( "reco", for example) in reverse chronological order:
adrci>>show tracefile %reco% -rt
   18-JUL-07 22:59:50  diag\rdbms\lapdb11\lapdb11\trace\lapdb11_reco_4604.trc
   12-JUL-07 09:48:23  diag\rdbms\lapdb11\lapdb11\trace\lapdb11_reco_4236.trc
   11-JUL-07 10:30:22  diag\rdbms\lapdb11\lapdb11\trace\lapdb11_reco_3256.trc

adrci offers many more options to view the alert log and related files in the most efficient manner. For a complete description of adrcicommands, see the documentation.



LOBs and SecureFiles

Database Resident BLOBS or OS Files

What do you store in an Oracle database? Mostly it's data that you store in a relational format for easy mapping into some type of defined pattern and in a defined datatype: customer names, account balances, status codes, and so on. But it's also increasingly likely that you may need to store information in a non-stuctured or semi-structured form. Examples include pictures, word processing documents, spreadsheets, XML files, and so on. How are these types of data stored?

There are usually two approaches: The data is stored in the database as LOB fields (BLOB for binary and CLOB for character data), or in OS files with the references to the files stored in the database.

Each approach has advantages and challenges. OS files can be cached by the OS and journaled filesystems that expedite recovery after crashes. They also generally consume less space than the data in the database since they can be compressed.

There are also tools that can intelligently identify patterns in the files and remove duplication for a more efficient storage; however, they are external to the database so the properties of the database do not apply to them. These files are not backed up, fine grained security does not apply to them, and such files are not part of a transaction--so concepts so innate to the Oracle database like read consistency do not apply.

What if you could get the best of both worlds? In Oracle Database 11g, you have the answer with SecureFiles, a completely new infrastructure inside the database that gives you the best features of database-resident LOBs and OS files. Let's see how. (By the way, traditional LOBs are still available in the form of BasicFiles.)

Real-life Example

Perhaps it's best to introduce the concept of SecureFiles through a simple example. Suppose you are developing a contract management system in which you want to put the copies of all the contracts into a table. The scanned documents are usually PDF files, not text. Some could be MS Word documents or even scanned pictures. This is a perfect use case for BLOBs because the column must be able to support binary data.

Traditionally, priot to Oracle Database 11g, you would have defined the table as follows:

create table contracts_basic
(
        contract_id     number(12),
        contract_name   varchar2(80),
        file_size       number,
        orig_file       blob
)
tablespace users
lob (orig_file)
(
        tablespace users
        enable storage in row
        chunk 4096
        pctversion 20
        nocache
        nologging
);
\

The column ORIG_FILE is where the actual file in binary format is stored. The various parameters indicate that the LOB should not be cached and logged during operations, should be stored in line with the table row, and should have a chunk size of 4KB and stored in the tablespace USERS. As you didn't specify it explicitly, the LOB is stored in the conventional format (BasicFiles) under Oracle Database 11g.


If you want to store the LOB as a SecureFile, all you have to do is place a clause— store as securefile—in the table creation, as shown below:

create table contracts_sec 
(
        contract_id     number(12),
        contract_name   varchar2(80),
        file_size       number,
        orig_file       blob
)
tablespace users
lob (orig_file)
                               
store as securefile
( tablespace users enable storage in row chunk 4096 pctversion 20 nocache nologging ) /
To create SecureFile LOBs, you need to comply with two conditions, both of which are default (so you may already be compliant).

After the table is created, you can load data in the same way you do for a regular pre-11g LOB (BasicFile). Your applications do not need to change and you don't need to remember some special syntax.

Here is a small program that loads into this table.

declare
    l_size      number;  
    l_file_ptr  bfile;   
    l_blob      blob;    
begin
    l_file_ptr := bfilename('SECFILE', 'contract.pdf'); 
    dbms_lob.fileopen(l_file_ptr); 
    l_size := dbms_lob.getlength(l_file_ptr); 
    for ctr in 1 .. 100 loop 
        insert into contracts_sec 
        (        
            contract_id, 
            contract_name, 
            file_size,   
            orig_file    
        )        
        values   
        (        
            ctr,         
            'Contract '||ctr, 
            null,        
            empty_blob() 
        )        
        returning orig_file into l_blob;  
        dbms_lob.loadfromfile(l_blob, l_file_ptr, l_size); 
    end loop; 
    commit; 
    dbms_lob.close(l_file_ptr); 
end;
/

This loads the file contract.pdf 100 times into 100 rows of the table. You would have already defined a directory object called SECFILE for the OS directory where the file contract.pdf is stored. Here is an example where the file contract.pdf is located in /opt/oracle.
SQL> create directory secfile as ’/opt/oracle’;

Once the LOB is stored as a SecureFile, you have a lot features available to you for optimal operation. Here are some of those very useful features.


Deduplication

Deduplication is likely to be the most popular feature in SecureFiles because it is the most widely sought after benefit of OS files in some high-end filesystems as opposed to database-resident blobs. Suppose a table has five records each with a BLOB. Three of the BLOBs are identical. If it were possible to store the BLOB only once and store only the reference to that copy on other two records, it would reduce the space consumption substantially. This is possible in OS files but would not have been possible in Oracle Database 10g LOBs. But with SecureFiles it's actually trivial via a property called deduplication. You can specify it during the table creation or modify it later as:

SQL> alter table contracts_sec
  2  modify lob(orig_file)
  3  (deduplicate)
  4  /
 
Table altered.

After the deduplication, the database calculates the hash values of the columns values in each row and compares them to the others. If the hash values match, the hash value is stored&m;dashnot the actual BLOB. When a new record is inserted its hash value is calculated, and if it matches to another value then the hash value is inserted; otherwise the real value is stored. 

Now, let's see the space savings after the deduplication process. You can examine the space consumption in the LOB segment through the package DBMS_SPACE. Here is a program that displays the space consumption:

declare  
    l_segment_name          varchar2(30); 
    l_segment_size_blocks   number; 
    l_segment_size_bytes    number; 
    l_used_blocks           number;  
    l_used_bytes            number;  
    l_expired_blocks        number;  
    l_expired_bytes         number;  
    l_unexpired_blocks      number;  
    l_unexpired_bytes       number;  

begin
    select segment_name 
    into l_segment_name 
    from dba_lobs 
    where table_name = 'CONTRACTS_SEC'; 
        dbms_output.put_line('Segment Name=' || l_segment_name);
 
    dbms_space.space_usage( 
        segment_owner           => 'ARUP',  
        segment_name            => l_segment_name, 
        segment_type            => 'LOB', 
        partition_name          => NULL, 
        segment_size_blocks     => l_segment_size_blocks, 
        segment_size_bytes      => l_segment_size_bytes, 
        used_blocks             => l_used_blocks, 
        used_bytes              => l_used_bytes, 
        expired_blocks          => l_expired_blocks, 
        expired_bytes           => l_expired_bytes, 
        unexpired_blocks        => l_unexpired_blocks, 
        unexpired_bytes         => l_unexpired_bytes 
    );   


    dbms_output.put_line('segment_size_blocks       => '||  l_segment_size_blocks);
    dbms_output.put_line('segment_size_bytes        => '||  l_segment_size_bytes);
    dbms_output.put_line('used_blocks               => '||  l_used_blocks);
    dbms_output.put_line('used_bytes                => '||  l_used_bytes);
    dbms_output.put_line('expired_blocks            => '||  l_expired_blocks);
    dbms_output.put_line('expired_bytes             => '||  l_expired_bytes);
    dbms_output.put_line('unexpired_blocks          => '||  l_unexpired_blocks);
    dbms_output.put_line('unexpired_bytes           => '||  l_unexpired_bytes);
end;
/

This script shows various space related statistics for the LOB. Before the deduplication process, here is the output:
Segment Name=SYS_LOB0000070763C00004$$
segment_size_blocks     => 1072
segment_size_bytes      => 8781824
used_blocks             => 601
used_bytes              => 4923392
expired_blocks          => 448
expired_bytes           => 3670016
unexpired_blocks        => 0
unexpired_bytes         => 0

After deduplication:
Segment Name=SYS_LOB0000070763C00004$$
segment_size_blocks     => 1456
segment_size_bytes      => 11927552
used_blocks             => 7
used_bytes              => 57344
expired_blocks          => 127
expired_bytes           => 1040384
unexpired_blocks        => 1296
unexpired_bytes         => 10616832

Only one metric from the above output is enough to study: used_bytes, which show the exact bytes stored by the LOB column. Before deduplication, it used to consume 4,923,392 bytes or about 5MB but after deduplication it shrank to 57,344 bytes or about 57KB, almost 1 percent of the original value. It happened because the deduplication process found the rows repeated with the same value 100 times (remember, we put the same value in LOB column for all rows) and kept only one row and made the other ones as pointers.


You can also reverse the deduplication process:

SQL> alter table contracts_sec
  2  modify lob(orig_file)
  3  (keep_duplicates)
  4  /
 
Table altered.

After this, if you check the space again:
Segment Name=SYS_LOB0000070763C00004$$
segment_size_blocks     => 1456
segment_size_bytes      => 11927552
used_blocks             => 601
used_bytes              => 4923392
expired_blocks          => 0
expired_bytes           => 0
unexpired_blocks        => 829
unexpired_bytes         => 6791168

Note the USED_BYTES went up to the original value of about 5MB.


Compression

Another feature of SecureFiles is compression. You can compress the values stored in the LOBs using the following SQL:

SQL> alter table contracts_sec
  2   modify lob(orig_file)
  3  (compress high)
  4  /
 
Table altered.

Now if you run the space finding PL/SQL block:
Segment Name=SYS_LOB0000070763C00004$$
segment_size_blocks     => 1456
segment_size_bytes      => 11927552
used_blocks             => 201
used_bytes              => 1646592
expired_blocks          => 0
expired_bytes           => 0
unexpired_blocks        => 1229
unexpired_bytes         => 10067968

Note the used_bytes metric is now 1,646,592 or about 1.5 MB, down from 5MB.


Compression is not the same as deduplication. Compression happens inside a LOB column, inside a row—each LOB column is compressed independently. In deduplication, all the rows are examined and duplicate values in the columns are removed and replaced with pointers. If you have two very different rows, deduplication will not reduce the size; but compression may optimize the space inside the LOB value. You can compress as well as deduplicate the table.

Compression takes up CPU cycles so depending on how much data is compressible, it may not be worthy of compression. For instance, if you have a lot of JPEG pictures they are compressed already, so further compression will not save any space. On the other hand, if you have an XML document as a CLOB, then compression may produce substantial reduction. SecureFiles compression automatically detects if the data is compressible and only spends CPU cycles if compression yields gains.

Oracle Text indexes can be installed on compressed SecureFiles LOBs. This is a major benefit of storing unstructured data inside the Oracle database as opposed to compressed files in a filesystem.

Also note that the LOB compression is independent of table compression. If you compress the table CONTRACTS_SEC, the LOBs are not compressed. The LOB compression occurs only when you issue the above SQL.

In Oracle Database 11g Release 2, there is a third compression option in addition to HIGH and MEDIUM: LOW. As the name suggests, it compresses less but also consumes a lot less CPU and completes faster. This approach uses a block-based lossless compression similar to the fast Lempel–Ziv–Oberhumer (LZO) algorithm.

Let's see an example of a table with the SecureFiles LOW compression:

create table docs
(
        doc_id  number,
        clearance       varchar2(20),
        doc_body        clob
)
LOB(doc_body) store as securefile
(
        compress low
)
/

If you omit the LOW clause, the default is MEDIUM. The LOW compression is not just for table creation; you can use it to alter an existing column as well.


Let's see an example with the same table and column. First, we modify the column to uncompressed:

SQL> alter table docs 
  2  modify LOB(doc_body)
  3  (
  4     nocompress
  5  )
  6  /

Table altered.

Now, let’s modify the column for low compression:
SQL> alter table docs 
  2  modify LOB(doc_body)
  3  (
  4     compress low
  5  )
  6  /

Table altered.


Encryption

You can use Transparent Database Encryption for the SecureFiles as you would do for any column. Here is how you will encrypt the column orig_file LOB using AES 128-bit encryption.

alter table contracts_sec
  modify lob(orig_file)
  (encrypt using 'AES128')
/

Before enabling encryption you have to set up encryption wallet. (A complete description of encryption wallets can be found in thisOracle Magazine article.) Here are the steps in summary: 

  1. Set the parameter in sqlnet.ora, if not set already to specify the location of the wallet:
    ENCRYPTION_WALLET_LOCATION= 
       (SOURCE= 
           (METHOD=FILE) 
           (METHOD_DATA= 
              (DIRECTORY= /opt/oracle/orawall)
         )        
    )       
    
    The directory /opt/oracle/orawall should already exist; if not then you should create it.
  2. Create the wallet:
    alter system set encryption key authenticated by "mypass"
    
    This creates the wallet with the password mypass and opens it.
  3. The above steps are needed only once. After the wallet is created and open, it stays open as long as the database is up (unless it is explicitly closed). If the database is restarted, you have to open the wallet with:
    alter system set encryption wallet open identified by "mypass"
    
When a SecureFile LOB column is encrypted, the column values of all the rows of that table are encrypted. After the encryption, you can't use the Conventional Export or Import in the table; you have to use Data Pump.

You can check the view dba_encrypted_columns to see which columns have been encrypted and how.

SQL> select table_name, column_name, encryption_alg
  2  from dba_encrypted_columns
  3  /
 
TABLE_NAME                     COLUMN_NAME        ENCRYPTION_ALG
------------------------------                ------------------          -----------------------------
CONTRACTS_SEC                  ORIG_FILE          AES 128 bits key

Caching

One of the advantages of storing unstructured data in OS files instead of database resident objects is the facility of caching. Files can be cached in the operating system's file buffers. A database resident object can also be cached in the database buffer cache. However in some cases the caching may actually be detrimental to performance. LOBs are usually very large (hence the term large objects) and if they come to the buffer cache, most other data blocks will need to be pushed out of the cache to make room for the incoming LOB. The LOB may never be used later yet its entry into the buffer cache causes necessary blocks to be flushed out. Thus in most cases you may want to disable caching for the LOBs.

In the example script for CONTRACTS_SEC you used the nocache clause to disable caching. To enable caching for the LOB, you can alter the table:

alter table contracts_sec
modify lob(orig_file)
(cache)
/

This enables the LOB caching. Note that the caching refers to the LOB only. The rest of the table is placed into the buffer cache and follow the same logic as any other table regardless of the setting of the LOB caching on that table.


The benefits of caching are very application dependent. In an application manipulating thumbnail images, performance may be improved with caching. However, for larger documents or images, it is better to turn off caching. With securefiles, you have the control.

Logging

Logging clause determines how the data changes in the LOB are recorded in the redo log stream. The default is full logging, as in case of any other data, but since the data in LOBs are usually large, you may want to eliminate logging in some cases. The NOLOGING clause used in the example above does exactly that.

SecureFiles offer another value for this clause— filesystem_like_logging—as shown below:

create table contracts_sec_fs
(
        contract_id     number(12),
        contract_name   varchar2(80),
        file_size       number,
        orig_file       blob
)
tablespace users
lob (orig_file)
store as securefile
(
        tablespace users
        enable storage in row
        chunk 4096
        pctversion 20
        nocache
        
                              
filesystem_like_logging
)
Note the line shown in bold, which makes the metadata of the LOB logged in the redo logs, not the entire LOB. This is similar to a filesystem. The file metadata is logged in the filesystem journals. Similarly this clause on the SecureFiles expedites recovery after a crash.

Initialization Parameter

The initialization parameter db_securefile determines the use of SecureFiles in the database. Here are the various values of the parameter and their effects:

Value

Effect

PERMITTED

The default value. The value indicates that SecureFile LOBs can be created in the database.

ALWAYS

Now that you see how the SecureFiles are so useful, you may want to make sure all LOBs from then onward should only be SecureFiles instead of the default BasicFiles, even if the user does not specify securefile. This parameter value ensures all the LOBs are created as SecureFiles by default. Remember, SecureFiles require ASSM tablespaces (which is default in 11g anyway), so if you are trying to create the LOB in a non-ASSM tablespace, you will get an error.

NEVER

Well, it’s just the opposite of always. You don’t like SecureFiles for some reason and do not want to allow its creation in the database. This parameter value will make the LOB created as a BasicFile when though a SecureFile clause is mentioned. The user does not get an error when the SecureFile clause is used but the LOB is silently created as BasicFile.

IGNORE

The securefile clause, along with all storage clauses is ignored.

 


Data Pump Gets Better

As I mentioned previously, Data Pump has been the tool of choice for moving large amounts of data, or for taking "logical" backups of the data efficiently, since the previous release. Similar to Export/Import, it's platform independent (for instance you can export from Linux to import into Solaris). It got a few enhancements in Oracle Database 11g.

One of the big issues with Data Pump was that the dumpfile couldn't be compressed while getting created. That was something easily done in the older Export/Import utility. In Oracle Database 11g, Data Pump can compress the dumpfiles while creating them. This is done via a parameter COMPRESSION in the expdp command line. The parameter has three options:

Here is how you compress the export of the table UNITS_FACT:
$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_comp.dmp compression=all

For comparison purposes, export without compression:
$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_uncomp.dmp

Now if you check the files created:
$ ls -l 
-rw-r-----  1 oracle dba  2576384 Jul  6 22:39 units_fact_comp.dmp
-rw-r-----  1 oracle dba 15728640 Jul  6 22:36 units_fact_uncomp.dmp

The compression ratio is 100*(15728640-2576384)/15728640 or about 83.61%! That's fairly impressive; the uncompressed dumpfile is 15MB while the compressed one is 1.5MB.

If you compress the dumpfile using gzip:

$ gzip units_factl_uncomp.dmp

-rw-r-----  1 oracle dba 3337043 Jul  6 22:36 units_fact_uncomp.dmp.gz

The compressed file is about 3.2MB, double the size of the compressed file in Data Pump. So, in addition to the compression being potentially more efficient, the decompression really adds value. When importing the dumpfile, the import does not have to decompress the file first; it decompresses as it reads it, making the process really fast.

The two other enhancements in Data Pump are:



Online Index Rebuild

Remember the ONLINE clause while rebuilding an index?

alter index in_tab_01 rebuild online;

The clause rebuilds the index without affecting the DML accessing it. It does so by tracking which blocks are being accessed and at the end merging these blocks with the newly built index. To accomplish this task the operation had to get an exclusive lock at the end of the process. Although short in duration, it was a lock nevertheless, and DMLs had to wait.


In Oracle Database 11g, the online rebuild is truly online: it does not hold an exclusive lock. The DMLs are not affected.


Different Tablespace for Temporary Tables

When you create a global temporary table, where does the allocation come from for the space occupied? It comes from the user's temporary tablespace. Usually this is not going to be an issue, but in some special cases, you may want to free up the temporary tablespace for the purpose it is supposed to be for (sorting, mostly). Sometimes you may want create the temporary tables to use another temporary tablespace on faster, more efficient disks to make the data access faster. In those cases you had no choice but to make that tablespace the user's temporary tablespace.

In Oracle Database 11g you can use another temporary tablespace for your global temporary tables. Let's see how. First you create another temporary tablespace:

SQL> create temporary tablespace etl_temp
  2> tempfile '+DG1/etl_temp_01.dbf'
  3> size 1G;
  
Tablespace created.  

Then, you create the GTT with a new tablespace clause:
SQL> create global temporary table data_load (
  2>    input_line varchar2 (2000)
  3>  )
  4> on commit preserve rows
  5> tablespace etl_temp;

Table created.

This temporary table is now created on tablespace etl_temp instead of the user's default temporary tablespace—TEMP.



SQL*Plus Error Logging
Suppose you have a SQL script called myscript.sql:

set puase on
set lines 132 pages 0 trimsppol on
select * from nonexistent_table
/

Note there are several errors in the script: the first line has "pause" misspelled, the second line has "trimspool" misspelled, and finally the third line has a select statement from a table that does not even exist. When you run the script via SQL*Plus prompt, unless you spooled the output, you will not be able to check the error afterward. Even if you spooled, you would have access to the physical server to examine the spool file, which may not be possible.

Oracle Database 11g has a perfect solution: Now you can log the errors coming from SQL*Plus on a special table. You should issue, as a first command:
SQL> set errorlogging on

Now you run the script:
SQL> @myscript

The run will produce the following error messages:
SP2-0158: unknown SET option "puase"
SP2-0158: unknown SET option "trimsppol"
select * from nonexistent_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist

which you may or may not have seen, depending on how you ran the script—in the foreground from an SQL*Plus prompt or in the background as a script invocation. After the script completes, you can log into the database and check the errors in a table named SPERRORLOG.
sql> col timestamp format a15
sql> col username format a15
sql> col script format a10
sql> col identifier format a15
sql> col statement format a20
sql> col message format a20
sql> select timestamp, username, script, statement, message from sperrorlog;

Note that you checked the error from a different session, not the session where the script was run. In fact the script has finished and the session has been terminated anyway. This gives you a powerful ability to check errors after they occurred in SQL*Plus sessions that were impossible, or at least difficult, to track otherwise.
The table SPERRORLOG is a special table that is created for this purpose only. You can also create your own table and populate that with errors from SQL*Plus. This table should be created as:

SQL> create table my_sperror_log
  2  (
  3     username varchar2(256),
  4     timestamp       timestamp,
  5     script          varchar2(1024),
  6     identifier      varchar(256),
  7     message         clob,
  8     statement       clob
  9  )
 10  /
 

Now you can use this table in error logging instead of the default table.
SQL> set errorlogging on table sh.my_sperror_log;
SQL> @myscript

Now MY_ERROR_LOG (and not SPERRORLOG) will hold the error log. You can truncate all the rows in the table by issuing
SQL> set errorlogging on truncate

There is an optional IDENTIFIER clause that allows you to tag the errors from specific sessions. Suppose you issue the command:
SQL> set errorlogging on identifier MYSESSION1

Now if you run the script, the records will be created with the column called IDENTIFIER populated with the value MYSESSION1. You can extract those records only by issuing the query:
select timestamp, username, script, statement, message
from sperrorlog
where identifier = 'MYSESSION1';

You will see the records from that session only. This is very useful if you are trying to isolate errors in multiple scripts and sessions.


Shrink Temporary Tablespace
You probably already know that temporary tablespaces are special; normal rules of space management may not apply to them. When a temporary segment is allocated, it is not deallocated. This is not really a problem, since the temporary segments (which are what the temporary tablespaces are for) are not part of the schema and are not stored across database recycles. The space is reused for another user or another query. Anyway, since the space is not deallocated, the temporary tablespaces just keep growing. But what if you want to trim them to make room for other tablespaces?

Until now the only option was to drop and recreate the tablespace—a rather trivial task that can be done almost always online. However there is a little "but": What if you can't afford to accept anything other than 100-percent uptime? In Oracle Database 11g, you can easily do that, by shrinking the temporary tablespace.

Here is how the tablespace TEMP1 is shrunk:
alter tablespace temp1 shrink space;

This deallocates all the unused segments from the tablespace and shrinks it. After the above operation, you can check the view DBA_TEMP_FREE_SPACE to check how much the allocated space and free space currently is.

SQL> select * from dba_temp_free_space;
 TABLESPACE_NAME               TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                 179306496       179306496  178257920

In a relatively quiet database the shrink operation might shrink the temporary tablespace to almost empty. You know that's just artificial; as the subsequent activities will expand the tablespace, you might want to leave some space inside, say 100MB. You can do it as follows:

SQL> alter tablespace temp shrink space keep 100m;
SQL> select * from dba_temp_free_space;
 
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                 105906176         1048576  104857600

All the space but 100MB was released. This approach helps you manage the space in various tablespaces. Now you can borrow the space from inside a temporary tablespace to give to other tablespace temporarily (no pun intended). Later when that space is no longer needed, you can give it back to the temporary tablespace. When you combine this feature with the tablespace for global temporary tables you can resolve many difficult space management issues in temporary tablespaces.


Scheduler Email Notification (Release 11gR2 Only)

Scheduler has long offered various advantages over the older DBMS_JOB functionality. Now you have one more reason to like it.

When a job completes, how do you know? Querying the DBA_SCHEDULER_JOBS view repeatedly will tell you, but that’s hardly practical. A more practical solution is via an email. But that brings its own set of problems – from change control approval to not being able to modify the source code.

In Oracle Database 11g Release 2, you don’t have to resort to these options; there is a much more elegant alternative whereby the Scheduler can send an email after completion. It can even specify in the email whether the completion ended in success or failure.

To demonstrate, let’s create a job to run a stored procedure named process_etl2:

begin
  dbms_scheduler.create_job (
    job_name        => 'process_etl2',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'process_etl2',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; bysecond=0',
    enabled         => TRUE);
end;
/

Now, to enable the email send function, we have to set some email-related parameters such as the email server name and how the sender’s details should be specified.

BEGIN  
  dbms_scheduler.set_scheduler_attribute(
    'email_server', 
    'mail.proligence.com:25'
  );  
  dbms_scheduler.set_scheduler_attribute(
     'email_sender', 
     'dbmonitor@proligence.com'
  );
END;
/

Note the SMTP server is given in the format server[:port]. If the port is not given, default 25 is assumed. Now we can add the email notification property to the job:

begin
 dbms_scheduler.add_job_email_notification (
  job_name   =>  'process_etl2',
  recipients =>  'arup@proligence.com',
  subject    =>  'Job: process_etl',
  body       =>  'ETL Job Status',
  events     =>  'job_started, job_succeeded');
END;
/

The parameters of the procedure are self-explanatory. One very important one is EVENTS, which specifies the events during which the emails should be sent. In this example we have specified that the email is sent when the job starts and when it succeeds (but not when it fails).

The EVENTS parameter can have table values job_failed, job_broken, job_sch_lim_reached, job_chain_stalled, job_over_max_dur, which represent if a job failed at the end, if a job was broken, if its limit in the scheduler was reached, if a chain which this job belongs to got stalled and if the job went over its duration, respectively.

If you want to find out the status of the email notification sent by the various jobs placed under this notification system, you can check the new data dictionary view USER_SCHEDULER_NOTIFICATIONS.

SQL> desc user_scheduler_notifications
 Name                                      Null?    Type
 -----------------------------------------                     --------     ----------------------------
 JOB_NAME                                  NOT NULL VARCHAR2(30)
 JOB_SUBNAME                                        VARCHAR2(30)
 RECIPIENT                                 NOT NULL VARCHAR2(4000)
 SENDER                                             VARCHAR2(4000)
 SUBJECT                                            VARCHAR2(4000)
 BODY                                               VARCHAR2(4000)
 FILTER_CONDITION                                   VARCHAR2(4000)
 EVENT                                              VARCHAR2(19)
 EVENT_FLAG                                NOT NULL NUMBER

Let’s check the contents of this view.

SQL> select job_name, recipient, event
  2  from user_scheduler_notifications;

JOB_NAME                  RECIPIENT            EVENT
-------------------------             --------------------           -------------------
PROCESS_ETL2              arup@proligence.com  JOB_STARTED
PROCESS_ETL2              arup@proligence.com  JOB_SUCCEEDED


The body column shows the actual mail that was sent:

SQL> select BODY, event_flag
  2  from user_scheduler_notifications
  3  /

BODY
--------------------------------------------------------------------------------
EVENT_FLAG
----------
ETL Job Status
         1

ETL Job Status
         2


The emails will keep coming based on the error defined. You can also define a filter that results in the notification system sending out emails only if the error codes are ORA-600 or ORA-7445. The expression should be a valid SQL predicate (the WHERE clause without the “where” keyword).

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'process_etl2',
  recipients =>  'arup@proligence.com',
  subject    =>  'Job: process_etl',
  body       =>  'ETL Job Status',
  filter_condition =>  ':event.error_code = 600 or :event.error_code = 7445',
  events     =>  'job_started, job_succeeded');
END;
/


Did you notice that the body was a simple “ETL Job Status”? This is not quite useful. It may be worth letting the default value take over for that. The email notifications are sent with the following body by default

Job: %job_owner%.%job_name%.%job_subname%
Event: %event_type%
Date: %event_timestamp%
Log id: %log_id%
Job class: %job_class_name%
Run count: %run_count%
Failure count: %failure_count%
Retry count: %retry_count%
Error code: %error_code%
Error message: %error_message%' 


As you can see the body has a lot of variables such as %job_owner%. These variables are explained here:

 

Variable

Description

%job_owner% 

The owner of the job

%job_name% 

The name of the job

%job_subname% 

When the job is an event based one, this column shows the chain of the events

%event_type% 

The event that triggered the notification, e.g. job_broken, job_started, etc.

%event_timestamp% 

The time the event occurred

%log_id% 

The details of the job execution are located in the views DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_JOB_RUN_DETAILS. The column LOG_ID on those views corresponds to this column.

%error_code% 

The error code, if any

%error_message% 

The error message, if any

%run_count% 

The number of times this job has run

%failure_count% 

The number of times this job has failed

%retry_count% 

The number of times it has been retried after failure


To remove the notification, you can use another procedure in the same package:

begin
 dbms_scheduler.remove_job_email_notification (
  job_name   =>  'process_etl2');
end;
/


Email notification makes the job system complete. Remember, emails are triggered by the Scheduler Job system, not by the code inside the programs or procedures called by the Scheduler. This allows you to set the notification schedule independent of the actual code under the job.