Oracle Database 12c

Several years ago, Oracle released the most recent version of its RDBMS software; 12c. Release 12.2 saw the light at the end of 2016 and the next patch set 12.2.0.2 (also known as Oracle Database 18, but more about that later) is on the way. Time for a short update on the most eye-catching features of this version and a preview of what will change with regard to the naming of this product.

Multi-tenant

The biggest and most striking change in 12c is probably the introduction of the so-called multitenant architecture. Where previously each database always had its own set of system tablespaces, for example SYSTEM, SYSAUX, UNDO and TEMP and also its own set of background processes and memory structures (the instance), from 12c this can also be set up in a different way become. The multitenant option introduces the concepts of container database, CDB, and pluggable database, PBD. The image below shows the basic layout of the new design:

From the outside, a Container database, CDB, looks similar to a conventional Oracle database. It contains all the necessary parts needed for the whole to function as a database. Think of the control files, the (system) data files and the online redo logs, but also the background processes and memory structures. Everything that together makes the instance falls under the CDB.

In addition, each CDB always has the following containers:

• Root Container. Each CDB has only one root container named cdb$root. For example, the root contains the system tables, v$views and dbms packages. Any additional options such as Spatial are also contained in this root container. In addition, the root contains all general users (eg SYS and SYSTEM) and roles needed to manage the CDB as a whole. The root therefore contains no user data.
• Pluggable Databases, PBD. A CDB contains one or more PBDs. A PBD is a user-created set of schemas, objects, and related structures that appear to the outside world and are accessible as a separate database. Since all basic parts of a database are contained in the root container, the PDB only needs to contain information that is specific to itself. He doesn’t have to worry about control files, archive logs, etc.. A PDB therefore only consists of data files and temp files to store its own user objects. In addition, it contains its own data dictionary containing only the metadata specific to that PDB.
• Seed Database This is a template database that is used to quickly create a new PBD.

The most important thing that can be concluded from the above is that with the multitenant option the user data is separated from the rest of the database. This has a number of advantages:

• A PDB can be easily copied or moved to another CDB with the same version. All that needs to be done is disconnect the PBD from the original CDB and reconnect it to the new CDB. This can be useful, for example, if a test database is needed, or if it needs to be refreshed with production data.
• Creating a new database in the multi-tenant environment is faster and easier than before; this is a matter of making a copy of the ever-present ‘seed’ database. There is now no need to create a separate instance and not the entire data dictionary and all dbms packages need to be created again; this usually takes a lot of time in the current non-CDB architecture.
• The upgrade process is simpler and in theory faster. In short, this means that you disconnect the PBD from the old CDB and then connect it to a CDB that runs with the new software version. You then have to run some kind of upgrade script and the database is upgraded.

A general advantage of the multitenant option is that it can be used to make a consolidation effort, whereby a number of separate database servers with many small databases can be reduced to 1 or a few database servers with a multitenant architecture.

As with other Oracle Database options, the multi-tenant option is only available in the Enterprise Edition (EE) and is an additional license option there. Despite this, Oracle has marked the non-CDB architecture as deprecated as of 12.1.0.2, meaning it will disappear in the long run. To accommodate non EE customers it is possible to create a container database containing a single pluggable database. This is called a ‘Single Tenant’ or ‘Lone-PDB’ and is freely available in all database editions. That also means that it is allowed to have multiple CDBs on one server, each with a single PDB. In this way it is possible to create a comparable environment as when using the classic non-CDB architecture. Only if one wants to use multiple PBDs in a single CDB, the multitenant option has to be purchased.

This clears the way for setting up future database environments based on this new architecture. Even if you do not have the necessary licenses, you can still make use of some of the advantages, such as the simplified upgrade process and the ease with which a database clone can be made.

Migration and Upgrading

There are two options to turn a classic non-CDB database into a single-tenant PDB or multi-tenant PDB:

1. 1. Using datapump export and import. This method also works for converting a pre-12c database to the new tenant architecture. The data is then imported into a pre-created empty PDB.
   2. Converting the existing non-CDB database to a PDB. Basically, the procedure DBMS_PBD.DESCRIBE creates an xml of the existing non-CDB database, containing information about the database structure. A PDB is then created on the basis of this XML file, in which the data files of the non-CDB are reused. The script ‘noncdb_to_pdb.sql’ then actually converts the database into a usable PDB.

Upgrading an existing CDB in its entirety can easily be done in the usual way using the DBUA, or by manually executing the necessary scripts.
Upgrading a single PDB can be done by disconnecting it from existing CDB and then attaching it to a higher version CDB. After executing an upgrade script in the PDB, it is upgraded. So an upgrade script still needs to be run, but in practice it will be ready faster than the same catupgrd.sql script in the classic non-CDB database.

New Patch Set Naming

Starting with the first patch set for 12.2, Oracle will completely overhaul the naming of its Oracle Database product. This means that patch set 12.2.0.2 will in practice be called “Oracle18” and the subsequent patch set (12.2.0.3) “Oracle 19”. The term patch set will disappear completely and there will be annual releases in the future where the version will be equal to the last two digits of the release year. The current patch terminology is being replaced by Release Updates (RU) and Release Update Revisions (RUR). Both are released quarterly with the first being similar to a proactive bundle patch and the second being the current CPUs. The new naming convention has already come into effect in Q1 2018 for the Oracle Public Cloud and on-premise engineered systems, such as ODA and Exadata. From July 2018, the other on-premise platforms (non-Engineered Systems) will transfer to this new name.

Other improvements

As mentioned, the introduction of the multitenant architecture is the biggest change in Oracle Database 12c and possibly the biggest change ever. In addition, however, there are still 500+ improvements, some of which are certainly interesting for daily use by a dba. Below is a small selection:

Rman DUPLICATE

An option ‘NOOPEN’ has been added to the Rman DUPLICATE command. This can prevent the duplicate from opening at the end of the duplicate action. This makes it possible to make adjustments before the database is finally opened. This feature is also very useful in upgrade scenarios where the database should not be opened before the upgrade scripts have been executed.

Data Pump

From 12c it is possible to disable the redo log mechanism, specifically only for the imported data. For this, the new parameter TRANSFORM has been introduced. When DISABLE_ARCHIVE_LOGGING is passed to this parameter, no redo data will be created for the objects being imported. This option prevents the creation of a huge amount of redo data and archive logs during the import of large tables and will also speed up the import. Until now this could only be prevented by temporarily taking the entire database out of archivelog mode, which means that the database had to be restarted 2x.

Restore/Recover data files over the network

Another big improvement in 12c is that it is now possible in a DataGuard configuration to copy a single data file, control file, spfile, tablespace or even entire database over the network from the primary database to standby databases and vice versa.
This is especially useful for synchronizing standby databases in a situation where it lags far behind the primary database and, for example, the necessary archive logs are no longer available immediately. No longer is a complex roll-forward procedure necessary to close the gap, but now an incremental backup of the primary database can be made using RMAN and the standby database is then directly updated via the network. Conversely, it is also possible to restore a data file in the primary database by copying it directly from the standby database.

Parallel upgrade

The turnaround time of a database upgrade is directly related to the number of components present in the database and less to the size of the database. In previous versions it was not possible to speed up an upgrade, but from 12c this is possible by using parallelization. For this, the parallel-upgrade utility is provided in the form of a perl script. This allows 2 or more parallel processes to be started that perform the final upgrade. The DBUA gives graphically the possibility to indicate the number of processes and automatically uses this new utility underwater.

Online renaming and moving of an active data file

Unlike in previous versions, in 12c it is no longer necessary to take a data file offline before it can be renamed or moved. It is now possible to do this directly with a simple ALTER DATABASE MOVE DATAFILE statement. While the data file is being moved, users can simply run DML and DDL on the objects in the data file. This is very useful in a situation where a data file is accidentally in the wrong location, but can also be used to move data files to new storage without having to stop the database. An online migration from and to ASM storage is also possible.