DBA Blogs
Oracle Redo log contents for Insert to new table.
create table BBBBB (attribute1 char(5));
alter system switch logfile;
insert into BBBBB values ('AAAAA');
commit;
select vlf.member from v$log vl, v$logfile vlf where vl.group#=vlf.group# and vl.status = 'CURRENT';
alter system switch logfile;
ALTER SYSTEM DUMP LOGFILE 'D:\ORACLE\WIN64\ONLINELOG\O1_MF_3_8L5T3995_.LOG';
In the comparison the dump file output will be red, and the log byte value will be blue.
I ran the example on Windows, so the redo block size is 512 bytes.
I skipped the first block of the redo log, which contains the file header, and the second block, which contains the redo header. Output starts with the 3rd block, which contains the first redo record.
You can see in the output in red that the RBA (Redo Byte Address) where the first redo record starts is log sequence 9, block 2 (not including file header block) offset 16. The total length of the redo record is 668 bytes (not including the 16 byte block headers). The record also includes the VLD which defines redo record header size, SCN, SUBSCN, and Timestamp.
REDO RECORD - Thread:1 RBA: 0x000009.00000002.0010 LEN: 0x029c VLD: 0x0d
SCN: 0x0000.00110ad8 SUBSCN: 1 02/28/2013 16:19:07
# Each block starts with a 16 byte block header containing signature, block number sequence, offset and checksum.
01 22 Signature (the same in every block)
00 00
02 00 00 00 Block Number (2)
09 00 00 00 Sequence Number
10 Offset to redo record (16)
80
66 B2 CheckSum
# At offset 16 the Redo Record Header starts. For VLD 13 the header is 68 bytes containing VLD, SCN, and Timestamp.
9C 02 Redo Record Size (668)
00 00
0D 00 VLD (13)
00 00
D8 0A 11 00 SCN
01 00 00 00 Sub SCN
00 00 00 00
00 00 00 00
00 00 01 00
02 00 00 00
02 00 00 00
08 00 00 00
D7 0A 11 00 SCN
00 00 00 00
00 00 00 00
00 00 00 00
D9 0A 11 00 SCN
00 00 00 00
7B 1A 32 30 Timestamp
# The first change after the redo record header relates to a change to object 73524 in DBA (Database Block Address) 0x00415129. The block Class (CLS) is 1 (data block) and the Absolute File Number (AFN) is 1. The change is Operation (OP) 13.5 which is a "block format" prior to the insert.
CHANGE #1 TYP:1 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad6 SEQ:1 OP:13.5 ENC:0 RBL:0
KTSFRBFMT (block format) redo: Segobjd: 0x00011f34 type: 1 itls: 2 cscn: 0x0000.00110ad6
# change record for OP 13.5 is 48 bytes
0D 05 OP (13.5)
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D6 0A 11 00 00 00 SCN
DC 58
01 SEQ
01 TYP
34 1F
06 00
08 00 08 00
00 00
34 1F 01 00 SegObjd
01 00 type
02 00 itls
D6 0A 11 00 00 00 cscn
00 00
# The second change in the redo record is to the same object and block, consisting of operation 13.6 which is a "block link modify" "lock set" following the format.
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:1 OP:13.6 ENC:0 RBL:0
KTSFRBLNK (block link modify) redo: Opcode: LSET (lock set)
Next dba: 0x0041512a itli: 0
# change record for OP 13.6 is 40 bytes
0D 06 OP
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
0D 00
01 SEQ
00 TYP
34 1F
04 00
0C 00 02 00
00 00
2A 51 41 00 Next dba
00 00 00 00 itli
# The third change in the redo records is operation 13.6 which is a "block link modify" "lock write"
CHANGE #3 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:2 OP:13.6 ENC:0 RBL:0
KTSFRBLNK (block link modify) redo: Opcode: LWRT (lock write)
Next dba: 0x00000000 itli: 0
# change record for OP 13.6 is 40 bytes
0D 06 OP
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
DC 58
02 SEQ
00 TYP
34 1F
04 00
0C 00 03 00
00 00 00 00 Next dba
00 00 00 00 itli
00 00
# The fourth change in the redo record is operation 13.7 to maintain the block linked list (freelist) and high water mark (hwm).
CHANGE #4 TYP:0 CLS:4 AFN:1 DBA:0x00415128 OBJ:73524 SCN:0x0000.00110ac8 SEQ:1 OP:13.7 ENC:0 RBL:0
KTSFRGRP (fgb/shdr modify freelist) redo:
Opcode: HWMMV (move hwm)
NBK: 1
Opcode: LUPD_LLIST (link a list)
Slot no: 0, Count: 1
Flag: = 1 xid or slot0 ccnt: 0x0000.000.00000001 Head: 0x00415129 Tail: 0x00415129
# change record for OP 13.7 is 72 bytes
0D 07 OP
04 00 CLS
01 00 AFN
01 00
28 51 41 00 DBA
C8 0A 11 00 00 00 SCN
4C 4C
01 SEQ
00 TYP
34 1F
06 00
08 00
20 00 38 34
01 00 00 00
01 00 00 00
05 00 00 00
01 00 00 00
00 00 00 00
01 00 00 00
00 00 00 00
01 00 00 00
29 51 41 00 head
29 51 41 00 tail
# The fifth change in the redo record is OP 11.2 which is the actual redo for the insert to the newly formatted block. It contains details on the System Change Number (SCN), Transaction ID (xid), Undo Block Address (uba), table column and data.
# The xid is 8 bytes composed of Undo segment number(0x006) , Undo segment header transaction table slot (0x009), and sequence number wrap (0x0000032d).
# The uba is 8 bytes composed of DBA of undo block (0x00c00182), Sequence number (0x00ca), and Record number in block (0x20).
CHANGE #5 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:3 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0006.009.0000032d uba: 0x00c00182.00ca.20
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00415129 hdba: 0x00415128
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) size/delt: 9
fb: --H-FL-- lb: 0x1 cc: 1
null: -
col 0: [ 5] 41 41 41 41 41
# change record for OP 11.2 is 112 bytes (will vary depending on table and data)
0B 02 OP
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
00 00
03 SEQ
00 TYP
34 1F
08 00
14 00 31 00
05 00 01 05
DC 58
06 00 09 00 2D 03 00 00 xid
82 01 C0 00 CA 00 20 00 uba
29 51 41 00 bdba
28 51 41 00 hdba
FF 12 maxfr
02 01
01 00 itli
00 00
2C 01
01 00
00 00
00 00 slot
00 00
00 00
00 00
00 00
00 00
00 00
00 00
00 00
09 00 size
00 00 lb
00 00
00 00
00 00
00 00
41 41 41 41 41 00 00 00 DATA (AAAAA)
# The sixth change is OP 5.2 containing SCN, uba.
CHANGE #6 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.00110a95 SEQ:2 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0009 sqn: 0x0000032d flg: 0x0012 siz: 108 fbi: 0
uba: 0x00c00182.00ca.20 pxid: 0x0000.000.00000000
# change record for OP 5.2 is 60 bytes
05 02 OP
1B 00 CLS
03 00 AFN
FF FF
D0 00 C0 00 DBA
95 0A 11 00 00 00 SCN
DC 58
02 SEQ
00 TYP
FF FF
04 00 20 00
09 00 slt
3A 00
2D 03 00 00 sqn
82 01 C0 00 CA 00 20 uba
00
12 00 flg
6C siz (108)
00
00 00 00 00
00 00 00 00
00 00 00 00
# The seventh change is OP 5.4 is the final commit, containing SCN, uba.
CHANGE #7 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.00110ad8 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0009 sqn: 0x0000032d srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00182.00ca.20 ext: 3 spc: 4250 fbi: 0
# change record for OP 5.4 is 72 bytes
05 04 OP
1B 00 CLS
03 00 AFN
FF FF
D0 00 C0 00 DBA
D8 0A 11 00 00 00 SCN
DC 58
01 SEQ
00 TYP
FF FF
08 00 14 00
10 00 04 00
09 00 00 00
2D 03 00 00 sqn
00 00
00 00 srt
09 00 sta
00 00
02 00 flg
00 00
82 01 C0 00 uba part 1
# The redo record did not fit into one 512 byte block, so now continues in block 3, which starts with a 16 byte header including offset.
# block header = 16 bytes
01 22 Signature
00 00
03 00 00 00 Block Number
09 00 00 00 Sequence Number
BC Offset
80
EE 58 CheckSum
# continuation of change 7 in new block
CA 00 20 00 uba part 2
03 00 ext
9A 10 spc
00 07 00 00
FB 12 2F 51
# The eighth and final change is OP 5.1 which is the undo record.
CHANGE #8 TYP:0 CLS:28 AFN:3 DBA:0x00c00182 OBJ:4294967295 SCN:0x0000.00110a95 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 108 spc: 4360 flg: 0x0012 seq: 0x00ca rec: 0x20
xid: 0x0006.009.0000032d
ktubl redo: slt: 9 rci: 0 opc: 11.1 [objn: 73524 objd: 73524 tsn: 0]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c00182.00ca.1d
prev ctl max cmt scn: 0x0000.00110540 prev tx cmt scn: 0x0000.00110544
txn start scn: 0x0000.00110ad1 logon user: 0 prev brb: 12583297 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00415129 hdba: 0x00415128
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0)
END OF REDO DUMP
# change record for OP 5.1 is 156 bytes
05 01 OP
1C 00 CLS
03 00 AFN
FF FF
82 01 C0 00 DBA
95 0A 11 00 00 00 SCN
00 00
03 SEQ
00 TYP
FF FF
0A 00 14 00
4C 00 02 00
14 00
00 00
6C siz (108)
00
08 11
12 00 flg
00 00
06 00 09 00 2D 03 00 00 xid
CA 00 seq
20 00 rec
34 1F 01 00 objn
34 1F 01 00 objd
00 00 00 00
00 00 00 00
0B 01 09 00
08 0C 01 00
00 00 00 00
82 01 C0 00 DBA
CA 00
1D 00
40 05 11 00 00 00 prev ctl max cmt scn
C0 00
44 05 11 00 00 00 prev tx cmt scn
00 00
00 00
00 00
D1 0A 11 00 00 00 txn start scn
00 00
81 01
C0 00
00 00 00 00
00 00 00 00
03 05
FF FF
29 51 41 00 bdba
28 51 41 00 hdba
FF 12 maxfr
03 01
01 00
00 00
00 00
00 58
5 Tips for Career Success at Pythian
December 3rd, 2012 is a date I’m not likely to forget anytime soon because it marks when I began my career path as a Training Coordinator at Pythian. I’ve only looked back once and that was just to reflect in awe on the three fantastic months which have just flown by.
Stepping into our office can sometimes feel like stepping into a vortex where time elapses at an exhilarating and ridiculously accelerated pace. My days are spent developing, coordinating, meeting, scheduling, coaching and thriving. This must be what it feels like when you truly love what you do. Time never drags.
Preparation is often key in any situation where a successful outcome is desired. For the most part, I had felt ready for everything that my new employer was going to bring to the proverbial table. I’ve learned a lot and met amazing people who have provided me with support, advice and guidance since Day One. This post provides me with an opportunity to pay it forward to someone else.
In homage to my three month anniversary at Pythian, I’ve comprised the following list of five tips for anyone who is considering a future within our corporation.
1. Believe The Hype: We’re truly a league of extraordinary people. Our offices around the globe are filled with some of the top IT talent in the world. We have Oracle ACEs, a Microsoft Certified Master, a powerful Executive team and an internal network of other brilliant minds. Since our services are remote and global, our team is a rich and diverse tapestry of talent. Pythian’s HR team is stringent with their hiring practices and carefully measures the caliber of the professionals they recruit into our environment. A lot of people say this (but thankfully for us it’s true), “Our business is built by the best of the best”.
2. Be open: I’m excited to show up for work because I know that something will have been created by the time I leave. Creative minds are often the catalysts for big change in our industry. The key is to be open.
Be open to innovation.
Be open to growth.
Be open to challenge.
3. Work Hard and Play Hard: Let’s face it. Each day in a competitive global market isn’t going to be sunshine and roses. For this reason it’s imperative to seek balance in working and playing hard. There aren’t a lot of offices out there that dare to incorporate fun into the workplace experience. Thankfully Pythian recognizes that it’s an integral component for success. Pythian Pride is brilliantly shown often throughout the year through initiatives such as Geek Day, Pancake Day and Volunteer Day. Our interest in giving back to communities has been captured at events such as charity bowl-a-thons, H.O.P.E. Volleyball tournaments and numerous User Groups around the world. It’s not unusual to see people smiling in our office. In fact, we encourage it.
4. Choosing Complacency is a Career Limiting Move, Opt for Greatness: Upon entering the doors of Pythian, bring your A-Game. Our environment is a breeding ground of business savvy, technical genius, and “people” know-how. Our agenda for growth over the next five years leaves no room for mediocrity, indifference or passivity. It’s not enough to like what you do here, there has to be an inherent desire to be the best you can be.
5. Speaking “geek” isn’t mandatory, but definitely an asset: Before I signed my job offer with Pythian, I didn’t know what a TARDIS was, I had never watched The Big Bang Theory and I couldn’t have told you anything about the Star Trek trilogies. Geek knowledge has the ability to bring people together and works as a brilliant strategic icebreaker when socializing within this I.T. environment. Try to give yourself a leg up and speak the language of the people.
There it is. Pretty short and sweet but in the end the message is simply this. When you are given a great opportunity, take it as the gift that it is and create something amazing.
I’ll wrap up this post with quick words of wisdom shared by many of my peers:
“Resistance is futile. Prepare to be assimilated into the Pythian collective.
We’ll happily share with you why it is that we love data.”
My First E-Book – Integrating Oracle Applications E-Business Suite 12.1.1 with OID 11.1.1.6 and OAM 11.1.2
I have released my first eBook on Amazon to share my experience on integrating Oracle Applications E-Business Suite 12.1.1 with Oracle Internet Directory 11.1.1.6 and Oracle Access Manager 11.1.2. The OAM version 11.1.2 got certified recently with 12.1 Ebusiness Suite. The initial idea was to make a blog post describing my experience and the bottlenecks faced. But as I went on digging deep into the project, I figured out that the path was not too short, simple, straight and smooth. I had to refer to numerous blogs, books, My Oracle Support documents, Oracle forums to seek help and look for answers to first-hand problems. Once the project was over, I thought of composing an eBook.
Link: eBook
I am personally very excited about this. The support I received from the Pythian crew, my fellow DBAs and from friends all around the world have been amazing and I am thrilled. I would like to write more!
We are all excited about Fusion, aren’t we? We are working on the Fusion technologies like Weblogic, OAM, OID, Portal, Webcenter, SSO, SOA, Databases, the various upgrade paths and the list is endless. When Oracle Applications 12.2 gets released, the architecture is going to change and new features like online patching, dual file system will be introduced. Middle tier will be hosted on Weblogic.
The following is what comes with IDM and IDAM:
Identity Management (IDM)
Includes:
- HTTP Server
- Internet Directory
- Directory Integration Platform
- Virtual Directory
- Directory Services Manager – for synchronization and provisioning
- Identity Federation
- Security Developer Tools
- Enterprise Manager Fusion Middleware Control
Required Additional Software:
- Identity Management
- WebLogic Server
- Repository Creation Utility
- Patch Scripts
- Oracle Database
Oracle Identity and Access Management (IDAM)
Includes:
- Access Manager
- Adaptive Access Manager
- Identity Manager
- Identity Navigator
- Oracle Security Token Service
- Oracle Entitlements Server
Required Additional Software:
- WebLogic Server
- Repository Creation Utility
- Patch Scripts
- Oracle Database
- BI Publisher
- SOA Suite (for Oracle Identity Manager)
Softwares used for the project:
- Oracle E-Business Suite R12.1.1 + few AD/TXK patches + AccessGate 1.2.1 patch
- Oracle JDK 6 Update 37 for Linux x86-64
- Weblogic 10.3.5
- Oracle Fusion Middleware Repository Creation Utility 11g (11.1.1.6.0)
- Oracle Identity Management 11g Patch Set 5 (11.1.1.6.0)
- Oracle Fusion Middleware Repository Creation Utility 11g (11.1.2)
- Oracle Identity and Access Management 11g (11.1.2.0.0)
- Oracle Fusion Middleware Web Tier Utilities 11g (11.1.1.2.0)
- Oracle Fusion Middleware Web Tier Utilities 11g Patch Set 4 (11.1.1.5.0)
- Oracle Access Manager WebGates 11.1.1.5.0
This book covers details about the basic architecture, flow of data/information, implementation plan comprising 52 steps and each step supported by command outputs and screenshots for every mouse click.
There are new things we need to be careful about while installing the products.
For example, the DIP (Directory Integration provisioning) process will fail to become active unless the required processes are started up in the correct order. First the IDM admin server, then the node manager followed by the ldap processes and finally the ODS managed server on top of which DIP application is resting. Review Note 887653.1 for the sequence of DIP startup if you find DIP is down.
If DIP is down, the asynchronous user propagation from OID to E-Biz will not happen. The provisioning template used for E-Biz and OID integration is bi-directional in this book.
OAM 11.1.2 comes up with additional steps related to the security store we need to perform before starting up the admin server, nodemanager and OAM managed server. Else, we need to wipe out the whole installation and go for it afresh.
We will also get to have a better understanding of the role of AccessGate and Webgate.
The troubleshooting section is interesting and I hope it will be of immense help.
This book is only available in the Kindle version. If you do not have a kindle/iPad/iPhone device, no worries! You can download the kindle software for your PC from Kindle for PC and then sign up in Amazon and get the book delivered directly to your kindle software installed on your PC.
The following documents act as good references:
- Oracle Fusion Middleware 11g – Video and Podcasts Index [ID 1307123.1]
- Overview of Single Sign-On Integration Options for Oracle E-Business Suite [ID 1388152.1]
- Migrating Oracle Single Sign-On 10gR3 (10.1.4.3) to Oracle Access Manager 11gR2 (11.1.2) with Oracle E-Business Suite [ID 1485033.1]
- Integrating Oracle E-Business Suite Release 12 with Oracle Access Manager 11gR2 (11.1.2) using Oracle E-Business Suite AccessGate [ID 1484024.1]
- LDAP Directories Explained: An Introduction and Analysis – by Brian Arkills
I am already working on my next book which will carry details about SSO 10g upgrade to OAM 11g leveraging mod-osso component and bypassing the AccessGate and webgate layer. It will also cover OAM integration with WNA (Windows Native Authentication) and OID 11g integration with Microsoft AD (Active Directory). I am planning to release it in quick time. So stay tuned!
Happy Reading!
Become a published author
When a few colleagues and I were getting started with writing books about our favourite software (you guessed it … the Oracle Server) we did not have a lot of options at our disposal. I had met some players in the Oracle space when attending my first conference in Anaheim USA in the fall of 1990. I was working for the Office of the Auditor General (OAG) in Ottawa and finally made the move to get to one of the user group shows. Once I demonstrated interest in going, my immediate superior plus a few colleagues decided they really wanted to go. My application was held up for a while as others checked in on their inability to attend based on schedule conflicts.
Off I went – a young whipper-snapper never having attended any events of that size. It was an educational beehive and I could not wait to get back to the office and try out new stuff. We were running 6.0 at the OAG in those days, my being roughly 10 months into my tenure when I toddled off to California for 5 days of my first Oracle software love-in. It was magic to say the least.
I got wind of a show happening in Washington DC the following April called ECO ’91 and that was the start of my torrid career giving papers at user group and vendor events. The following September I gave a paper at IOUW 1991 in Miami Beach called “Hark I’ve Logged”. The tidal wave began. I hooked up with some people from the Boston area who assisted a meteoric rise in the Oracle space for this young lad from Eastern Canada. In early 1994, I was approached to assist rescuing an initiative called “Tuning Oracle” which turned out to be the first publication in the Oracle Press series.
The first round of raw material had been presented to the publisher called Osborne McGraw-Hill in those days. There were lots of holes in the work and I spent hours of my time getting the finishing touches on the work, published in the fall of 1994. We had no other avenues to get technical chit-chat out there; no BLOGging, no social media but some hard-copy publications. The first round of editing was done via a brown paper envelope being dispatched from Berkeley California to my doorstep followed by surface mail back to the copy editor. It was a chore but was exciting.
A third player joined the team and we went on to write four works and a wealth of updates as new versions of the Oracle software were released. By 2002 we had settled into a steady list of OracleX: A Beginner’s Guide, having left the other topics for others better armed to pull it off. As we speak, Ian, Michelle, yours truly and a handful of others are putting the finishing touches on Oracle Database XX: Install, Deploy, Manage slated for release coincident with Oracle’s next version of the database.
So what’s changed … the routes one can follow to be a published author. Check this out as a prime example of where the publishing business has gone … electronic, digital and did I mention “electronic” or “digital”. Have a look at Subhajit’s offering at Amazon and let that get your creative juices flowing. No more hard-copy; no more exhaustive rounds of back and forth editing and revising. Just ensure at least one other person sees the work as it progresses and keep the following in mind from someone who has been there and back many may times:
- put a stake in the ground called “deadline” and stick to it as if someone was pressuring you to be done by that date
- once others have seen and offered feedback on the content, freeze the chapter and don’t be tempted to go back for another round of revisions – if you do this you will never finish
- be careful about publishing copyrighted material
- spell check your work as you go keeping in kind that this electronic engine is not foolproof and a manual viewing of all written material is warranted as well
- be careful with acronyms … when first used in a chapter, expand them to remove confusion they could generate in the readers’ mind
- avoid idioms and local colloquialisms in your text as they can alienate your readership – the last thing you want to do
Happy writing and publishing …
Short-Circuiting the COST
If it finds a predicate that causes a "short-circuit" --- one that prevents rows from being returned by the query --- it can evaluate the COST of the query to 0 (zero). Normally, we would never expect a COST to zero --- even where we know zero rows will be returned by the query, Oracle may have to undertake some I/O (a Full Table Scan or an Index Range Scan in the simplest cases) and some CPU cycles to verify the resulting blocks for the expected result.
Here I show a simple example of a short-circuit.
First, I build a Table and an Index, with statistics :
SQL> create table my_tableSo we know that all the statistics (Row Count in the table and number of Distinct values in the Index) are consistent. I then run a simple query :
2 as select * from dba_objects;
Table created.
SQL> select count(*) from my_table;
COUNT(*)
----------
76609
SQL> create index my_table_ndx on my_table(owner);
Index created.
SQL> exec dbms_stats.gather_table_stats('','MY_TABLE',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> select num_rows, sample_size
2 from user_tab_statistics
3 where table_name = 'MY_TABLE';
NUM_ROWS SAMPLE_SIZE
---------- -----------
76609 76609
SQL> select num_distinct
2 from user_tab_col_statistics
3 where table_name = 'MY_TABLE'
4 and column_name = 'OWNER';
NUM_DISTINCT
------------
44
SQL> select leaf_blocks, distinct_keys, num_rows, sample_size
2 from user_ind_statistics
3 where table_name = 'MY_TABLE'
4 and index_name = 'MY_TABLE_NDX';
LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS SAMPLE_SIZE
----------- ------------- ---------- -----------
183 44 76609 76609
SQL>
SQL> col object_name format a30Oracle has estimated that it will fetch 1741 rowids from the index in the plan step id=3 and then fetch 1,741 rows and also apply a filter for object_name in plan step id=2 to reduce the row count to 24.
SQL> col object_type format a18
SQL> set autotrace on
SQL> select object_name, object_type
2 from my_table
3 where owner = 'HEMANT'
4 and object_name like 'S%'
5 order by 1,2;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
SOURCE_PK INDEX
SOURCE_PK1 INDEX
SOURCE_TABLE TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 1587485563
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 984 | 53 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 24 | 984 | 53 (2)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| MY_TABLE | 24 | 984 | 52 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | MY_TABLE_NDX | 1741 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME" LIKE 'S%')
3 - access("OWNER"='HEMANT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
579 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
The actual resulting row count is 3. (The 24 row count is an estimate)
The estimated cost of the Index Range Scan is 5, the estimated cost of the Table Access (1741 rowids) and Filter is 47 (52-5) and the estimated cost of the Sort (for 24 rows) is 1 (53-52), resulting in a total cost of 53.
Oracle read a total of 5 blocks (consistent gets).
What happens if I add an "AND 1=2" predicate (which is always FALSE) ?
SQL> select object_name, object_typeThe query returns no rows (i.e. zero rows).
2 from my_table
3 where owner = 'HEMANT'
4 and object_name like 'S%'
5 and 1=2
6 order by 1,2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 422461895
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 0 (0)| |
| 1 | SORT ORDER BY | | 1 | 41 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| MY_TABLE | 24 | 984 | 52 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | MY_TABLE_NDX | 1741 | | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
3 - filter("OBJECT_NAME" LIKE 'S%')
4 - access("OWNER"='HEMANT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
356 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
But notice the "filter (NULL IS NOT NULL)". This is the short-circuit that Oracle adds. This will always evaluate to FALSE. This is Oracle's way of translating the "1=2" condition that I added to the query.
Although there is a supposed COST of 5 for the 1,741 rowids from the Index Range Scan and a supposed COST of 47 (52-5) for the Table Access of the 1,741 rowids and filtering to 24 rows for "OBJECT_NAME LIKE 'S%'), Oracle then proceeds to add the NULL IS NOT NULL FILTER before the SORT ORDER BY. This converts the COST to 0.
We can see that Oracle*did not do any I/O* because the Statistics show 0 block gets (consistent gets).
Let me say that again : Oracle did NOT do any I/O inspite of the supposed Index Range Scan and Table Access by Index Rowid.
.
.
.
Don’t focus on cost of execution plan
I don’t focus on the Oracle optimizer’s cost of a particular execution plan when I’m tuning a query and I’m worried that many of the developers and DBAs that I’ve talked with about query tuning are too focused on lowering the cost of a plan when attempting to tune a query.
I hear comments like this all the time when talking about making a change X to improve the performance of a query. “I changed X and the cost in the explain plan was so much lower.” X could be adding an index or hint or making a parameter change, etc.
I just cringe inside when I hear this and I hope I am gracious but all the time I’m filled with fear that the person I’m talking to is missing a key concept when it comes to Oracle query tuning. The concept is just that in many cases the optimizer’s estimated cost is far off from reality. So, making a change and seeing the cost of the plan go down really doesn’t mean much. It could directly correlate to corresponding improvement in the query run time or it could be just the opposite. It is kind of like the buffer cache hit ratio. Sometimes this ratio really means something and sometimes it doesn’t.
Instead of focusing on the cost I focus on the plan itself. Based on my investigation of the tables in the query and how many rows will be accessed from each I’ve come up with an idea of a plan that should be better than the one I’m improving. So, my question about a proposed change X becomes “Does change X cause the plan to change to the one I determined to be better?”
I attempted to lay out this approach in my Intro to SQL Tuning presentation. For me query tuning is kind of like programming. I’m figuring out the best way to really do the steps of the plan based on my own study of the existing tables. Then I just have to figure out what change to make to get the optimizer to run the query my way. This is a time consuming approach but I would only spend the time on queries that really need it. Who has time to tune every query?
So, my recommended approach to query tuning is to figure out a good plan on your own and then to figure out how to get the database to run it your way. I don’t recommend focusing on what the cost of the new plan is compared with the original plan.
- Bobby
Log Buffer #309, A Carnival of the Vanities for DBAs
Oracle, SQL Server, and MySQL; these database technologies among various other similar innovations are running this world virtually and bloggers have got lot to say in this regard. This Log Buffer Edition is yet another voice in this arena.
Oracle:
Oliver Steinmeier is explaining the JDeveloper and Fusion Applications in a lucid way.
London prepares for a surge of developer brainpower during the Devoxx UK conference on Tuesday and Wednesday, March 26th and 27th, Yolande Poirer tells us.
It’s hard to believe that another year has passed from last RSA. But, indeed, time flies when you’re busy, I guess, Slavik has more.
Joel Goodman asks; Can Supporting an Exadata Database Machine Make You Certifiable?
Christopher Gait has blogged about Oracle Priority Service Infogram.
SQL Server:
Michael Swart is following up on Ad hoc TVP contention.
James Serra is sharing his Data Warehouse Architecture presentation slides.
Jen McCown is live on the PowerScripting podcast.
Chris Shaw is telling us as why do a security audits.
The Public Preview of Data Explorer (which some of you know I’ve been following for a while, since it first appeared in SQL Azure Labs), is now available for download. Chris Webb tells.
MySQL:
Björn Melinder is having a hands on with jet profiler.
Percona Server on the Nexus 7: Your own MySQL Database Server on an Android Tablet
A good blog post about installing Apache2 With PHP5 And MySQL Support On Scientific Linux 6.3 (LAMP).
Daniel van Eeden is talking about MySQL Events.
Daniel Nichter says that MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server .
How many CPU cores do I really have?
How Many CPU Cores Do I Really Have?
The view operating system statistic view v$osstat is can be misleading with regards to CPU cores. Not that the information is incorrect, it's well... let's say troubling. If I ask ten people to email a sample AWR report, I'm likely to see CPU core-like statistics such as CPU_SOCKETS, NUM_CPUS, VCPU, LCPU, CPU_THREADS, and probably a variety of other names. Wow… what a mess!
But I'd still like to know because it's important for my work. For two reasons:
First, it helps me to understand how high the CPU utilization can go before performance starts to degrade. Based on queuing theory, the more processes a system can simultaneously process the higher the average utilization the system can sustain before performance begins to degrade. I write about this in the Operating System section in my Oracle Performance Firefighting book.
Second, I always check what the math and my observations indicate versus what the OS administrator and OS commands (such as vmstat, war, top) tell me. Paranoid perhaps, but doing Oracle work for 20-plus years has taught me a few things...
Call It a Server, Not a Core, Lcpu, Thread, etc.
To avoid the entire discussion about which provides the processing power; core or thread, let's simply call the unit of processing power a "server." Why? Two reasons. First, because it provides CPU service to processes, so it truly is a "server." Second, that's what capacity planners call something that services transactions; a server. In fact, its symbol is M (capital "m").
By the way, it is very easy to determine, on your system, what provides the true CPU processing power (cores, threads, or something else). I blogged about this in June of 2011.
So the question is, how many "servers" does your database host contain? That's what this posting is all about.
If you recall from my previous posting, I demonstrated two ways to calculate CPU utilization. Both follow the classic; requirements divided by capacity. But the capacity is where the two approaches differ.
Capacity Calculation Using "servers"
Using "servers" to calculate the capacity is simply the number of servers multiplied by the snapshot interval. So a 2 server (think two cores) host over a 60 minute period can provide a maximum of 120 minutes or 7200 seconds of CPU power.
Here's the utilization formula using the capacity approach:
U = R / C
where;
R = CPU consumption over the interval (seconds)
C = CPU "servers" X interval (seconds)
For example, looking at a real AWR report, over a 60 minute interval, the AWR's Operating System Statistics show show a BUSY_IIME of 1913617, IDLE_TIME of 7159367 and the NUM_CPUS of 24.
Therefore, the average CPU utilization over the interval is:
U = 19136.17 / ( 24 * 60 * 60 ) = 0.221 = 22%
Capacity Calculation Using Busy and Idle Time
In my previous posting I introduced using only v$osstat's BUSY_TIME and IDLE_TIME values to calculate the average CPU utilization over the snapshot interval. Here's the formula:
U = R / C = BUSY_TIME / ( BUSY_TIME + IDLE_TIME )
Using the above examples numbers;
U = 1913617 / ( 1913617 + 7159367 ) = 0.211 = 21%
Yes, the two utilization calculation results don't match perfectly but they are very close… close enough.
Calculating the Number of "Servers"
Notice that in the busy and idle time capacity calculation there is no reference to the number of servers. Suppose you don't trust the v$osstat CPU core-like statistics or are simply not sure which one is important. In other words, you want to understand the effective number of CPU "servers." Using the two utilization formulas and some algebra we can figure this out!
Making sure to use the same unit of time, here are two capacity calculations:
C = servers * interval
C = busy_time + idle_time
Let's put them together and solve for "servers".
servers * interval = busy_time + idle_time
servers = ( busy_time + idle_time ) / interval
OK… but does this really work? Let's give it a try! (I'm going to use seconds as my unit of time.)
effective servers = ( 19136.17 + 71593.67 ) / ( 60 * 60 ) = 25.2
The math tells us that based on the collected data, on average the system is operating with effectively 25 "servers." I know in this situation there are physically 24 CPU cores, so we're pretty close.
What to Do With AIX
While this "effective servers" formula has proven its worth in many systems, I still find it does not work well many times in an AIX environment. Sometimes it does, but not always. So do the math and compare it with vmstat or some other AIX based tool.
The Take-Aways
The big one:
servers = ( busy_time + idle_time ) / interval
Personally, I never initially trust the CPU number related v$osstat statistics. I always check with the OS administrator and also run a simple OS command like top or sar or do a "cat /proc/stat". It's always a good idea to casually check with the OS administrator. You don't want to be thinking and working with 12 "servers" when the administrator is thinking 24 "servers."
For me, knowing the number of CPU "servers" is important. And since I never blindly trust the v$osstat CPU statistics, this is a very fast and reliable way (so far at least) to check my work.
Thanks for reading!
Craig.
If you enjoy my blog, I suspect you'll get a lot out of my courses; Oracle Performance Firefighting, Advanced Oracle Performance Analysis, and my one-day Oracle Performance Research Seminar. I teach these classes around the world multiple times each year. For the latest schedule, click here. I also offer on-site training and consulting services.
P.S. If you want me to respond to a comment or you have a question, please feel free to email me directly at craig@orapub .com. Another option is to send an email to OraPub's general email address, which is currently orapub.general@comcast .net.
Delphix First Month
My company is trying out a new product called Delphix.
We have had Delphix installed for about a month and I wanted this post to contain my first impressions for others who may be considering it. Essentially Delphix provides fast copies of databases so that a terabyte database can be refreshed in minutes. My most recent example took three minutes for a development db that is just over one TB.
Delphix is an appliance running Open Solaris that runs in a VMWare ESX virtual machine. At least, that is how we have it installed. You license Delphix by the number of CPUs in the virtual machine so you can use it as much as you want as long as the CPUs can handle it. Your database file systems are connected to the Delphix appliance using NFS. So, the critical component of Delphix is the network connection between your target database servers and the appliance. In our case we put new 10 gigabit ethernet hardware in to connect our HP-UX Itanium virtual machines (target database servers) to the Delphix vm. We made sure they were all on the same IP subnet with nothing but a nice high speed switch between them all. After some initial testing Delphix’s performance and support teams made some network configuration changes that helped with an initial performance/hang issue. Looks like it may have something to do with the packet flow control within TCP/IP but it works fine now.
The Delphix appliance uses RMAN to pull data from a source database. The source database has to be in archivelog mode and for best performance you need to enable block change tracking. Delphix does an initial full level 0 backup and then regular incremental level 1 backups. To create a new clone copy of the original database you pick one of these incremental backups – called snapshots – and point to the target machine and Delphix automatically mounts the appropriate NFS filesystems with the datafiles, tempfile, redo logs, etc. and brings up a clone of the source system with the new name you designate.
Also, Delphix takes snapshots of the clone databases – called VDBs – on a regular basis so these can be used as backups of the clones themselves and you can clone the clones – sounds like Star Wars Clone Wars doesn’t it?
One interesting challenge is space management. Each new clone takes up very little space – until you start updating it. I did some tests where I copied a 30 gig table to a new table and the vdb which previously took less than 100 megabytes of space now took gigabytes. Everything is compressed so I think it was less than half of the 30 gig but the point is that the more the copies get updated the more disk space you need for the copies. If you have an application that needs copies quickly but the copies aren’t heavily updated you can make many copies with very little disk space. I think this kind of capability opens up all kinds of possibilities we haven’t considered before since each copy without Delphix would take up an equal amount of space as the original if you just use normal disk storage. I.e. Before Delphix we had to minimize the number of production copies we used for development and testing. Imagine how our processes might change if we can spin off a quick production copy, use it for a short while, and then get rid of it all with minimal additional disk space and with the clone occurring in minutes.
Right now I’m working with a Delphix consultant on a script that the developers can use to refresh their own database from the most recent snapshot of its source. Delphix has a couple of ways it can be controlled from a Unix shell script. One way is with ssh and their command line interface (CLI). You can setup ssh with a public and private key so you can ssh into the VM as the “delphix admin” user and then run a set of commands in Delphix’s proprietary language. Delphix also has a GUI but it is nice that you can run a script and do things like kick off a refresh or clone. Also, there is a web service and they gave me a sample Python script to communicate with the VM through the web service but I haven’t delved into it. For one thing, we don’t have Python installed on our HP-UX servers. You can access Delphix’s documentation online here including the CLI interface commands.
Well, I’m not trying to sell Delphix to anyone but I thought it would be good to put down some of my experiences. The database cloning and refreshing is remarkably fast. Time will tell how the network NFS performance holds out during heavy development and testing but assuming we manage that performance and the disk space usage I’m pleased with the remarkable clone times that are supported by the Delphix VM.
- Bobby
A demonstration of Oracle row movement within a block.
SQL> create table TEST_TABLE(id integer, attribute1 varchar2(200)) tablespace test;
Table created.
SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST';
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------
5
D:\ORACLE\WIN64\DATAFILE\O1_MF_TEST_8LRBSQ59_.DBF
SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
73516 73516
SQL> insert into test_table values (1,'AAAAA');
1 row created.
SQL> insert into test_table values (2,'BBBBB');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segme
nt_name = 'TEST_TABLE';
FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
5 160 8 5
We extract the block contents using the following program :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html
Block 163 Contains Object ID 73516
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971683
SCN Base :- 1075559
SCN Wrap :- 0
Sequence :- 01
Flag 01-NEW :- 06
CheckSum :- 32492
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73516
Cleanout SCN Base :- 1075556
Cleanout SCN Wrap :- 0
Spare 4 :- 00
Spare 5 :- 00
ITL Slots :- 2
UNKNOWN Byte :- 00
Flag 00-FREE :- 32
ITL TX Feeelist Slot :- 00
Next Block On Free List :- 20971680
ITLSlot :- 1
Undo Segment :- 1
Undo Segment Slot :- 5
Transaction Sequence :- 633
Undo Block Address :- 12583566
Undo Sequence :- 165
Undo Record Number :- 23
Spare 1 :- 0
Flag :- 8194
_ktbitun :- 0
Base :- 1075559
ITLSlot :- 2
Undo Segment :- 0
Undo Segment Slot :- 0
Transaction Sequence :- 0
Undo Block Address :- 0
Undo Sequence :- 0
Undo Record Number :- 0
Spare 1 :- 0
Flag :- 0
_ktbitun :- 0
Base :- 0
Data Header start :- 100
Flags :- 00
Number of Tables :- 1
Number of Rows :- 2
Offset to Freespace Start :- -1
Offset to Freespace End :- 22
Available Space :- 8064
Available Space after Commit:- 8042
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8076(+100)
Row Offset 2 :- 8064(+100)
1 Row Header start :-8176
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 2
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- AAAAA
2 Row Header start :-8164
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 2
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- BBBBB
From the above we can see that there are two rows in block 163, starting at bytes 8164 and 8176. The first record inserted (AAAAA) is at the end of the block, with no room to grow.
If we update the record with more data :-
SQL> update test_table set attribute1 = '012345678901234567890123456789012345678
9012345678901234567890123456789012345678901234567890' where attribute1 = 'AAAAA'
;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
We can now see that :-
1. the block SCNs and sequence have been updated
2. the block CheckSum has been updated
3. the Available Space in the block has decreased
4. the record that previously started at byte 8176 has now moved to byte 8066 to allow it to grow.
Block 163 Contains Object ID 73516
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971683
SCN Base :- 1075759
SCN Wrap :- 0
Sequence :- 02
Flag 01-NEW :- 06
CheckSum :- -25092
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73516
Cleanout SCN Base :- 1075757
Cleanout SCN Wrap :- 0
Spare 4 :- 00
Spare 5 :- 00
ITL Slots :- 2
UNKNOWN Byte :- 00
Flag 00-FREE :- 32
ITL TX Feeelist Slot :- 00
Next Block On Free List :- 20971680
ITLSlot :- 1
Undo Segment :- 1
Undo Segment Slot :- 5
Transaction Sequence :- 633
Undo Block Address :- 12583566
Undo Sequence :- 165
Undo Record Number :- 23
Spare 1 :- 0
Flag :- -32768
_ktbitun :- 0
Base :- 1075559
ITLSlot :- 2
Undo Segment :- 7
Undo Segment Slot :- 22
Transaction Sequence :- 605
Undo Block Address :- 12583559
Undo Sequence :- 222
Undo Record Number :- 49
Spare 1 :- 0
Flag :- 8193
_ktbitun :- 0
Base :- 1075759
Data Header start :- 100
Flags :- 00
Number of Tables :- 1
Number of Rows :- 2
Offset to Freespace Start :- -1
Offset to Freespace End :- 22
Available Space :- 7966
Available Space after Commit:- 7956
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 7966(+100)
Row Offset 2 :- 8064(+100)
1 Row Header start :-8066
Flags :- 2C Table Data
Lock Status :- 02
Number of Columns :- 2
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 91 Data :- 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
2 Row Header start :-8164
Flags :- 2C Table Data
Lock Status :- 00
Number of Columns :- 2
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- BBBBB
What happens to Oracle data blocks during Truncate.
SQL> create table TEST_TABLE(id integer, attribute1 char(5), attribute2 varchar2 (5)) tablespace test;
Table created.
SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST';
FILE_ID
----------
FILE_NAME
-----------------------------------------------------------------
5
D:\ORACLE\WIN64\DATAFILE\O1_MF_TEST_8LRBSQ59_.DBF
SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
73514 73514
SQL> insert into test_table values (1,'AAAAA','BBB');
1 row created.
SQL> insert into test_table values (2,'CCCCC','DDDDD');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segment_name = 'TEST_TABLE';
FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
5 152 8 5
We can find the data for the table in file 5 in an extent starting at block 152. When we scan for the data, we find that the records we inserted are in block 155, with empty blocks 156,7,8,9. The following output is from :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html
Block 155 Contains Object ID 73514Block Header start :- 0 Block Type 06-DATA :- 06 Block Format :- A2 Spare1 :- 00 Spare2 :- 00 Relative Block Address :- 20971675 SCN Base :- 1074483 SCN Wrap :- 0 Sequence :- 01 Flag 01-NEW :- 06 CheckSum :- 14737 Spare3 :- 0Transaction Header start :- 20 Type 01-DATA 02-INDEX :- 01 Spare 1 :- 00 Spare 2 :- 00 Spare 3 :- 00 Object ID :- 73514 Cleanout SCN Base :- 1074478 Cleanout SCN Wrap :- 0 Spare 4 :- 00 Spare 5 :- 00 ITL Slots :- 2 UNKNOWN Byte :- 00 Flag 00-FREE :- 32 ITL TX Feeelist Slot :- 00 Next Block On Free List :- 20971672ITLSlot :- 1 Undo Segment :- 1 Undo Segment Slot :- 14 Transaction Sequence :- 632 Undo Block Address :- 12583042 Undo Sequence :- 162 Undo Record Number :- 51 Spare 1 :- 0 Flag :- 8194 _ktbitun :- 0 Base :- 1074483ITLSlot :- 2 Undo Segment :- 0 Undo Segment Slot :- 0 Transaction Sequence :- 0 Undo Block Address :- 0 Undo Sequence :- 0 Undo Record Number :- 0 Spare 1 :- 0 Flag :- 0 _ktbitun :- 0 Base :- 0Data Header start :- 100 Flags :- 00 Number of Tables :- 1 Number of Rows :- 2 Offset to Freespace Start :- -1 Offset to Freespace End :- 22 Available Space :- 8054 Available Space after Commit:- 8032Table Directory start :-114 Table :- 1 Offset :- 0 Number of Rows :- 2 Total Number of Rows :- 2Row Directory start :-118 Row Offset 1 :- 8072(+100) Row Offset 2 :- 8054(+100)1 Row Header start :-8172 Flags :- 2C Table Data Lock Status :- 01 Number of Columns :- 3Column 1 Bytes 2 Data :- C1 02 Column 2 Bytes 5 Data :- AAAAAColumn 3 Bytes 3 Data :- BBB2 Row Header start :-8154 Flags :- 2C Table Data Lock Status :- 01 Number of Columns :- 3Column 1 Bytes 2 Data :- C1 03 Column 2 Bytes 5 Data :- CCCCCColumn 3 Bytes 5 Data :- DDDDD
Block 156 Contains Object ID 73514Block 157 Contains Object ID 73514Block 158 Contains Object ID 73514Block 159 Contains Object ID 73514
Now we truncate the table. Selecting from the data dictionary table we can see that the data blocks listed in DBA_EXTENTS did not change, but in DBA_OBJECTS the DATA_OBJECT_ID was updated.
SQL> truncate table test_table;
Table truncated.
SQL> alter system checkpoint;
System altered.
SQL> select * from test_table;
no rows selected
SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST_TABLE';
OBJECT_ID DATA_OBJECT_ID---------- -------------- 73514 73515
SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segment_name = 'TEST_TABLE';
FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO---------- ---------- ---------- ------------ 5 152 8 5
If we again scan for blocks associated with the old DATA_OBJECT_ID 73514 we can see that they are unchanged and still have the original data in them. This means that even after a table is truncated, the data remains in the blocks on disk, and could be extracted (until the blocks are overwritten).
Block 155 Contains Object ID 73514
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971675
SCN Base :- 1074483
SCN Wrap :- 0
Sequence :- 01
Flag 01-NEW :- 06
CheckSum :- 14737
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73514
Cleanout SCN Base :- 1074478
Cleanout SCN Wrap :- 0
Spare 4 :- 00
Spare 5 :- 00
ITL Slots :- 2
UNKNOWN Byte :- 00
Flag 00-FREE :- 32
ITL TX Feeelist Slot :- 00
Next Block On Free List :- 20971672
ITLSlot :- 1
Undo Segment :- 1
Undo Segment Slot :- 14
Transaction Sequence :- 632
Undo Block Address :- 12583042
Undo Sequence :- 162
Undo Record Number :- 51
Spare 1 :- 0
Flag :- 8194
_ktbitun :- 0
Base :- 1074483
ITLSlot :- 2
Undo Segment :- 0
Undo Segment Slot :- 0
Transaction Sequence :- 0
Undo Block Address :- 0
Undo Sequence :- 0
Undo Record Number :- 0
Spare 1 :- 0
Flag :- 0
_ktbitun :- 0
Base :- 0
Data Header start :- 100
Flags :- 00
Number of Tables :- 1
Number of Rows :- 2
Offset to Freespace Start :- -1
Offset to Freespace End :- 22
Available Space :- 8054
Available Space after Commit:- 8032
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8072(+100)
Row Offset 2 :- 8054(+100)
1 Row Header start :-8172
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start :-8154
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD
Block 156 Contains Object ID 73514
Block 157 Contains Object ID 73514
Block 158 Contains Object ID 73514
Block 159 Contains Object ID 73514
What happens in an Oracle block during a delete.
SQL> create table TEST_TABLE(id integer, attribute1 char(5), attribute2 varchar2 (5)) tablespace test;
Table created.
SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST';
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------
5
D:\ORACLE\WIN64\DATAFILE\O1_MF_TEST_8LRBSQ59_.DBF
SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
73513 73513
SQL> insert into test_table values (1,'AAAAA','BBB');
1 row created.
SQL> insert into test_table values (2,'CCCCC','DDDDD');
1 row created.
SQL> commit;
Commit complete.
SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segment_name = 'TEST_TABLE';
FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
5 144 8 5
SQL> alter system checkpoint;
System altered.
When we scan the file for data object ID 73513 we find the data in block 150. The following block data was extracted using :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html
Block 150 Contains Object ID 73513
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971670
SCN Base :- 1073062
SCN Wrap :- 0
Sequence :- 01
Flag 01-NEW :- 06
CheckSum :- 14473
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73513
Cleanout SCN Base :- 1073058
Cleanout SCN Wrap :- 0
Spare 4 :- 00
Spare 5 :- 00
ITL Slots :- 2
UNKNOWN Byte :- 00
Flag 00-FREE :- 32
ITL TX Feeelist Slot :- 00
Next Block On Free List :- 20971664
ITLSlot :- 1
Undo Segment :- 5
Undo Segment Slot :- 33
Transaction Sequence :- 814
Undo Block Address :- 12583110
Undo Sequence :- 169
Undo Record Number :- 6
Spare 1 :- 0
Flag :- 8194
_ktbitun :- 0
Base :- 1073062
ITLSlot :- 2
Undo Segment :- 0
Undo Segment Slot :- 0
Transaction Sequence :- 0
Undo Block Address :- 0
Undo Sequence :- 0
Undo Record Number :- 0
Spare 1 :- 0
Flag :- 0
_ktbitun :- 0
Base :- 0
Data Header start :- 100
Flags :- 00
Number of Tables :- 1
Number of Rows :- 2
Offset to Freespace Start :- -1
Offset to Freespace End :- 22
Available Space :- 8054
Available Space after Commit:- 8032
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8072(+100)
Row Offset 2 :- 8054(+100)
1 Row Header start :-8172
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start :-8154
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD
Now we delete one row :-
SQL> delete from test_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from test_table;
ID ATTRI ATTRI
---------- ----- -----
2 CCCCC DDDDD
SQL> alter system checkpoint;
System altered.
And we can extract the block data again and confirm that the result of the delete is that :-
1. the block SCNs and Sequence have been updated2. the block CheckSum has been updated3. ITL Slot 2 was used for the transaction4. ITL Slot 1 flag was set to -32768 (unused)5. The row header flag was updated from 2C to 3C6. The row header lock status was updated to 02
Note that the delete did not actually remove the record from the block, it just updated the row flag. This means that even if a record has been deleted from an Oracle database table is possible to read the data for the deleted record directly from the block (until it is overwritten).
Block 150 Contains Object ID 73513
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971670
SCN Base :- 1073294
SCN Wrap :- 0
Sequence :- 02
Flag 01-NEW :- 06
CheckSum :- -6419
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73513
Cleanout SCN Base :- 1073292
Cleanout SCN Wrap :- 0
Spare 4 :- 00
Spare 5 :- 00
ITL Slots :- 2
UNKNOWN Byte :- 00
Flag 00-FREE :- 32
ITL TX Feeelist Slot :- 00
Next Block On Free List :- 20971664
ITLSlot :- 1
Undo Segment :- 5
Undo Segment Slot :- 33
Transaction Sequence :- 814
Undo Block Address :- 12583110
Undo Sequence :- 169
Undo Record Number :- 6
Spare 1 :- 0
Flag :- -32768
_ktbitun :- 0
Base :- 1073062
ITLSlot :- 2
Undo Segment :- 6
Undo Segment Slot :- 27
Transaction Sequence :- 797
Undo Block Address :- 12583068
Undo Sequence :- 174
Undo Record Number :- 43
Spare 1 :- 0
Flag :- 8193
_ktbitun :- 14
Base :- 1073294
Data Header start :- 100
Flags :- 00
Number of Tables :- 1
Number of Rows :- 2
Offset to Freespace Start :- -1
Offset to Freespace End :- 22
Available Space :- 8054
Available Space after Commit:- 8032
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8072(+100)
Row Offset 2 :- 8054(+100)
1 Row Header start :-8172
Flags :- 3C Deleted
Lock Status :- 02
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start :-8154
Flags :- 2C Table Data
Lock Status :- 00
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD
What happens in an Oracle Block during an update.
SQL> create table TEST_TABLE(id integer, attribute1 char(5), attribute2 varchar2(5)) tablespace test;
Table created.
SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST';
FILE_ID
----------
FILE_NAME
-----------------------------------------------------------------
5
D:\ORACLE\WIN64\DATAFILE\O1_MF_TEST_8LRBSQ59_.DBF
SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST
_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
73512 73512
SQL> insert into test_table values (1,'AAAAA','BBB');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test_table values (2,'CCCCC','DDDDD');
1 row created.
SQL> commit;
Commit complete.
SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segme
nt_name = 'TEST_TABLE';
FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
5 136 8 5
SQL> alter system checkpoint;
System altered.
The table we are working on for this test is stored in file 5 with extent starting at block 136 in blocks with DATA_OBJECT_ID 73512 . When we scan the file for blocks with data object ID 73512 we can see that the rows we are interested in are in block 139. Two ITL Slots have been created in the block.
The following block data was extracted using :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html
Block 139 Contains Object ID 73512
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971659
SCN Base :- 1072047
SCN Wrap :- 0
Sequence :- 02
Flag 01-NEW :- 06
CheckSum :- 17170
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73512
Cleanout SCN Base :- 1072042
Cleanout SCN Wrap :- 0
Spare 4 :- 00
Spare 5 :- 00
ITL Slots :- 2
UNKNOWN Byte :- 00
Flag 00-FREE :- 32
ITL TX Feeelist Slot :- 00
Next Block On Free List :- 20971656
ITLSlot :- 1
Undo Segment :- 10
Undo Segment Slot :- 29
Transaction Sequence :- 633
Undo Block Address :- 12583185
Undo Sequence :- 140
Undo Record Number :- 3
Spare 1 :- 0
Flag :- 8193
_ktbitun :- 0
Base :- 1072043
ITLSlot :- 2
Undo Segment :- 5
Undo Segment Slot :- 19
Transaction Sequence :- 814
Undo Block Address :- 12583108
Undo Sequence :- 169
Undo Record Number :- 33
Spare 1 :- 0
Flag :- 8193
_ktbitun :- 0
Base :- 1072047
Data Header start :- 100
Flags :- 00
Number of Tables :- 1
Number of Rows :- 2
Offset to Freespace Start :- -1
Offset to Freespace End :- 22
Available Space :- 8054
Available Space after Commit:- 8032
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8072(+100)
Row Offset 2 :- 8054(+100)
1 Row Header start :-8172
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start :-8154
Flags :- 2C Table Data
Lock Status :- 02
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD
Now we update one record in the table.
SQL> update test_table set attribute1 = 'EEEEE' where attribute1 = 'AAAAA';
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
After dumping the block we can see that :-
1. block SCNs were updated
2. block CheckSum was updated
3. ITL Slot 1 was used for the transaction
4. ITL Slot 2 flag was updated to -32768 (I guess indicating unused)
5. The Row lock status for the untouched row was cleared.
6. The data was updated.
Block 139 Contains Object ID 73512
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971659
SCN Base :- 1072247
SCN Wrap :- 0
Sequence :- 02
Flag 01-NEW :- 06
CheckSum :- -6509
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73512
Cleanout SCN Base :- 1072246
Cleanout SCN Wrap :- 0
Spare 4 :- 00
Spare 5 :- 00
ITL Slots :- 2
UNKNOWN Byte :- 00
Flag 00-FREE :- 32
ITL TX Feeelist Slot :- 00
Next Block On Free List :- 20971656
ITLSlot :- 1
Undo Segment :- 4
Undo Segment Slot :- 16
Transaction Sequence :- 596
Undo Block Address :- 12583572
Undo Sequence :- 147
Undo Record Number :- 55
Spare 1 :- 0
Flag :- 8193
_ktbitun :- 0
Base :- 1072247
ITLSlot :- 2
Undo Segment :- 5
Undo Segment Slot :- 19
Transaction Sequence :- 814
Undo Block Address :- 12583108
Undo Sequence :- 169
Undo Record Number :- 33
Spare 1 :- 0
Flag :- -32768
_ktbitun :- 0
Base :- 1072047
Data Header start :- 100
Flags :- 00
Number of Tables :- 1
Number of Rows :- 2
Offset to Freespace Start :- -1
Offset to Freespace End :- 22
Available Space :- 8054
Available Space after Commit:- 8032
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8072(+100)
Row Offset 2 :- 8054(+100)
1 Row Header start :-8172
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- EEEEE
Column 3 Bytes 3 Data :- BBB
2 Row Header start :-8154
Flags :- 2C Table Data
Lock Status :- 00
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD
Java program to extract records from Oracle Control Files.
It is a large Java program laid out in order of execution for easy reading and learning purposes. Yes, I know that a "real Java programmer" would do a better job, and there is lots of room for improvement, but it is good enough for learning about Oracle data structures.
Anyone is welcome to download it, modify it, and use it for any purpose, but I don't guarantee that it extracts all records available, or will work on all OS or DB versions. Use it at your own risk. Enjoy !
An example of the output can be found here :- http://blog.contractoracle.com/2013/02/oracle-control-file-physical-structure.html
# ControlScanCMD.java
import java.io.IOException;
import java.io.RandomAccessFile;
public class ControlScanCMD {
public static void main(String[] args) {
try {
RandomAccessFile raf = new RandomAccessFile("D:\\oracle\\WIN64\\CONTROLFILE\\O1_MF_8L5T35GK_.CTL", "r");
int BlockSize = 8192*2;
int BlockStart = 0;
int BlockNum;
int FileSize =(int)raf.length();
int NumBlocks = FileSize / BlockSize;
int NextRecordStart;
int FileRecordSize = 524;
int TablespaceRecordSize = 68;
int HeaderSize =18;
int TailSize = 4;
int RecordCount = 0;
int FileIdOffset = 4;
int TablespaceFirstChar = 9;
int NameOffset = 14;
int Pointer = 0;
byte MyByte = 0;
char MyChar = 0;
int FileID = 0;
System.out.println("Number of Blocks :- " + NumBlocks);
// get database name
BlockStart = BlockSize * 1;
System.out.println("Block :- 2");
System.out.print("Tablespace Name :- ");
raf.seek(BlockStart + HeaderSize + NameOffset);
for(int ct1 = 1; ct1 <= 8; ct1++)
{
MyByte = raf.readByte();
if (MyByte == 0)
{
System.out.print("");
//System.out.println(String.format("%02X ", raf.readByte()));
}
else if (32 < MyByte && MyByte <= 126)
{
//MyChar = ((char)Integer.parseInt(String.format("%02X", MyByte), 16));
MyChar = (char)MyByte;
System.out.print("" + MyChar);
//jTextAreaFile.append(" " + ct + "-" + String.format("%02X", MyByte) + " ");
}
else
{
System.out.print(" " + ct1 + "-" + String.format("%02X", MyByte) + " ");
}
}
System.out.println("");
System.out.println("");
// get file details
for(BlockNum = 31; BlockNum <= 32; BlockNum++)
{
BlockStart = BlockSize * BlockNum;
NextRecordStart = HeaderSize;
RecordCount = 0;
System.out.println("Block :- " + BlockNum);
raf.seek(BlockStart + NextRecordStart + FileIdOffset);
FileID = raf.readByte();
while (FileID != 0 && (NextRecordStart + FileRecordSize) < BlockSize)
{
RecordCount = RecordCount + 1;
raf.seek(BlockStart + NextRecordStart);
System.out.println("Record :- " + RecordCount);
System.out.println("Byte 1 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 2 :- " + String.format("%02X ", raf.readByte()));
System.out.println("File Type 3-LOG, 4-DATA, 7-TEMP :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 4 :- " + String.format("%02X ", raf.readByte()));
System.out.println("File ID :- " + raf.readByte());
System.out.println("Byte 6 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 7 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 8 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 9 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 10 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 11 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 12 :- " + String.format("%02X ", raf.readByte()));
System.out.print("File Name :- ");
for(int ct1 = 1; ct1 <= 512; ct1++)
{
MyByte = raf.readByte();
if (MyByte == 0)
{
System.out.print("");
//System.out.println(String.format("%02X ", raf.readByte()));
}
else if (32 < MyByte && MyByte <= 126)
{
//MyChar = ((char)Integer.parseInt(String.format("%02X", MyByte), 16));
MyChar = (char)MyByte;
System.out.print("" + MyChar);
//jTextAreaFile.append(" " + ct + "-" + String.format("%02X", MyByte) + " ");
}
else
{
System.out.print(" " + ct1 + "-" + String.format("%02X", MyByte) + " ");
}
}
System.out.println("");
System.out.println("");
NextRecordStart = NextRecordStart + FileRecordSize;
raf.seek(BlockStart + NextRecordStart + FileIdOffset);
FileID = raf.readByte();
}
// get tablespace names
for(BlockNum = 179; BlockNum <= 179; BlockNum++)
{
BlockStart = BlockSize * BlockNum;
NextRecordStart = HeaderSize;
RecordCount = 0;
System.out.println("Block :- " + BlockNum);
raf.seek(BlockStart + NextRecordStart + TablespaceFirstChar);
FileID = raf.readByte();
while (FileID != 0 && (NextRecordStart + TablespaceRecordSize) < BlockSize)
{
RecordCount = RecordCount + 1;
raf.seek(BlockStart + NextRecordStart);
System.out.println("Record :- " + RecordCount);
System.out.println("Byte 1 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 2 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Tablespace ID :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 4 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 5 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 6 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 7 :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 8 :- " + String.format("%02X ", raf.readByte()));
System.out.print("Tablespace Name :- ");
for(int ct1 = 1; ct1 <= 30; ct1++)
{
MyByte = raf.readByte();
if (MyByte == 0)
{
System.out.print("");
//System.out.println(String.format("%02X ", raf.readByte()));
}
else if (32 < MyByte && MyByte <= 126)
{
//MyChar = ((char)Integer.parseInt(String.format("%02X", MyByte), 16));
MyChar = (char)MyByte;
System.out.print("" + MyChar);
//jTextAreaFile.append(" " + ct + "-" + String.format("%02X", MyByte) + " ");
}
else
{
System.out.print(" " + ct1 + "-" + String.format("%02X", MyByte) + " ");
}
}
System.out.println("");
System.out.println("");
NextRecordStart = NextRecordStart + TablespaceRecordSize;
raf.seek(BlockStart + NextRecordStart + TablespaceFirstChar);
FileID = raf.readByte();
}
}
}
raf.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
Java program to extract data from Oracle datafile blocks.
Because it extracts directly from the block it can be used to extract data from corrupt files or blocks, truncated tables, and even extract deleted rows.
The program is written purely for learning, so I don't guarantee it will extract all records in a block, or that it can handle all data types. I am happy for anyone to copy the program or improve it, but I do not provide any guarantee for it. Use it at your own risk.
It is a large Java program laid out in order of execution for easy reading and learning purposes. Yes, I know that a "real Java programmer" would do a better job, and there is lots of room for improvement, but it is good enough for learning about Oracle data structures.
An example of the program output can be found here :- http://blog.contractoracle.com/2013/02/extracting-data-directly-from-oracle.html
# OraTabExport.java
import java.io.IOException;
import java.io.RandomAccessFile;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
public class OraTabExport {
public static void main(String[] args) {
try {
RandomAccessFile raf = new RandomAccessFile("D:\\oracle\\WIN64\\DATAFILE\\O1_MF_TEST_8LRBSQ59_.DBF", "r");
int BlockSize = 8192;
int ObjectID = 73520;
int StartByte = 0;
int ObjectIDLoc = 24;
int ITLSlotsLoc = 36;
int ITLSlots = 2;
int ITLSlot = 0;
int NumTablesLoc = 0;
int BlockObjectID = 0;
int FileSize =(int)raf.length();
int NumBlocks = FileSize / BlockSize;
int BlockNum = 0;
int DataHeaderStart = 0;
int TableDirectoryStart = 0;
int RowDirectoryStart =0;
int RowStart = 0;
int BlockHeaderSize = 20;
int TransactionHeaderSize = 24;
int DataHeaderSize = 14;
int TableDirectorySize = 4;
int NumTables = 0;
int TableNum = 0;
int NumTableRows = 0;
int TotalTableRows = 0;
int RowNumber = 0;
int NumColumns = 0;
int ColNum = 0;
int ColumnSize = 0;
int RowFlag = 0;
short RowOffset = 0;
int MinOffset = 0;
int MaxOffset = 0;
int OffsetCount = 0;
int ByteNum = 0;
Byte MyByte = 0;
char MyChar = 0;
for(BlockNum = 0; BlockNum <= NumBlocks -1; BlockNum++)
{
StartByte = BlockSize * BlockNum;
raf.seek(StartByte + ObjectIDLoc);
BlockObjectID = Integer.reverseBytes(raf.readInt());
raf.seek(StartByte + ITLSlotsLoc);
ITLSlots = raf.read();
if ( BlockObjectID == ObjectID)
{
raf.seek(StartByte);
System.out.println("\nBlock " + BlockNum + " Contains Object ID " + BlockObjectID);
// The first 20 bytes is the Block Header
System.out.println("Block Header start :- " + 0);
System.out.println(" Block Type 06-DATA :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Block Format :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare1 :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare2 :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Relative Block Address :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" SCN Base :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" SCN Wrap :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Sequence :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Flag 01-NEW :- " + String.format("%02X ", raf.readByte()));
System.out.println(" CheckSum :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Spare3 :- " + Short.reverseBytes(raf.readShort()));
// The next 24 bytes are Fixed Transaction Header
System.out.println("Transaction Header start :- " + BlockHeaderSize);
System.out.println(" Type 01-DATA 02-INDEX :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare 1 :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare 2 :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare 3 :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Object ID :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" Cleanout SCN Base :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" Cleanout SCN Wrap :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Spare 4 :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare 5 :- " + String.format("%02X ", raf.readByte()));
System.out.println(" ITL Slots :- " + raf.readByte());
System.out.println(" UNKNOWN Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Flag 00-FREE :- " + String.format("%02X ", raf.readByte()));
System.out.println(" ITL TX Feeelist Slot :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Next Block On Free List :- " + Integer.reverseBytes(raf.readInt()));
// Each ITL Slot has 24 bytes allocated.
for(ITLSlot = 1; ITLSlot <= ITLSlots; ITLSlot++)
{
System.out.println("ITLSlot :- " + ITLSlot);
System.out.println(" Undo Segment :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Undo Segment Slot :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Transaction Sequence :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" Undo Block Address :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" Undo Sequence :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Undo Record Number :- " + raf.readByte());
System.out.println(" Spare 1 :- " + raf.readByte());
System.out.println(" Flag :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" _ktbitun :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Base :- " + Integer.reverseBytes(raf.readInt()));
}
DataHeaderStart = BlockHeaderSize + TransactionHeaderSize + ITLSlots*TransactionHeaderSize + 8;
raf.seek(StartByte + DataHeaderStart);
// The Data header contains details of the number of tables, rows, and free space in the block.
System.out.println("Data Header start :- " + DataHeaderStart);
System.out.println(" Flags :- " + String.format("%02X ", raf.readByte()));
NumTables = raf.readByte();
System.out.println(" Number of Tables :- " + NumTables);
System.out.println(" Number of Rows :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Offset to Freespace Start :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Offset to Freespace End :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Available Space :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Available Space after Commit:- " + Short.reverseBytes(raf.readShort()));
TableDirectoryStart = DataHeaderStart + DataHeaderSize;
raf.seek(StartByte + TableDirectoryStart);
// The Table Directory has a list of tables in the block and row counts
System.out.println("Table Directory start :-" + TableDirectoryStart);
TotalTableRows = 0;
for(TableNum = 1; TableNum <= NumTables; TableNum++)
{
System.out.println(" Table :- " + TableNum);
System.out.println(" Offset :- " + Short.reverseBytes(raf.readShort()));
NumTableRows = Short.reverseBytes(raf.readShort());
TotalTableRows = TotalTableRows + NumTableRows;
System.out.println(" Number of Rows :- " + NumTableRows);
}
System.out.println(" Total Number of Rows :- " + TotalTableRows);
RowDirectoryStart = TableDirectoryStart + NumTables*TableDirectorySize;
raf.seek(StartByte + RowDirectoryStart);
// The Row Directory contains a list of rows in the block, with offsets to the first byte
System.out.println("Row Directory start :-" + RowDirectoryStart);
MinOffset = TotalTableRows*2;
MaxOffset = BlockSize - DataHeaderStart - 4;
RowOffset = 1;
OffsetCount = 0;
List RowOffsetList = new ArrayList();
while (RowOffset != 0 && OffsetCount < TotalTableRows)
{
RowOffset = Short.reverseBytes(raf.readShort());
if (RowOffset > MinOffset && RowOffset < MaxOffset)
{
OffsetCount = OffsetCount + 1;
System.out.println(" Row Offset " + OffsetCount + " :- " + RowOffset + "(+" + DataHeaderStart + ")");
RowOffsetList.add(RowOffset);
}
}
// Now go to each offset and get the row header which contains number of columns, row status etc
Iterator OffsetIterator = RowOffsetList.iterator();
RowNumber = 0;
while(OffsetIterator.hasNext())
{
RowOffset = (Short)OffsetIterator.next();
RowNumber = RowNumber + 1;
RowStart = DataHeaderStart + RowOffset;
raf.seek(StartByte + RowStart);
System.out.println(RowNumber + " Row Header start :-" + RowStart);
RowFlag = raf.readByte();
String RowFlagString = String.format("%02X ", RowFlag );
String RowFlagDecode = "";
// Translate the Row Flag to identify deleted rows, table data etc
switch (RowFlag) {
case 3: RowFlagDecode = "Junk ?";
break;
case 12: RowFlagDecode = "Chained ?";
break;
case 32: RowFlagDecode = "Chained ?";
break;
case 44: RowFlagDecode = "Table Data";
break;
case 60: RowFlagDecode = "Deleted";
break;
case 84: RowFlagDecode = "Cluster Key ?";
break;
case -84: RowFlagDecode = "Cluster Key ?";
break;
case 108: RowFlagDecode = "Cluster Data";
break;
case 124: RowFlagDecode = "Deleted";
break;
default: RowFlagDecode = "Invalid Flag";
break;
}
System.out.println(" Flags :- " + RowFlagString + " " + RowFlagDecode);
System.out.println(" Lock Status :- " + String.format("%02X ", raf.readByte()));
NumColumns = raf.readByte();
System.out.println(" Number of Columns :- " + NumColumns);
if (NumColumns == 0)
{
System.out.println("Integer :- " + Integer.reverseBytes(raf.readInt()));
System.out.println("Short :- " + Short.reverseBytes(raf.readShort()));
}
if (NumTables > 1)
{
System.out.println(" Cluster Byte :- " + String.format("%02X ", raf.readByte()));
}
if (RowFlag == 76)
{
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
}
if (RowFlag == 12)
{
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Integer :- " + Integer.reverseBytes(raf.readInt()));
System.out.println("Short :- " + Short.reverseBytes(raf.readShort()));
}
if (RowFlag != 3) {
for(ColNum = 1; ColNum <= NumColumns; ColNum++){
ColumnSize = raf.read();
if (ColumnSize == 255)
{
System.out.print("Column " + ColNum + " Bytes " + ColumnSize + " Data :- ");
System.out.println("NULL");
}
else if (ColumnSize == 254)
{
ColumnSize = Short.reverseBytes(raf.readShort());
System.out.print("Column " + ColNum + " Bytes " + ColumnSize + " Data :- ");
for(ByteNum = 0; ByteNum < ColumnSize; ByteNum++)
{
//System.out.print("" + String.format("%02X ", MyByte));
//MyByte = raf.readByte();
System.out.print("" + (char)raf.readByte());
}
System.out.println("");
}
else
{
System.out.print("Column " + ColNum + " Bytes " + ColumnSize + " Data :- ");
for(ByteNum = 0; ByteNum < ColumnSize; ByteNum++)
{
MyByte = raf.readByte();
//System.out.print("" + raf.readByte());
if (MyByte < 32 || MyByte > 126)
{
System.out.print("" + String.format("%02X ", MyByte));
}
else
{
MyChar = ((char)Integer.parseInt(String.format("%02X", MyByte), 16));
System.out.print("" + MyChar);
}
}
System.out.println("");
}
}
}
}
}
}
raf.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
Extracting data directly from Oracle datafiles using Java.
This allows me to extract any data I need direct from datafiles while bypassing audit and security at database level. Following is example output running the program to scan for object 10 which is a clustered table containing USER$ and extracting data including usernames and password hashes.
Being able to directly access data in blocks allows more detailed analysis and auditing, so I should be able to extract data for tables that have been truncated, extract deleted rows, or identify which blocks have changed since a specific SCN.
The source code can be found here :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html
The output still contains a bit of Hex data, so more work is needed to convert to proper char and date formats.
Block 209 Contains Object ID 10Block Header start :- 0 Block Type 06-DATA :- 06 Block Format :- A2 Spare1 :- 00 Spare2 :- 00 Relative Block Address :- 4194513 SCN Base :- 954865 SCN Wrap :- 0 Sequence :- 01 Flag 01-NEW :- 06 CheckSum :- 10511 Spare3 :- 0
Transaction Header start :- 20 Type 01-DATA 02-INDEX :- 01 Spare 1 :- 00 Spare 2 :- 00 Spare 3 :- 00 Object ID :- 10 Cleanout SCN Base :- 954861 Cleanout SCN Wrap :- 0 Spare 4 :- 00 Spare 5 :- 00 ITL Slots :- 2 UNKNOWN Byte :- 00 Flag 00-FREE :- 03 ITL TX Feeelist Slot :- 00 Next Block On Free List :- 0
Data Header start :- 92 Flags :- 00 Number of Tables :- 2 Number of Rows :- 43 Offset to Freespace Start :- -1 Offset to Freespace End :- 108 Available Space :- 4711 Available Space after Commit:- 5519
Table Directory start :-106 Table :- 1 Offset :- 0 Number of Rows :- 21 Table :- 2 Offset :- 21 Number of Rows :- 22 Total Number of Rows :- 43
Row Directory start :-114 Row Offset 1 :- 8074(92) Row Offset 2 :- 8005(92) Row Offset 3 :- 7829(92) Row Offset 4 :- 7740(92) Row Offset 5 :- 7650(92) Row Offset 6 :- 7559(92) Row Offset 7 :- 7473(92) Row Offset 8 :- 7294(92) Row Offset 9 :- 7192(92) Row Offset 10 :- 7089(92) Row Offset 11 :- 6987(92) Row Offset 12 :- 6809(92) Row Offset 13 :- 6709(92) Row Offset 14 :- 6609(92) Row Offset 15 :- 6504(92) Row Offset 16 :- 6412(92) Row Offset 17 :- 6220(92) Row Offset 18 :- 6116(92) Row Offset 19 :- 6021(92) Row Offset 20 :- 5842(92) Row Offset 21 :- 5733(92) Row Offset 22 :- 5951(92) Row Offset 23 :- 4882(92) Row Offset 24 :- 7762(92) Row Offset 25 :- 7672(92) Row Offset 26 :- 7581(92) Row Offset 27 :- 7495(92) Row Offset 28 :- 4711(92) Row Offset 29 :- 7214(92) Row Offset 30 :- 7111(92) Row Offset 31 :- 7009(92) Row Offset 32 :- 5391(92) Row Offset 33 :- 6731(92) Row Offset 34 :- 6631(92) Row Offset 35 :- 6526(92) Row Offset 36 :- 6434(92) Row Offset 37 :- 6242(92) Row Offset 38 :- 6138(92) Row Offset 39 :- 6043(92) Row Offset 40 :- 5864(92) Row Offset 41 :- 5755(92) Row Offset 42 :- 5647(92) Row Offset 43 :- 5562(92)
23 Row Header start :-4974 Flags :- 108 6C Cluster Data Lock Status :- 00 Number of Columns :- 22 Cluster Byte :- 01 Column 1 Bytes 3 Data :- SYSColumn 2 Bytes 2 Data :- C1 02 Column 3 Bytes 16 Data :- DCB748A5BC5390F2Column 4 Bytes 1 Data :- 80 Column 5 Bytes 2 Data :- C1 04 Column 6 Bytes 7 Data :- xn03 1E 0B 08 7Column 7 Bytes 7 Data :- xq02 13 0B 3'Column 8 Bytes 7 Data :- xn03 1E 0C 07 9Column 9 Bytes 7 Data :- xn03 1E 0C 07 9Column 10 Bytes 1 Data :- 80 Column 11 Bytes 255 Data :- NULLColumn 12 Bytes 2 Data :- C1 02 Column 13 Bytes 255 Data :- NULLColumn 14 Bytes 255 Data :- NULLColumn 15 Bytes 1 Data :- 80 Column 16 Bytes 1 Data :- 80 Column 17 Bytes 22 Data :- DEFAULT_CONSUMER_GROUPColumn 18 Bytes 255 Data :- NULLColumn 19 Bytes 1 Data :- 80 Column 20 Bytes 255 Data :- NULLColumn 21 Bytes 255 Data :- NULLColumn 22 Bytes 62 Data :-S:CDD630F4165A338BF851D4552897EB41EB6A9FCF7587B366260E0352A7C7
28 Row Header start :-4803 Flags :- 108 6C Cluster Data Lock Status :- 02 Number of Columns :- 22 Cluster Byte :- 05 Column 1 Bytes 6 Data :- SYSTEMColumn 2 Bytes 2 Data :- C1 02 Column 3 Bytes 16 Data :- EED9B65CCECDB2E9Column 4 Bytes 1 Data :- 80 Column 5 Bytes 2 Data :- C1 04 Column 6 Bytes 7 Data :- xn03 1E 0B 08 8Column 7 Bytes 7 Data :- xq02 13 0B 3'Column 8 Bytes 7 Data :- xn03 1E 0C 07 9Column 9 Bytes 7 Data :- xn03 1E 0C 07 9Column 10 Bytes 1 Data :- 80 Column 11 Bytes 255 Data :- NULLColumn 12 Bytes 2 Data :- C1 02 Column 13 Bytes 255 Data :- NULLColumn 14 Bytes 255 Data :- NULLColumn 15 Bytes 1 Data :- 80 Column 16 Bytes 1 Data :- 80 Column 17 Bytes 22 Data :- DEFAULT_CONSUMER_GROUPColumn 18 Bytes 255 Data :- NULLColumn 19 Bytes 1 Data :- 80 Column 20 Bytes 255 Data :- NULLColumn 21 Bytes 255 Data :- NULLColumn 22 Bytes 62 Data :- S:83FDD7C19ABAB64415514C28AD798DDAA3FE619B10B1AEA79E8463DC2566
If anyone wants more details on the Oracle block structure you can check :-http://orafaq.com/papers/dissassembling_the_data_block.pdfand http://www.v3rity.com/OracleForensicsDataBlock.pdf
Oracle Control File Physical Structure
The Java program I wrote to extract the data below can be found here :- http://blog.contractoracle.com/2013/02/java-program-to-extract-records-from.html
The basics :-
- The controlfile is composed of blocks that are 2X database block size.
- Each block has a header of 18 bytes.
- Each block has a tail of 4 bytes.
- Each block is duplexed within the datafile for redundancy. E.g Block 31 is duplexed to Block 32.
Number of Blocks :- 595Block :- 2Tablespace Name :- WIN64
File information starts in block 31 and is contained in records of 524 bytes.The first 12 bytes is record header information, and the last 512 bytes is the file name. Byte 3 indicates the file type (3=LOG, 4=DATA, 7=TEMPFILE) and byte 5 appears to be file ID (unique for each file type)
Following is an example of extracting file details from a Control File :-
Number of Blocks :- 595Block :- 31Record :- 1Byte 1 :- 00 Byte 2 :- 00 File Type 3-LOG, 4-DATA, 7-TEMP :- 03 Byte 4 :- 00 File ID :- 3Byte 6 :- 00 Byte 7 :- 00 Byte 8 :- 00 Byte 9 :- 00 Byte 10 :- 00 Byte 11 :- 00 Byte 12 :- 00 File Name :- D:\ORACLE\WIN64\ONLINELOG\O1_MF_3_8L5T3995_.LOG
Record :- 2Byte 1 :- 00 Byte 2 :- 00 File Type 3-LOG, 4-DATA, 7-TEMP :- 03 Byte 4 :- 00 File ID :- 2Byte 6 :- 00 Byte 7 :- 00 Byte 8 :- 00 Byte 9 :- 00 Byte 10 :- 00 Byte 11 :- 00 Byte 12 :- 00 File Name :- D:\ORACLE\WIN64\ONLINELOG\O1_MF_2_8L5T38G3_.LOG
Record :- 3Byte 1 :- 00 Byte 2 :- 00 File Type 3-LOG, 4-DATA, 7-TEMP :- 03 Byte 4 :- 00 File ID :- 1Byte 6 :- 00 Byte 7 :- 00 Byte 8 :- 00 Byte 9 :- 00 Byte 10 :- 00 Byte 11 :- 00 Byte 12 :- 00 File Name :- D:\ORACLE\WIN64\ONLINELOG\O1_MF_1_8L5T37VY_.LOG
Record :- 4Byte 1 :- 00 Byte 2 :- 00 File Type 3-LOG, 4-DATA, 7-TEMP :- 04 Byte 4 :- 00 File ID :- 4Byte 6 :- 00 Byte 7 :- 00 Byte 8 :- 00 Byte 9 :- 00 Byte 10 :- 00 Byte 11 :- 00 Byte 12 :- 00 File Name :- D:\ORACLE\WIN64\DATAFILE\O1_MF_USERS_8L5T0ZC4_.DBF
Record :- 5Byte 1 :- 00 Byte 2 :- 00 File Type 3-LOG, 4-DATA, 7-TEMP :- 04 Byte 4 :- 00 File ID :- 3Byte 6 :- 00 Byte 7 :- 00 Byte 8 :- 00 Byte 9 :- 00 Byte 10 :- 00 Byte 11 :- 00 Byte 12 :- 00 File Name :- D:\ORACLE\WIN64\DATAFILE\O1_MF_UNDOTBS1_8L5T0ZBF_.DBF
Record :- 6Byte 1 :- 00 Byte 2 :- 00 File Type 3-LOG, 4-DATA, 7-TEMP :- 04 Byte 4 :- 00 File ID :- 2Byte 6 :- 00 Byte 7 :- 00 Byte 8 :- 00 Byte 9 :- 00 Byte 10 :- 00 Byte 11 :- 00 Byte 12 :- 00 File Name :- D:\ORACLE\WIN64\DATAFILE\O1_MF_SYSAUX_8L5T0ZB4_.DBF
Record :- 7Byte 1 :- 00 Byte 2 :- 00 File Type 3-LOG, 4-DATA, 7-TEMP :- 04 Byte 4 :- 00 File ID :- 1Byte 6 :- 00 Byte 7 :- 00 Byte 8 :- 00 Byte 9 :- 00 Byte 10 :- 00 Byte 11 :- 00 Byte 12 :- 00 File Name :- D:\ORACLE\WIN64\DATAFILE\O1_MF_SYSTEM_8L5T0Z83_.DBF
Record :- 8Byte 1 :- 00 Byte 2 :- 00 File Type 3-LOG, 4-DATA, 7-TEMP :- 07 Byte 4 :- 00 File ID :- 1Byte 6 :- 00 Byte 7 :- 00 Byte 8 :- 00 Byte 9 :- 00 Byte 10 :- 00 Byte 11 :- 00 Byte 12 :- 00 File Name :- D:\ORACLE\WIN64\DATAFILE\O1_MF_TEMP_8L5T3MYY_.TMP
Tablespace information starts at block 179 and is contained in 68 byte records. The first 8 bytes of the record are header information, with byte 3 being TABLESPACE#.
Following is an example of tablespace information extract from a Control File.
Block :- 179Record :- 1Byte 1 :- 00 Byte 2 :- 00 Tablespace ID :- 00 Byte 4 :- 00 Byte 5 :- 00 Byte 6 :- 00 Byte 7 :- 06 Byte 8 :- 00 Tablespace Name :- SYSTEM
Record :- 2Byte 1 :- 00 Byte 2 :- 00 Tablespace ID :- 01 Byte 4 :- 00 Byte 5 :- 00 Byte 6 :- 00 Byte 7 :- 06 Byte 8 :- 00 Tablespace Name :- SYSAUX
Record :- 3Byte 1 :- 00 Byte 2 :- 00 Tablespace ID :- 02 Byte 4 :- 00 Byte 5 :- 00 Byte 6 :- 00 Byte 7 :- 08 Byte 8 :- 00 Tablespace Name :- UNDOTBS1
Record :- 4Byte 1 :- 00 Byte 2 :- 00 Tablespace ID :- 04 Byte 4 :- 00 Byte 5 :- 00 Byte 6 :- 00 Byte 7 :- 05 Byte 8 :- 00 Tablespace Name :- USERS
Record :- 5Byte 1 :- 00 Byte 2 :- 00 Tablespace ID :- 03 Byte 4 :- 00 Byte 5 :- 00 Byte 6 :- 00 Byte 7 :- 04 Byte 8 :- 00 Tablespace Name :- TEMP
OTN Yathra - past the 1/2 way mark
I'm going to cheat tremendously and link to Lucas Jellema's blog about the tour, starting with http://technology.amis.nl/2013/01/27/otn-yathra-2013-the-six-city-oracle-tour-of-india/ as the overview.
Shamelessly linking Lucas' map of the Yathra.
I arrived in Chandigarh, Punjab on February 10, and was met by Oracle ACE and friend Aman Sharma. The trip: left Edmonton on Friday evening, landed London Heathrow Saturday morning, left LHR Saturday evening, arrived Delhi Sunday morning, left Delhi Sunday afternoon and into Chandigarh early evening.
On Monday and Tuesday, much to my delight, Aman showed me his home country and local community. And I did some shopping.
Wednesday, headed back to Delhi by taxi, and prepared for the gathering and presentations.
On Friday the team gathered at the Country Inn by Carlson in Saket. ACE Director team members are
Murali Vallath - our host and RAC ACED from India
Lucas Jellema - Development ACED, including SQL and Java
Edward Roske - BI/Hyperion ACED, presenting on Hyperion, Essbase and BI
Raj Mattamal - Development ACED, specializing in APEX
Hans Forbrich - Infrastructure ACED, presenting on LDAP, Cloud Control and Linux
In each city we have been fortunate to have ACEs such as Aman Sharma join us to present
Saturday was the first presentation day at the FMDI (Fertilizer Marketing Development Institute) which has wonderful classroom facilities.
Sunday, a quick tour of Delhi, off to the airport to Mumbai (formerly Bombay), and settle into the Holiday Inn.
Monday, presented using the facilities of the Women's Technical Institute, and then by car off to Pune.
Since we arrived in Pune Monday evening, Tuesday was a touring day out to Mahabalshawar and the head of the Krishna river.
Wednesday we presented in Pune at the Oracle offices.
Thursday by plane to Bangalore, and
Friday presented in the Oracle offices in Bangalore. Lucas has a fantastic half-way write-up at http://technology.amis.nl/2013/02/21/otn-yathra-2013-spreading-the-story-of-oracle-across-india-half-time/
Raj and Edward left today to return home.
Tomorrow Lucas and I will head to Hyderabad and continue this tour.