Why is it time?

Time flies fast, and for Oracle Database 11.2 users, not to mention earlier versions, it is time to upgrade. January 31, 2015, the Oracle Database 11.2 Premier Support period ended. This means that if you, for example, schedule a hardware server update, then you will have to install a new version of the operating system on it, because drivers for the previous version are no longer available, and the new operating system is no longer certified for version 11.2.



Detailed information about support for various versions of the Oracle Database can be found in the official Oracle Lifetime Support Policy document. For example, the extended support for version 11.1 has just ended (fig. 1). As for the most popular today version 11.2 - for it the first year of extended support now ends, and from January of next year a gradual increase in the cost of technical support will begin, first by 10%, in subsequent years by 20%, and this is another economic incentive for enterprises to think about upgrading to version 12c.

In preparing for the transition to the new version of the database, it is important to understand the difference between the terms “update” and “migration”.
')
Upgrading a database is a transition to a new version within the framework of the previous operating environment — the platform and the operating system do not change, the data does not “move” from one server to another, only the database version changes. The actual update is in the procedure for updating the dictionary and the Oracle metadata, the data itself does not change or move. In this case, the size of the database does not matter and does not affect the update rate, only the number of objects in the database matters. That is, everything is clear with the update, only the database version changes, in this case, we upgrade to the database version 12, but the platform and the operating system do not change.

It is important which version of Oracle Database is upgraded to version 12c from. Starting from version 10, Data Pump technology is available. For upgrading from old versions there is a process consisting of export and subsequent data import, which requires additional disk space. This method is often used for small databases. Oracle recommends using a special upgrade tool called the Oracle Database Upgrade Assistant, which comes free with the Oracle Database distribution kit, its applicability to different database versions is shown in the diagram in Figure. 2. Starting from version 12, the manual update option is also available, which is provided as a special Perl script.

You can minimize downtime during the upgrade if you use the additional feature that appeared in the Recovery Manager utility — transfer files to the database using transportable table spaces, and then apply incremental copies of the Recovery Manager. This technology is called RMAN incremental backup recovery. If idle time is unacceptable, you need to use a special tool that provides an on-the-fly update called Golden Gate - but this is the most expensive way in terms of software licensing, preparing and adapting replication mechanisms.

Migration (migration) is a transition to a new operating environment — to a new server, to a new operating system. And in this case, of course, the physical size of the database is of paramount importance. Often, the update and migration tasks are performed jointly, that is, for example, they simultaneously switch to a new version of the Oracle Database and at the same time change the equipment.

The most reliable way to migrate the database of the migration database is the good old export-import. Since the export file does not depend on the platform, you can upload the old version from the database on one software and hardware platform, and import the new version into the database on another software and hardware platform. Starting from version 10.2, tablespace transportation technology is available. The RMAN technology incremental backup recovery is applicable, unfortunately, not for all software and hardware platforms. If zero downtime is needed, you can use Golden Gate technology.

You can learn more about the ways to upgrade and migrate from the documents listed at the end of the article.

Patches and certification

If during the transition to the new version of the database, you change the hardware and operating system, you need to figure out whether this software and hardware platform is certified to the version of Oracle Database 12c. Please read the information on the certification of software and hardware platforms listed on the technical support site support.oracle.com (“Certification” tab). On where to look for distributions, we will not stop here - it is important not to forget to get all the latest service packs. Oracle releases quarterly large cumulative update packages that fix major bugs. The recommended patches for version 12.1.0.2 are currently PSU Update 4 and OJVM PSU Update 4 (Oracle Java Virtual Machine update, Java embedded machine).

If you are currently using version 11.2.0.3 or 11.2.0.4, and have already installed certain patches to eliminate certain errors, you should make sure that these errors are fixed in version 12. Most likely, this is true, but, nevertheless, it is recommended by the patch number For version 11, check whether the specific error in the cumulative update of version 12 has been fixed, and if not, get the appropriate patch to fix this error. If such a patch has not yet been released, you need to make an appropriate request to the technical support service for your software and hardware platform.

Starting with version 12.1.0.2, Oracle releases a special patch called the Oracle Database In-Memory patch of Exadata Engineering System. It includes an update to the Oracle Database In-Memory technology and, despite its name, is suitable not only for Exadata, but also for regular distributions, and which is recommended to install if you use Oracle Database In-Memory technology.

An important update concerns the features of time measurement in Russia - this is due to the fact that, as you remember, with us, the principle of calculating time in time zones was changed twice at intervals of several years. Therefore, it is very important to install special Time Zone updates that take this situation into account for Russian time zones. If you actively use stored procedures in Java and Java variables of type Date and Time with time zones, you will need a special patch to correct the time in time zones for the Java virtual machine.

Do not forget to update the OPatch utility in order to install a new update. In Oracle Database 12, a new package, DMBS_QOPatch, has been added.

Preparing the source database

Before you begin the upgrade, you need to prepare the source database. The “checklist” of such preparation is shown in fig. 3

Empty trash can before upgrading. Starting with Oracle 12c, it is executed using the preupgrade_fixups.sql script. It is recommended to clean the trash bin at least once a week using an automatic task in the period of minimum load on the database.

Check for erroneous objects. There should be no error objects in the SYS and SYSTEM schemes. To perform such a check, you need to try to compile objects that are in the “Invalid” state before updating or migrating using the utlrp.sql script. If this does not help, you can not proceed with the upgrade. It is worth checking whether there are objects with the same name in the SYS and SYSTEM schemes, this can be done with the following query:

select OBJECT_NAME, OBJECT_TYPE from DBA_OBJECTS where (OBJECT_NAME,OBJECT_TYPE) in select OBJECT_NAME, OBJECT_TYPE from DBA_OBJECTS where OWNER=SYS') and OWNER='SYSTEM' and OBJECT_NAME not in ('AQ$_SCHEDULES_PRIMARY', 'AQ$_SCHEDULES','DBMS_REPCAT_AUTH'); 

Also remove all outdated and undocumented parameters , including events. If you work with large, complex applications, such as ERP SAP or Oracle EBS, be sure to read the attached database update documentation for such applications. There is an example of updating the database of the Oracle company itself - when all undocumented parameters were deleted, the update rate increased sevenfold.

There is an excellent utility Health Check - this is a script that allows you to check the integrity of the dictionary. This script checks known issues in Oracle8i, Oracle9i, Oracle 10g, and Oracle 11g. For better performance, it can run in several parallel channels or only for individual data files or table spaces. Health Check is performed on a database and displays information on all components in the SQL + console. If a dictionary inconsistency is detected, the script will output this information, and you will have to solve this problem before updating, because the dictionary must be in a consistent state. You can download the utility from the technical support site.

Also in version 12, a new special Pre-Upgrade preupgrd.sql script appeared. It performs checks before upgrading, runs in the old version database and performs checks on the old database environment for the transition to version 12. If any inconsistencies are found, two special scripts are formed: preupgrade_fixups.sql and postupgrade_fixups.sql.

To increase the update rate, it is recommended to have up-to-date statistics — first of all, statistics on Oracle DBMS metadata. Dictionary statistics is collected by calling the package DBMS_STATS, GATHER_DICTIONARY_STATS. Relevant statistics can be considered as collected no more than a day before the update.

Database update

Now we can proceed to the update procedure itself. The duration of the upgrade to Oracle Database 12c mainly depends on the number of components, options, and objects in the database — since, in version 12, many new tables appeared and the structure of the base tables was reorganized. To a lesser extent, the duration of the update depends on the system performance, i.e. frequency and number of central processing units and speeds of input-output subsystems. In version 12, the dictionary is updated in parallel mode - by default, in four threads. Unfortunately, the maximum degree of parallelism is limited to 8 - i.e. You can create a maximum of eight threads, since the dictionary has a maximum of eight independent components that can be updated in parallel. Perhaps in future versions the degree of concurrency will be increased. However, already now the documentation promises a 40% increase in the speed of updating the dictionary, and customer experience shows that a higher speed is possible.

Simplifies upgrading to Oracle Database 12c Database Upgrade Assistant administration tool, which is included in the Oracle DBMS distribution kit and does not require additional licensing. You can call it through the Enterprise Manager Cloud Control common console, which is convenient if you need to carry out a massive update of databases of the same type of configuration. If you need to update a clustered database, you can use the Enterprise Manager Cloud Control console.

Completion of the update

To perform a set of steps to complete the upgrade, you should use a special script called utlu121s.sql, or the Database Upgrade Assistant utility. The script displays a list of database components - the status of each component status must be VALID, otherwise the update failed. Separately, you should check the result of installing special Time Zone updates - binary patches for defining time zones and a special script that makes changes to the dictionary.

In fig. 4 is a checklist for checking the performance of an updated database. You can use the technology Real Application Testing, which allows you to record the load on the database of the old version, play it on the database of the new version and compare performance.

If you use the automatic degree of parallelism, then the required step is the input-output calibration, i.e. speed and throughput. For Exadata, the calibration and collection of system statistics is performed by a special call to DBMS_STATS.GATHER_SYSTEM_STATS, since there is a specific input-output system.

What to read?

Before you begin the update process, you should at least familiarize yourself with the documentation that is available from the technical support site support.oracle.com. The main document you need is called Upgrade Companion 12c - this is an addition to the Oracle Database version upgrade documentation, available from docs.oracle.com. If you have not registered on the Oracle Technology Network portal at otn.oracle.com, do it - you will find a lot of useful information at otn.oracle.com/upgrade.

Separately, we recommend the blogs.oracle.com/UPGRADE page - this is the blog of Mike Dietrich, a leading Oracle database updating specialist. On the page there is a link to Michael's presentation on updating databases - this is a comprehensive document that currently contains more than 600 slides.