Secure Files and Improved Database Security and Partitioning Upgrades

Secure Files

Oracle Database 11g provides a series of brand-new methods for storing large binary objects (also known as LOBs) inside the database. These new features, collectively called Secure Files, will allow Oracle Database 11g to store images, extremely large text objects, and the more advanced data types introduced in prior Oracle releases (e.g., XML Type, Spatial, and medical imaging objects that utilize the DICOM [Digital Imaging and Communications In Medicine] format). Secure Files promises to offer performance that com-pares favorably with file system storage of these object types, as well as the ability to transparently compress and "deduplicate" these data. (Deduplication is yet another brand-new feature in Oracle Database 11g. It can detect identical LOB data in the same LOB column

that's referenced in two or more rows, and then stores just one copy of that data, thus reducing the amount of space required to store these LOBs.) Perhaps most importantly, Oracle Database 11g will also ensure that these data can be encrypted using Transparent Data Encryption (TDE) methods - especially important (and welcome) in the current security-conscious environments we inhabit today as database administrators.

Improved Database Security

Oracle Database 10g Release 2 dramatically improved the options for encrypting sensitive data both within Oracle database tables and indexes, as well as outside the database (i.e., RMAN backups and Data Pump export files) with Transparent Data Encryption (TDE). Oracle Database 11g continues to expand the use of TDE within the database. For example, it's now possible to encrypt data at the tablespace level as well as the table and index level. Also, logical standby data-bases can utilize TDE to protect data that's been transferred from its corresponding primary standby database site. Moreover, secured storage of the TDE master encryption key is ensured by allowing it to be stored

externally from the database server in a separate Hardware Security Module. Secure By Default. Oracle Database 11g also implements a new set of out-of-the-box security enhancements that are collectively called Secure By Default. These security settings can be enabled during data-base creation via the Database Configuration Assistant (DBCA), or they can be enabled later after the data-base has been created. Here's a sample of these new security features-

# Every user account password is now checked automatically to ensure sufficient password complexity is being used.

# To further strengthen password security, the DEFAULT user profile now sets standard values for password grace time, lifetime, and lock time, as well as for the maximum number of failed login attempts

# Auditing will be turned on by default for over 20 of the most sensitive DBA activities (e.g., CREATE ANY PROCEDURE, GRANT ANY PRIVILEGE, DROP USER, and so forth). Also, the AUDIT_TRAIL parameter is set to DB by default when the database is created, so this means that a data-base "bounce" will no longer be required to activate auditing

# Fine-Grained Access Control (FGAC) is now available for network callouts when using raw TCP (e.g.,

via the UTL_TCP package), FGAC will be able to construct Access Control Lists (ACLs) to provide fine-grained access to external network services for specific Oracle Database 11g database user accounts.

# Enterprise Manager now provides interfaces for direct management of the External Security Module

(ESM), Fine-Grained Auditing (FGA) policies, and Row-Level Security (RLS) policies.

# Finally, an RMAN recovery catalog can now be secured via Virtual Private Catalog to prevent unauthorized users from viewing backups that are registered within the catalog.

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 data type 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 sub-partitions 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.

Advisors and Fault Diagnostics and Flashback Enhancements

Advisors and Fault Diagnostics

Oracle Database 10g introduced an impressive plethora of database performance advisors like the Segment Advisor, the Undo Advisor, the SQL Access Advisor, the SQL Tuning Advisor, the MTTR Advisor, and the ultimate expert system for tuning database performance: the Automatic Database Diagnostic Monitor (ADDM). Oracle Database 11g expands this advisory framework with several new Database Repair Advisors. The chief goals of these new Advisors are to locate root causes of a failure, identify and present options for repairing these root causes, and even correct the identified problems with self-healing mechanisms. Oracle Database 11g also adds a series of improved fault diagnostics to make it extremely easy for even an inexperienced DBA to detect and quickly resolve problems with Oracle Database 11g. Here are the highlights of these new features-

# Automatic Health Monitoring. When a problem within the database is detected, the new Health Monitor (HM) utility will automatically perform a series of integrity checks to determine if the problem

can be traced to corruption within database blocks, redo log blocks, undo segments, or dictionary table blocks. HM can also be fired manually to perform checks against the database's health on a periodic basis.

# Automatic Diagnostic Repository. The Automatic Diagnostic Repository (ADR) is at the heart of Oracle Database 11g's new fault diagnostic framework. The ADR is a central, file-based repository external to the database itself, and it's composed of the diagnostic data -- alert logs (in XML format), core dumps, back-ground process dumps, and user trace files -- collect-ed from individual database components from the first moment that a critical error is detected.

# Support Workbench. Though it's stored outside of the database itself, the ADR can be accessed via

either Enterprise Manager or command-line utilities. Once the ADR has detected and reported a critical

problem, the DBA can interrogate the ADR, report on the source of the problem, and in some cases

even implement repairs through the Support Workbench, a new facility that's part of Enterprise Manager.

# Incident Packaging Service. If the problem can't be solved using these tools, it may be time to ask for help from Oracle Support. The new Incident Packaging Service (IPS) facility provides tools for

gathering and packaging all necessary logs that Oracle Support typically needs to resolve a Service

Request.  

# Hang Manager. Oracle Database 10g introduced the Hang Analysis tool in Enterprise Manager, and

Oracle Database 11g now expands this concept with the Hang Manager. Through a series of dynamic

views, it allows the DBA to traverse what's called a hang chain to determine exactly which processes and sessions are causing bottlenecks because they are blocking access to needed resources. And since it's activated by default on all single-instance databases, RAC clustered databases, and ASM instances, it's now possible to track down the source of a hang from one end of the system to the other.

Flashback Enhancements

Oracle Database 10g dramatically expanded database recoverability with the ability to perform an incomplete recovery of the database with Flashback Database. Oracle Database 10g also provided four new logical database recovery features: Flashback Table, Flashback Drop, Flashback Version Query, and Flashback Transaction Query. Oracle Database 11g expands this arsenal of recovery tools with two new. Flashback features-

# Flashback Transaction. Essentially an extension of the Flashback Transaction Query functionality introduced in Oracle Database 10g, Flashback Transaction allows the DBA to back out of the database one or more transactions -- as well as any corresponding dependent transactions -- by applying the appropriate reciprocal UNDO statements for the affected transaction(s) to the corresponding affected rows in the database.

# Total Recall. This new feature offers the ability to retain the reciprocal UNDO information for critical data significantly beyond the point in time that it would be flushed out of the UNDO tablespace. Therefore, it's now possible to hold onto these reciprocal transactions essentially indefinitely. Once this feature is enabled, all retained transaction history can be viewed, and this eliminates the cumbersome task of creating corresponding history tracking tables for critical transactional tables. And as you might expect, Oracle Database 11g also provides methods to automatically purge data retained in the data archive once a specified retention period has been exceeded.

Result Caches and Improved SQL Tuning

Result Caches

I've often wished that the Oracle database would pro-vide a method to retain in memory the result set from a complex query that contains what I like to call reference information. These are data that hardly ever change, but must still be read and used across multiple applications. For instance, a list of all country codes and their corresponding names for lookup when processing addresses for new international customers, or a list of all the zip codes of North India. Oracle Database 11g fills this gap with three new structures called result caches, and each structure has a different purpose:

# The SQL query result cache is an area of memory in the Shared Global Area (SGA) that can retain the result sets that a query generates.

# The PL/SQL function result cache can store the results from a PL/SQL function call.

# Finally, the client result cache can retain results from queries or functions on the application server from which the call originated.

By retaining result sets in these in-memory caches, the results are immediately available for reuse by any user session. For user sessions that connect to the database through an application server, the client cache permits those sessions to simply share the results that are already cached on the application server without having to reissue a query directly against the database. These result caches therefore hold great promise for eliminating unnecessary "round trips" to the database

server to collect relatively static reference data that still.

Improved SQL Tuning

If you've already experienced the advice for SQL performance improvements that Oracle Database 10g's SQL Tuning Advisor and SQL Access Advisor provide, you'll be pleasantly surprised with Oracle Database 11g's enhanced SQL tuning capabilities. Here's a brief sample-

# SQL statements can now tune themselves via an expansion to the automatic SQL tuning features that were introduced in Oracle Database 10g.

# Statistics for the Cost-Based Optimizer (CBO) are now published separately from being gathered. This means that recomputed statistics for the CBO will not necessarily cause existing cursors to become invalidated.

# Multi-column statistics can be collected for two or more columns in a table. This gives the CBO the ability to more accurately select rows based on common multi-column conditions or joins.

# SQL Access Advisor can now make recommendations on how partitioning might be applied to

existing tables, indexes, and materialized views to improve an application's performance.  

# Oracle Database 11g now supports retention of historical execution plans for a SQL statement. This

means that the CBO can compare a new execution plan against the original plan and, if the old plan still offers better performance than the new one, it can decide to continue to use the original execution plan.

New System Testing Tools

As a DBA, one of the most bedeviling problems that I've regularly faced is to be able to predict accurately how the next set of changes to the database's application code, database patch set, or hardware configuration will affect that database's performance. That usually meant purchasing a relatively expensive third-party package (e.g., Mercury Interactive's  Load Runner) to generate a sample workload against the database using the next version of the application code, and then comparing the results against baseline performance for the current application code version.

Fortunately, Oracle Database 11g has come to the res-cue with two new utilities that offer monumental strides forward in system testing-

Database Replay

System changes such as a database upgrade require substantial testing and validation before you can actually migrate the changes to a production system. The trick is to simulate a real production workload on a test system. The Database Replay feature enables you to perform real-life testing of major changes by letting you capture the actual database workload on the production system and replay it on a test system. Thus, you essentially re-create the production workload effortlessly on a test system. Database Replay performs a sophisticated replay of the production workload by adhering to the original concurrency and timing characteristics. Once you complete the testing, you can analyze and review the reports produced by Database Replay to see if there was a performance divergence between the two runs and also if there were any errors. Finally, you can choose to implement the recommendations made by Database Replay to fix any problems it encountered during the replay of the production workload.

You can employ Database Replay to test significant system changes such as the

following:

#Operating system and database upgrades and migrations.

# Configuration changes such as moving to an oracle RAC environment.

# Storage changes.

Database Replay doesn’t capture the following types of client requests:

# SQL*Loader direct path load of data

# Oracle Streams

# Data Pump Import and Export

# Advanced replication streams

# Non–PL/SQL-based Advanced Queuing (AQ)

# Flashback Database and Flashback queries

# Distributed transactions and remote describe/commit operations

# Shared server

# Non–SQL-based object access

The following data dictionary views help you manage the Database Replay feature:

# DBA_WORKLOAD_CAPTURES - It shows all workload captures you performed in a database.

# DBA_WORKLOAD_FILTERS – It shows all workload filters you defined in a database.

# DBA_WORKLOAD_REPLAYS – It shows all workload replays you performed in a database.

# DBA_WORKLOAD_REPLAY_DIVERGENCE – It helps monitor workload divergence.

# DBA_WORKLOAD_THREAD – It helps monitor the status of external replay clients.

# DBA_WORKLOAD_CONNECTION_MAP – It shows all connection strings used by workload replays.

SQL Performance Analyzer

SQL Performance Analyzer, which, along with the Database Replay constitutes the Total Replay

feature, lets you test the impact of potential changes such as a server or database upgrade on SQL workload response time. The SQL Performance Analyzer focuses on comparing the performance of a specific SQL workload before and after a major system change. The analyzer does this by building two versions of the SQL workload performance, which includes both the SQL execution plans as well as their execution statistics. After analyzing SQL performance both before and after you make a major

change, the SQL Performance Analyzer provides suggestions to prevent potential performance degradation of SQL statements. This is especially handy when you’re planning an upgrade of your database to a newer release of the Oracle database. The SQL Performance Analyzer, by enabling you to compare SQL performance on two systems running on different versions of the Oracle database, lets you know ahead of the upgrade which of the SQL statements may show a deterioration in performance. Thus, you can reengineer those statements prior to the actual upgrade.

You can use the SQL Performance Analyzer to predict performance changes resulting from the following system changes:

# Database and application upgrades

# Hardware upgrades

# Operating system upgrades

# Initialization parameter changes

# SQL tuning actions such as the creation of SQL profiles

# Statistics gathering

# Schema changes