Friday 19 September 2014

Dumpig Oracle Database



Database
Dump database block

Demo provided by Richard Foote, Canberra, Australia
CREATE TABLE bowie_stuff (
album  VARCHAR2(30),
year   NUMBER,
rating VARCHAR2(30));

INSERT INTO bowie_stuff VALUES ('Man Who Sold The World', 1970, 'Bloody Good!!');

INSERT INTO bowie_stuff VALUES ('Diamond Dogs', 1974 , 'Brilliant');

INSERT INTO bowie_stuff VALUES ('Outside', 1995, 'Underrated Masterpiece');
COMMIT;

ALTER SYSTEM CHECKPOINT;

SELECT *
FROM bowie_stuff;

SELECT album, dbms_rowid.rowid_to_absolute_fno(rowid, 'UWCLASS', 'BOWIE_STUFF') ABSOLUTE_FNO,
dbms_rowid.rowid_block_number(rowid) BLOCKNO, dbms_rowid.rowid_row_number(rowid) ROWNUMBER
FROM bowie_stuff
WHERE album LIKE '%Dogs%';

ALTER SYSTEM DUMP DATAFILE 14 BLOCK 20238;

Trace file C:\APP\ORACLE\diag\rdbms\orabase2\orabase2\trace\orabase2_ora_8284.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 4 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:3425M/8075M, Ph+PgF:11352M/16148M
Instance name: orabase2
Redo thread mounted by this instance: 1
Oracle process number: 46
Windows thread id: 8284, image: ORACLE.EXE (SHAD)


*** 2014-01-05 10:17:07.304
*** SESSION ID:(246.1839) 2014-01-05 10:17:07.304
*** CLIENT ID:() 2014-01-05 10:17:07.304
*** SERVICE NAME:(pdbdev) 2014-01-05 10:17:07.304
*** MODULE NAME:(SQL*Plus) 2014-01-05 10:17:07.304
*** ACTION NAME:() 2014-01-05 10:17:07.304
*** CONTAINER ID:(4) 2014-01-05 10:17:07.304

Start dump data blocks tsn: 4 file#:14 minblk 20238 maxblk 20238
Block dump from cache:
Dump of buffer cache at level 4 for pdb=4 tsn=4 rdba=58740494
BH (0x7ff15f85a98) file#: 14 rdba: 0x03804f0e (14/20238) class: 1 ba: 0x7ff153c6000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 29,28
  dbwrid: 0 obj: 93311 objn: 93311 tsn: [4/4] afn: 14 hint: f
  hash: [0x7ff6fff2b10,0x7ff91b40628] lru: [0x7ff15f85cc8,0x7ff15f85a48]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x7ff15f85cf0,0x7ff8010fa28] objaq: [0x7ff15f85d00,0x7ff8010fa18]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x0.626ef5 tch: 5
  flags: block_written_once
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 4 rdba: 0x03804f0e (14/20238)
scn: 0x0.626ef6 seq: 0x01 flg: 0x06 tail: 0x6ef60601
frmt: 0x02 chkval: 0x0fd5 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015B46000 to 0x0000000015B48000
015B46000 0000A206 03804F0E 00626EF6 06010000 [.....O...nb.....]
015B46010 00000FD5 00000001 00016C7F 00626EF5 [.........l...nb.]
015B46020 00000000 00320002 03804F00 00100001 [......2..O......]
015B46030 00001311 01400C03 002605CC 00002003 [......@...&.. ..]
015B46040 00626EF6 00000000 00000000 00000000 [.nb.............]
015B46050 00000000 00000000 00000000 00000000 [................]
015B46060 00000000 00030100 0018FFFF 1F101F28 [............(...]
015B46070 00001F10 1F6C0003 1F281F4E 00000000 [......l.N.(.....]
015B46080 00000000 00000000 00000000 00000000 [................]
        Repeat 495 times
015B47F80 00000000 00000000 00000000 0703012C [............,...]
015B47F90 7374754F 03656469 166014C2 65646E55 [Outside...`.Unde]
015B47FA0 74617272 4D206465 65747361 65697072 [rrated Masterpie]
015B47FB0 012C6563 69440C03 6E6F6D61 6F442064 [ce,...Diamond Do]
015B47FC0 C2037367 42094B14 6C6C6972 746E6169 [gs...K.Brilliant]
015B47FD0 1603012C 206E614D 206F6857 646C6F53 [,...Man Who Sold]
015B47FE0 65685420 726F5720 C203646C 420D4714 [ The World...G.B]
015B47FF0 646F6F6C 6F472079 2121646F 6EF60601 [loody Good!!...n]
Block header dump: 0x03804f0e
 Object id on Block? Y
 seg/obj: 0x16c7f csc: 0x00.626ef5 itc: 2 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x3804f00 ver: 0x01 opc: 0
     inc: 0 exflg: 0

 Itl           Xid                   Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.010.00001311   0x01400c03.05cc.26  --U-    3  fsc 0x0000.00626ef6
0x02   0x0000.000.00000000   0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x03804f0e
data_block_dump,data header at 0x15b46064
===============
tsiz: 0x1f98
hsiz: 0x18
pbl:  0x15b46064
      76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f28
avsp=0x1f10
tosp=0x1f10
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f6c
0x14:pri[1]     offs=0x1f4e
0x16:pri[2]     offs=0x1f28
block_row_dump:
tab 0, row 0, @0x1f6c
tl: 44 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [22]
 4d 61 6e 20 57 68 6f 20 53 6f 6c 64 20 54 68 65 20 57 6f 72 6c 64
col 1: [ 3] c2 14 47
col 2: [13] 42 6c 6f 6f 64 79 20 47 6f 6f 64 21 21
tab 0, row 1, @0x1f4e
tl: 30 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [12] 44 69 61 6d 6f 6e 64 20 44 6f 67 73
col 1: [ 3] c2 14 4b
col 2: [ 9] 42 72 69 6c 6c 69 61 6e 74
tab 0, row 2, @0x1f28
tl: 38 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 7] 4f 75 74 73 69 64 65
col 1: [ 3] c2 14 60
col 2: [22]
 55 6e 64 65 72 72 61 74 65 64 20 4d 61 73 74 65 72 70 69 65 63 65
end_of_block_dump
End dump data blocks tsn: 4 file#: 14 minblk 20238 maxblk 20238
File Headers
ALTER SYSTEM SET EVENTS 'immediate trace name file_hdrs level 1';

-- does not appear to generate a file
Redlo Log File Headers
ALTER SYSTEM SET EVENTS 'immediate trace name redohdr level 1';

-- does not appear to generate a file
Control File

Level 1: file header
Level 2: database and checkpoint records
Level 3: circular reuse record types
Level 10: dump contents
col value format a50

SELECT value
FROM gv$parameter
WHERE name LIKE 'back%dump%dest';

ALTER SESSION SET EVENTS 'immediate trace name controlf level 3';

[Click here to view trace file (116K)]

Instance
PGA (Process Global Area)
ORADEBUG DUMP HEAPDUMP 1

ALTER SESSION SET EVENTS 'immediate trace name global_area level 1';
SGA (System Global Area)
ORADEBUG DUMP HEAPDUMP 2

ALTER SESSION SET EVENTS 'immediate trace name global_area level 2';
PGA + UGA
ORADEBUG DUMP HEAPDUMP 3

ALTER SESSION SET EVENTS 'immediate trace name global_area level 3';
UGA (User Global Area)
ORADEBUG DUMP HEAPDUMP 4

ALTER SESSION SET EVENTS 'immediate trace name global_area level 4';
PGA + UGA
ORADEBUG DUMP HEAPDUMP 5

ALTER SESSION SET EVENTS 'immediate trace name global_area level 5';
SGA + UGA
ORADEBUG DUMP HEAPDUMP 6

ALTER SESSION SET EVENTS 'immediate trace name global_area level 6';
PGA + SGA + UGA
ORADEBUG DUMP HEAPDUMP 7

ALTER SESSION SET EVENTS 'immediate trace name global_area level 7';
System State
ALTER SESSION SET EVENTS 'immediate trace name SYSTEMSTATE level 10';

ALTER SESSION SET EVENTS 'immediate trace name SYSTEMSTATE_CACHE OFF';

[Click here to view trace file (1.5M)]
Process State
ALTER SESSION SET EVENTS 'immediate trace name PROCESSSTATE level 10';

ALTER SESSION SET EVENTS 'immediate trace name PROCESSSTATE OFF';

[Click here to view trace file (347K)]
Library Cache
ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 10';

ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE OFF';

[Click here to view trace file (12K)]

Segments
Datafile Block
ALTER SYSTEM DUMP DATAFILE 6 BLOCK 21229;
Dumping an index tree including branch block headers, leaf block headers, and leaf block contents
col object_name format a30

SELECT object_name, object_id
FROM user_objects;

ALTER SESSION SET EVENTS 'immediate trace name treedump level 54220';
Alternative index dump
ORADEBUG DUMP TREEDUMP 54220;
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)