Learn Oracle Database 12c New Features with Robert Freeman and Pd controleur 12669 inc

Oracle Database 12c New Features Robert Freeman Pd controleur 12669 inc

Introduction

Oracle Database 12c is the latest version of the world's most popular database management system. It offers a number of new and improved features that enhance the performance, availability, scalability, security and manageability of enterprise data. Oracle Database 12c also introduces a revolutionary new architecture called Oracle Multitenant, which enables multiple databases to share a common infrastructure and simplify administration.

Oracle Database 12c New Features Robert Freeman Pd controleur 12669 inc

Robert Freeman is a Master Principle Database Expert at Oracle Corporation and an Oracle ACE. He has over 30 years of experience in the IT industry and has written several books on Oracle Database, including Oracle Database 12c New Features. In this book, he provides a comprehensive and practical guide to the new and enhanced capabilities of Oracle Database 12c, with examples, tips and best practices.

Pd controleur 12669 inc is a company that specializes in providing IT consulting and training services for Oracle Database technologies. They have been using Oracle Database 12c since its release and have successfully implemented it for various clients across different industries. They have also developed a number of courses and workshops on Oracle Database 12c New Features, based on Robert Freeman's book and their own experience.

Getting Started with Oracle Database 12c

Before you can start using Oracle Database 12c, you need to download and install it on your system. You can download the software from the Oracle Technology Network (OTN) website or order it on DVDs from Oracle Store. You can also use the Oracle Universal Installer (OUI) to install or deinstall Oracle Database 12c components.

If you already have an existing Oracle Database version, you can upgrade or migrate it to Oracle Database 12c using various methods, such as the Database Upgrade Assistant (DBUA), the manual upgrade process, the transportable tablespace method or the data pump method. You can also use the pre-upgrade information tool to check the readiness of your database for upgrade and the parallel processing option to speed up the upgrade process.

One of the major changes in Oracle Database 12c is the replacement of the Database Control web-based interface with the Database Express interface. Database Express is a lightweight and secure interface that provides basic administrative and monitoring functions for your database. You can access Database Express through a web browser by entering the URL https://hostname:port/em/express/login.

Architectural Changes in Oracle Database 12c

The most significant architectural change in Oracle Database 12c is the introduction of Oracle Multitenant, which enables you to create multiple pluggable databases (PDBs) within a single container database (CDB). A PDB is a self-contained database that has its own data files, users, schemas, objects and privileges. A CDB is a database that contains one or more PDBs as well as common data files, users, schemas, objects and privileges that are shared by all PDBs.

The main benefit of using Oracle Multitenant is that it reduces the cost and complexity of managing multiple databases by allowing you to consolidate them into a single CDB. You can also perform operations such as backup, recovery, patching, upgrading and cloning at the CDB level rather than at each individual PDB level. Moreover, you can easily plug or unplug PDBs from one CDB to another without affecting their functionality or performance.

To create, manage and monitor PDBs and CDBs, you can use various tools such as SQL*Plus, SQL Developer, Enterprise Manager Cloud Control or the new Multitenant Self-Service Provisioning Application. You can also use new commands such as CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE DATABASE and DROP PLUGGABLE DATABASE to manipulate PDBs and new views such as V$PDBS, DBA_PDBS and CDB_PDBS to query information about PDBs and CDBs.

Oracle Real Application Clusters (RAC) New Features

Oracle Real Application Clusters (RAC) is a feature that allows you to run multiple instances of Oracle Database on different servers that are connected by a shared storage system. This provides high availability, scalability and performance for your database applications. In Oracle Database 12c, RAC introduces several new features that improve these aspects even further.

adding more database instances. This is useful for applications that have a large number of clients that do not require direct database access.

Another new feature is Oracle Flex Automatic Storage Management (ASM), which simplifies the storage management of your RAC cluster by allowing you to create disk groups that span multiple servers and automatically balance the data across them. You can also use Oracle Flex ASM to run ASM instances on a separate pool of servers from the database instances, reducing the CPU and memory overhead on the database servers.

A third new feature is Oracle ASM Cluster File System (ACFS), which is a general-purpose file system that supports multiple file types such as database files, executables, binaries, libraries, configuration files and user files. You can use Oracle ACFS to store and access files across your RAC cluster with high performance, availability and security. You can also use Oracle ACFS to enable features such as snapshot, replication, encryption and tagging for your files.

Oracle Recovery Manager (RMAN) Enhancements

Oracle Recovery Manager (RMAN) is a tool that allows you to perform backup and recovery operations for your Oracle Database. It supports various backup methods such as full, incremental, differential and compressed backups and various recovery methods such as complete, point-in-time and tablespace recovery. In Oracle Database 12c, RMAN introduces several enhancements that make backup and recovery easier and faster.

One of the enhancements is the ability to perform cross-platform backup and recovery with RMAN. This means that you can backup your database on one platform and restore it on another platform, as long as they have the same endian format. For example, you can backup your database on Linux and restore it on Windows or vice versa. This is useful for migrating your database across different platforms or testing your backup on a different platform.

Another enhancement is the ability to use new compression and encryption options for RMAN backups. You can use the new HIGH compression algorithm to reduce the size of your backups by up to 50% compared to the previous algorithms. You can also use the new TRANSPARENT encryption mode to encrypt your backups without requiring a wallet or a password. This improves the security of your backups without compromising their usability.

A third enhancement is the ability to use new multisection and tablespace point-in-time recovery features of RMAN. You can use the multisection feature to split your backup or restore operation into multiple sections that can run in parallel, reducing the time and resources required for the operation. You can also use the tablespace point-in-time recovery feature to recover a specific tablespace to a point in time without affecting the rest of the database. This is useful for recovering from logical errors or corruption in a tablespace.

Oracle Data Guard Improvements

Oracle Data Guard is a feature that allows you to create and maintain one or more standby databases that are synchronized with your primary database. This provides disaster recovery, data protection and high availability for your database applications. In Oracle Database 12c, Data Guard introduces several improvements that enhance these aspects even further.

One of the improvements is the ability to use Fast Sync to reduce network latency for synchronous redo transport. This means that you can configure your standby database to acknowledge the receipt of redo data from the primary database before writing it to disk, reducing the response time of transactions on the primary database. This improves the performance of your primary database while maintaining zero data loss protection.

Another improvement is the ability to use Far Sync to extend zero data loss protection over long distances. This means that you can deploy a lightweight intermediary server between your primary and standby databases that receives redo data from the primary database and forwards it to the standby database over a long-distance network. This reduces the network bandwidth and latency requirements for synchronous redo transport and enables you to achieve zero data loss protection even when your primary and standby databases are geographically dispersed.

the status of your Far Sync server and automatically fail over to another standby database in case of a failure, ensuring zero data loss protection. You can use Automatic Block Repair to automatically repair corrupted blocks on your primary or standby database by fetching the correct blocks from another database, improving data integrity and availability.

SQL, PL/SQL, DML and DDL New Features

SQL, PL/SQL, DML and DDL are the languages and commands that you use to interact with your Oracle Database. They allow you to create, manipulate and query data and objects in your database. In Oracle Database 12c, they introduce several new features that make your work easier and more efficient.

One of the new features is the row limiting clause for pagination queries. This allows you to specify the number of rows to return or skip in a query result set, without using subqueries or analytic functions. For example, you can use the FETCH FIRST 10 ROWS ONLY clause to return only the first 10 rows of a query result set. This is useful for displaying query results in pages or batches.

Another new feature is the identity columns for auto-incrementing values. This allows you to create a column that automatically generates a unique numeric value for each row inserted into a table, without using sequences or triggers. For example, you can use the IDENTITY clause to create an identity column that starts with 1 and increments by 1 for each row. This is useful for creating primary keys or surrogate keys for your tables.

A third new feature is the invisible columns for backward compatibility. This allows you to create a column that is hidden from queries and DML statements, unless explicitly referenced by name. For example, you can use the INVISIBLE clause to create an invisible column that stores some internal information that is not relevant for most users. This is useful for maintaining backward compatibility with existing applications or views that do not expect the new column.

A fourth new feature is the temporal validity feature for tracking data changes over time. This allows you to associate a valid time period with each row in a table, indicating when the row is valid or current. For example, you can use the PERIOD FOR clause to create a temporal column that stores the start and end dates of a row's validity. You can also use the AS OF PERIOD FOR clause to query data as of a specific point in time or during a specific time interval. This is useful for managing historical or future data in your database.

A fifth new feature is the PL/SQL features such as implicit result sets, UDF pragma and ACCESSIBLE BY clause. You can use implicit result sets to return multiple result sets from a PL/SQL subprogram without using OUT parameters or pipelined functions. You can use UDF pragma to indicate that a PL/SQL function is a user-defined function (UDF) and enable optimization techniques such as inlining and subquery caching. You can use ACCESSIBLE BY clause to restrict the accessibility of a PL/SQL subprogram or variable to specific invokers or definers. These features improve the performance, security and readability of your PL/SQL code.

Partitioning Improvements

Partitioning is a feature that allows you to divide a large table or index into smaller and more manageable pieces called partitions. This improves the performance, availability and manageability of your database applications. In Oracle Database 12c, partitioning introduces several new methods, features and enhancements that further improve these aspects.

One of the new methods is the interval-reference partitioning method, which allows you to create a reference-partitioned table based on an interval-partitioned table. A reference-partitioned table is a table that inherits its partitioning structure from another table based on a referential constraint. An interval-partitioned table is a table that automatically creates partitions based on an interval expression. For example, you can create an interval-reference partitioned table that partitions its rows by month based on a date column that references an interval-partitioned table that partitions its rows by year based on the same date column. This simplifies the maintenance and querying of related tables.

the update of global indexes after dropping a partition from a table. This improves the performance and availability of partition maintenance operations.

A third new feature is the partial indexes for partitioned tables feature, which allows you to create indexes on a subset of partitions of a table. For example, you can use the CREATE INDEX statement with the PARTIAL clause to create an index on only the most recent partition of a table. This reduces the storage and maintenance costs of indexes and improves the query performance on specific partitions.

A fourth new feature is the online move partition feature, which allows you to move a partition from one tablespace to another tablespace without taking the partition offline. For example, you can use the ALTER TABLE statement with the MOVE PARTITION clause to move a partition to a different tablespace while the partition is still accessible for queries and DML operations. This improves the availability and manageability of partitions.

A fifth new feature is the multiple indexes per partition feature, which allows you to create more than one index on the same set of columns for each partition of a table. For example, you can create two indexes on the same column for a partition, one with compression and one without compression. This allows you to use different indexes for different purposes, such as query performance or storage optimization.

A sixth new feature is the interval statistics feature, which allows you to gather statistics for interval-partitioned tables at the interval level rather than at the partition level. For example, you can use the DBMS_STATS package with the INTERVAL option to gather statistics for an interval-partitioned table at the monthly level rather than at the individual partition level. This reduces the overhead and frequency of statistics gathering and improves the accuracy and consistency of statistics.

Business Intelligence and Data Warehousing Capabilities

Business intelligence and data warehousing are processes that involve collecting, transforming, storing and analyzing large volumes of data for decision making and reporting purposes. Oracle Database 12c provides a number of capabilities that support these processes and enable faster and smarter insights from your data.

One of the capabilities is the in-memory column store feature, which allows you to store a copy of your data in memory in a columnar format rather than in a row format. A columnar format stores data by columns rather than by rows, which improves the compression ratio and scan speed of your data. For example, you can use the INMEMORY clause to enable in-memory column store for a table or a partition and specify various options such as compression level, priority level and distribution method. This improves the query performance of your data warehouse applications by orders of magnitude.

Another capability is the adaptive query optimization feature, which allows you to optimize your query execution plans based on runtime conditions and feedback. Adaptive query optimization consists of two components: adaptive plans and adaptive statistics. Adaptive plans are execution plans that can change during execution based on factors such as data volume, data distribution and resource availability. Adaptive statistics are statistics that are collected or modified during execution based on factors such as data skewness, data correlation and cardinality estimation errors. For example, you can use adaptive plans to switch between join methods or join orders during execution based on the actual number of rows returned by each join input. You can also use adaptive statistics to refine your statistics based on dynamic sampling or SQL plan directives during execution based on the actual data characteristics. This improves the efficiency and reliability of your query execution plans.

A third capability is the pattern matching feature, which allows you to identify and analyze patterns in your data using SQL queries. Pattern matching uses a new clause called MATCH_RECOGNIZE, which enables you to define a pattern using regular expressions, specify measures and aggregates based on pattern variables, and filter or group results based on pattern conditions. For example, you can use pattern matching to find customers who have repeated purchases or cancellations within a time window, or to detect outliers or anomalies in your data. This enables you to perform complex analytics on your data using SQL.

Security Enhancements

Security is a critical aspect of any database system that involves protecting your data from unauthorized access, modification or disclosure. Oracle Database 12c provides several enhancements that improve the security of your database applications.

the privileges analysis feature, which allows you to audit and reduce excessive privileges granted to users or roles in your database. Privileges analysis uses a new package called DBMS_PRIVILEGE_CAPTURE, which enables you to create and manage privilege capture policies that track the privileges used or unused by users or roles during a specified time period. For example, you can use privileges analysis to identify the minimum set of privileges required for a user or role to perform their tasks and revoke the unnecessary privileges. This improves the security and compliance of your database applications.

Another enhancement is the data redaction feature, which allows you to mask sensitive data on the fly before returning it to the end users or applications. Data redaction uses a new package called DBMS_REDACT, which enables you to create and manage data redaction policies that define the conditions and rules for redacting data in different columns of different tables or views. For example, you can use data redaction to mask credit card numbers or social security numbers with asterisks or random numbers based on the user's role or session context. This protects your data from unauthorized disclosure or leakage.

A third enhancement is the administrative-level privileges feature, which allows you to grant specific administrative privileges to users or roles without granting them the powerful SYSDBA or SYSOPER roles. Administrative-level privileges include SYSBACKUP for backup and recovery operations, SYSDG for Data Guard operations and SYSKM for encryption key management operations. For example, you can use administrative-level privileges to delegate backup and recovery tasks to a backup administrator without giving them full access to the database. This enables you to implement separation of duties and