UKOUG Database and AIM SIG 2013
The UKOUG Combined Database and AIM SIG was held at the Institute of Physics in London on 18th July 2013
The keynote was presented by Mike Appleyard and his topic was what is new in Oracle 12c apart from the multi-tenant database. This proved to be an excellent session discussing a number of features that merit further investigation.
The remainder of this post discusses some of highlights of his presentation
In Oracle 12c, the database can connect to ASM using a client server protocol, as opposed the bequeath protocol used in earlier releases. The benefit of this is that ASM clients can connect to an ASM instance on another node. If an ASM instance fails, the ASM clients on the local node will no longer fail immediately; instead they can reconnect to a surviving instance on another node.
In Oracle 12c there are two types of RAC nodes; hub nodes which are traditional RAC nodes and leaf nodes which run a new lightweight cluster stack that allow processing to be offloaded from the busier hub nodes. This new feature offers new architectural possibilities and the immediate question was how this was going to be licenced. Surprisingly the answer is that a leaf node is running Grid Infrastructure, no further licence is required for the leaf node. This does sound too good to be true; either functionality is very limited or Oracle Corporation has become uncharacteristically generous. As only CPU is offloaded, possibly the functionality of the leaf node is limited because it does not perform any I/O.
Later in the pub we agreed this feature definitely requires further investigation as it has the potential to be very significant for some users.
Data Guard Real Time Cascading
Support for Data Guard cascaded databases has been enhanced in Oracle 12c. In Oracle 11gR2, redo would only be forwarded to a cascaded standby database from a physical standby when a redo log switch occurred; in Oracle 12c real-time redo forwarding ensures that redo is forwarded to the cascaded standby database immediately.
The Data Guard Broker has also been enhanced to fully support cascaded standby databases.
Data Guard Fast Sync
In an Oracle 11gR2 Data Guard configuration where redo is being forwarded synchronously, an acknowledgement is only sent by the RFS process when the system call to write the redo to disk has completed successfully. In Oracle 12c, Data Guard Fast Sync can be configured to send the acknowledgement before the redo has been flushed to disk.
Data Guard Other Features
Data Guard has also been enhanced to support
- DML on global temporary tables
- Sequences on the standby database
- Data Guard rolling upgrades
- Additional data types including XML OR, Binary XML, Spatial, Oracle Text and ADTs
In Oracle 12c the RECOVER TABLE command allows one or more tables to be recovered from an RMAN backup
Cross Platform Backup and Restore
Already supported for Exadata only in Oracle 18.104.22.168, in Oracle 12c cross-platform backup and restore is supported for all platforms. This means that it is possible to migrate a database from one platform to another e.g. Solaris to Linux in a single operation. Any endian conversion is performed during the restore operation. The benefit if this feature is that it eliminates a full copy operation of the entire database during the migration, significantly reducing downtime. This feature may also be supported in Oracle 22.214.171.124.
The real power of this feature is that it appears to be able to perform the endian conversion on incremental backup files. So it is possible to perform an initial full backup on the source system and full restore on the target system while the source system remains in production. Subsequent incremental backups of the source system can then be applied to the target system until the databases are logically equivalent at which point the target database can become the production database.
This could turn out to be a killer feature. At first appearance it is competition for Oracle GoldenGate. However, on reflection, GoldenGate supports cross-version migrations and also cross-database replication which RMAN-based features do not. It is interesting that cross-platform backup and restore is being marketed as a feature to migrate from AIX to Linux – now HP-UX is gone AIX is the only big-endian competition for Solaris.
Online Data File Move
In Oracle 12c, data files can be moved online from file systems to ASM and vice versa. This was not supported online in previous versions.
If this works it will become a major new feature for RAC. There are two elements. Transaction Guard is a API that returns the outcome of the last transaction. Application continuity captures in-flight transactions and replays them in a different instance in the event the original node fails.
This is the basic functionality that many believed would be provided by Transparent Application Failover (TAF). In fact TAF supported similar functionality on a read-only (SELECT) basis; Application Continuity supports it for DML as well.
Application continuity will be implemented using additional libraries. It is only supported in limited environments so far including Java (JDBC) and possibly ODBC. Many of my customers have been forced to implement this functionality manually within their applications; I believe that Java supports similar mechanisms already. The Oracle solution will be good for green-field sites, possibly difficult to reverse into existing applications.
Interval Reference Partitioning
This is a new variant of partitioning which combines interval partitioning where range partitions are added automatically if and when data exists for that partition, together with reference partitioning where parent and child partition created is coordinated.
Online Partition Move
In this version it is possible to move partitions online.
Asynchronous Global Index Maintenance
In Oracle 12c it is possible to configure global indexes to be maintained online. This means that operations against partitioned tables with global indexes will complete earlier while any global indexes will continue in the background. The DBA_INDEXES view has a new column called ORPHANED_ENTRIES which will be YES if the index is being updated; NO otherwise.
Partial Local and Global Indexes
In Oracle 12c it is possible to specify that local and/or global indexes should only be created against a subset of partitions for a specific table.
There was some debate about execution plans for these tables. It may be that some decisions have to be deferred until run time as plans may vary for partitions with and without indexes.
There are some clear benefits of this new feature. For range partitioned tables it will allow indexes to be created against the hottest partitions such as the current month, but to be dropped for earlier partitions thereby reducing storage requirements. The reduction in storage requirements will allow additional partial indexes to be created for the most recent partitions thereby potentially improving performance and response times.
It may also be possible to develop alternate indexing strategies for older and newer partitions, providing optimum execution plans for both current and historic partitions.
Implicit sequences are based on an existing ANSI standard. They use the identity column. For example:
CREATE TABLE t1 ( id NAME GENERATED AS IDENTITY, .... );
If the identity column is specified it is no longer necessary to specify the NEXTVAL pseudo-column.
This seems like a good idea and it should be used in new applications. However, it will probably not be possible to justify re-engineering existing applications to use this new feature.
VARCHAR2 Data Type
The maximum size of VARCHAR2 and NVARCHAR2 has increased to 32767 (32K). This includes values of these types returned by functions. Apparently these columns have been implemented the same way as LOBs with columns less than 4K bytes being stored inline and longer columns being stored out of line. Further testing is required to confirm this.
The longer columns will provide clear benefits to developers who can use these columns to store longer text fields. In my opinion, the underlying LOB storage mechanism will also provide clear benefits to independent consultants such as myself, as LOBs are often the root cause of performance issues.
Fetch first rows
A new FETCH FIRST clause allows the number of rows to be fetched by a query to be explicitly limited. This will provide more information for the optimizer allowing it to create better execution plans.
Here are a couple of examples:
SELECT ..... FETCH FIRST 5 ROWS ONLY;
SELECT ..... FETCH FIRST 5 PERCENT ROWS WITH TIES;
There have been some significant changes to support temporal data.
In Oracle 11g flashback data archive allows a table to contain temporal data. A row effectively exists for a limited amount of time; the row contains the start and end times.
Flashback data archive has seen very low levels of adoption. Most sites build similar functionality at database level, so they do not need flashback data archives. More significant, however, was that Flashback Data Archive (formerly Total Recall) was a licensable option and it was very difficult to justify the license costs based on the additional functionality.
I guess some sites must have implemented flashback data archive, but I have not seen it at any of my customers. However, this may be about to change, as flashback data archive is no longer a licensable option and is now freely available in Enterprise Edition. This may see wider adoption of flashback data archive technology, though I suspect this will be slow as there is little communal knowledge or experience with this feature.
In Oracle 12c the flashback data archive has been extended to support schema evolution. In other words it will be possible to perform DDL statements against flashback data archives. There are probably some restrictions in place to limit this functionality, but it is obviously worth further investigation.
In Oracle 12c is it possible to create a table with virtual start and end columns. For example
CREATE TABLE ..... PERIOD FOR user_valid_time (a,b);
When rows are inserted, values will be specified for the start (a) and end (b) times. The end time (at least) can be NULL if it is not currently known.
Many sites use similar mechanisms to handle temporal data which only exists for a limited period of time. It is very common to index the start and end times in order to find the most recent rows. It will be interesting to investigate whether Oracle implements a similar mechanism internally or if additional indexes are required.
The rows returned by a flashback data archive can be controlled by the ENABLE_AT_VALID_TIME procedure in the DBMS_FLASHBACK_ARCHIVE package.
The new data redaction feature allows the database to obfuscate data when presented to specific users. Policies can be implemented to control which users see which data. For example for credit cards, most users only need to see the last four digits e.g. “**** **** **** 3456″
. However some privileged users may need to see then entire card number e.g. “1234 5678 9012 3456″
This is a nice feature that will probably see limited uptake as it is part of the Advanced Security option.
Database Control is now re-engineered as Database Express. The Oracle 12c version is more lightweight based on XDB. Much of the responsibility for rendering has been moved to the browser, reducing the amount of work done on the database server.
The default URL for Database Express is “http://:5500/em/”
Current versions of Enterprise Manager 12c do not automatically support Oracle Database 12c. It is necessary to download a 12c plug-in which was released at the same time as the database. Presumably subsequent Enterprise Manager 12c patch sets will be updated to include this support, so the plug-in download should only be a temporary requirement.
ADDM now runs proactively every 3 seconds searching for known performance issues. The example given was a build-up of latches.
End of post