Oracle Migration | Migrate to the latest version of Oracle Database

Many of our customers (for good reasons) still use Oracle version 11g databases. Despite the fact that there is nothing to complain about the usability and reliability, it has been more than 7 years since 11.2 was released. Version 12.1 of Oracle Database was launched in 2013 and version 12.2 is already available for both On-premise use and the Oracle Cloud.

In this article we want to break a lance for the newer database versions. DBA.nl believes that it is worthwhile for everyone to prepare an upgrade to version 12. Oracle 12 is the platform to use, only in cases where there is really no other option, for example the application supplier only supports 11.2, it would be a consideration not to perform an Oracle migration.

Why migrate from database 11.2 ?

We can imagine that it does not seem obvious to invest time (and money) in an upgrade when the existing environment works well. After all, every change creates uncertainty and (also) in the IT world the adage “if it ain’t broken, don’t fix it” is well known. Yet the reality is more nuanced. As we also indicate in our publication on Oracle Support , there are limits to Oracle’s willingness to continue to actively support older software at a normal rate, so an Oracle update is a consideration. For a software producer, it is no easy task to continue to provide complex older software with (security) fixes and to test it with the latest platform updates. This is in addition to the need to keep the knowledge in their support organization up to date and to ensure that the software is still “compilable” at all on modern servers and operating systems. It is therefore not surprising that Oracle uses the notion of “extended support” to only support older Database releases at a higher rate than that of the usual “premier” support. Contrary to previous reports, Oracle recently decided to postpone “free” extended support on the latest patch level (11.2.0.4) until the end of 2018. In June 2018. Until that date, Oracle still applies a transitional arrangement in which the support rate is equal to that of “premier”, although 11.2 has been in “extended” support since 2015. However, it will eventually stop. Here is an overview of Oracle’s support terms and levels:

In short, the useful life of Oracle database version 11.2 is insight and its support will in principle entail additional costs from the end of 2018. Despite the fact that the end of 2018 still seems far away, there is pressure from various application suppliers to make the step to Oracle 12c, an update Oracle to the latest version is always wise.

What does Oracle Database 12c bring?

We can distinguish between Oracle database 12c (12.1) and version 12cR2 (12.2).

Releases 12.1 and 12.2 contain too many new features to list; we limit ourselves here to a selection of points that are probably the most interesting for our customers. All mentioned features are already included in the Oracle update of version 12.1 and where possible have been further improved in the Oracle 12.2 update.

temporary undo

It is now possible to set a database parameter that ensures that transaction information for explicitly temporary data (so-called global temporary tables) is itself regarded as temporary data. This greatly reduces the overhead for such data. This parameter can be set at session level (by an application) and therefore does not necessarily have to be set for the entire database.

ASM Disk Scrubbing

Many of our customers use Oracle’s ASM storage software. This has received many improvements in version 12, one of which is especially important for long-term reliability of data. A standard file system can eventually be damaged without anyone noticing (this is called “bit rot”). Such damage cannot be determined if data is not provided with checksums and even if that is the case, data corruption will still only become visible when these data+checksums are actually read. In version 12c, ASM can proactively read data in the background and automatically repair corruption (when ASM with Host-Based Mirroring is configured). This means that you no longer have to worry that some data is almost never read.

ACFS improvements

The ACFS file system (a traditional looking file system on top of ASM) is now usable on a RAC environment for all types of database files. As a result, it is now possible to use ASM and still access the database files via ACFS in a user-friendly way. ACFS offers some advantages over a traditional filesystem such as NTFS (Windows) and ext4 (Linux). Advantages are for example:

• Usable as shared storage (cluster aware and increased availability)
• Supports snapshots
• Benefits from redundancy, striping and auto-repair from ASM
• Data is automatically redistributed when a disc is added and removed.
• Performance: Optimized for database workloads.

Flex ASM

It is possible for the first time since version 12 to decouple ASM instances from databases that use ASM storage. With Flex ASM, an ASM instance no longer needs to be on the same server as the databases. A cluster of ASM instances can provide highly available ASM storage to databases on any server (as long as the physical storage is accessible to all servers).

multi-tenancy

Oracle 12 Enterprise Edition introduced the ability to consolidate multiple databases with relative ease. A standalone database can be read once into a “pluggable database” (PDB) and afterwards only contains a lightweight subset of the Oracle Data Dictionary instead of the (heavy) full system. A PDB is managed through an overarching Container database (CDB). Graphically it looks like this:

A pluggable database behaves exactly the same as a “standalone” database. So there is no need to modify applications in any way.

This has made consolidation of application databases much easier. Previously, for reasons of shielding and to avoid conflicts in schema naming and permissions, it was often decided to deploy one database per application. This is not very efficient because each database instance must have a guaranteed amount of memory and a minimum number of background domestic processes running. Even if the database is idle, these memory and CPU resources are still in use. This “overhead” is duplicated for each database instance. With the multitenant option it is possible to place multiple databases under one instance while each database for the application still appears to be a complete, independent instance. The pluggable databases can take advantage of the resources allocated to the master CDB and not each have to run their own background tasks. This can avoid unnecessary overhead and also reduces the amount of database instances to manage/maintain/patch. And finally, it is also possible to move a pluggable database to another (higher version) CDB on another system (“plugging”). This can be an advantage in future migrations.

Multitenant is a paid option (the price is the same as the “Spatial” option) for Enterprise Edition but due to the potential reduction in management and hardware resources it can be interesting. In some cases it is even possible that your existing license already covers this option.

In addition to being multi-tenant, it is possible (even in Standard Edition) to apply the same “database virtualization” technique for a single pluggable database. This is called “single tenant” and does not require an additional license. This can also facilitate future version upgrades because in this case plug-in and plug-in can also be used instead of an in-place upgrade or datapump export/import.

RMAN table-level recovery

We regularly see situations where an unexpected (application) error results in data having to be restored from a backup. An up-to-date datapump export is not always available and then you have to fall back on a full restore of the relevant database. That is not always desirable; why should the whole database be restored if only part of it is damaged ? With Oracle 12c it becomes relatively easy to read back only the tables that are needed. Under water, RMAN performs all activities necessary for a restore+export+import of the piece of data in question. This is much faster than if a DBA had to set this up and run it all by hand.

More DDL without locking

In 12c it is possible to make more structural changes to tables/indexes without disturbing active users. More DDL statements now support the “online” option, making it less burdensome to perform index maintenance during production time, for example.

Online data file move

It sometimes happens that over time a database gets files in non-optimal locations. A tablespace may have been created incorrectly or there have been disk reorganizations. In the past, downtime was required to properly organize all data files. The data file had to be taken offline and copied to another location. From 12c the move can happen while everything just stays online.

DDL logging

Database auditing has always been possible, but it was relatively difficult to set up and maintain correctly and had an impact on database performance. With 12c it becomes possible to have DDL statements (“alter”, “drop”, “create”, etc.) written to a text file so that it is easy to see when which structural changes were made to the database. This is a tool for troubleshooting failures after changes.

Unified auditing

Although auditing was not very simple as mentioned, it can be of great value in meeting legal data protection requirements. In 12c, auditing is greatly improved and a lot easier to manage thanks to the so-called “unified audit trail” that bundles all auditing information. The performance of auditing has also been greatly improved so that there will be more situations where it can become interesting to enable auditing without noticeably negatively affecting database performance.

DataPump Improvements

Datapump export and import are widely used. In 12c this is further improved with the possibility to get detailed information about time lapse. In addition, there is now the option to make imports much faster with a “nologging” option.

Overall improvements

Finally, a lot of work has been done on performance improvements and better diagnostic information, and additional SQL functions have been added for developers.

In conclusion, Oracle Database 12c is greatly improved compared to version 11.2 and a worthy upgrade. Apart from the importance of staying up-to-date for support (Oracle support), there is really no reason not to upgrade to 12c and we have not experienced an upgrade leading to application problems. DBA.nl will gladly support you during the upgrade process to 12c.