Sundaramurthy Blog

September 27, 2010

RMAN Buffer size

Filed under: RMAN Internals — sundar5 @ 11:34 pm

RMAN has two packages to controle entire backup and restore activities .  It is  avaialble under $ORACLE_HOME/rdbms/admin directory

recover.bsq

Package names are:

1. dbms_backup_restore

2. dbms_rcvman

Defaults: MAXOPENFILES=8, FILESPERSET=64

Number of Files Read Per Channel Buffer Size
Multiplexing 4 Each buffer    = 1MB, total buffer size for channel is up to 16MB.
Number of buffers per file will depend on the number of files.
4 >Multiplexing 8 Each buffer = 512KB, total buffer size for channel is up to 16MB.
Numbers of buffers per file will depend on number of files.
Multiplexing > 8                Each file will have 4 512KB buffers.

The number and size of the buffers used for each RMAN job can be found in the

V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO views

Oracle Note:

If restoring to ASM you may find setting the following two parameters useful:

_ BACKUP_KSFQ_BUFCNT
_BACKUP_KSFQ_BUFSZ

The default is 16 for _BACKUP_KSFQ_BUFCNT and 1 MB for _BACKUP_KSFQ_BUFSZ.
These defaults are acceptable for ASM diskgroups with fewer than 32 disks and when ASM stripe size is 1 MB otherwise to set:

_BACKUP_KSFQ_BUFCNT to the number of disks in the ASM disk group and _BACKUP_KSFQ_BUFSZ to the size of the ASM stripe.

The number and size of input buffers can be changed by using the undocumented parameters _backup_ksfq_bufcnt and _backup_ksfq_bufsz. When using these parameters to increase backup I/O throughput, it is recommended that _backup_ksfq_bufsz be first set to the stripe size of the files being read and to test the effect that setting has on performance.

Controlefile section:

RMAN CONFIGURATION RECORDS
(size = 1108, compat size = 1108, section max = 50, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 283, numrecs = 50)

July 3, 2010

dmidecode (Linux)

Filed under: Linux — sundar5 @ 11:25 pm

dmidecode:

dmidecode can give you a quick   hardware configuration info  such as

bios
system
baseboard
chassis
processor
memory
cache
connector
slot
etc….

DMI TYPE is used individually or in a comma separated list provide you with targeted information

Type Information
0 BIOS
1 System
2 Base Board
3 Chassis
4 Processor
5 Memory Controller
6 Memory Module
7 Cache
8 Port Connector
9 System Slots
10 On Board Devices
11 OEM Strings
12 System Configuration Options
13 BIOS Language
14 Group Associations
15 System Event Log
16 Physical Memory Array
17 Memory Device
18 32-bit Memory Error
19 Memory Array Mapped Address
20 Memory Device Mapped Address
21 Built-in Pointing Device
22 Portable Battery
23 System Reset
24 Hardware Security
25 System Power Controls
26 Voltage Probe
27 Cooling Device
28 Temperature Probe
29 Electrical Current Probe
30 Out-of-band Remote Access
31 Boot Integrity Services
32 System Boot
33 64-bit Memory Error
34 Management Device
35 Management Device Component
36 Management Device Threshold Data
37 Memory Channel
38 IPMI Device
39 Power Supply

Note: Either you can use type number or information string

Example : Processor

dmidecode -t Processor
dmidecode -t 4

1. Without argument

root# dmidecode -t
dmidecode: option requires an argument — t
Type number or keyword expected
Valid type keywords are:
bios
system
baseboard
chassis
processor
memory
cache
connector
slot

2. processor info

root# dmidecode -t processor
# dmidecode 2.9
SMBIOS 2.5 present.

Handle 0x0004, DMI type 4, 40 bytes
Processor Information
Socket Designation: CPU 0
Type: Central Processor
Family: Xeon MP
Manufacturer: Intel
ID: 7A 06 01 00 FF FB EB BF

3. processor info using type#(4)

root# dmidecode -t 4
# dmidecode 2.9
SMBIOS 2.5 present.

Handle 0x0004, DMI type 4, 40 bytes
Processor Information
Socket Designation: CPU 0
Type: Central Processor
Family: Xeon MP
Manufacturer: Intel
ID: 7A 06 01 00 FF FB EB BF
Signature: Type 0, Family 6, Model 23, Stepping 10
Flags:
….
…..
Version: Intel(R) Xeon(R) CPU           L5420  @ 2.50GHz
Voltage: 1.2 V
External Clock: 1333 MHz
Max Speed: 3400 MHz
Current Speed: 2500 MHz
Status: Populated, Enabled
Upgrade: Socket LGA771
L1 Cache Handle: 0x0005
L2 Cache Handle: 0x0006
L3 Cache Handle: 0x0007
Serial Number: To Be Filled By O.E.M.
Asset Tag: To Be Filled By O.E.M.
Part Number: To Be Filled By O.E.M.
Core Count: 4
Core Enabled: 4
Thread Count: 4
Characteristics:
64-bit capable

4. Chassis info

root# dmidecode -t 3

# dmidecode 2.9
SMBIOS 2.5 present.

Handle 0x0003, DMI type 3, 21 bytes
Chassis Information
Manufacturer: HP
Type: Rack Mount Chassis
Lock: Not Present
Version: 1.0
Serial Number: MXQ91800WX
Asset Tag: 529993
Boot-up State: Safe
Power Supply State: Safe
Thermal State: Safe
Security Status: None
OEM Information: 0x00000000
Height: Unspecified
Number Of Power Cords: 1
Contained Elements: 0

5.  Memory info

root# dmidecode -t Memory
# dmidecode 2.9
SMBIOS 2.5 present.

Handle 0x002C, DMI type 16, 15 bytes
Physical Memory Array
Location: System Board Or Motherboard
Use: System Memory
Error Correction Type: Multi-bit ECC
Maximum Capacity: 16 GB
Error Information Handle: Not Provided
Number Of Devices: 6

Handle 0x002E, DMI type 17, 27 bytes
Memory Device
Array Handle: 0x002C
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 4096 MB
Form Factor: DIMM
Set: None
Locator: DIMM1 A
Bank Locator: BANK1
Type: DDR2
Type Detail: Other
Speed: 667 MHz (1.5 ns)
Manufacturer: aD00000000000000
Serial Number: 00006018
Asset Tag:
Part Number: 48594D503135315037324350342D59352020

…..

biosdecode:

biosdecode is a command line utility to parses the BIOS memory and prints information about all structures

root # biosdecode
# biosdecode 2.9
BIOS32 Service Directory present.
Revision: 0
Calling Interface Address: 0x000F0010
PCI Interrupt Routing 1.0 present.
Router ID: 00:1f.0
Exclusive IRQs: None
…
Slot Entry 1: ID 00:00, on-board
Slot Entry 2: ID 00:02, on-board
Slot Entry 3: ID 00:03, on-board
Slot Entry 4: ID 00:04, on-board
Slot Entry 5: ID 00:05, on-board
Slot Entry 6: ID 00:06, on-board
…..
Slot Entry 14: ID 05:00, on-board
Slot Entry 15: ID 06:00, on-board
Slot Entry 16: ID 07:00, slot number 2
Slot Entry 17: ID 08:04, slot number 1
Slot Entry 18: ID 01:00, slot number 4
PNP BIOS 1.0 present.
Event Notification: Not Supported
Real Mode 16-bit Code Address: F000:6DE2
….
16-bit Protected Mode Data Address: 0x000F0000
ACPI 2.0 present.
OEM Identifier: HP
RSD Table 32-bit Address: 0xDFFA0000
XSD Table 64-bit Address: 0x00000000DFFA0100
SMBIOS 2.5 present.
Structure Table Length: 3061 bytes
Structure Table Address: 0x000FC690
Number Of Structures: 65
Maximum Structure Size: 222 bytes

Type Information
0 BIOS
1 System
2 Base Board
3 Chassis
4 Processor
5 Memory Controller
6 Memory Module
7 Cache
8 Port Connector
9 System Slots
10 On Board Devices
11 OEM Strings
12 System Configuration Options
13 BIOS Language
14 Group Associations
15 System Event Log
16 Physical Memory Array
17 Memory Device
18 32-bit Memory Error
19 Memory Array Mapped Address
20 Memory Device Mapped Address
21 Built-in Pointing Device
22 Portable Battery
23 System Reset
24 Hardware Security
25 System Power Controls
26 Voltage Probe
27 Cooling Device
28 Temperature Probe
29 Electrical Current Probe
30 Out-of-band Remote Access
31 Boot Integrity Services
32 System Boot
33 64-bit Memory Error
34 Management Device
35 Management Device Component
36 Management Device Threshold Data
37 Memory Channel
38 IPMI Device
39 Power Supply

June 18, 2010

Oracle Data Block Internals (DBA 4,31)

Filed under: Oracle internals — sundar5 @ 2:22 pm

When you create a table object on a tablespace, oracle creates the segment header block, BMB (Bit Map Block) block and data blocks in the data file.  Below is the data block internal structure  where the input data gets inserted.

For example, I have created the  following table structure and inserted the record.  See the record in the data block.

SQL> CREATE TABLE T1 (N1 number, desc1 varchar2(10))  TABLESPACE POCTABLESPACE;

SQL> SELECT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE FROM DBA_OBJECTS

WHERE OBJECT_NAME=’T1′ AND OWNER=’SYS’

OBJECT_NAM  OBJECT_ID OBJECT_TYPE

———- ———- ————-

T1               9858 TABLE

SQL> INSERT INTO T1 VALUES (100,’TESTDATA’);

1 row created.
SQL> COMMIT;
SQL> select * from t1;
N1 DESC1
---------- ----------
100 TESTDATA

Let see how oracle internally store the data in hex format:

SQL> select dump(100,16),dump(‘TESTDATA’,16) FROM DUAL;

—————– ————————————-

Typ=2 Len=2: c2,2 Typ=96 Len=8: 54,45,53,54,44,41,54,41

Transaction data Block Internal Structure:

Block header
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x0100001f
ub4 bas_kcbh @8 0x00054f17
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x6fc8
ub2 spare3_kcbh @18 0x0000
Transaction Layer
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00002682
ub4 ktbbhod1 @24 0x00002682
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x00054f17
ub2 kscnwrp @32 0x0000
b2 ktbbhict @36 7938
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x01000009
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x000a
ub2 kxidslt @46 0x000f
ub4 kxidsqn @48 0x000000ef
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00800318
ub2 kubaseq @56 0x0041
ub1 kubarec @58 0x22
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
b2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x00054ec3
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0000
ub2 kxidslt @70 0x0000
ub4 kxidsqn @72 0x00000000
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00000000
ub2 kubaseq @80 0x0000
ub1 kubarec @82 0x00
ub2 ktbitflg @84 0x0000 (NONE)
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
Table Directory
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
b1 kdbhntab @101 1
b2 kdbhnrow @102 1
Free List
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 20
sb2 kdbhfseo @108 8073
b2 kdbhavsp @110 8053
b2 kdbhtosp @112 8053
Row Directory
sb2 kdbr[0] @118 8073
rowdata[0]
———-
ub1 rowdata[0] @8173 0x2c
Row
rowdata[0] @8173
———-
flag@8173: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8174: 0x00
cols@8175:    2
col    0[2] @8176: 100
col    1[8] @8179: TESTDATA

Transaction Data block:

Legend:

June 15, 2010

Data file first block structure (DBA 4,1)

Filed under: Oracle Block,Oracle internals — sundar5 @ 6:32 pm

When you create any tablespace in the oracle environment, it reserves the first block of a data file for an  internal purpose. All information related to checkpoint, recovery status, SCN etc. are kept in the first block.  When you open the database, oracle always validates  the control file information with the first block of data files. Below is the structure of the first block.

Data file internal structure dba 4,1
UNIT       NAME OFFSET VALUE
struct kcvfh, 676 bytes @0
struct kcvfhbfh, 20 bytes @0
ub1 type_kcbh @0 0x0b
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x01000001
ub4 bas_kcbh @8 0x00000000
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x8412
ub2 spare3_kcbh @18 0x0000
struct kcvfhhdr, 76 bytes @20
ub4 kccfhswv @20 0x00000000
ub4 kccfhcvn @24 0x0a200000
ub4 kccfhdbi @28 0x43f4c8dc
text kccfhdbn[0] @32 E
text kccfhdbn[1] @33 T
text kccfhdbn[2] @34 L
text kccfhdbn[3] @35 T
text kccfhdbn[4] @36 E
text kccfhdbn[5] @37 S
text kccfhdbn[6] @38 T
text kccfhdbn[7] @39
ub4 kccfhcsq @40 0x00000192
ub4 kccfhfsz @44 0x00006400
s_blkz kccfhbsz @48 0x00
ub2 kccfhfno @52 0x0004
ub2 kccfhtyp @54 0x0003
ub4 kccfhacid @56 0x00000000
ub4 kccfhcks @60 0x00000000
text kccfhtag[0] @64
text kccfhtag[1] @65
text kccfhtag[2] @66
text kccfhtag[3] @67
text kccfhtag[4] @68
text kccfhtag[5] @69
text kccfhtag[6] @70
text kccfhtag[7] @71
text kccfhtag[8] @72
text kccfhtag[9] @73
text kccfhtag[10] @74
text kccfhtag[11] @75
text kccfhtag[12] @76
text kccfhtag[13] @77
text kccfhtag[14] @78
text kccfhtag[15] @79
text kccfhtag[16] @80
text kccfhtag[17] @81
text kccfhtag[18] @82
text kccfhtag[19] @83
text kccfhtag[20] @84
text kccfhtag[21] @85
text kccfhtag[22] @86
text kccfhtag[23] @87
text kccfhtag[24] @88
text kccfhtag[25] @89
text kccfhtag[26] @90
text kccfhtag[27] @91
text kccfhtag[28] @92
text kccfhtag[29] @93
text kccfhtag[30] @94
text kccfhtag[31] @95
ub4 kcvfhrdb @96 0x00000000
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0x0002a4da
ub2 kscnwrp @104 0x0000
ub4 kcvfhcrt @108 0x2ae967a3
ub4 kcvfhrlc @112 0x2ae9645c
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x00000001
ub2 kscnwrp @120 0x0000
ub4 kcvfhbti @124 0x00000000
struct kcvfhbsc, 8 bytes @128
ub4 kscnbas @128 0x00000000
ub2 kscnwrp @132 0x0000
ub2 kcvfhbth @136 0x0000
ub2 kcvfhsta @138 0x0004 (KCVFHOFZ)
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000cb354
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2b0044ee
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000a
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
ub4 kcvfhcpc @140 0x00000010
ub4 kcvfhrts @144 0x00000000
ub4 kcvfhccc @148 0x0000000f
struct kcvfhbcp, 36 bytes @152
struct kcvcpscn, 8 bytes @152
ub4 kscnbas @152 0x00000000
ub2 kscnwrp @156 0x0000
ub4 kcvcptim @160 0x00000000
ub2 kcvcpthr @164 0x0000
union u, 12 bytes @168
struct kcvcprba, 12 bytes @168
ub4 kcrbaseq @168 0x00000000
ub4 kcrbabno @172 0x00000000
ub2 kcrbabof @176 0x0000
ub1 kcvcpetb[0] @180 0x00
ub1 kcvcpetb[1] @181 0x00
ub1 kcvcpetb[2] @182 0x00
ub1 kcvcpetb[3] @183 0x00
ub1 kcvcpetb[4] @184 0x00
ub1 kcvcpetb[5] @185 0x00
ub1 kcvcpetb[6] @186 0x00
ub1 kcvcpetb[7] @187 0x00
ub4 kcvfhbhz @312 0x00000000
struct kcvfhxcd, 16 bytes @316
ub4 space_kcvmxcd[0] @316 0x00000000
ub4 space_kcvmxcd[1] @320 0x00000000
ub4 space_kcvmxcd[2] @324 0x00000000
ub4 space_kcvmxcd[3] @328 0x00000000
word kcvfhtsn @332 4
ub2 kcvfhtln @336 0x000d
text kcvfhtnm[0] @338 P
text kcvfhtnm[1] @339 O
text kcvfhtnm[2] @340 C
text kcvfhtnm[3] @341 T
text kcvfhtnm[4] @342 A
text kcvfhtnm[5] @343 B
text kcvfhtnm[6] @344 L
text kcvfhtnm[7] @345 E
text kcvfhtnm[8] @346 S
text kcvfhtnm[9] @347 P
text kcvfhtnm[10] @348 A
text kcvfhtnm[11] @349 C
text kcvfhtnm[12] @350 E
text kcvfhtnm[13] @351
text kcvfhtnm[14] @352
text kcvfhtnm[15] @353
text kcvfhtnm[16] @354
text kcvfhtnm[17] @355
text kcvfhtnm[18] @356
text kcvfhtnm[19] @357
text kcvfhtnm[20] @358
text kcvfhtnm[21] @359
text kcvfhtnm[22] @360
text kcvfhtnm[23] @361
text kcvfhtnm[24] @362
text kcvfhtnm[25] @363
text kcvfhtnm[26] @364
text kcvfhtnm[27] @365
text kcvfhtnm[28] @366
text kcvfhtnm[29] @367
ub4 kcvfhrfn @368 0x00000004
struct kcvfhrfs, 8 bytes @372
ub4 kscnbas @372 0x00000000
ub2 kscnwrp @376 0x0000
ub4 kcvfhrft @380 0x00000000
struct kcvfhafs, 8 bytes @384
ub4 kscnbas @384 0x00000000
ub2 kscnwrp @388 0x0000
ub4 kcvfhbbc @392 0x00000000
ub4 kcvfhncb @396 0x00000000
ub4 kcvfhmcb @400 0x00000000
ub4 kcvfhlcb @404 0x00000000
ub4 kcvfhbcs @408 0x00000000
ub2 kcvfhofb @412 0x0000
ub2 kcvfhnfb @414 0x0000
ub4 kcvfhprc @416 0x00000000
struct kcvfhprs, 8 bytes @420
ub4 kscnbas @420 0x00000000
ub2 kscnwrp @424 0x0000
struct kcvfhprfs, 8 bytes @428
ub4 kscnbas @428 0x00000000
ub2 kscnwrp @432 0x0000
ub4 kcvfhtrt @444 0x00000000

June 9, 2010

How to calculate oracle row size using block dump?

Filed under: Oracle Block,Oracle internals — sundar5 @ 7:39 pm

Row bytes calculation:

data block internal structure

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes                  @0
ub1 type_kcbh                              @0
ub1 frmt_kcbh                              @1
ub1 spare1_kcbh                         @2
ub1 spare2_kcbh                         @3
ub4 rdba_kcbh                           @4
ub4 bas_kcbh                            @8
ub2 wrp_kcbh                            @12
ub1 seq_kcbh                            @14
ub1 flg_kcbh                            @15
ub2 chkval_kcbh                         @16
ub2 spare3_kcbh                         @18
struct ktbbh, 72 bytes                  @20
ub1 ktbbhtyp                            @20
union ktbbhsid, 4 bytes                 @24
struct ktbbhcsc, 8 bytes                @28
b2 ktbbhict                             @36
ub1 ktbbhflg                            @38
ub1 ktbbhfsl                            @39
ub4 ktbbhfnx                            @40
struct ktbbhitl[2], 48 bytes            @44

ITL STRUCTURE
————–
struct ktbbhitl[0], 24 bytes                @44
struct ktbitxid, 8 bytes                 @44
ub2 kxidusn                           @44       0x0001
ub2 kxidslt                           @46       0x000f
ub4 kxidsqn                           @48       0x000000da
struct ktbituba, 8 bytes                 @52
ub4 kubadba                           @52       0x008008d9
ub2 kubaseq                           @56       0x0083
ub1 kubarec                           @58       0x12
ub2 ktbitflg                             @60       0x2003 (KTBFUPB)
union _ktbitun, 2 bytes                  @62
b2 _ktbitfsc                          @62       0
ub2 _ktbitwrp                         @62       0x0000
ub4 ktbitbas                             @64       0x000bd7c1
struct ktbbhitl[1], 24 bytes                @68
struct ktbitxid, 8 bytes                 @68
ub2 kxidusn                           @68       0x0000
ub2 kxidslt                           @70       0x0000
ub4 kxidsqn                           @72       0x00000000
struct ktbituba, 8 bytes                 @76
ub4 kubadba                           @76       0x00000000
ub2 kubaseq                           @80       0x0000
ub1 kubarec                           @82       0x00
ub2 ktbitflg                             @84       0x0000 (NONE)
union _ktbitun, 2 bytes                  @86
b2 _ktbitfsc                          @86       0
ub2 _ktbitwrp                         @86       0x0000
ub4 ktbitbas                             @88       0x00000000
struct kdbh, 14 bytes                   @100
ub1 kdbhflag                            @100
b1 kdbhntab                             @101
b2 kdbhnrow                             @102
sb2 kdbhfrre                            @104
sb2 kdbhfsbo                            @106
sb2 kdbhfseo                            @108
b2 kdbhavsp                             @110
b2 kdbhtosp                             @112
struct kdbt[1], 4 bytes                 @114
b2 kdbtoffs                             @114
b2 kdbtnrow                             @116
sb2 kdbr[389]                           @118
ub1 freespace[1052]                     @896
ub1 rowdata[6240]                       @1948
ub4 tailchk                             @8188

row directory
kdbr
sb2 kdbr[0]                              @118      4107
sb2 kdbr[1]                              @120      4119
sb2 kdbr[2]                              @122      4133

row data:
rowdata[2285]                               @4233

————-

flag@4233: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4234: 0x00

cols@4235:    2

col    0[2] @4236: 28

col    1[4] @4239: CON$

rowdata[2296]                               @4244

————-

flag@4244: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4245: 0x00

cols@4246:    2

col    0[2] @4247: 15

col    1[5] @4250: UNDO$

rowdata[2308]                               @4256

————-

flag@4256: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4257: 0x00

cols@4258:    2

col    0[2] @4259: 29

col    1[7] @4262: C_COBJ#

3 bytes for row header
1 bytes for each colume size
3 bytes for column size, if colume value  is more than 254 bytes(xfe xxxx)
x bytes for column data
1 bytes if column value is null

dump the block using  oracle command and locate the rows in the bottom of the block

Example 1

tab 0, row 0, @0x1f88
tl: 16 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 2] c2 02
col 1: [ 9] 54 45 53 54 44 41 54 41 31

16 = 3 + 1 + 2 +1 + 9

Example 2

tab 0, row 0, @0x1f89
tl: 15 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 1] 80
col 1: [ 9] 54 45 53 54 44 41 54 41 30

15 = 3+1 + 1 + 1 + 9

Example 3

tab 0, row 1, @0x1f78
tl: 17 fb: –H-FL– lb: 0x1 cc: 7
col 0: [ 2] 31 31
col 1: *NULL*
col 2: *NULL*
col 3: *NULL*
col 4: *NULL*
col 5: *NULL*
col 6: [ 5] 46 49 52 53 54

17 =  3 +1 + 2 + 1 +1 + 1 + 1 +1+ 1+5

Example 4

tab 0, row 4, @0x1f4c
tl: 6 fb: –H-FL– lb: 0x2 cc: 1
col 0: [ 2] 34 34

6 = 3+1+2

Example 5

column size is more than 254 bytes

tab 0, row 0, @0x1e63
tl: 309 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c1 0b
col  1: [300]
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78

309 = 3 + 1 + 2 +3 + 300

Hex dump

79797979 7979792c 000202c1 0bfe2c01 l yyyyyyy,…Á.þ,.
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 0206d607 l xxxxxxxxxxxx..Ö.

02c1 0bfe2c01

02 bytes first column size
c1 0b = first column value
fe=  Indicator above 254 bytes column size (second column more than 254 bytes)
012c = 300 bytes second column size

How to recover deleted rows

Filed under: Oracle Block,Oracle internals — sundar5 @ 3:59 am

Caution:  Below example only for education purpose.  It should not be used any real production environment

1. Create tablespace

CREATE TABLESPACE deleterow   DATAFILE ‘/mnt/poc/ETLTEST/deleterow.dbf’ SIZE 5M   SEGMENT SPACE MANAGEMENT AUTO;

2.   SQL> SELECT TABLESPACE_NAME,FILE_ID,BYTES,BLOCKS,FILE_NAME FROM DBA_DATA_FILES;

TABLESPACE_NAME    FILE_ID      BYTES     BLOCKS FILE_NAME
--------------- ---------- ---------- ---------- ---------------------------------
DELETEROW                9    5242880        640 /mnt/poc/ETLTEST/deleterow.dbf

3.SQL> SELECT * FROM DBA_FREE_SPACE;

TABLESPACE_NAME    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
DELETEROW                9          9    5177344        632            9

4. SQL> CREATE TABLE T10 (N1 number, desc1 varchar2(10)) TABLESPACE DELETEROW;

Table created.

5. SQL> SELECT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE FROM DBA_OBJECTS
 WHERE OBJECT_NAME='T10' AND OWNER='SYS'

OBJECT_NAM  OBJECT_ID OBJECT_TYPE
---------- ---------- ------------
T10             10576 TABLE

6. SQL> SELECT * FROM DBA_FREE_SPACE;

TABLESPACE_NAME    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
DELETEROW                9         17    5111808        624            9

7.SQL> SELECT SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS
 FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE 'T10' AND OWNER ='SYS'
 /
SEGMENT_NA HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
---------- ----------- ------------ ---------- ---------- ----------
T10                  9           11      65536          8          1

8.SQL> select * from t10;

no rows selected

9.SQL> insert into t10 values (100,'TESTDATA1');

1 row created.

SQL> insert into t10 values (200,'TESTDATA2');

1 row created.

SQL> insert into t10 values (300,'TESTDATA2');


1 row created.

SQL> insert into t10 values (400,'TESTDATA4');

1 row created.

SQL> insert into t10 values (500,'TESTDATA5');

1 row created.

SQL> COMMIT;

Commit complete.

10. SQL>
 1  select ROWID,DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
 2  DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
 3  DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",
 4  DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW",N1,DESC1
 5* from T10
ROWID                  OBJECT       FILE      BLOCK        ROW         N1 DESC1
------------------ ---------- ---------- ---------- ---------- ---------- ----------
AAAClQAAJAAAAAPAAA      10576          9         15          0        100 TESTDATA1
AAAClQAAJAAAAAPAAB      10576          9         15          1        200 TESTDATA2
AAAClQAAJAAAAAPAAC      10576          9         15          2        300 TESTDATA2
AAAClQAAJAAAAAPAAD      10576          9         15          3        400 TESTDATA4
AAAClQAAJAAAAAPAAE      10576          9         15          4        500 TESTDATA5

11. SELECT N1,DUMP(N1,16) DUMP_N1,DESC1,DUMP(DESC1,16) DUMP_DESC1 FROM T10
 N1 DUMP_N1              DESC1        DUMP_DESC1
----- -------------------- ------------ ----------------------------------------
 100 Typ=2 Len=2: c2,2    TESTDATA1    Typ=1 Len=9: 54,45,53,54,44,41,54,41,31
 200 Typ=2 Len=2: c2,3    TESTDATA2    Typ=1 Len=9: 54,45,53,54,44,41,54,41,32
 300 Typ=2 Len=2: c2,4    TESTDATA2    Typ=1 Len=9: 54,45,53,54,44,41,54,41,32
 400 Typ=2 Len=2: c2,5    TESTDATA4    Typ=1 Len=9: 54,45,53,54,44,41,54,41,34
 500 Typ=2 Len=2: c2,6    TESTDATA5    Typ=1 Len=9: 54,45,53,54,44,41,54,41,35

12. dump

buffer tsn: 9 rdba: 0x0240000f (9/15)
scn: 0x0000.00090d89 seq: 0x02 flg: 0x02 tail: 0x0d890602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

Block header dump:  0x0240000f
 Object id on Block? Y
 seg/obj: 0x2950  csc: 0x00.8ff2e  itc: 2  flg: E  typ: 1 - DATA
 brn: 0  bdba: 0x2400009 ver: 0x01 opc: 0
 inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.02d.000000a5  0x00800659.005b.0f  C---    0  scn 0x0000.0008fece
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0x6820864
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x06820864
bdba: 0x0240000f
 76543210
flag=--------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f48
avsp=0x1f2c
tosp=0x1f2c
0xe:pti[0]      nrow=5  offs=0
0x12:pri[0]     offs=0x1f88
0x14:pri[1]     offs=0x1f78
0x16:pri[2]     offs=0x1f68
0x18:pri[3]     offs=0x1f58
0x1a:pri[4]     offs=0x1f48
block_row_dump:
tab 0, row 0, @0x1f88
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 02
col  1: [ 9]  54 45 53 54 44 41 54 41 31
tab 0, row 1, @0x1f78
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 03
col  1: [ 9]  54 45 53 54 44 41 54 41 32
tab 0, row 2, @0x1f68
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 04
col  1: [ 9]  54 45 53 54 44 41 54 41 32
tab 0, row 3, @0x1f58
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 05
col  1: [ 9]  54 45 53 54 44 41 54 41 34
tab 0, row 4, @0x1f48
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 06
col  1: [ 9]  54 45 53 54 44 41 54 41 35
end_of_block_dump

13.02c11302 c1080600 4002a100 060cc000 l .Á..Á...@.¡...À.
   10001800 10003100 02000100 03000200 l ......1.........
   fd000000 fb000000 00000000 c3028000 l ý...û.......Ã...
   0b010e00 00009e00 02010000 2c000202 l ............,...
   c2060954 45535444 41544135 2c000202 l Â..TESTDATA5,...
   c2050954 45535444 41544134 2c000202 l Â..TESTDATA4,...
   c2040954 45535444 41544132 2c000202 l Â..TESTDATA2,...
   c2030954 45535444 41544132 2c000202 l Â..TESTDATA2,...
   c2020954 45535444 41544131 01062eff l Â..TESTDATA1....

14.SQL> select * from t10;

 N1 DESC1
---------- ----------
 100 TESTDATA1
 200 TESTDATA2
 300 TESTDATA2
 400 TESTDATA4
 500 TESTDATA5

15. SQL> delete t10 where n1=300;

1 row deleted.

SQL>  select * from t10;

 N1 DESC1
---------- ----------
 100 TESTDATA1
 200 TESTDATA2
 400 TESTDATA4
 500 TESTDATA5

16.
buffer tsn: 9 rdba: 0x0240000f (9/15)
scn: 0x0000.00090d89 seq: 0x02 flg: 0x02 tail: 0x0d890602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump:  0x0240000f
Object id on Block? Y
seg/obj: 0x2950  csc: 0x00.8ff2e  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x2400009 ver: 0x01 opc: 0
inc: 0  exflg: 0
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.02d.000000a5  0x00800659.005b.0f  C—    0  scn 0x0000.0008fece
0x02   0x000a.020.0000014e  0x008005f9.0073.2a  –U-    1  fsc 0x000e.00090d89
data_block_dump,data header at 0x6820864
data_block_dump,data header at 0x6820864
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x06820864
bdba: 0x0240000f
76543210
flag=——–
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f48
avsp=0x1f2c
tosp=0x1f3c
0xe:pti[0]      nrow=5  offs=0
0x12:pri[0]     offs=0x1f88
0x14:pri[1]     offs=0x1f78
0x16:pri[2]     offs=0x1f68
0x18:pri[3]     offs=0x1f58
0x1a:pri[4]     offs=0x1f48
block_row_dump:
tab 0, row 0, @0x1f88
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 02
col  1: [ 9]  54 45 53 54 44 41 54 41 31
tab 0, row 1, @0x1f78
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 03
col  1: [ 9]  54 45 53 54 44 41 54 41 32
tab 0, row 2, @0x1f68
tl: 2 fb: –HDFL– lb: 0x2
tab 0, row 3, @0x1f58
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 05
col  1: [ 9]  54 45 53 54 44 41 54 41 34
tab 0, row 4, @0x1f48
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 06
col  1: [ 9]  54 45 53 54 44 41 54 41 35
end_of_block_dump
17. hex dump
 10001800 10003100 02000100 03000200 l ......1.........
 fd000000 fb000000 00000000 c3028000 l ý...û.......Ã...
 0b010e00 00009e00 02010000 2c000202 l ............,...
 c2060954 45535444 41544135 2c000202 l Â..TESTDATA5,...
 c2050954 45535444 41544134 3c020202 l Â..TESTDATA4<...
 c2040954 45535444 41544132 2c000202 l Â..TESTDATA2,...
 c2030954 45535444 41544132 2c000202 l Â..TESTDATA2,...
 c2020954 45535444 41544131 0206890d l Â..TESTDATA1....

18.  using hex editor , change the 0ffset 8140 to  2c

    tl: 2 fb: --HDFL-- lb: 0x2

oracle row header flag value changed  2c to 3c while deleting the row (–HDFL)

Flag byte
– – – – – – –  Continues data piece from previous row
H: Head of row piece
K: Cluster key
C: Cluster table member
D: Deleted row
F: First data piece,
L: Last data piece
P: First column continues from previous row
N: Last column continues

19

buffer tsn: 9 rdba: 0x0240000f (9/15)
scn: 0x0000.00090d89 seq: 0x02 flg: 0x06 tail: 0x0d890602
frmt: 0x02 chkval: 0xe269 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

 006822770 0213C102 000608C1 00A10240 00C00C06  [........@.......]
 006822780 00180010 00310010 00010002 00020003  [......1.........]
 006822790 000000FD 000000FB 00000000 008002C3  [................]
 0068227A0 000E010B 009E0000 00000102 0202002C  [............,...]
 0068227B0 540906C2 44545345 35415441 0202002C  [...TESTDATA5,...]
 0068227C0 540905C2 44545345 34415441 0202022C  [...TESTDATA4,...]
 0068227D0 540904C2 44545345 32415441 0202002C  [...TESTDATA2,...]
 0068227E0 540903C2 44545345 32415441 0202002C  [...TESTDATA2,...]
 0068227F0 540902C2 44545345 31415441 0D890602  [...TESTDATA1....]

Block header dump:  0x0240000f
Object id on Block? Y
seg/obj: 0x2950  csc: 0x00.8ff2e  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x2400009 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.02d.000000a5  0x00800659.005b.0f  C—    0  scn 0x0000.0008fece
0x02   0x000a.020.0000014e  0x008005f9.0073.2a  –U-    1  fsc 0x000e.00090d89

data_block_dump,data header at 0x6820864
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x06820864
bdba: 0x0240000f
76543210
flag=——–
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f48
avsp=0x1f2c
tosp=0x1f3c
0xe:pti[0]      nrow=5  offs=0
0x12:pri[0]     offs=0x1f88
0x14:pri[1]     offs=0x1f78
0x16:pri[2]     offs=0x1f68
0x18:pri[3]     offs=0x1f58
0x1a:pri[4]     offs=0x1f48
block_row_dump:
tab 0, row 0, @0x1f88
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 02
col  1: [ 9]  54 45 53 54 44 41 54 41 31
tab 0, row 1, @0x1f78
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 03
col  1: [ 9]  54 45 53 54 44 41 54 41 32
tab 0, row 2, @0x1f68
tl: 16 fb: –H-FL– lb: 0x2  cc: 2
col  0: [ 2]  c2 04
col  1: [ 9]  54 45 53 54 44 41 54 41 32
tab 0, row 3, @0x1f58
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 05
col  1: [ 9]  54 45 53 54 44 41 54 41 34
tab 0, row 4, @0x1f48
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 06
col  1: [ 9]  54 45 53 54 44 41 54 41 35
end_of_block_dump

20.

fd000000 fb000000 00000000 c3028000 l ý…û…….Ã…
0b010e00 00009e00 02010000 2c000202 l …………,…
c2060954 45535444 41544135 2c000202 l Â..TESTDATA5,…
c2050954 45535444 41544134 2c020202 l Â..TESTDATA4,…
c2040954 45535444 41544132 2c000202 l Â..TESTDATA2,…
c2030954 45535444 41544132 2c000202 l Â..TESTDATA2,…
c2020954 45535444 41544131 0206890d l Â..TESTDATA1….

21.

SQL> l
1  SELECT ROWID,DBMS_ROWID.ROWID_OBJECT(ROWID) “OBJECT”,
2  DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) “FILE”,
3  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) “BLOCK”,
4  DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) “ROW”,N1,DESC1
5* FROM T10
SQL> /

ROWID                  OBJECT       FILE      BLOCK        ROW         N1 DESC1
—————— ———- ———- ———- ———- ———- ———-
AAAClQAAJAAAAAPAAA      10576          9         15          0        100 TESTDATA1
AAAClQAAJAAAAAPAAB      10576          9         15          1        200 TESTDATA2
AAAClQAAJAAAAAPAAC      10576          9         15          2        300 TESTDATA2
AAAClQAAJAAAAAPAAD      10576          9         15          3        400 TESTDATA4
AAAClQAAJAAAAAPAAE      10576          9         15          4        500 TESTDATA5

SQL> select * from t10;

N1 DESC1
———- ———-
100 TESTDATA1
200 TESTDATA2
300 TESTDATA2
400 TESTDATA4
500 TESTDATA5

June 8, 2010

How to findout corrupted object from oracle error

Filed under: Oracle Block,Oracle internals — sundar5 @ 9:09 pm

From alertlog file.

——————-

Example

ORA-01578: ORACLE data block corrupted (file # 9, block # 15)

ORA-01110: data file 9: ‘/mnt/poc/ETLTEST/deleterow.dbf’

ORA-26040: Data block was loaded using the NOLOGGING option

AFN :  file 9: Absolute FILE NUMBER

SQL>SELECT tablespace_name, segment_type, owner, segment_name,FILE_ID,BLOCK_ID,

BLOCKS  FROM dba_extents WHERE file_id = 9 and 15 between block_id

AND block_id + blocks – 1

/

TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME  FILE_ID BLOCK_ID  BLOCKS

————— ———— —– ————- ——- ——–  ——

DELETEROW       TABLE        SYS   T10                 9        9       8

or

SQL> set long 64000

SQL> select dbms_metadata.get_ddl(segment_type, segment_name, owner)

FROM dba_extents WHERE file_id=9 AND 15 BETWEEN block_id AND block_id + blocks -1;

DBMS_METADATA.GET_DDL(SEGMENT_TYPE,SEGMENT_NAME,OWNER)

——————————————————————————–

CREATE TABLE “SYS”.”T10″

(    “N1” NUMBER,

“DESC1” VARCHAR2(10)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE “DELETEROW”

June 7, 2010

Locally Managed Tablespace internals

Filed under: Tablespace internals — sundar5 @ 3:03 pm

May 6, 2010

How to convert data block address to file number and block number

Filed under: Oracle Block,Oracle internals — sundar5 @ 10:33 pm

Because of a  host  crash,  I  had a corruption issue on my undo tablespace  in my database .
The alert log was showing the following error.

I wanted to find out the object that had caused the issue

SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
SMON: about to recover undo segment 212
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktbsdp1], [], [], [], [], [], [], []

Here is the steps
Note: some time alert log show object id also

1. Find out the segment in our case 212

Select usn,name from v$rollname where usn=212;
USN NAME

———- ——————————

212 _SYSSMU212$
2. segement Tabelspace was UNDOTBS3
3. alter system dump undo header “_SYSSMU212$” ;

********************************************************************************
Undo Segment:  _SYSSMU212$ (212)
********************************************************************************
Extent Control Header
—————————————————————–
Extent Header:: spare1: 0      spare2: 0      #extents: 114    #blocks: 57359
last map  0x00000000  #maps: 0      offset: 4080
Highwater::  0x0301a93f  ext#: 112    blk#: 950    ext size: 1024
#blocks in seg. hdr’s freelists: 0
#blocks below: 0
mapblk  0x00000000  offset: 112
Unlocked
Map Header:: next  0x00000000  #extents: 114  obj#: 0      flag: 0x40000000
Extent Map
—————————————————————–
0x03000542  length: 7
0x030000d9  length: 8
0x03000c09  length: 128
0x03005a89  length: 128
0x03005189  length: 128

index  state cflags  wrap#    uel         scn            dba parent-xid    nub     stmt_num    cmt
————————————————————————————————
0x00    9    0x00  0x193ae  0x0015  0x08ff.296efb8e  0x03006e62  0x0000.000.00000000  0x00000001   0x00000000  1273026159
0x01    9    0x00  0x196fd  0x000f  0x08ff.296efb91  0x03006e62  0x0000.000.00000000  0x00000001   0x00000000  1273026159
0x29   10    0x90  0x19715  0x0071  0x08ff.296efae3 0x0300c7f8 0x0000.000.00000000  0x00005b3b   0x00000000  0

4. undo data block

SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE( TO_NUMBER(‘0300c7f8‘, ‘XXXXXXXX’) ) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK( TO_NUMBER(‘0300c7f8‘, ‘XXXXXXXX’) ) BLOCK#
from dual;
2    3
FILE#     BLOCK#
———- ———-
12      51192

or we can use binary to finout the file and block number

0x0300c7f8

0=0000

3=0011

0=0000

0=0000

c=1100

7=0111

f=1111

8=1000

0000 0011 0000 0000 1100 0111 1111 1000

0000 0011 00   000000 11000111 1111 1000

0000 0011 00 =12

00 0000 1100 0111 1111 1000 = 51192

5. alter system dump datafile 12 block 51192;

* Rec #0x29 slt: 0x29  objn: 5462030(0x0053580e)  objd: 5462030 tblspc: 423(0x000001a7)
*       Layer:  11 (Row)   opc: 1   rci 0x28
undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0300c7f8.08a7.28
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0xee44d2d2  hdba: 0xee444e0c
itli: 2  ispac: 0  maxfr: 8098
tabn: 0 slot: 22(0x16) flag: 0x2c lock: 2 ckix: 0
ncol: 23 nnew: 10 size: 0
Vector content:
col  3: [ 5]  19 11 18 1f f
col  4: [ 9]  34 69 72 35 33 74 6f 72 79
col  5: [14]  3e 65 77 73 20 61 6e 64 20 4d 65 64 69 61
col  6: [ 5]  32 6c 6f 67 73
col  7: [19]  33 6f 6c 6c 61 72 6f 72 61 77 79 76 75 70 72 7c 7f 77 73
col  8: [ 9]  34 68 65 20 53 70 61 72 6b
col  9: [19]  54 79 73 78 71 72 64 20 53 74 61 35 36 36 31 33 38 35 72
col 10: [32]
54 4f 40 43 4c 45 64 40 40 49 43 40 48 45 4d 41 4e 4b 40 54 4f 40 4e 61 70
20 44 69 76 49 4e 65
col 11: *NULL*
col 12: *NULL*

7. select owner,object_name from dba_objects where object_id=5462030

OWNER                          OBJECT_NAME
—————————— ——————–
POC                            DIMTEST

April 29, 2010

Importing oracle trusted certificate into oracle wallets

Filed under: Oracle commands — sundar5 @ 10:10 pm

I am assuming user is created trusted certificate and hand over to you. As a oracle dba
you have to import the trusted certificate in the oracle database server.

How to import user trusted certificate into oracle wallets

1. Create wallet

Syntax: orapki wallet create -wallet <wallet_location>

$orapki wallet create -wallet ‘/home/oracle/admin/WALLETS’
Enter password:
Enter password again:

2. copy/ftp the user trusted certificate to database server temp location

/tmp/testwallet_ssl.cer

3. To add a trusted certificate to an Oracle wallet:

Syntax:  orapki wallet add -wallet <wallet_location> -trusted_cert -cert <certificate_location>

$orapki wallet add -wallet ‘/home/oracle/admin/WALLETS’ -trusted_cert -cert ‘/tmp/testwallet_ssl.cer’

4. To view an Oracle wallet:
Syntax:  orapki wallet display -wallet <wallet_location>

$orapki wallet display -wallet ‘/home/oracle/product/WALLETS/oracle’

Next Page »

Blog at WordPress.com.