oracle--dump块信息操作

原创

kingle_work 2022-01-20 17:29:25 博主文章分类：oracle 数据库 ©著作权

©著作权归作者所有：来自51CTO博客作者kingle_work的原创作品，请联系作者获取转载授权，否则将追究法律责任

01, 查看表

SQL> select * from tab where TNAME='T';

TNAME                                                                 TABTYPE
-------------------------------------------------------------------------------------------------------------------------------- ------
T                                                                 TABLE

02,数据块位置定位

SQL> select SEGMENT_NAME,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where SEGMENT_NAME='T';

SEGMENT_NAME    TABLESPACE_NAME            HEADER_FILE HEADER_BLOCK
--------------- ------------------------------ ----------- ------------
T        SYSTEM                     1     103800

SQL> select owner from dba_extents where SEGMENT_NAME='T'; 

OWNER
--------------------------------------------------------------------------------------------------------------------------------
SYS

SQL> select a.file_id,a.block_id,a.blocks,b.name from dba_extents a,v$datafile b where a.file_id=b.file# and a.owner='SYS' and a.segment_name='T';

   FILE_ID   BLOCK_ID      BLOCKS NAME 
---------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------

     1     103800           8    /orcl/app/oracle/oradata/orcl/system01.dbf

03,dump 数据块

SQL> alter system dump datafile 1 block 103800;

System altered.

--查看dump位置

SQL> show parameters dump;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
background_core_dump             string     partial
background_dump_dest             string     /orcl/app/oracle/product/12.1.
                         0/db_1/rdbms/log
core_dump_dest                 string     /orcl/app/oracle/diag/rdbms/no
                         de12c01/orcl/cdump
max_dump_file_size             string     unlimited
shadow_core_dump             string     partial
user_dump_dest                 string     /orcl/app/oracle/product/12.1.
                         0/db_1/rdbms/log


--查看位置号

SQL> select p.spid from v$session s,v$process p where s.paddr=p.addr and s.sid in (select userenv('sid') from dual);

SPID
------------------------
30291

--查看位置

SQL> select    '!vi ' || d.VALUE  || '/'
       || LOWER (RTRIM (i.INSTANCE, CHR (0)))
       || '_ora_'       || p.spid   || '.trc' trace_file_name
  FROM (select p.spid FROM v$mystat m, v$session s, v$process p
         WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
       (select t.INSTANCE  FROM v$thread t, v$parameter v WHERE v.NAME = 'thread'
           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
       (select value from v$parameter where name = 'user_dump_dest') d
/
TRACE_FILE_NAME
-----------------------------------------------------------------------------------------------------
!vi /orcl/app/oracle/product/12.1.0/db_1/rdbms/log/orcl_ora_30291.trc

04,寻找

　　理论上上面操作位置成立的但实际上,进去找不到,有点你难受

　　万能的查找方法,对这个数据的操作日志都全在了

[root@node12c01 log]# find / -name "*30291*"
/proc/30291
/proc/30291/task/30291
/orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_30291.trc
/orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_30291.trm
/orcl/app/oracle/admin/orcl/adump/orcl_ora_30291_20190416033909817727143795.aud

查看的内容:

[root@node12c01 adump]# ll /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_30291.trc
-rw-r----- 1 oracle oinstall 4356 Apr 16 03:45 /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_30291.trc
[root@node12c01 adump]# more /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_30291.trc
Trace file /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_30291.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME:    /orcl/app/oracle/product/12.1.0/db_1
System name:    Linux
Node name:    node12c01
Release:    3.10.0-693.el7.x86_64
Version:    #1 SMP Tue Aug 22 21:09:27 UTC 2017
Machine:    x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 36
Unix process pid: 30291, image: oracle@node12c01 (TNS V1-V3)


*** 2019-04-16T03:45:59.833889-04:00 (CDB$ROOT(1))
*** SESSION ID:(31.13469) 2019-04-16T03:45:59.833923-04:00
*** CLIENT ID:() 2019-04-16T03:45:59.833928-04:00
*** SERVICE NAME:(SYS$USERS) 2019-04-16T03:45:59.833933-04:00
*** MODULE NAME:(sqlplus@node12c01 (TNS V1-V3)) 2019-04-16T03:45:59.833937-04:00
*** ACTION NAME:() 2019-04-16T03:45:59.833941-04:00
*** CLIENT DRIVER:(SQL*PLUS) 2019-04-16T03:45:59.833945-04:00
*** CONTAINER ID:(1) 2019-04-16T03:45:59.833949-04:00

Start dump data blocks tsn: 0 file#:1 minblk 103800 maxblk 103800
Block dump from cache:
Dump of buffer cache at level 4 for pdb=1 tsn=0 rdba=4298104
BH (0xcfff2998) file#: 1 rdba: 0x00419578 (1/103800) class: 4 ba: 0xcfeca000
  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 0 obj: 75220 objn: 75220 tsn: [1/0] afn: 1 hint: f
  hash: [0x7a4dee48,0x7a4dee48] lru: [0xe6fa20b8,0xe6fa19d8]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x91105108,0x7ffdc260] objaq: [0x911050f8,0x7ffdc270]
  st: XCURRENT md: NULL fpin: 'ktewh25: kteinicnt' fscn: 0x3b0593 tch: 23
  flags: block_written_once
  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x4ea6cf] HSUB: [238]
  Printing buffer operation history (latest change first):
  cnt: 1
  01. sid:06 L192:kcbbic2:bic:FBD     02. sid:06 L191:kcbbic2:bic:FBW
  03. sid:06 L602:bic1_int:bis:FWC    04. sid:06 L822:bic1_int:ent:rtn
  05. sid:06 L832:oswmqbg1:clr:WRT    06. sid:06 L930:kubc:sw:mq
  07. sid:06 L913:bxsv:sw:objq        08. sid:06 L608:bxsv:bis:FBW
  09. sid:06 L607:bxsv:bis:FFW        10. sid:12 L464:chg1_mn:bic:FMS
  11. sid:12 L778:chg1_mn:bis:FMS     12. sid:12 L353:gcur:set:MEXCL
  13. sid:12 L353:gcur:set:MEXCL      14. sid:12 L464:chg1_mn:bic:FMS
  15. sid:12 L778:chg1_mn:bis:FMS     16. sid:12 L353:gcur:set:MEXCL
Block dump from disk:
buffer tsn: 0 rdba: 0x00419578 (1/103800)
scn: 0x4ea6cf seq: 0x02 flg: 0x04 tail: 0xa6cf1002
frmt: 0x02 chkval: 0x5692 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F12ACAA4000 to 0x00007F12ACAA6000
7F12ACAA4000 0000A210 00419578 004EA6CF 04020000  [....x.A...N.....]
7F12ACAA4010 00005692 00000000 00000000 00000000  [.V..............]
7F12ACAA4020 00000000 00000001 00000007 00001020  [............ ...]
7F12ACAA4030 00000000 00000003 00000007 0041957C  [............|.A.]
7F12ACAA4040 00000000 00000000 00000001 00000003  [................]
7F12ACAA4050 00000000 00000000 00000000 00000001  [................]
7F12ACAA4060 00000000 000125D4 40000000 00419579  [.....%.....@y.A.]
7F12ACAA4070 00000007 00000000 00000000 00000000  [................]
7F12ACAA4080 00000000 00000000 00000000 00000000  [................]
        Repeat 250 times
7F12ACAA5030 00000000 00010000 00010001 00000001  [................]
7F12ACAA5040 00000000 00000003 0041957B 0041957B  [........{.A.{.A.]
7F12ACAA5050 00000000 00000000 00000000 00000000  [................]
        Repeat 249 times
7F12ACAA5FF0 00000000 00000000 00000000 A6CF1002  [................]
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 7
                  last map  0x00000000  #maps: 0      offset: 4128
      Highwater::  0x0041957c  ext#: 0      blk#: 3      ext size: 7
  #blocks in seg. hdr's freelists: 1
  #blocks below: 3
  mapblk  0x00000000  offset: 0
                   Unlocked
     Map Header:: next  0x00000000  #extents: 1    obj#: 75220  flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x00419579  length: 7

  nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 3
  SEG LST:: flg: USED   lhd: 0x0041957b ltl: 0x0041957b
End dump data blocks tsn: 0 file#: 1 minblk 103800 maxblk 103800