Why is it time?

Time flies quickly, and for users of Oracle Database 11.2, not to mention earlier versions, the time has come to upgrade. On January 31, 2015, the Premier Support period for Oracle Database 11.2 ended. This means that if, for example, you plan to upgrade a hardware server, 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.



For detailed support information for the various versions of Oracle Database, see the Oracle Lifetime Support Policy white paper. For example, the extended support for version 11.1 has just completed (Fig. 1). As for the most popular version 11.2 for today - the first year of expanded support is now ending for her, and from January 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 preparation for the transition to a new version of the database, it is important to understand the difference between the terms “update” and “migration”.

UpgradeDatabases are a transition to a new version within the framework of the previous operational environment - the platform and operating system do not change, data does not "move" from one server to another, only the version of the database changes. Actually, the update consists in the procedure for updating the dictionary and 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 speed; 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 12 version of the database, but the platform and operating system do not change.

It is important which version of Oracle Database is upgraded to version 12c. Starting with version 10, Data Pump technology is available. To upgrade from older versions, a process is provided 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 and comes free of charge as part of the Oracle Database distribution kit; its applicability to different versions of the databases is shown in the diagram in Fig. 2. Starting from version 12, a manual update option is also available, which is provided as a special script in Perl.

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 Recovery Manager. This technology is called RMAN incremental backup recovery. If simple is unacceptable, you need to use a special tool that provides updates on the fly and is called Golden Gate - but this is the most expensive way from the point of view of software licensing, preparation and adaptation of replication mechanisms.

Migration- This 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 utmost importance. Often, update and migration tasks are performed jointly, i.e., for example, they simultaneously upgrade to a new version of Oracle Database and at the same time change equipment.

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

You can learn more about updating and migration methods from the documents listed at the end of the article.

Patches and certification

If during the transition to a new version of the database you change the hardware and operating system, you need to find out if this software and hardware platform is certified for version Oracle Database 12c. Please see the information on certification of firmware platforms on the technical support site support.oracle.com (the Certification tab). We will not dwell on where to look for distributions - it is important not to forget to get all the current service packs. Oracle releases large cumulative service packs quarterly that fix serious bugs. The recommended patches for version 12.1.0.2 at the moment are PSU Update 4 and OJVM PSU Update 4 (update for Oracle Java Virtual Machine, 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 fix certain errors, you should make sure that these errors are fixed in version 12. Most likely, this is the case, but, nevertheless, it is recommended by the patch number to version 11, check whether a specific error has been fixed in the cumulative update of version 12, and if not, then get the appropriate “patch” to fix this error. If such a patch has not yet been released, you must make a corresponding request to the technical support service for your firmware platform.

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

An important update concerns the features of measuring time in Russia - this is due to the fact that, as you remember, the principle of calculating time in time zones has been changed twice with an interval 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 are actively using Java stored procedures and Java variables such as Date and Time with time zones, you will need a special patch to correct time in time zones for the Java virtual machine.

Remember to update the OPatch utility in order to install a new update. Oracle Database Version 12 introduces the new DMBS_QOPatch package.

Preparing the source database

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

Empty the trash before upgrading. Starting with Oracle 12c, it is executed using the preupgrade_fixups.sql script. It is recommended that you empty the trash at least once a week by automatically setting during the period of minimum load on the database.

Check for erroneous objects. There should be no erroneous objects in the SYS and SYSTEM schemes. To perform this check, you must 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 should not proceed with the upgrade. It is worth checking if there are SYS and SYSTEM in the circuits.objects with the same name , this can be done using 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 obsolete and undocumented parameters , including events. If you are working with large, complex applications such as SAP ERP or Oracle EBS, be sure to check out the documentation that came with them about updating the database for such applications. There is an example of updating the database of the Oracle company itself - when all undocumented parameters were deleted, the update speed increased seven times.

There is a great utility Health Check - a script that allows you to check the integrity of the dictionary. This script checks for 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 runs 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 script preupgrd.sql appeared. It performs checks before updating, runs in the database of the old version and checks the environment of the old database for compliance with the transition to version 12. If any inconsistencies are found, then two special scripts are generated: preupgrade_fixups.sql and postupgrade_fixups.sql.

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

Database update

Now we can begin 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 version 12 introduced many new tables and reorganized the structure of the base tables. To a lesser extent, the duration of the update depends on system performance, i.e. the frequency and number of central processors; and the speed of the I / O subsystems. In version 12, the dictionary is updated in parallel - by default, in four streams. Unfortunately, the maximum degree of parallelism is limited to 8 - i.e. You can create a maximum of eight threads, because the dictionary has a maximum of eight independent components that can be updated in parallel. Perhaps in future versions the degree of parallelism will be increased. However,

The upgrade to Oracle Database 12c simplifies the administration of the Database Upgrade Assistant, which is part of the Oracle DBMS distribution and does not require additional licensing. You can call it through the common Enterprise Manager Cloud Control console, this is convenient if you need to conduct a massive update of databases of the same configuration. If you need to upgrade a clustered database, you can use the Enterprise Manager Cloud Control console.

Update completion

To complete the 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 should be VALID, otherwise the update failed. Separately, you should check the result of installing special updates of Time Zone - binary patches for determining time zones and a special script that changes the dictionary.

In fig. Figure 4 shows a checklist for checking the performance of an updated database. You can use the Real Application Testing technology, 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 an automatic degree of parallelism, then an obligatory step is to calibrate the I / O, i.e. speed and bandwidth. For Exadata, calibration and collection of system statistics is performed by a special call DBMS_STATS.GATHER_SYSTEM_STATS, since there is a specific input-output system.

What to read?

Before you begin the upgrade process, you must, at a minimum, familiarize yourself with the documentation that is available from the support service website support.oracle.com. The main document that you need is called Upgrade Companion 12c, which is an extension to the Oracle Database version upgrade documentation, available from docs.oracle.com. If you have not registered with the Oracle Technology Network at otn.oracle.com, do so - 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, Oracle's leading specialist in updating databases. There is a link on the page to Michael's presentation on updating databases - this is a comprehensive document that currently contains more than 600 slides.