12.2

Introduction

There is a new statistic preference, PREFERENCE_OVERRIDES_PARAMETER available from Oracle 12.2.  It allows the DBA to override any parameters specified when gathering statistics in favour of any statistics preferences that are defined.  This new preference can be specified at database-level or at table-level, or both.

From the introduction of the cost-based optimizer in Oracle 7, we all had to write scripts to collect statistics.  The introduction of the maintenance window in Oracle 10g was supposed to supersede that with a regularly scheduled maintenance window.  It still is not uncommon to find systems that rely on custom scripts that collect object statistics.  Sometimes, commands to collect statistics are embedded in applications.  

You cannot put non-deterministic functions into virtual columns or function-based index.  Regular expressions have always been sensitive to National Language Support (NLS) settings because what is a character is different in different languages.  However, from Oracle 12.2. regular expressions are considered to be non-deterministic because Oracle has fixed bug 20804063.

Background 

A developer came to me with the following query, complaining it was slow.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT …
FROM   …
AND    LOWER(REGEXP_REPLACE(ASCIISTR(my_column), '[^a-zA-Z0-9]')) ='2732018'

Application Container brings a new way to share data among databases, and adds a new dimension to referential integrity. A foreign key in an application PDB can reference a row belonging to a root data link table. But then, should a delete on the root validate that there are no orpheans in the application PDBs? And what if those PDBs are closed at the time of this delete? Here is a small example.

If you run this in 12.2.0.1 you will get an error because the search for parent key is done only on the current container. This is considered as a bug: 21955394: CDB:ORA-02291 WHEN FOREIGN KEY REFERS TO THE PRIMARY KEY IN DATA LINK

The example that follows is run with the patch applied to fix this bug.

12.1 introduced Unified Auditing where you define policies and then enable them. As with the traditional audit, you enable them for all users or for specific users. The unified auditing adds a syntax to audit all users except some listed ones. 12.2 adds a syntax to audit a group of users, defined by the role granted. This is the best way to enable a policy for a group of users, including those created later.

I create a simple policy, to audit logon and DBA role usage:

SQL> create audit policy DEMO_POLICY actions logon, roles DBA;
Audit POLICY created.

I create a new DBA user, USER1

SQL> create user USER1 identified by covfefe quota unlimited on USERS;
User USER1 created.
SQL> grant DBA to USER1;
Grant succeeded.

I want to enable the policy for this user because I want to audit all DBAs

When cloning a PDB from a remote CDB you need to define a database link to be used in the CREATE PLUGGABLE DATABASE … FROM …@… command. The documentation is not completely clear about the privileges required on the source for the user defined in the database link, so here are the different possibilities.

Remote clone

Here is what the documentation says:

So you can connect to the CDB or to the PDB.

In a previous post, I explained that Cross-PDB DML, executing an update/delete/insert with the CONTAINERS() clause, seems to be implemented with implicit database links. Connecting through a database link requires a password and this blog post is about an error you may encounter: ORA-01017: invalid username/password; logon denied

Since 12cR2 is out, we give our 12c new feature workshop with hands-on exercises on 12.1 and 12.2 releases. When I gave it last month, I had a small problem when doing demos: sometimes the connections as sysdba failed with “ORA-01017: invalid username/password”. It was at random, about one every 5 login attempts and I was sure that the password did not change. As I give another of this training next week, I tried to reproduce and fix this issue and finally found out that the problem was really random: dependent on the entropy when reading /dev/random

Multitenant has been introduced in 12.1.0.1 with the goal to share resources but isolate data. However, having all PDBs in the same root may be convenient to manipulate data in multiple PDBs. In the first patchset, 12.1.0.2, a way to query cross-container has been introduced for the CDB administrator to see data in other containers. In the second release, 12.2.0.1, this goes further with the introduction of Application Containers and cross-PDB DML. Currently, not all possibilities are documented and not all documented features are actually working. This will probably improve in next patchset. I’ll start here with something simple: insert from root into a table which is in a PDB.

If you are in multitenant, you probably already felt the desire to open the PDB$SEED in READ WRITE mode.

• Can you open PDB$SEED read write yourseld? Yes and No.
• Should you open PDB$SEED read write yourself? Yes and No.
• How to run upgrade scripts that need to write to PDB$SEED? catcon.pl

In 12.1 you have no reason to open the seed read write yourself. In 12.2 there is one reason when you are in LOCAL UNDO mode, because you may want to customize the UNDO tablespace.

I had a recent question about the following mention from the 12cR2 Multitenant book, about Partial PDB backups:
.
Here is an example in 12.2 with local undo to illustrate the answer, which may help to understand what is a partial PDB backup.