return of the clones: oracle and zfs

1. Create a zpool.

zpool create orapool mirror c1t1d0 c1t2d0

2. Create the zfs file system and create a link to the filesystem (optional but useful)

zfs create orapool/oradata

chown -R oracle:dba /orapool/oradata

zfs create orapool/oradata/testdb1

chown –R oracle:dba /orapool/oradata/testdb1

ln -s /orapool/oradata /oradata

3. After creating the first oracle database with all datafiles and controlfiles in this file system, shut down the database. Remember to generate the sql file for control file recreation.

4. Take the first snapshot of the database. You can start the database back.

zfs snapshot orapool/oradata/testdb1@first

5. Clone the database to and create an additional one called testdb2

zfs clone orapool/oradata/testdb1@first orapool/oradata/testdb2

6. Duplicate the admin folder and edit the int.ora file. Add an entry in oratab.

cp –r testdb1 testdb2

7. Perform an oraenv to set the environment to the new database. Proceed as ususal with the startup nomount pfile=xxxx and call the create control file sql file that was generated.

Now for the real surprise! When you list the file system it’s observed that only the changed blocks are added to the new file system. That’s a whoppingly huge saving of disk space!!!

Top 2 DB2 WTF's to be aware of during migration

I return back after a really long time. I got tied up with another of those weary DB2 to Oracle migration projects. Sometimes one wonders if there is some sanity in other RDBMS implementations.

When doing a migration exercise, be on the look out of the following two WTF's of DB2:

• Empty strings - basically allows you to pass in a '' into a column defined as a NOT NULL.
• Undefined default values - don't even bother understanding what this is all about. It basically allows one to define a column as NOT NULL and ascribe an UNDEFINED default value (enter the empty string).

And, mind you, developers love to consume these features!

A virtual Sun

For about a week or so I have been totally frustrated in trying to get a Solaris 10 to run on my VMWare (on Windows XP) community edition server so that I can connect to the virtual server using putty and ftp.
Why you may ask. The answer is simple. It gives me a private development environment that I can mess with without causing high blood pressure to others on the team.
The main hurdle came in getting the right combination and online guides (which were aplenty) on Solaris and Oracle 10g on Solaris installations did not give this crucial piece of information. Imagine when I found the solution (took < 60 seconds to implement) how overjoyed I might have been!
More on the solution in the next post. Sorry to end like a typical Hindi soap opera.

The Oracle DBA's arsenal

When you want to administer Oracle on a Windows platform and come from the Unix world, you realize how limited you are with respect to what you can do.
Well, that's a thing of the past! It's now much easier and efficient to work on Windows doing the kind of wizardry that Oracle DBA's from the Unix/Linux world perform thanks to a ton of free software available. The tools that I commonly employ are:

Operating System:

• SysInternals Process Explorer and PS Tools
• Most notable amongst the suite is CONTIG which allows one to defragment datafiles.
  
• GNU Core Utilities
• All the utilities ported to Window
• Grep, Sed, Gawk (ported to Windows)

Oracle:

• JBSQL - a light SQL*Plus replacement, very lean and very fast though development has been stopped. I personally enjoy working with it very much as SQL Developer though powerful, does not allow one to work speedily
• Senora - An Oracle shell replacement in Perl
  
• DBEdit2 - I use this one sometimes when I work on migration projects and have to connect to different types of RDBMS's
  
• HammerOra - an all-time favourite for performing benchmarks and scalability tests
• ORASRP (Oracle Session Resource Profiler) - Amazingly fast tool for making sense of the extended SQL trace files. Again, a must have

Happy Administering!

first fifty blocks...2

Although I started with the title of this series of posts as 'first fifty blocks', obviously inspired by the movie (First Fifty Dates) I realize that the bulk of the information resides in the initial blocks. The first block is the data file header followed by Bitmapped File Space Header at Block 2. These two blocks are followed by the extent bitmaps that are described in detail at Oak Table Network by Connor McDonald way back in 2003.
Apart from changes in the SCN (system change number), one also sees that the RDBA (Relative Data Block Address) changes when a table is created in the tablespace. This is accompanied by changes in Memory addresses. Also initially when the blocks are not used they do not receive a File ID (compare the bold File/Block values in the table in the previous post) and are marked as 'unknown' type. Disturbingly, one sees the dreaded word 'CORRUPT'. When the table is created these block id's are associated with the File ID and receive valid RDBAs and different memory addresses.

first fifty blocks...1

In continuation with my previous post, some more revelations. The minimum size of a datafile that I can create in a 8k block size tablespace is 128 Kb. I created such a tablespace and this is what I saw after the KTFB Bitmapped File Space Bitmap block.

buffer tsn: 6 rdba: 0x00000009 (0/9)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0xa709 type: 0x00=unknown
Hex dump of corrupt header 4 = CORRUPT
Dump of memory from 0x06FD2200 to 0x06FD2214

So when there is no data the rdba does not specify the file ID while the block ID is specified.
Next I populated this tablespace with some data and compared both files with WinMerge.
The same block id is now present at a different rdba.

buffer tsn: 6 rdba: 0x02c00009 (11/9)
scn: 0x0000.003ac39c seq: 0x02 flg: 0x00 tail: 0xc39c2002
frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04B12200 to 0x04B14200

Now we also have a SCN and a new type. Memory addresses have also now changed. This I need to look into further. What I found most unusual is marking blocks as CORRUPT (enough to give someone a heart attack!).

Managed to get the before and after view in a tabular format.

Empty Tablespace

Non Empty TableSpace

buffer tsn: 6 rdba: 0x02c00002 (11/2) scn: 0x0000.003ac320 seq: 0x01 flg: 0x00 tail: 0xc3201d01 frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header Hex dump of block: st=0, typ_found=1 Dump of memory from 0x06FD2200 to 0x06FD4200 -- buffer tsn: 6 rdba: 0x02c00003 (11/3) scn: 0x0000.003ac314 seq: 0x01 flg: 0x00 tail: 0xc3141e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x06FD2200 to 0x06FD4200 -- buffer tsn: 6 rdba: 0x02c00004 (11/4) scn: 0x0000.003ac316 seq: 0x01 flg: 0x00 tail: 0xc3161e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x06FD2200 to 0x06FD4200 -- buffer tsn: 6 rdba: 0x02c00005 (11/5) scn: 0x0000.003ac318 seq: 0x01 flg: 0x00 tail: 0xc3181e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x06FD2200 to 0x06FD4200 -- buffer tsn: 6 rdba: 0x02c00006 (11/6) scn: 0x0000.003ac31a seq: 0x01 flg: 0x00 tail: 0xc31a1e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x06FD2200 to 0x06FD4200 -- buffer tsn: 6 rdba: 0x02c00007 (11/7) scn: 0x0000.003ac31c seq: 0x01 flg: 0x00 tail: 0xc31c1e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x06FD2200 to 0x06FD4200 -- buffer tsn: 6 rdba: 0x02c00008 (11/8) scn: 0x0000.003ac31e seq: 0x01 flg: 0x00 tail: 0xc31e1e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x06FD2200 to 0x06FD4200 -- buffer tsn: 6 rdba: 0x00000009 (0/9) scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 frmt: 0x02 chkval: 0xa709 type: 0x00=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x06FD2200 to 0x06FD2214 -- buffer tsn: 6 rdba: 0x0000000a (0/10) scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 frmt: 0x02 chkval: 0xa70a type: 0x00=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x06FD2200 to 0x06FD2214 -- buffer tsn: 6 rdba: 0x0000000b (0/11) scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 frmt: 0x02 chkval: 0xa70b type: 0x00=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x06FD2200 to 0x06FD2214 -- buffer tsn: 6 rdba: 0x0000000c (0/12) scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 frmt: 0x02 chkval: 0xa70c type: 0x00=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x06FD2200 to 0x06FD2214 -- buffer tsn: 6 rdba: 0x0000000d (0/13) scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 frmt: 0x02 chkval: 0xa70d type: 0x00=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x06FD2200 to 0x06FD2214 -- buffer tsn: 6 rdba: 0x0000000e (0/14) scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 frmt: 0x02 chkval: 0xa70e type: 0x00=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x06FD2200 to 0x06FD2214 -- buffer tsn: 6 rdba: 0x0000000f (0/15) scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 frmt: 0x02 chkval: 0xa70f type: 0x00=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x06FD2200 to 0x06FD2214 -- buffer tsn: 6 rdba: 0x00000010 (0/16) scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 frmt: 0x02 chkval: 0xa710 type: 0x00=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x06FD2200 to 0x06FD2214

buffer tsn: 6 rdba: 0x02c00002 (11/2) scn: 0x0000.003ac37f seq: 0x02 flg: 0x00 tail: 0xc37f1d02 frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c00003 (11/3) scn: 0x0000.003ac37f seq: 0x01 flg: 0x00 tail: 0xc37f1e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c00004 (11/4) scn: 0x0000.003ac316 seq: 0x01 flg: 0x00 tail: 0xc3161e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c00005 (11/5) scn: 0x0000.003ac318 seq: 0x01 flg: 0x00 tail: 0xc3181e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c00006 (11/6) scn: 0x0000.003ac31a seq: 0x01 flg: 0x00 tail: 0xc31a1e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c00007 (11/7) scn: 0x0000.003ac31c seq: 0x01 flg: 0x00 tail: 0xc31c1e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c00008 (11/8) scn: 0x0000.003ac31e seq: 0x01 flg: 0x00 tail: 0xc31e1e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c00009 (11/9) scn: 0x0000.003ac39c seq: 0x02 flg: 0x00 tail: 0xc39c2002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c0000a (11/10) scn: 0x0000.003ac39c seq: 0x01 flg: 0x00 tail: 0xc39c2101 frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c0000b (11/11) scn: 0x0000.003ac39a seq: 0x02 flg: 0x00 tail: 0xc39a2302 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c0000c (11/12) scn: 0x0000.003ac382 seq: 0x02 flg: 0x04 tail: 0xc3820602 frmt: 0x02 chkval: 0x8358 type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c0000d (11/13) scn: 0x0000.003ac382 seq: 0x02 flg: 0x04 tail: 0xc3820602 frmt: 0x02 chkval: 0x3a63 type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c0000e (11/14) scn: 0x0000.003ac382 seq: 0x02 flg: 0x04 tail: 0xc3820602 frmt: 0x02 chkval: 0x7f8e type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c0000f (11/15) scn: 0x0000.003ac39c seq: 0x01 flg: 0x00 tail: 0xc39c0601 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200 -- buffer tsn: 6 rdba: 0x02c00010 (11/16) scn: 0x0000.003ac39c seq: 0x02 flg: 0x00 tail: 0xc39c0602 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x04B12200 to 0x04B14200

first fifty blocks

Yesterday, I started off on a trip to find out what goes inside the data file. I started with first dumping the headers of all data files using:

SQL > ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME FILE_HDRS LEVEL 10';

This gives you details from the initial blocks of the data file. Subsequent blocks could be dumped using the command:

SQL > ALTER SYSTEM DUMP DATAFILE 4 BLOCK MIN 2 BLOCK MAX 50;

Once I had the above file ready, I used grep to filter out the information that I was interested in.

grep -i -a -A 2 -B 2 frmt /oracle/admin/my10gdb/udump/tracefilename.trc

This is what I get out of doing this:

buffer tsn: 4 rdba: 0x01000002 (4/2)
scn: 0x0000.00093217 seq: 0x02 flg: 0x04 tail: 0x32171d02
frmt: 0x02 chkval: 0x959c type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04BF2200 to 0x04BF4200
--
buffer tsn: 4 rdba: 0x01000003 (4/3)
scn: 0x0000.00093217 seq: 0x01 flg: 0x04 tail: 0x32171e01
frmt: 0x02 chkval: 0xbee7 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04BF2200 to 0x04BF4200
.....

--
buffer tsn: 4 rdba: 0x01000009 (4/9)
scn: 0x0000.0006e954 seq: 0x02 flg: 0x04 tail: 0xe9542002
frmt: 0x02 chkval: 0x78dc type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04BF2200 to 0x04BF4200
--
...further down...

buffer tsn: 4 rdba: 0x0100000b (4/11)
scn: 0x0000.0006e954 seq: 0x02 flg: 0x04 tail: 0xe9542302
frmt: 0x02 chkval: 0x1529 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04BF2200 to 0x04BF4200
--
buffer tsn: 4 rdba: 0x0100000c (4/12)
scn: 0x0000.0006e954 seq: 0x01 flg: 0x04 tail: 0xe9540601
frmt: 0x02 chkval: 0x17a9 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04BF2200 to 0x04BF4200
--

Now I have my hands full on trying to make head or tail out of this information. One interesting stuff I noticed yesterday. If the data file does not contain any tables, the blocks following the bitmaps are marked as CORRUPT!