Oracle | Dumping Blocks, File Header, Control Files, and Poos

Dumping Oracle Version 12.2

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

This isn't what you were thinking ... but you have to admit it is a great name. Why would anyone "dump" the best commercial database product on the planet?

Well to create trace files of course ... so we shall. And this page will become an accumulation of tips-and-tricks on various techniques for doing so.

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;