Oracle upgrade experience 11.2.0.4 to 12c

All welcome. I am a representative of the billing systems development department in the regional telecoms operator. I want to share the experience of upgrading Oracle to version 12c (12.2.0.1)
(For some reason, many people confuse the upgrade process with migration, it is clearly written here when and in what cases to use this or that value). All events took place last year.

Organizational events

From the beginning of the year, we began organizational preparatory work, first of all it was necessary to deploy a test zone. No, we had a test zone, only Oracle was deployed in it under SUSE. And in the Oracle industrial environment, we are installed on servers with the IA-64 platform, and HP-UX as an OS, while the deployment of HP-UX in a virtual environment turned out to be another quest — HP-UX as a guest OS is supported only by one VM — Integrity Virtual Machines, which should be installed on a server with the same architecture. As a result, we decided to carry out work on the production standby-db-server.

The second step was to configure the HP-UX OS ( as recommended by Oracle ). Taking into account that we have neither a “test zone” nor a TP on HP-UX, we began to look for an HP-UX specialist who would undertake this work with regard to risks. There were no such specialists among the familiar ones, they started calling HP managers. Of course, the dialogue with the manager was reduced to the acquisition of technical support, otherwise there is nothing to talk about.
')
The last time we spent the calculation of the cost of technical support for HP (hardware and software) about 5 years ago. For the sake of interest, I requested an assessment of the cost of TP For the cost that was provided to us, you can deploy a small data center, and plus technical support for all for 3 years, so the issue of its acquisition has disappeared.

We tried to consider options with technical support only for software, with limited TP for the period of work, we were looking for a system integrator, a stale second-hand Itanium server, as a test zone, but all to no avail.

As a result, a specialist from the supplier of billing responded, for which he thanks a lot.
The OS settings were made, as stated above, on a standby-db-server. Oracle itself was updated on the test zone under SUSE. There were no problems. Encouraged by the result, we planned an upgrade in the industrial zone on the night of October 24 to 25, taking into account that by the beginning of the next working day everything had to work (according to our ideal plan).

Preparing for the upgrade process

We started training on the afternoon of October 24, together with the DBA sent to us.
The required parameters were set, extraordinary backups were set up, some “heavy” tables were cleared. Next stop services, business processes, job-s and so on. In general, the preparation took almost the whole day, the update process itself began around 12 at night and ended at 4 in the morning. After we started to start everything in the reverse order, it was about 6 am, we were already mentally at home, ready to go to bed, but it was not there. All services are running, except for the application server. We found out that his service refused to start due to the fact that the version of the Oracle Client (10) was lower than the server one, it was necessary to update all the servers where the client part was lower than the acceptable version. Updated - earned.

It turned out it was only a small of problems. During the validation of business processes. found that the subscriber profile server functionality is broken. When accessing certain data, an error ORA-00600 [qmcxdGetQNameInfo2] popped up, which, in fact, was the root of the problem. We opened the service request (SR) in Oracle support in the status of “Severity 2”, in parallel we looked for possible solutions to the problem. The situation was tense with the fact that we could neither register subscribers, nor maintain: a subscriber service system (SBMS), during subscriber registration could not create a profile, CRM also did not function.

By evening, the load subsided, and the situation returned to normal. In addition, we have a solution to the problem. We found that errors occur only when referring to fields of type XML TYPE. At the same time, the XDB component (Oracle XML DB) was valid. It was decided to try setting the COMPATIBLE parameter to a value of 12.2, which was at that time still in the value of 11.2, since the Database Upgrade Guide on version 12.2 says: compatibility level is not possible after you raise the COMPATIBLE initialization parameter value . those. after setting this parameter to the corresponding value, the return to the previous version becomes impossible. But in another Oracle document (Doc ID 1292089.1) there is the following remark: ... after the upgrade, you must set the database compatibility to at least 12.1.0.1. If the compatibility is less than 12.1.0.1, it is an error. So we decided to try to fix the situation by donating the possibility of a downgrade. But, as it turned out, this decision did not bring results. After that, we postponed the solution of the problem until the morning, since the lack of sleep affected, and it was more difficult to think with each passing hour. In addition, it was necessary to wait for a response from the Oracle support.

Solving the main problem (BUG 26814058)

On the morning of October 26, we received a response from Oracle, which identified the problem as: unpublished BUG 26814058 - SELECT FROM TABLE WITH XMLTYPE FAILS WITH ORA-600 [QMCXDGETQNAMEINFO2] , which is classified as a “Code / Hardware Bug” in status 11. Bug is relatively fresh (registered September 16), moreover on the previous version (12.1). Neither the patch nor the workaround existed for him at that time (possibly for the current one). Status 11 indicates that work is underway on a patch, but at the same time no precise release dates for the patch can be obtained from Oracle, even if they release it in a couple of days. They raised the level of our SR to "Severity 1", but there was no hope for a quick solution from Oracle. It was necessary to make a decision - to return to version 11 or try to fix what is.

The second day without subscriber service affected, so we decided to wait for the night and roll back to version 11, using a standby-db-server, because we could not return using the standard downgrade procedure because of the COMPATIBLE parameter. After consulting and analyzing the tables, it turned out that a significant part of the services did not work, but all of them were tied to the server profiles (GUP). Moreover, it was possible to localize the problem to two problem tables. Particularly difficult was one of them, since it was more than 10 million. records (about 4GB). The error occurred both when trying to process the data of fields of type XML TYPE, and when trying to export data from tables. The operation “create table ... as select ...” took place, but did not give a result, the data in the new table were also damaged.

We decided to try to extract the data from the standby-db-server using the DATA PUMP, which was in the state "BEFORE the update". But it turned out that the data there is also damaged. The fact is that in preparation for the upgrade, XDB was reassembled in one of the steps in accordance with the Oracle recommendations. Presumably, the data in the XML TYPE columns is damaged as a result of this particular operation, which, by the way, occurs directly when upgrading to Oracle 12.2, since starting from version 12 the XDB component becomes mandatory and it is no longer possible to reinstall it. However, the update instruction requires that all database components be valid at the time of the update, otherwise they should be reinstalled. Thus, by the end of the working day, the problem was reduced to the search for the possibility of extracting intact table data.

Conclusion

At the most critical moment, when we tried all the options and nothing helped, we were rescued by a backup from a standby-db-server, which a colleague did before starting the update on October 24th. It was possible, through RMAN, to partially restore (only the required table spaces) standby DB at the time “before rebuilding XDB” and extract the necessary data. After that, using DATA PUMP, the problem tables were exported from standby to the main-db-server. This operation was completed at about 2 am on October 27th. After that, the functionality of the GUP server was restored. Despite the fact that the data in the restored table is outdated in time, the table was relevant, since all attempts to process the damaged data ended in failure. Those. in fact, the database was in the read-only state and the data in it did not change from the moment “before the update”.

If there is a test zone, perhaps we would identify this problem at the preparatory stage for the upgrade, but since Oracle classified the BUG as a “Code / Hardware Bug”, a match was required not only by Oracle versions and user data, but also by platform and version OS that was not possible.

Do not forget to backup, good luck to all.