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


14.02.2018
by Kamil Stawiarski

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 😉

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:

[oracle@rico ~]$ cat listfile.txt
/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_system_f88k6mg3_.dbf
/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_sysaux_f88k56b9_.dbf
/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_undotbs1_f88k8cph_.dbf
/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_system_f88k9mvk_.dbf
/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_users_f88k8bmv_.dbf
/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_sysaux_f88k9mvf_.dbf
/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_system_f88kr04k_.dbf
/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_sysaux_f88kr04f_.dbf
/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_users_f88kr04n_.dbf
/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_example_f88kr049_.dbf
/u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_system_f88l7sop_.dbf
/u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_sysaux_f88l7sov_.dbf
/u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_users_f88l7sow_.dbf
/u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_example_f88l7sox_.dbf

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

WAIT #140575638426528: nam='db file sequential read' ela= 8 file#=12 block#=2817 blocks=1 obj#=1509 tim=21570820295
WAIT #140575638426528: nam='db file sequential read' ela= 6 file#=12 block#=2809 blocks=1 obj#=1508 tim=21570820339

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:

[oracle@rico ~]$ python rico2.py listfile.txt
RICO v2 by Kamil Stawiarski (@ora600pl | www.ora-600.pl)
This is open source project to map BBED functionality.
If you know how to use BBED, you will know how to use this one.
Not everything is documented but in most cases the code is trivial to interpret it.
So if you don't know how to use this tool - then maybe you shouldn't ;)

Usage: python2.7 rico2.py listfile.txt
The listfile.txt should contain the list of the DBF files you want to read

 !!! CAUTION !!!!

This tool should be used only to learn or in critical situations!
The usage is not supported!
If found on production system, this software should be considered as malware and deleted immediately!

1	/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_system_f88k6mg3_.dbf
2	/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_sysaux_f88k56b9_.dbf
3	/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_undotbs1_f88k8cph_.dbf
4	/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_system_f88k9mvk_.dbf
5	/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_users_f88k8bmv_.dbf
6	/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_sysaux_f88k9mvf_.dbf
7	/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_system_f88kr04k_.dbf
8	/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_sysaux_f88kr04f_.dbf
9	/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_users_f88kr04n_.dbf
10	/u01/app/oracle/oradata/SKIPER/datafile/o1_mf_example_f88kr049_.dbf
11	/u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_system_f88l7sop_.dbf
12	/u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_sysaux_f88l7sov_.dbf
13	/u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_users_f88l7sow_.dbf
14	/u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_example_f88l7sox_.dbf

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

rico2 > set dba 11,2809
	DBA		0x2c00af9 (46140153 11,2809)
rico2 > map
 File: /u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_system_f88l7sop_.dbf(11)
 Block: 2809			Dba: 0x2c00af9
------------------------------------------------------------
 DATA Table/Cluster

 struct kcbh, 20 bytes				@0

 struct ktbbh,  72 bytes 			@20

 sb2 kdbr[16]					@110

 ub1 rowdata[7893]				@295

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

rico2 > p ktbbh
struct ktbbh, 72 bytes			@20
  ub1 ktbbhtyp				@20	0x1
  union ktbbhsid, 4 bytes		@24
	ub4 ktbbhsg1			@24	0x5e4			[raw hex: e4050000 OBJD: 1508]
	ub4 ktbbhod1			@24	0x5e4
  struct ktbbhcsc, 8 bytes		@28
	ub4 kscnbas			@28	0x1d3898		[raw hex: 98381d00]
	ub2 kscnwrp			@32	0x0
  sb2 ktbbhict				@36	0x2
  ub1 ktbbhflg				@38	0x3
  ub1 ktbbhfsl				@39	0x0
  ub4 ktbbhfnx				@40	0x0     		[raw hex: 00000000]
  struct ktbbhitl[0], 24 bytes		@44
    struct ktbitxid, 8 bytes		@44
	  ub2 kxidusn			@44	0x3			[raw hex: 0300]
	  ub2 kxidslt			@46	0xb			[raw hex: 0b00]
	  ub4 kxidsqn			@48	0x61a			[raw hex: 1a060000]
    struct ktbituba, 8 bytes		@52
          ub4 kubadba                   @52     0x100aacf               [raw hex: cfaa0001]
	  ub2 kubaseq			@56	0x18b			[raw hex: 8b01]
	  ub1 kubarec			@58	0x17
    ub2 ktbitflg			@60	0x8000			[raw hex: 0080]
    union _ktbitun, 2 bytes		@62
	  sb2 _ktbitfsc			@62	0x0			[raw hex: 0000]
	  ub2 _ktbitwrp			@62	0x0			[raw hex: 0000]
    ub4 ktbitbas                        @64     0x1badd2                [raw hex: d2ad1b00]
  struct ktbbhitl[1], 24 bytes		@68
    struct ktbitxid, 8 bytes		@68
	  ub2 kxidusn			@68	0x0			[raw hex: 0000]
	  ub2 kxidslt			@70	0x0			[raw hex: 0000]
	  ub4 kxidsqn			@72	0x0			[raw hex: 00000000]
    struct ktbituba, 8 bytes		@76
          ub4 kubadba                   @76     0x0                     [raw hex: 00000000]
	  ub2 kubaseq			@80	0x0			[raw hex: 0000]
	  ub1 kubarec			@82	0x0
    ub2 ktbitflg			@84	0x0			[raw hex: 0000]
    union _ktbitun, 2 bytes		@86
	  sb2 _ktbitfsc			@86	0x0			[raw hex: 0000]
	  ub2 _ktbitwrp			@86	0x0			[raw hex: 0000]
    ub4 ktbitbas                        @88     0x0                     [raw hex: 00000000]

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

rico2 > select col0=c:APEX
Found at *kdbr[13]

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$

rico2 > p *kdbr[13]
rowdata[0]				@295	0x2c
-------------
flag@295:	0x2c
lock@296:	0x0
cols@297:	24


col    0[4]     @298:  41504558
col    1[26]    @303:  4f7261636c65204170706c69636174696f6e2045787072657373
col    2[2]     @330:  c163
col    3[1]     @333:  80
col    4[0]     @335:  *NULL*
col    5[0]     @336:  *NULL*
col    6[2]     @337:  c102
col    7[1]     @340:  80
col    8[7]     @342:  7876020e102118
col    9[0]     @350:  *NULL*
col   10[59]    @351:  4f7261636c65204170706c69636174696f6e20457870726573732052656c6561736520342e322e352e30302e3038202d2050726f64756374696f6e
col   11[13]    @411:  56414c49444154455f41504558
col   12[0]     @425:  *NULL*
col   13[7]     @426:  7876020e102119
col   14[7]     @434:  78720707072a03
col   15[7]     @442:  78720707073413
col   16[0]     @450:  *NULL*
col   17[0]     @451:  *NULL*
col   18[0]     @452:  *NULL*
col   19[0]     @453:  *NULL*
col   20[0]     @454:  *NULL*
col   21[0]     @455:  *NULL*
col   22[0]     @456:  *NULL*
col   23[6]     @457:  534552564552


rico2 > x /rccnnccn
rowdata[0]				@295	0x2c
-------------
flag@295:	0x2c
lock@296:	0x0
cols@297:	24


col    0[4]     @298:  41504558                                 APEX
col    1[26]    @303:  4f7261636c65204170706c69636174696f6e2045787072657373 Oracle Application Express
col    2[2]     @330:  c163                                     98
col    3[1]     @333:  80                                       0
col    4[0]     @335:  *NULL*
col    5[0]     @336:  *NULL*
col    6[2]     @337:  c102                                     1
col    7[1]     @340:  80
col    8[7]     @342:  7876020e102118
col    9[0]     @350:  *NULL*
col   10[59]    @351:  4f7261636c65204170706c69636174696f6e20457870726573732052656c6561736520342e322e352e30302e3038202d2050726f64756374696f6e
col   11[13]    @411:  56414c49444154455f41504558
col   12[0]     @425:  *NULL*
col   13[7]     @426:  7876020e102119
col   14[7]     @434:  78720707072a03
col   15[7]     @442:  78720707073413
col   16[0]     @450:  *NULL*
col   17[0]     @451:  *NULL*
col   18[0]     @452:  *NULL*
col   19[0]     @453:  *NULL*
col   20[0]     @454:  *NULL*
col   21[0]     @455:  *NULL*
col   22[0]     @456:  *NULL*
col   23[6]     @457:  534552564552

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.

rico2 > set offset 338
rico2 > d
 File: /u01/app/oracle/oradata/SKIPER/652E0234EC88260AE0530100007FFD8B/datafile/o1_mf_system_f88l7sop_.dbf(11)
 Block: 2809 Offsets: 338 to 850		Dba: 0x2c00af9
---------------------------------------------------------------
c1020180 07787602 0e102118 ff3b4f72 | .....xv...!..;Or
61636c65 20417070 6c696361 74696f6e | acle Application
20457870 72657373 2052656c 65617365 |  Express Release
20342e32 2e352e30 302e3038 202d2050 |  4.2.5.00.08 - P

Now I can put the new hex values:

rico2 > modify
Usage: First - set offset to a place that you want to modify.
Then: modify [-s bytestring | -h hex]
rico2 > modify -h c10a
You want to modify block: 2809 at offset: 338
New value: c10a
Are you sure? (Y/N)  y
Block data changed. To save changes set edit mode and type: save

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

rico2 > sum apply
checksum int = 24944
checksum hex = 0x6170
Block data changed. To save changes set edit mode and type: save

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

rico2 > save
You have to be in edit mode to save block to disk. Type: set mode edit
rico2 > set mode edit
rico2 > save
Current block data successfully saved to disk. To revert changes, type: dupa

And check the database:

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter pluggable database kowalsky open;

Pluggable database altered.

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

SQL> alter session set container=kowalsky;

Session altered.

SQL> select version, status from registry$ where cid='APEX';

VERSION 			   STATUS
------------------------------ ----------
					9

SQL> update registry$ set version='4.2.5.00.08', status=1 where cid='APEX';

1 row updated.

SQL> commit;

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 😉


Contact us

Database Whisperers sp. z o. o. sp. k.
al. Jerozolimskie 200, 3rd floor, room 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter Sign up to be updated