Oracle Database 12c Release 1: What's New, Release History & Support Lifecycle

What Is New in Oracle Database 12c Release 1

What is the Oracle Multitenant architecture and why does it matter in 12c?

The Oracle Multitenant architecture is the single most impactful structural change in 12c: it allows one physical Oracle database instance -- called a Container Database (CDB) -- to host many fully isolated, portable Pluggable Databases (PDBs). Each PDB looks and behaves like a classic pre-12.1 Oracle database to its applications, but shares the CDB's background processes, memory, and binary.

In practice, this means you can consolidate dozens of previously separate databases onto one server, each in its own PDB, without rewriting a single line of application code. Provisioning a new environment that used to take hours drops to seconds: you clone a PDB, plug it in, and it is running. Patching and upgrades become a single operation at the CDB level instead of one per database.

The key PDB operations to know are:

• Plug / Unplug -- a PDB can be unplugged from one CDB and plugged into another, making cross-version or cross-platform migration straightforward.
• Clone -- including metadata-only clone (data model only, no user data), remote clone over a database link, and snapshot clone on file systems supporting sparse files.
• PDB SAVE STATE -- preserves the PDB open mode across CDB restarts, so PDBs reopen automatically after a bounce.
• CONTAINERS clause -- allows a query from CDB root to aggregate results across all PDBs in a single SQL statement.

-- Open all PDBs and save their state across restarts
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL SAVE STATE;

-- Query a table across all PDBs from root
SELECT con_id, ename
FROM CONTAINERS(scott.emp)
WHERE con_id IN (3, 4, 5);

Watch out for licensing: hosting more than one user-created PDB in a CDB requires the Multitenant option. A single user-defined PDB per CDB is free. The non-CDB architecture still works in 12.1 but is deprecated, and planning for CDB adoption is now a mandatory part of any 12c upgrade strategy.

How does Oracle 12c improve query performance with Adaptive Query Optimization and In-Memory?

Oracle 12c introduces two major performance paradigms: Adaptive Query Optimization, which allows the optimizer to correct its own estimation mistakes at runtime, and the In-Memory Column Store (released in 12.1.0.2), which stores table data in a columnar format in SGA memory for dramatically faster analytic queries.

Adaptive Query Optimization

The optimizer can now switch join methods mid-execution when actual row counts diverge from estimates. For example, a NESTED LOOP chosen for an estimated cardinality of 1 is automatically replaced with a HASH JOIN once 1,000 rows have been processed. This is called an adaptive plan. Separately, automatic reoptimization monitors the first execution of a query and uses its real statistics when the same query runs again.

SQL Plan Directives complement this by persisting compilation and execution-time statistics in SYSAUX so they are reused across multiple SQL statements -- not just within a single cursor lifetime. Most teams will notice fewer sudden plan regressions after statistics refreshes.

In-Memory Column Store (12.1.0.2)

By setting the INMEMORY_SIZE parameter, you allocate a portion of SGA as a columnar store. Tables or partitions marked INMEMORY are loaded there transparently; the optimizer automatically routes analytical queries to the column store. No application changes are required.

-- Enable In-Memory Column Store (requires restart)
ALTER SYSTEM SET inmemory_size = 4G SCOPE=SPFILE;

-- Populate a table into the column store
ALTER TABLE sales INMEMORY;

-- Monitor column store population
SELECT segment_name, populate_status, bytes_not_populated
FROM v$im_segments;

In-Memory Aggregation further accelerates star schema queries by introducing KEY VECTOR and VECTOR GROUP BY operations that the optimizer can choose automatically. This is particularly effective for data warehouse workloads where joins between large fact tables and dimension tables dominate runtime.

Also notable in 12.1.0.2: Full Database Caching forces all tables -- including large ones previously excluded from the buffer cache -- to be cached when the buffer cache exceeds total database size. And APPROX_COUNT_DISTINCT() offers approximate COUNT DISTINCT with negligible deviation and a fraction of the CPU cost, useful for analytics on very high-cardinality columns.

What SQL language changes were introduced in Oracle Database 12c Release 1?

Oracle 12c Release 1 closes long-standing gaps between Oracle SQL and ANSI standards while also adding capabilities that meaningfully reduce migration friction from other database vendors.

The most immediately useful additions are:

• FETCH FIRST / OFFSET: Native top-N and pagination without rownum tricks. SELECT ... FETCH FIRST 10 ROWS ONLY and OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY are now valid Oracle SQL, compliant with ANSI SQL:2008.
• IDENTITY Columns: Tables can now declare auto-incrementing identity columns directly in DDL, eliminating the sequence-plus-trigger pattern that Oracle developers have used for decades.
• DEFAULT ON NULL: The DEFAULT clause now accepts an ON NULL modifier, so the database applies the default even when a NULL is explicitly inserted -- not just when the column is omitted.
• Extended VARCHAR2 / NVARCHAR2 / RAW: Maximum size raised from 4,000 to 32,767 bytes when MAX_STRING_SIZE=EXTENDED and COMPATIBLE >= 12.0. Run utl32k.sql in UPGRADE mode first.
• JSON support: Store, query, and index JSON with IS JSON constraints and dot-notation path expressions in SQL. JSON data can be validated, searched, and joined against relational data without external libraries.
• MATCH_RECOGNIZE: Native row pattern matching in SQL using regular expression syntax to define and detect event sequences across ordered rows -- invaluable for financial time series, clickstream analysis, and log parsing.
• PL/SQL functions in the WITH clause: Procedural logic for a query can now be scoped inside the WITH clause, avoiding schema-level function creation and often yielding better performance.
• Temporal Validity: Rows can carry valid-time period dimensions declared directly in the table DDL, with AS OF and VERSIONS BETWEEN Flashback Query extended to valid-time intervals.

-- Identity column (no sequence or trigger needed)
CREATE TABLE orders (
  order_id  NUMBER GENERATED ALWAYS AS IDENTITY,
  customer  VARCHAR2(100),
  amount    NUMBER
);

-- Pagination with OFFSET / FETCH
SELECT product_id, revenue
FROM sales
ORDER BY revenue DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- PL/SQL function scoped inside WITH clause
WITH FUNCTION calc_discount(p_amount NUMBER) RETURN NUMBER IS
BEGIN
  RETURN p_amount * 0.9;
END;
SELECT product_id, calc_discount(list_price) AS discounted
FROM products;

The SQL Translation Framework is notable for migration projects: it allows SQL submitted from client applications to be intercepted and rewritten before hitting the Oracle parser. Teams migrating from SQL Server or Sybase can implement dialect translation transparently, without changing application code.

How does Oracle 12c improve high availability with Application Continuity and Data Guard?

Oracle 12c Release 1 delivers two categories of HA advancement: Application Continuity, which masks outages from running applications by replaying in-flight requests, and a significantly improved Data Guard with new protection modes, simplified management, and reduced RPO options.

Application Continuity and Transaction Guard

Application Continuity (available through Universal Connection Pool and JDBC Thin) intercepts a recoverable outage below the application layer and replays the uncommitted request against a new server session. From the application's perspective, the database ran slowly -- not failed. Transaction Guard underpins this by giving the database a durable record of every transaction's commit outcome (the Logical Transaction ID, LTXID), so replay never double-commits a transaction.

Most teams will configure this through the service definition rather than application code. The key requirement is that the application uses a supported connection pool and does not hold session state that cannot be replayed (for example, non-transactional state changes mid-request).

Data Guard Enhancements

• Far Sync: A lightweight Far Sync instance (control file and redo logs only, no data files) sits close to the primary and forwards redo asynchronously to a remote standby. Zero data loss protection is now achievable across long WAN distances without the latency impact of synchronous transport over the wire.
• Fast Sync: In Maximum Availability mode, the standby acknowledges redo receipt in memory before writing to a standby redo log, isolating the primary from slow standby I/O.
• Real-Time Apply is now the default: Creating a standby with SQL no longer defaults to archived log apply. Redo is applied directly from the standby redo log, reducing failover time and giving Active Data Guard read-only queries access to current data.
• Single command role transitions: Switchover and failover are now single DDL statements, replacing the multi-step process of prior releases.
• Active Data Guard DML on global temporary tables: Read-only standby databases can now write to GTTs, extending the range of reporting workloads that can be offloaded without primary database access.

RMAN: Table-Level Recovery

One of the most operationally valuable RMAN additions is the ability to recover a single table or set of tables from an existing backup without restoring the entire tablespace to a separate location. RMAN handles the restore, recovery, and export/import internally.

-- Recover a single table from backup without full tablespace restore
RECOVER TABLE scott.orders
UNTIL TIME "TO_DATE('2024-06-01 03:00:00','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '/u01/aux'
DATAPUMP DESTINATION '/u01/export'
DUMP FILE 'orders_recovery.dmp'
NOTABLEIMPORT
REMAP TABLE scott.orders:scott.orders_recovered;

What security and auditing changes should DBAs know before upgrading to Oracle 12c?

Oracle 12c overhauled auditing fundamentally with Unified Auditing, and the security changes carry meaningful upgrade implications that deserve careful planning on any production system.

Unified Auditing

New databases created under 12c automatically use Unified Auditing, which consolidates all audit records -- database, Fine-Grained Auditing, RMAN, Data Pump, SQL*Loader, and more -- into a single audit trail stored in the AUDSYS schema and queried via the UNIFIED_AUDIT_TRAIL view. Upgraded databases start in a mixed mode where both old and new auditing coexist. Oracle strongly recommends migrating fully to Unified Auditing, but the migration must be planned: audit policies are defined differently, and old AUD$ / FGA_LOG$ queries do not work against the new trail.

New Administrative Privileges

Three new SYSDBA-level administrative privileges are introduced specifically to allow privilege separation without granting full SYSDBA access:

• SYSBACKUP: For RMAN and backup operations only.
• SYSDG: For Data Guard management only.
• SYSKM: For Transparent Data Encryption key management only.

Breaking Security Changes

• The RESOURCE role no longer grants UNLIMITED TABLESPACE. Any user who relied on this implicit grant will receive ORA-01536 quota errors after upgrade. Audit RESOURCE role usage before upgrading and grant explicit quotas where needed.
• Data Redaction is now a built-in database feature, allowing granular masking of sensitive column data returned to specific users or applications without changing application code.
• Nine Oracle Label Security features are deprecated. If your environment uses OLS, review the deprecation list against your deployed policies before migrating.
• SQLNET.ALLOWED_LOGON_VERSION is deprecated; replace with SQLNET.ALLOWED_LOGON_VERSION_SERVER and SQLNET.ALLOWED_LOGON_VERSION_CLIENT. Clients using older authentication verifiers may fail to connect after upgrade if the parameter defaults change.

-- Create a user with SYSBACKUP instead of full SYSDBA for RMAN jobs
CREATE USER rman_operator IDENTIFIED BY "SecurePass1";
GRANT SYSBACKUP TO rman_operator;

-- Fix RESOURCE role quota issue post-upgrade
ALTER USER appuser QUOTA UNLIMITED ON users;
ALTER USER appuser QUOTA 500M ON data_ts;

-- Migrate to Unified Auditing (run as SYSDBA)
EXEC DBMS_AUDIT_MGMT.set_audit_trail_property(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  audit_trail_property => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,
  audit_trail_property_value => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_NOWAIT);