Image Courtesy: Oracle Corporation
What is Oracle 18c
Oracle 18c is a Database Management System (DBMS) that has the following elements:
- Kernel Code that manages the memory and storage for DBMS
- Repository of metadata usually called a Data Dictionary
- Query language that enables applications to access the data
The following operations happen inside an RDBMS
- Logical Database Operations: The application teams have to specify what content is required inside the database. For example, application teams decide to store employee record to a table inside the database schema.
- Physical Database Operations: For physical operations, the RDBMS decides how things should be happening. For example, when the application queries for records inside a table, the database might use an index to find the required rows, read the data into memory and may perform many other needed steps before returning the result set to the end user. The RDBMS stores and retrieves data and hence physical operations are transparent to the data applications.
Oracle 18c New Features for Database Administrators
Server Draining well ahead of maintenance
Isn’t it nice if the database by itself drains all the sessions before a planned maintenance?
The sessions can be marked for draining when the PDB or the service is relocated or stopped. Once the sessions are marked for draining, the drivers and the databases start looking for draining the sessions wherever the applications are not interrupted.
Exclusive rules are set inside the database to take a decision on when to drain a session without affecting the application.
What is the benefit of server draining?
The tangible benefit is that organizations do not need to lose access to their applications to support maintenance operations (Updates and repairs)
Request Boundaries – Oracle Real Application Clusters
Request boundaries determine where application servers and applications borrow and return connections from their respective connection pools. Without request boundaries, it is hard for the database to recognize how the applications and application servers manage their connections. By making the request boundaries visible to database, it allows oracle database to isolate important events such as draining sessions before planned maintenance, load balancing and multiplexing at the database level and hence the sessions can be re-established without any visible disruption to the application layers. Request boundaries are supported in AWR, session, service and system statistics.
Transparent Application Continuity (TAC) – Oracle Real Application Clusters
TAC stands for Transparent Application Continuity that tracks and records transactional and session state so that recoverable outages are hidden from users. This enables application continuity for all application users since there is no reliance on application code changes or knowledge on applications. This feature is available for applications using OCI, thin Java and ODP.NET unmanaged provider.
Oracle Data Guard Multi-Instance redo apply provides support for use of Block change tracking files for RMAN backups. Block change tracking file can be enabled on Oracle Data Guard Active Standby setup that is utilizing multi-instance Redo apply.
Oracle Active Data Guard is empowered by the fastest redo apply and incremental backup technologies to exploit the best of both the features.
Automatic Correction of Non-Logged blocks at a Data Guard Standby database
The well known database nologging facility has been extended for efficient use in Oracle Data Guard environment without increasing the amount of redo generated.
There are 2 new nologging modes:
Standby nologging for Load Performance:
Ensures that the standby environments receive nonlogged data changes with very less impact on the loading speed at the primary database but at the expense of standby databases holding the transient nonlogged blocks. These blocks are automatically resolved by managed standby recovery.
Standby nologging for Data Availability:
Ensures all the standby databases have the data when primary load commits but at the expense of controlling the speed of loading data at the primary, which means that the standbys never have any nonlogged changes.
Shadow Lost Write Protection
This is a very good feature that minimizes data loss inside a database due to data corruption. Shadow Lost Write Protect catches a lost write before it results in a major data corruption. Shadow Lost Write Protection feature can be enabled at the database, tablespace or at the data file levels without requiring an Oracle Data Guard standby database.
Duplicate PDBs between encrypted and non-encrypted CDBs
RMAN can duplicate an encrypted PDB or tablespaces within the PDB so that they are encrypted in the destination CDB. AS ENCRYPTED clause of DUPLICATE command is used to perform this duplication. Similarly, use AS DECRYPTED clause of DUPLICATE command to perform duplication without using encryption. Databases can be duplicated easily between on-premise databases and Oracle Cloud by leveraging Oracle Cloud.
RMAN Recover Standby Simplification
There are enhancements made to the RECOVER command to enable standby database to catch up with the changes made to Primary database with very minimal manual intervention.
It is to be observed that the simplification of rolling forward a standby database has been enabled to avoid errors made through manual intervention.
Backup to archive storage
Enhancements are done to Oracle Cloud SBT library to support archive storage. RMAN is equipped to send backups to archive storage.
Centralized Diagnosability and Manageability for Sharded Databases
The SHARDS clause allows users to query Oracle supplied objects such as V$ views, DBA, USER, ALL views and dictionary tables across all shards from a central shard catalog. SHARD clause can only be used on a shard catalog database.
Using this feature, centralized management is possible that provides the ability to query audit, performance and diagnostic data from oracle supplied objects such as V$, DBA/ALL/USER views, tables and dictionary objects) across all shards from a central shard catalog.
Support for PDBs as Shards and Catalogs
In Oracle 18c, Oracle Sharding supports a shard or a shard catalog as a single PDB in a CDB. Multiple PDBs in a single CDB is not supported.
In support of this feature, the following GDSCTL commands are extended:
- ADD SHARD
- CREATE SHARD
The following new commands are implemented:
- ADD CDB
- CREATE CDB
- MODIFY CDB
- CONFIG CDB and
- REMOVE CDB
User-Defined Sharding Method
This method is Oracle Sharding allows us to define a RANGE or LIST based partitioning strategy and exclusively specify the mapping of data to individual shards.
User-defined sharding is used when certain data needs to be stored in a particular shard and you need full control on moving data across shards due to performance, regulatory or other reasons. In the event of a planned or unplanned maintenance, you know what data is not available. This feature comes with a cost of monitoring and maintaining balanced distribution of data and workload across different shards.
Sharding Support for JSON, LOBs, and Spatial Objects
Sharded configurations are now equipped to hold Oracle Binary Large Objects (BLOBs) and Character Large Objects (CLOBs). This enables Oracle sharding for applications using BLOBs, CLOBs, JSON and spatial functionalities.
Cross-shard queries are supported for Query and DML statements. DML operations involving a single shard are now supported but DML operations involving multiple shards are not supported.
All DBMS_LOB and OCILob functionality is supported, except for operations performed on BFILEs. For LOBs coming from the same shard, functionality that operates on multiple shards is restricted.
A sharded table has a new method, DBMS_LOB.isremote and OCILob descriptor OCI_ATTR_LOB_REMOTE that returns TRUE for a LOB that is obtained from the sharding table.
Consistency Levels for Multi-Shard Queries
We can define various consistency levels for queries across multiple shards in a sharded database. To quote an example, we might want some queries to avoid the cost of SCN synchronization across shards that are globally distributed. The parameter MULTISHARD_QUERY_DATA_CONSISTENCY can be used to set different consistency levels while executing multi-shard queries across shards and this feature allows slight stale data from standby databases.
Optimizer Enhancements for Sharded Database Queries
Sharding is the perfect solution if you want to store and manage data across different databases with a common schema. Sharding was introduced in Oracle 12c Database Release 2. Various improvements have been introduced to enhance the robustness and fault tolerance of shard queries. Query explain plan can now display information for all shards participating in the query.
Automatic Deployment of Oracle GoldenGate
This wonderful feature automates the configuration of Oracle GoldenGate bi-directional replication between the shards. conflict detection and resolution is enabled automatically without modifying anything on the application side. This feature enables read-write workloads to be distributed across one or more synchronized copies of a partition to increase the utilization and provide continuous data access in case a copy becomes unavailable.
Oracle GoldenGate Automatic CDR
This features extends Conflict Detection and Resolution (CDR) functionality in Oracle GoldenGate by providing built-in conflict detection and resolution mechanism that makes it easier to use active-active replication configurations. This mechanism handles all delete conflicts and LOB columns as well. This mechanism is simple to use, administer and doesn’t require any user application changes.
Oracle RAC Sharding
To enable RAC sharding, no changes are required to be made to the database schema. This feature affinitizes table partitions to RAC instances, and routes databases requests specifying a particular partition key to the instance that holds the partition. This feature reduces block pings across instances and improves cache utilization. We can only add the partitioning key to most performance critical requests. Performance and Scalability are achieved using Oracle RAC sharding with minimal application changes.
Automatic CDR Support of Tables with Unique Indexes/Constraints
Automatic CDR was introduced in Oracle 12c Database Release 2 and Oracle GoldenGate 12.3. Auto CDR was only allowed on tables with primary keys in 12c release 2. In Oracle Database 18c, Auto CDR can be used on tables with just unique keys, Unique indexes but not on primary keys.
These are some important features that would significantly simplify the job of database administrators.