How to view and edit data on ASM using BBED ver. 1.0

Marcin Przepiorowski, July 2009 [email protected] http://oracleprof.blogspot.com/

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected]

Contents Introduction ...................................................................................................................................... 3 Preparation ....................................................................................................................................... 4 Data access ....................................................................................................................................... 5 Final comment ................................................................................................................................ 14 Bibliography .................................................................................................................................... 14

2

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected]

Introduction The following document describes my work related to check if it is possible to read and write data which are placed inside ASM structures. An idea of that article comes to my mind when I recall that Oracle has been shipped with a Binary Block Editor which is an internal Oracle tool to view and edit database block. I was trying to read data files based on ASM but with no luck. So I decide to investigate it a little bit deeper. This article is an example only and I can’t take responsibility for any damages of your databases. Do not edit database block on production or other important system without assistance from Oracle Support.

3

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected]

Preparation First problem appear on very beginning – there is no BBED in 11g. But with little help from Miladin Modrakovic blog I have solve it. Now I got a tool – BBED was running so what I needed was a data to view. There second problem appeared – database file had been placed on ASM and not a file system. BBED is a very old tool and it not working with ASM. There are two possibilities to fix that – I could use a RMAN to copy a data file from ASM into file system but this is not a good idea if your files are big and you want to see a live data. Second option is to copy only a required database blocks (related to data which you want to see) directly from ASM into file system and then view it. But how to copy only some block from ASM into file system ? ASM is based on disks, so if we now where the data are placed we can use “dd” command to copy them. Some important information about ASM structure I have found in Luca Canali presentation for UKOUG. After I read it I was ready to perform some tests. Here are results of my work.

Creating of test table 1. Create a table which has been used in my test [oracle@piorovm ~]$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 18 09:18:57 2009 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> connect pioro/pioro Connected. SQL> create table secret_table (id number, name varchar2(100), cardnum varchar2(20)) tablespace users; Table created. SQL> insert into secret_table values (1,'Marcin Przepiorowski','4444-11112222-3333'); 1 row created. SQL> insert into secret_table values (2,'Jim Smith','4444-2222-3333-5555'); 1 row created. SQL> commit; Commit complete.

4

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected] SQL> select id, name, cardnum from pioro.secret_table; ID NAME CARDNUM ---------- --------------------------------------------------------------------------------------------------- -------------------1 Marcin Przepiorowski 4444-1111-2222-3333 2 Jim Smith 4444-2222-3333-5555 SQL>

2. Check if DBA can see a data SQL> connect / as sysdba Connected. SQL> select count(*) from pioro.secret_table; COUNT(*) ---------2

Data access Now I need some information related to table structure and location of the table.

SQL> desc pioro.secret_table Name Null? ----------------------------------------- -------ID NAME CARDNUM

Type ---------------------------NUMBER VARCHAR2(100) VARCHAR2(20)

SQL> select EXTENT_ID, BLOCK_ID, BLOCKS, FILE_ID from dba_extents where SEGMENT_NAME='SECRET_TABLE' and OWNER='PIORO'; EXTENT_ID BLOCK_ID BLOCKS FILE_ID ---------- ---------- ---------- ---------0 3465 8 4

SQL> SQL> select name from v$datafile where file#=4; NAME -------------------------------------------------------------------------------+DATA/pioro/datafile/users.262.689687725

5

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected]

From above queries I have got enough information to find a database blocks related to SECRET_TABLE and I have a table structure too. This is a time to find a database blocks on disks. Let’s start with some ASM research. All information about ASM has been taken from Luca Canali presentation – thanks. Information which should be kept in mind is that AU size is equal to 1MB and database block size has been set to 8 kB. ASM file number a part of database file name and in that case this number is 262. So what is a first allocation unit (AU) for that file ?

[oracle@piorovm ~]$ export ORACLE_SID=+ASM [oracle@piorovm ~]$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 18 10:08:45 2009 Copyright (c) 1982, 2008, Oracle.

All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> select min(AUNUM_KFDAT) from X$KFDAT where fnum_kfdat=262; MIN(AUNUM_KFDAT) ---------------2697

Now I have information about first AU in data file, so using a information from dba_extents I can find a allocation unit where SECRET_TABLE exist. To do that I need to recalculate number of a first block in table into number of allocation unit offset. Block number is equal to 3456 so after multiplication with database block size (8 kb) I have a bytes offset where table begin in datafile, if I truncate that number into number of AU (remember 1 AU = 1MB) we will find a number of AU where first block of table exist. If we do that same with a last block from extend (block_id+blocks) we will find a last AU. SQL> select AUNUM_KFDAT from X$KFDAT where AUNUM_KFDAT >= (select min(AUNUM_KFDAT)+trunc(3465*8/1024) from X$KFDAT where fnum_kfdat=262) 2 and AUNUM_KFDAT <= (select min(AUNUM_KFDAT)+trunc((3465+8)*8/1024) from X$KFDAT where fnum_kfdat=262); AUNUM_KFDAT ----------2724

3465 – block_id – first block of table

6

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected] 8 – blocks – size of table in blocks 8 kb – database block size

Now I need an ASM disk and group number for AU SQL> select GROUP_KFDAT Group# ,NUMBER_KFDAT Disk#, AUNUM_KFDAT AU# from X$KFDAT where fnum_kfdat=262 and AUNUM_KFDAT = 2724; GROUP# DISK# AU# ---------- ---------- ---------1 0 2724

After that I need to translate a disk and group number into physical disk name SQL> select name, path from v$asm_disk where group_number=1 and disk_number=0; NAME PATH ------------------------------ -----------------------------DATA ORCL:DATA SQL>

Ok still not physical disk but one step closer [oracle@piorovm ~]$ /etc/init.d/oracleasm querydisk -d DATA Disk "DATA" is a valid ASM disk on device [253, 1] [oracle@piorovm ~]$ cat /proc/partitions | grep 253 253 0 5242880 dm-0 253 1 5242880 dm-1 253 2 7340032 dm-2 [root@piorovm ~]# ls -l /dev/mapper/ total 0 crw------- 1 root root 10, 63 Jun 19 09:39 control brw-rw---- 1 root disk 253, 0 Jun 19 09:39 VGora-oraclebin brw-rw---- 1 root disk 253, 1 Jun 19 09:39 VGora-tabaza brw-rw---- 1 root disk 253, 2 Jun 19 09:39 VGora-tenbackup

7

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected]

Disk number 253,1 is accessible as /dev/VGora/VGora-tabaza Now some additional calculations: First table block is 3465 First AU for datafile is 2697 Offset between first AU and AU with table blocks is 2724 – 2697 = 27

First database block in AU where table exist 27 * 1024 / 8 kb = 3456 where 8 kb is a database block size and 1024 allow recalculating number of AU into kB.

Offset of first table block in AU 3465 - 3456 = 9

First AU block in number of database blocks 2724 * 1024 / 8 = 348672

This will be used for „dd” command Add offset of first table block to first AU block 348672 + 9 = 348681

This will be skip parameter for „dd”

8

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected]

Because I don’t know which block of table contain my rows I have to display all blocks dd if=/dev/VGora/tabaza bs=8k count=8 skip=348681 | strings [root@piorovm ~]# dd if=/dev/VGora/tabaza bs=8k count=8 skip=348681 | strings 8+0 records in 8+0 records out 65536 bytes (66 kB) copied, 0.000390783 seconds, 168 MB/s Jim Smith 4444-2222-3333-5555, Marcin Przepiorowski 4444-1111-2222-3333 [root@piorovm ~]#

Where I have seen that information? O yes in my SECRET_TABLE. But how to check what is it ? I have to create a file which is a copy of database block used by my table. dd if=/dev/VGora/tabaza bs=8k count=8 skip=348681 of=secure_table.bbed

After that I can prepare a dummy file for BBED which has a information about my datafiles. Where -

4 is a database block number

-

Secure_table.bbed is a file name for dd

-

65536 is a file size

[oracle@piorovm bin]$ cat fl1.txt 4 /oracle/app/product/11.1.0/db_1 /secure_table.bbed 65536

Now I can start BBED [oracle@piorovm bin]$ ./bbed listfile=fl1.txt BBED: Release 2.0.0.0.0 - Limited Production on Thu Jun 18 11:12:01 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED>

I will check my table using a brilliant BBED documentation provided by Graham Thornton. Because I don’t know which block is filled by data I have to check all – if block is empty a BBED can exist but don’t worry and check other one

9

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected]

BBED> set blocksize 8192 BLOCKSIZE 8192 BBED> set dba 4,8 DBA 0x01000008 (16777224 4,8) BBED> p kdbr sb2 kdbr[0] @118 sb2 kdbr[1] @120

8041 8005

Ok we have block with 2 rows inside. Check a first one BBED> p *kdbr[1] rowdata[0] ---------ub1 rowdata[0]

@8105

BBED> x /r rowdata[0] ---------flag@8105: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8106: 0x01 cols@8107: 3

0x2c

@8105

col 0[2] @8108: 0xc1 0x03 col 1[9] @8111: 0x4a 0x69 0x6d 0x20 0x53 0x6d col 2[19] @8121: 0x34 0x34 0x34 0x34 0x2d 0x32 0x33 0x33 0x33 0x33 0x2d 0x35 0x35 0x35 0x35

0x69 0x32

0x74 0x32

0x68 0x32

0x2d

Because I know what a structure of table is I can display it in more human readable format.

BBED> x /rncc rowdata[0] ---------flag@8105: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8106: 0x01 cols@8107: 3 col col col

@8105

0[2] @8108: 2 1[9] @8111: Jim Smith 2[19] @8121: 4444-2222-3333-5555

What else I can do with data? I can change it and try to put inside DB again. Let’s try. First of all BBED has be started in edit mode

10

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected] [oracle@piorovm bin]$ ./bbed listfile=fl1.txt mode=edit BBED: Release 2.0.0.0.0 - Limited Production on Fri Jun 19 15:39:52 2009 Copyright (c) 1982, 2007, Oracle.

All rights reserved.

************* !!! For Oracle Internal Use only !!! *************** BBED> set blocksize 8192 BLOCKSIZE 8192 BBED>

set dba 4,8 DBA

0x01000008 (16777224 4,8)

BBED> p *kdbr[0] rowdata[36] ----------ub1 rowdata[36]

@8141

0x2c

This is first row from table. I will change a first 4 digits of credit card number from 4444 to 0000. BBED> d /v File: secure_table.bbed (4) Block: 8 Offsets: 8141 to 8191 Dba:0x01000008 ------------------------------------------------------2c010302 c102144d 61726369 6e205072 l ,...Á..Marcin Pr 7a657069 6f726f77 736b6913 34343434 l zepiorowski.4444 2d313131 312d3232 32322d33 33333301 l -1111-2222-3333. 069792 l ... <16 bytes per line>

Check and set offset BBED> d /v offset 8169 File: secure_table.bbed (4) Block: 8 Offsets: 8169 to 8191 Dba:0x01000008 ------------------------------------------------------34343434 2d313131 312d3232 32322d33 l 4444-1111-2222-3 33333301 069792 l 333.... <16 bytes per line> BBED> set offset 8169 OFFSET

8169

11

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected]

Change of data BBED> m /c 0000 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: secure_table.bbed (4) Block: 8 Offsets: 8169 to 8191 Dba:0x01000008 -----------------------------------------------------------------------30303030 2d313131 312d3232 32322d33 33333301 069792 <32 bytes per line>

Last check like it look after change BBED> d /v offset 8169 File: secure_table.bbed (4) Block: 8 Offsets: 8169 to 8191 Dba:0x01000008 ------------------------------------------------------30303030 2d313131 312d3232 32322d33 l 0000-1111-2222-3 33333301 069792 l 333.... <16 bytes per line>

OK so I can generate a new block check sum BBED> sum Check value for File 4, Block 8: current = 0x0c2b, required = 0x0c2b BBED> sum apply Check value for File 4, Block 8: current = 0x0c2b, required = 0x0c2b BBED> exit

Now a last part – I have to copy that block back. Just to make you aware database can blow up with ORA-00600 during that operation. I don’t need copy back all tables block but only one which I have changed. I need to generate a file with my block. It was block number 8 (set dba 4,8) so last block allocated to that table. dd if=secure_table.bbed of=myblock bs=8k skip=7 count=1

First block table was 348681 and I need to add 7 to point dd exactly to last table block dd if=/oracle/app/product/11.1.0/db_1/bin/myblock of=/dev/VGora/tabaza bs=8k count=1 seek=348688

12

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected]

Uff database is still working. Final check [oracle@piorovm bin]$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jun 19 15:46:55 2009 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> connect pioro/pioro Connected. SQL> set linesize 200 SQL> select * from secret_table; ID NAME CARDNUM ---------- --------------------------------------------------------------------------------------------------- -------------------1 Marcin Przepiorowski 0000-1111-2222-3333 2 Jim Smith 4444-2222-3333-5555 SQL>

If I have compared it to first result of my query I can see that CARDNUM has been changed from 4444-1111-2222-3333 into 0000-1111-2222-3333 SQL> select id, name, cardnum from pioro.secret_table; ID NAME CARDNUM ---------- --------------------------------------------------------------------------------------------------- -------------------1 Marcin Przepiorowski 4444-1111-2222-3333 2 Jim Smith 4444-2222-3333-5555 SQL>

There is no entries about that activities in redo logs, audit table, undo tablespace – nowhere.

13

How to view and edit data on ASM using BBED Marcin Przepiorowski – [email protected]

Final comment If you are working as DBA always remember that people who have access to files, block devices or any storage where tour DB exist can be potential intruder and can change a data without authorization.

Bibliography All my work was based on these excellent papers, blogs and presentations: -

Graham Thornton – Disassembling the Oracle Data Block – September 2005

-

Luca Canali, CERN IT - A Closer Look inside Oracle ASM – UKOUG Conference 2007

-

Miladin Modrakovic blog

14