Project RICO2 and the history of APEX upgrade that went terribly wrong.

In my last blog post I explained a XOR alghorithm that is used to count Oracle database block checksum.

I also wrote, that sometimes you are facing problems, that are unresolvable without a low-level knowledge. This is the story of this kind of situation. The story of misread documentation. The story of haste and hex.

About a year ago, I got a call from one company that did an APEX upgrade for one of their customers. Very big customer. Quite a big database and a very important one, working 24/7.

They told me that they upgraded APEX on a database with one PDB and a week later they tried to apply some patches and restarted the database. After the restart they got the following error:

Oracle Support told them that they messed up APEX upgrade and that they should restore a database from before the upgrade and perform it correctly. They were right. But the customer was not happy to find out that they are about to lose a week of production data.

And this is where a true fun begins!!!

To investigate which query is the problem, you can use the following syntax:

The trace file showed that this query was a problematic one:

After tracing the session with event 10046 on level 12 I found the execution plan for this query:

Here we can see, that column "version" should contain some value for ‘APEX’ but it is null. We could try to put this value in a datafile but it is not easy to extend a row manually. The other thing we could do is to locate blocks of index REGISTRY_PK and make sure that the query will not return any rows (like change value APEX to APEK or something like that).

Or just try to change the status of this row to value 9 or 99.

Back then I used BBED to do it, but there were other cases when I had to do it with a bunch of scripts and a hex editor because I couldn’t compile BBED. So I decided to write my own copy of BBED to have something useful when needed 😉

I called the project RICO2 – based on my first tool to extract data from database files: RICO.

How does it work? Well, it is very similar to BBED but the whole functionality is not covered properly yet. I will make it better with each case I will work on.

First of all, let’s prepare the listfile for rico2. It is simple – just put a list of files you will want to edit. The file can look like this:

In a trace file I located appropriate wait events that will show me which blocks and files are interesting for me:

1509 is REGISTRY_PK and 1508 is REGISTRY$. File 12 is: /u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_system_f88l7sop_.dbf

So now it is time to start our rico2.py:

Now we have to set pointer to appropriate block. Let’s start with a table block:

In this block we can see 16 records. We can confirm that this is the correct block by checking the ktbbh structure (@24).

Now we have to find the appropriate row. In addition to regular BBED I added a possibility to search rows by a column:

Great! My row is 14th row in a data set (remember that numbers start with 0).
Let’s display the row and format it accordingly to columns in REGISTRY$

As you can see, col 6 (7th column in a table, so "STATUS") has value "1″. The problematic query is skipping values 9 and 99. 9 is c10a in hex representation for NUMBER and 99 is c164 – both are ok, because I only have to be sure not to change the length of the row.

So let’s try to change it to c10a. First of all I have to set offset to the appropriate value.

Now I can put the new hex values:

After this step, we have to recalculate database block checksum:

The last thing to do is to save changes to disk:

And check the database:

It WORKS!!! 😀
OK, now let’s just revert the value of the status column to "1″ with normal SQL:

After the final restart (just to be sure) you can open another beer and be happy 😉

You have to be aware of one thing – this is an unsupported and highly risky method of fixing things.
It should be the last resort situation. Additionally, this tool should be considered a malware on a productional system and although it simulates some original BBED behaviors, it is NOT based on reverse engineering – so RICO2 is just a set of my ideas of how the database block works, based on official materials. If you want to use this tool – use it at your own risk.

Is that clear!? Good! Have fun 😉
https://github.com/ora600pl/rico2

P.S.
Soon we will start building agenda for POUG18! http://poug.org/ You won’t believe the value of the content! Soon I will start a contest in which you will be able to win discount codes for the tickets, so observe the blog 😉