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 😉