Search

Top 60 Oracle Blogs

Recent comments

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:

SQL> alter pluggable database kowalsky open;
alter pluggable database kowalsky open
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01405: fetched column value is NULL

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:

SQL> alter session set events '1405 trace name errorstack level 1';

Session altered.

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

----- Error Stack Dump -----
ORA-01405: fetched column value is NULL
----- Current SQL Statement for this session (sql_id=) -----
select version from registry$ where status not in (9, 99) and namespace='SERVER' and cid='APEX'

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

STAT #140575638426528 id=1 cnt=1 pid=0 pos=1 obj=1508 op='TABLE ACCESS BY INDEX ROWID REGISTRY$ (cr=2 pr=0 pw=0 time=10 us cost=1 size=25 card=1)'
STAT #140575638426528 id=2 cnt=1 pid=1 pos=1 obj=1509 op='INDEX UNIQUE SCAN REGISTRY_PK (cr=1 pr=0 pw=0 time=6 us)'

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 </p />
</p></div>

    	  	<div class=