Thursday Jul 24, 2014

Oracle Database 12c Release is Here!

...with the long awaited In-Memory option, plus 21 new features. Oracle Database 12c Release supports Linux and Oracle Solaris (SPARC and x86 64 bit).

Get the download here.

And for those of us who just LOVE *sparknotes, here's a quick index of those new features with quick links from good friends and scribes in the Oracle Doc group.  But first, a cool picture. 

Sea Dragon photo taken at the Birch Aquarium in San Diego by my friend Joel Broude, a veteran of the Sun Microsystems documentation team. 

Oracle Database 12c Release New Features 

Advanced Index Compression

Advanced Index Compression works well on all supported indexes, including those indexes that are not good candidates for the existing prefix compression feature; including indexes with no, or few, duplicate values in the leading columns of the index.

Advanced Index Compression improves the compression ratios significantly while still providing efficient access to the index.

Approximate Count Distinct

The new and optimized SQL function, APPROX_COUNT_DISTINCT(), provides approximate count distinct aggregation. Processing of large volumes of data is significantly faster than the exact aggregation, especially for data sets with a large number of distinct values, with negligible deviation from the exact result.

The need to count distinct values is a common operation in today's data analysis. Optimizing the processing time and resource consumption by orders of magnitude while providing almost exact results speeds up any existing processing and enables new levels of analytical insight.

Attribute Clustering

Attribute clustering is a table-level directive that clusters data in close physical proximity based on the content of certain columns. This directive applies to any kind of direct path operation, such as a bulk insert or a move operation.

Storing data that logically belongs together in close physical proximity can greatly reduce the amount of data to be processed and can lead to better compression ratios.

Automatic Big Table Caching

In previous releases, in-memory parallel query did not work well when multiple scans contended for cache memory. This feature implements a new cache called big table cache for table scan workload.

This big table cache provides significant performance improvements for full table scans on tables that do not fit entirely into the buffer cache.

FDA Support for CDBs

Flashback Data Archive (FDA) is supported for multitenant container databases (CDBs) in this release.

Customers can now use Flashback Data Archive in databases that they are consolidating using Oracle Multitenant, providing the benefits of easy history tracking to applications using pluggable databases (PDB) in a multitenant container database.

Full Database Caching

Full database caching can be used to cache the entire database in memory. It should be used when the buffer cache size of the database instance is greater than the whole database size. In Oracle RAC systems, for well-partitioned applications, this feature can be used when the combined buffer caches of all instances, with some extra space to handle duplicate cached blocks between instances, is greater than the database size.

Caching the entire database provides significant performance benefits, especially for workloads that were previously limited by I/O throughput or response time. More specifically, this feature improves the performance of full table scans by forcing all tables to be cached. This is a change from the default behavior in which larger tables are not kept in the buffer cache for full table scans.

In-Memory Aggregation

In-Memory Aggregation optimizes queries that join dimension tables to fact tables and aggregate data (for example, star queries) using CPU and memory efficient KEY VECTOR and VECTOR GROUP BY aggregation operations. These operations may be automatically chosen by the SQL optimizer based on cost estimates.

In-Memory Aggregation improves performance of star queries and reduces CPU usage, providing faster and more consistent query performance and supporting a larger number of concurrent users. As compared to alternative SQL execution plans, performance improvements are significant. Greater improvements are seen in queries that include more dimensions and aggregate more rows from the fact table. In-Memory Aggregation eliminates the need for summary tables in most cases, thus simplifying maintenance of the star schema and allowing access to real-time data.

In-Memory Column Store

In-Memory Column Store enables objects (tables or partitions) to be stored in memory in a columnar format. The columnar format enables scans, joins and aggregates to perform much faster than the traditional on-disk formats for analytical style queries. The in-memory columnar format does not replace the on-disk or buffer cache format, but is an additional, transaction-consistent copy of the object. Because the column store has been seamlessly integrated into the database, applications can use this feature transparently without any changes. A DBA simply has to allocate memory to In-Memory Column Store. The optimizer is aware of In-Memory Column Store, so whenever a query accesses objects that reside in the column store and would benefit from its columnar format, they are sent there directly. The improved performance also allows more ad-hoc analytic queries to be run directly on the real-time transaction data without impacting the existing workload.

The last few years have witnessed a surge in the concept of in-memory database objects to achieve improved query response times. In-Memory Column Store allows seamless integration of in-memory objects into an existing environment without having to change any application code. By allocating memory to In-Memory Column Store, you can instantly improve the performance of their existing analytic workload and enable interactive ad-hoc data extrapolation.

JSON Support

This feature adds support for storing, querying and indexing JavaScript Object Notation (JSON) data to Oracle Database and allows the database to enforce that JSON stored in the Oracle Database conforms to the JSON rules. This feature also allows JSON data to be queried using a PATH based notation and adds new operators that allow JSON PATH based queries to be integrated into SQL operations.

Companies are adopting JSON as a way of storing unstructured and semi-structured data. As the volume of JSON data increases, it becomes necessary to be able to store and query this data in a way that provides similar levels of security, reliability and availability as are provided for relational data. This feature allows information represented as JSON data to be treated inside the Oracle database.

See: Oracle XML DB Developer's Guide for details.

New FIPS 140 Parameter for Encryption

The new database parameter, DBFIPS_140, provides the ability to turn on and off the Federal Information Processing Standards (FIPS) 140 cryptographic processing mode inside the Oracle database.

Use of FIPS 140 validated cryptographic modules are increasingly required by government agencies and other industries around the world. Customers who have FIPS 140 requirements can turn on the DBFIPS_140parameter.

See: Oracle Database Security Guide for details.


The CONTAINERS clause is a new way of looking at multitenant container databases (CDBs). With this clause, data can be aggregated from a single identical table or view across many pluggable databases (PDBs) from the root container. The CONTAINERS clause accepts a table or view name as an input parameter that is expected to exist in all PDBs in that container. Data from a single PDB or a set of PDBs can be included with the use of CON_ID in the WHERE clause. For example:

SELECT ename FROM CONTAINERS(scott.emp) WHERE CON_ID IN (45, 49);

This feature enables an innovative way to aggregate user-created data in a multitenant container database. Reports that require aggregation of data across many regions or other attributes can leverage the CONTAINERS clause and get data from one single place.

PDB File Placement in OMF

The new parameter, CREATE_FILE_DEST, allows administrators to set a default location for Oracle Managed Files (OMF) data files in the pluggable database (PDB). When not set, the PDB inherits the value from the root container.

If a file system directory is specified as the default location, then the directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files. A file created in this manner is an Oracle-managed file.

The CREATE_FILE_DEST parameter allows administrators to structure the PDB files independently of the multitenant container database (CDB) file destination. This feature helps administrators to plug or to unplug databases from one container to another in a shared storage environment.

PDB Logging Clause

The PDB LOGGING or NOLOGGING clause can be specified in a CREATE or ALTER PLUGGABLE DATABASE statement to set or modify the logging attribute of the pluggable database (PDB). This attribute is used to establish the logging attribute of tablespaces created within the PDB if the LOGGING clause was not specified in the CREATE TABLESPACE statement.

If a PDB LOGGING clause is not specified in the CREATE PLUGGABLE DATABASE statement, the logging attribute of the PDB defaults to LOGGING.

This new clause improves the manageability of PDBs in a multitenant container database (CDB).

PDB Metadata Clone

An administrator can now create a clone of a pluggable database only with the data model definition. The dictionary data in the source is copied as is but all user-created table and index data from the source is discarded.

This feature enhances cloning functionality and facilitates rapid provisioning of development environments.

PDB Remote Clone

The new release of Oracle Multitenant fully supports remote full and snapshot clones over a database link. A non-multitenant container database (CDB) can be adopted as a pluggable database (PDB) simply by cloning it over a database link. Remote snapshot cloning is also supported across two CDBs sharing the same storage.

This feature further improves rapid provisioning of pluggable databases. Administrators can spend less time on provisioning and focus more on other innovative operations.

PDB Snapshot Cloning Additional Platform Support

With the initialization parameter CLONEDB set to true, snapshot clones of a pluggable database are supported on any local, Network File Storage (NFS) or clustered file systems with Oracle Direct NFS (dNFS) enabled. The source of the clone must remain read-only while the target needs to be on a file system that supports sparseness.

Snapshot cloning support is now extended to other third party vendor systems.

This feature eases the requirement of specific file systems for snapshot clones of pluggable databases. With file system agnostic snapshot clones, pluggable databases can be provisioned even faster than before.


The STANDBYS clause allows a user to specify whether a pluggable database (PDB) needs to be a part of the existing standby databases. The STANDBYS clause takes two values: ALL and NONE. While ALL is the default value, when a PDB is created with STANDBYS=NONE, the PDB's data files are offlined and marked as UNNAMED on all of the standby databases. Any of the new standby databases instantiated after the PDB has been created needs to explicitly disable the PDB for recovery to exclude it from the standby database. However, if a PDB needs to be enabled on a standby database after it was excluded on that standby database, PDB data files need to be copied to the standby database from the primary database and the control file needs to be updated to reflect their paths after which a user needs to connect to the PDB on that standby database and issue ALTER PLUGGABLE DATABASE ENABLE RECOVERY which automatically onlines all of the data files belonging to the PDB.

This feature increases consolidation density by supporting different service-level agreements (SLAs) in the same multitenant container database (CDB).

PDB State Management Across CDB Restart

The SAVE STATE clause and DISCARD STATE clause are now available with the ALTER PLUGGABLE DATABASE SQL statement to preserve the open mode of a pluggable database (PDB) across multitenant container database (CDB) restarts.

If SAVE STATE is specified, open mode of specified PDB is preserved across CDB restart on instances specified in the INSTANCES clause. Similarly, with the DISCARD STATE clause, the open mode of specified PDB is no longer preserved.

These new SQL clauses provide the flexibility to choose the automatic startup of application PDBs when a CDB undergoes a restart. This feature enhances granular control and effectively reduces downtime of an application in planned or unplanned outages.

PDB Subset Cloning

The USER_TABLESPACES clause allows a user to specify which tablespaces need to be available in the new pluggable database (PDB). An example of the application of this clause is a case where a customer is migrating from a non-multitenant container database (CDB) where schema-based consolidation was used to separate data belonging to multiple tenants to a CDB where data belonging to each tenant is kept in a separate PDB. TheUSER_TABLESPACES clause helps to create one PDB for each schema in the non-CDB.

This powerful clause helps convert cumbersome schema-based consolidations to more agile and efficient pluggable databases.

Rapid Home Provisioning

Rapid Home Provisioning allows deploying of Oracle homes based on gold images stored in a catalog of pre-created homes.

Provisioning time for Oracle Database is significantly improved through centralized management while the updating of homes is simplified to linkage. Oracle snapshot technology is used internally to further improve the sharing of homes across clusters and to reduce storage space.

Zone Maps

For full table access, zone maps allow I/O pruning of data based on the physical location of the data on disk, acting like an anti-index.

Accessing only the relevant data optimizes the I/O necessary to satisfy a query, increasing the performance significantly and reducing the resource consumption.

See: Oracle Database Data Warehousing Guide for details.

Ciao for now!


Wednesday Jul 16, 2014

Oracle Critical Patch Advisory -- July 15, 2014

ATTN: Oracle DBAs: Oracle has published its Critical Patch Advisory dated July 15, 2014.  Get the advisory Here

Here's what you need to know:

This Critical Patch Update provides 113 new security fixes across a wide range of product families including: Oracle Database, Oracle Fusion Middleware, Oracle Java SE, Oracle Linux and Virtualization, Oracle MySQL, Oracle Hyperion, Oracle Enterprise Manager Grid Control, Oracle E-Business Suite, and Oracle Business Applications and more.

REMEMBER: Critical Patch Update fixes are intended to address significant security vulnerabilities in Oracle products and also include code fixes that are prerequisites for the security fixes. As a result, Oracle recommends that this Critical Patch Update be applied as soon as possible. 

Read more about the specific fixes here.

Get the Critical Patch Advisory on  OTN here.

Ciao for Now!


Tuesday Jul 15, 2014

Oracle Big Data SQL - Bringing Structured Queries to an Unstructured World

Oracle has just launched Oracle Big Data SQL.  Watch the replay of the web launch here.

Oracle Big Data SQL

And it's interesting.  Big Data is unstructured. It's widely varied. And it "sits" in many places. Stovepipes. Caves. Vaults. And thanks to innovations from the Exadata technologies, this unstructured data can now be successfully and efficiently queried using Structured Query Language. (SQL)  That is to say: An approach to providing unified query over data in Oracle Database, Hadoop and select NoSQL datastores.  It's a step beyond "federated" query.  It's actually much more than that. 

It's the ability to run SQL queries that work seamlessly across multiple datastores. As Dan McClary points out in his blog on today's announcement, "Ideally, in a Big Data world, SQL should "play data where it lies," using the declarative power of the language to provide answers from all data."

All data? or just the relevant data?  Oracle Big Data SQL decided to go for Relevant Data by empowering very complex SQL statements that zero in on the relevant information across all these datastores to get a more valuable set of data that clearly pinpoints trends to analyze.

How? Unifying Metadata and Optimizing Performance.  

Dan McClary's blog gives an excellent overview.

Get more information.

And watch for the download that will be available later this quarter on the OTN Database Community page.

Ciao for Now!


Thursday Jul 10, 2014

Database Application Development VM--Get It Now

Learning your way around a new software stack is challenging enough without having to spend multiple cycles on the install process. Instead, we have packaged such stacks into pre-built Oracle VM VirtualBox appliances that you can download, install, and experience as a single unit. Just downloaded/assemble the files, import into VirtualBox (available for free), import, and go (but not for production use or redistribution)!

Some of these VMs are designed to support Developer Day workshops, and have specific hands on labs embedded in them, but they're available to all. Just be sure to install VirtualBox first.

VirtualBox is powerful Cross-platform Virtualization Software for x86-based systems.
"Cross-platform" means that it installs on Windows, Linux, Mac OS X and Solaris x86 computers. And "Virtualization Software" means that you can create and run multiple Virtual Machines, running different operating systems, on the same computer at the same time. For example, you can run Windows and Linux on your Mac, run Linux and Solaris on your Windows PC, or run Windows on you Linux systems. 

Oracle VM VirtualBox is also available as Open Source or pre-built Binaries for Windows, Linux, Mac OS X and Solaris. GET IT HERE.

The DBA/DEV Crowd Favorite:    --> The Database Application Development VM <--

Kris Rice spends a lot of time grooming this little virtual environment...see all the exercises and tutorials he has included and let us know what you think!  

Ciao for Now!


Tuesday Jul 08, 2014

What's New in Oracle JDBC?

 If you're talking database connectivity, JDBC deserves a good look.

According to Sun's JDBC page, the Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases—SQL databases and other tabular data sources, such as spreadsheets or flat files. The JDBC API provides a call-level API for SQL-based database access. JDBC technology allows you to use the Java programming language to exploit "Write Once, Run Anywhere" capabilities for applications that require access to enterprise data. With a JDBC technology-enabled driver, you can connect all corporate data even in a heterogeneous environment.

Which version of JDBC drivers support which version of Oracle database?

Check out this table for the Oracle database versions supported by JDBC drivers.  Best Practice that we recommend is, JDBC driver version should always be either same as or higher than the Oracle database version being used in order to leverage the latest capabilities of the JDBC driver. 

Interoperability Matrix Database 12.1.0.x Database 11.2.0.x Database 11.1.0.x


JDBC 12.1.0.x
JDBC 11.2.0.x
JDBC 11.1.0.x
JDBC 10.2.0.x

Whats New in JDBC?

  • Support for Latest Java Standards:  Oracle JDBC and UCP now support JSE 7 with JDBC 4.1 specification. 

  • Support for Multitenant Container Support:The Oracle database 12c introduces a new multitenant architecture consisting of a root infrastructure called Container Database(CDB) which contains exclusively Oracle provided metadata, then a set of pluggable databases(PDBs), which are full fledged databases containing customers, applications data and metadata. Oracle JDBC and UCP furnish the Multitenant Datasource for Java by allowing sharing a single pool of connections across multiple PDBs i.e., tenants.  

  • Support for New SQL DataTypes: Java pplications may leverage new data types including: 32K VARCHAR, NVARCHAR, and RAW, invisible/hidden columns, implicit results, auto-increment or IDENTITY columns, PL/SQL packaged types and AS parameters, larger row count data type, and XStream enhancements. 

  • Improved Performance and Scalability:New performance and scalability enhancements include: New JDBC memory managemen, Database Resident Connection Pool (DRCP), very large network buffers(SDU), and RuntimeConnections Load Balancing across geographies (Global Data Services). 

  • Transaction Guard and Application Continuity for Java: When a database outage occurs, four problems confront applications:(1) hangs, (2) errors, (3) determining the outcome of in-flight work and (4) the resubmission of in-flight work.  Oracle database 12c handles these outages better throughTransaction Guard for a reliable outcome of in-flight work and Application Continuity for capturing and replaying in-flight transactions.

  • Better Manageability, Ease of use:Oracle database 12c furnishes row count per iteration for array DML,monitoring and tracing database operations, intelligent client connectivity and faster dead connection detection. 

  • Advanced Security:With Oracle database 12c, JDBC now supports SHA-2 hashing algorithms(including:SHA-256, SHA-384,and SHA-512) to generate secure message digests. Overall,Java applications can use the following hashing algorithms:MD5, SHA1, SHA-256, SHA-384 or SHA-512. 

  • Easier Applications Migration: Solves the problem for migrating Java applications built against non Oracle RDBMS.  Oracle 12c provides a framework for translating foreign SQL syntax into Oracle SQL syntax before being submitted to the Oracle RDBMS, SQL engine for compilation and execution. 

    Get the whole story from the JDBC page on OTN.

    Download JDBC Drivers and Documentation HERE.

    Ciao for now!



The OTN DBA/DEV Watercooler is your official source of news covering Oracle Database technology topics and community activities from throughout the OTN Database and Developer Community. Find tips and in-depth technology information you need to master Oracle Database Administration or Application Development here. This Blog is compiled by @oracledbdev, the Oracle Database Community Manager for OTN, and features insights, tech tips and news from throughout the OTN Database Community.


« July 2014 »