Oracle Database 12c review: Finally, a true cloud database
Pluggable databases bring a new level of efficiency and ease to database consolidation, while a wealth of other new features address performance, availability, and more
Imagine a global environment where different databases are used to serve different segments of users. For example, a database in New York serves users on the East Coast, while a database in San Francisco serves users on the West Coast, and both databases are synchronized by replication software. In Database 12c, services are truly global, and a global equivalent of the familiar SCAN (Single Client Access Name) listener, called the Global Data Listener, is utilized to redirect the application connection to a database that can better serve the specific client. This feature also improves availability because new connections to failed databases can be redirected quickly to a surviving database.
After a service failover to another instance, applications usually do not know the status of in-flight transactions. While the changes made by a committed transaction are permanent, as dictated by the ACID properties of Oracle Database, commit status messages to the application are transient. The result is that an instance failure creates a classic dilemma: If the application reissues an already committed transaction it can lead to logical corruption, but if the application does not reissue a failed transaction then changes can be permanently lost.
Database 12c resolves this dilemma with Transaction Guard, a new feature that maintains transaction status permanently. Transaction Guard assigns a unique global transaction ID to each transaction, and maintains the status of that global transaction for a predefined period of time. After a failover, the application can requery the status of a transaction and take corrective action deterministically.
Oracle did not stop at merely providing a mechanism to identify transaction status. Version 12c also introduces Application Continuity. With this feature, a new client-side replay driver remembers submitted SQL statements, and after the failure detection, statements are replayed to insert failed transactions into the database. Note that code changes may be required to safely integrate the replay driver with the application, though.
No database release is complete without partitioning enhancements, and Database 12c is not an exception.
Traditionally, indexes are created in all partitions of a partitioned table. Version 12c introduces Partial Indexing, which allows you to create indexes on a partial set of partitions. For example, if you have partitions p1, p2, p3 ... p10 in a table, you could choose to create an index on partitions p1, p2, and p3 only, and not to create indexes on the other partitions. This feature will be useful in time-variant partitioning schemes, typically in which older partitions are mostly only queried and newer partitions are updated heavily.
We can reduce transaction workload by deferring indexes on transaction-intensive partitions and add indexes only when the partitions become less transaction-intensive. Also, the user can build new indexes in multiple steps to reduce locking and resource consumption issues associated with a traditional index rebuild. Of course, you probably would have guessed, partition pruning is a key requirement for the Optimizer to choose partial indexes, and partial indexes are not supported for unique indexes.
Global indexes cause problems for partition-level operations. For example, if a partition is dropped, then a global index on that table must be updated. While the drop/truncate partition commands are fast (because they are DDL operations), updates to global index entries are far slower, leading to availability issues during DDL operations. For this reason, Version 12c decouples global index maintenance from DDL command execution. Thus, only global index metadata is updated during partition DDL operation, and actual index maintenance can be deferred for a later time. A new scheduled job lets you clear stale global index entries at, say, 2 a.m., improving the viability of global indexes on critical tables.
Also in Database 12c, reference partitioning now truncates dependent partitions. With a reference partitioning scheme, tables in both ends of a referential constraint are partitioned along the same partitioning boundaries. From 12c onward, truncating or dropping a parent table partition will cascade to child table partitions.
Backup and recovery
Traditionally, restoring a single table is a cumbersome process involving a tablespace restore, exporting the restored table from the restored tablespace, and then importing to the production database. The new restore table command in Recovery Manager (RMAN) simplifies this task.
Re-instantiation of a primary database to a standby site or Data Guard site is a huge problem for VLDB (Very Large Database) sites, especially if the database is separated by thousands of miles. Prior to Database 12c, RMAN did not support native compression during active duplication, so generally, DBAs resorted to another method of restoring from backup, such as copying files over the network through a compressed pipe, or even shipping a tape. In Database 12c, RMAN supports datafile copies over the network with compression. This feature will ease database cloning efforts tremendously. Also, the Active Duplicate command supports network compression during the data transfer, enabling faster clones directly from the production database.
Executing SQL statements from the RMAN command line is not only unwieldy, but the syntax is not exactly user-friendly. Version 12c enhances the RMAN command line so that you can execute SQL statements natively in RMAN without needing the additional SQL clause.