Oracle 后台进程初探

每一个Oracle版本都有其特定的后台进程，目前研究的版本为 引用SQLgt; select * from v$version; BANNER ---------------------

每一个Oracle版本都有其特定的后台进程，目前研究的版本为

引用
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

通过关联x$ksbdd，x$ksbdp，x$ksmfsv可以查到当前数据版本支持的后台进程数量限制，进程作用及进程在SGA中的结构体名字（KSMFSNAM字段）

引用
SQL> COL KSBDDIDN FOR A10
SQL> COL KSMFSNAM FOR A10
SQL>
    SELECT ksbdd.ksbddidn, ksmfsv.ksmfsnam, ksbdd.ksbdddsc
  2      FROM x$ksbdd ksbdd, x$ksbdp ksbdp, x$ksmfsv ksmfsv
  3      WHERE ksbdd.indx = ksbdp.indx
  4      AND ksbdp.addr = ksmfsv.ksmfsadr
  5      ORDER BY ksbdd.ksbddidn;

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
ARB0       kfgbr0_    ASM Rebalance 0
ARB1       kfgbr1_    ASM Rebalance 1
ARB2       kfgbr2_    ASM Rebalance 2
ARB3       kfgbr3_    ASM Rebalance 3
ARB4       kfgbr4_    ASM Rebalance 4
ARB5       kfgbr5_    ASM Rebalance 5
ARB6       kfgbr6_    ASM Rebalance 6
ARB7       kfgbr7_    ASM Rebalance 7
ARB8       kfgbr8_    ASM Rebalance 8
ARB9       kfgbr9_    ASM Rebalance 9
ARBA       kfgbrA_    ASM Rebalance 10

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
ARC0       kcrrd0_    Archival Process 0
ARC1       kcrrd1_    Archival Process 1
ARC2       kcrrd2_    Archival Process 2
ARC3       kcrrd3_    Archival Process 3
ARC4       kcrrd4_    Archival Process 4
ARC5       kcrrd5_    Archival Process 5
ARC6       kcrrd6_    Archival Process 6
ARC7       kcrrd7_    Archival Process 7
ARC8       kcrrd8_    Archival Process 8
ARC9       kcrrd9_    Archival Process 9
ARCa       kcrrdxa_   Archival Process 10

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
ARCb       kcrrdxb_   Archival Process 11
ARCc       kcrrdxc_   Archival Process 12
ARCd       kcrrdxd_   Archival Process 13
ARCe       kcrrdxe_   Archival Process 14
ARCf       kcrrdxf_   Archival Process 15
ARCg       kcrrdxg_   Archival Process 16
ARCh       kcrrdxh_   Archival Process 17
ARCi       kcrrdxi_   Archival Process 18
ARCj       kcrrdxj_   Archival Process 19
ARCk       kcrrdxk_   Archival Process 20
ARCl       kcrrdxl_   Archival Process 21

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
ARCm       kcrrdxm_   Archival Process 22
ARCn       kcrrdxn_   Archival Process 23
ARCo       kcrrdxo_   Archival Process 24
ARCp       kcrrdxp_   Archival Process 25
ARCq       kcrrdxq_   Archival Process 26
ARCr       kcrrdxr_   Archival Process 27
ARCs       kcrrdxs_   Archival Process 28
ARCt       kcrrdxt_   Archival Process 29
ASMB       kfnbgd_    ASM Background
CJQ0       kkjcjq0_   Job Queue Coordinator
CKPT       kcvcdp_    checkpoint

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
CTWR       krcbgp_    Change Tracking Writer
DBW0       kcbdw0_    db writer process 0
DBW1       kcbdw1_    db writer process 1
DBW2       kcbdw2_    db writer process 2
DBW3       kcbdw3_    db writer process 3
DBW4       kcbdw4_    db writer process 4
DBW5       kcbdw5_    db writer process 5
DBW6       kcbdw6_    db writer process 6
DBW7       kcbdw7_    db writer process 7
DBW8       kcbdw8_    db writer process 8
DBW9       kcbdw9_    db writer process 9

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
DBWa       kcbdw10_   db writer process 10 (a)
DBWb       kcbdw11_   db writer process 11 (b)
DBWc       kcbdw12_   db writer process 12 (c)
DBWd       kcbdw13_   db writer process 13 (d)
DBWe       kcbdw14_   db writer process 14 (e)
DBWf       kcbdw15_   db writer process 15 (f)
DBWg       kcbdw16_   db writer process 16 (g)
DBWh       kcbdw17_   db writer process 17 (h)
DBWi       kcbdw18_   db writer process 18 (i)
DBWj       kcbdw19_   db writer process 19 (j)
DIAG       kstdtp_    diagnosibility process

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
DMON       rfmprc_    DG Broker Monitor Process
EMN0       kponem0_   Event Monitor Process 0
FMON       ksfmfmon_  File Mapping Monitor Process
GMON       kfdp_gmon_ diskgroup monitor
INSV       rfi_insv_  Data Guard Broker INstance SlaVe Process
LCK0       kclck0_    Lock Process 0
LGWR       kcrprc_    Redo etc.
LMD0       kjid0_     global enqueue service daemon 0
LMON       kjidm_     global enqueue service monitor
LMS0       kjmlms0_   global cache service process 0
LMS1       kjmlms1_   global cache service process 1

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
LMS2       kjmlms2_   global cache service process 2
LMS3       kjmlms3_   global cache service process 3
LMS4       kjmlms4_   global cache service process 4
LMS5       kjmlms5_   global cache service process 5
LMS6       kjmlms6_   global cache service process 6
LMS7       kjmlms7_   global cache service process 7
LMS8       kjmlms8_   global cache service process 8
LMS9       kjmlms9_   global cache service process 9
LMSa       kjmlmsa_   global cache service process 10
LMSb       kjmlmsb_   global cache service process 11
LMSc       kjmlmsc_   global cache service process 12

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
LMSd       kjmlmsd_   global cache service process 13
LMSe       kjmlmse_   global cache service process 14
LMSf       kjmlmsf_   global cache service process 15
LMSg       kjmlmsg_   global cache service process 16
LMSh       kjmlmsh_   global cache service process 17
LMSi       kjmlmsi_   global cache service process 18
LMSj       kjmlmsj_   global cache service process 19
LMSk       kjmlmsk_   global cache service process 20
LMSl       kjmlmsl_   global cache service process 21
LMSm       kjmlmsm_   global cache service process 22
LMSn       kjmlmsn_   global cache service process 23

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
LMSo       kjmlmso_   global cache service process 24
LMSp       kjmlmsp_   global cache service process 25
LMSq       kjmlmsq_   global cache service process 26
LMSr       kjmlmsr_   global cache service process 27
LMSs       kjmlmss_   global cache service process 28
LMSt       kjmlmst_   global cache service process 29
LMSu       kjmlmsu_   global cache service process 30
LMSv       kjmlmsv_   global cache service process 31
LMSw       kjmlmsw_   global cache service process 32
LMSx       kjmlmsx_   global cache service process 33
LMSy       kjmlmsy_   global cache service process 34

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
LMSz       kjmlmsz_   global cache service process 35
LNS0       kcrrns0_   Network Server 0
LNS1       kcrrns1_   Network Server 1
LNS2       kcrrns2_   Network Server 2
LNS3       kcrrns3_   Network Server 3
LNS4       kcrrns4_   Network Server 4
LNS5       kcrrns5_   Network Server 5
LNS6       kcrrns6_   Network Server 6
LNS7       kcrrns7_   Network Server 7
LNS8       kcrrns8_   Network Server 8
LNS9       kcrrns9_   Network Server 9

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
LNSa       kcrrns10_  Network Server 10
LNSb       kcrrns11_  Network Server 11
LNSc       kcrrns12_  Network Server 12
LNSd       kcrrns13_  Network Server 13
LNSe       kcrrns14_  Network Server 14
LNSf       kcrrns15_  Network Server 15
LNSg       kcrrns16_  Network Server 16
LNSh       kcrrns17_  Network Server 17
LNSi       kcrrns18_  Network Server 18
LNSj       kcrrns19_  Network Server 19
LSP0       krvslsp_   Logical Standby

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
LSP1       krvsdbp_   Dictionary build process for Logical Standby
LSP2       krvssgsp_  Set Guard Standby Information for Logical Standby
MMAN       kmgsdp_    Memory Manager
MMNL       kebmmnl_   Manageability Monitor Process 2
MMON       kebmmon_   Manageability Monitor Process
MRP0       krsmmrp_   Managed Standby Recovery
NSV0       rfins0_    Data Guard Broker NetSlave Process 0
NSV1       rfins1_    Data Guard Broker NetSlave Process 1
NSV2       rfins2_    Data Guard Broker NetSlave Process 2
NSV3       rfins3_    Data Guard Broker NetSlave Process 3
NSV4       rfins4_    Data Guard Broker NetSlave Process 4

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
NSV5       rfins5_    Data Guard Broker NetSlave Process 5
NSV6       rfins6_    Data Guard Broker NetSlave Process 6
NSV7       rfins7_    Data Guard Broker NetSlave Process 7
NSV8       rfins8_    Data Guard Broker NetSlave Process 8
NSV9       rfins9_    Data Guard Broker NetSlave Process 9
PMON       ksuclp_    process cleanup
PSP0       kso_spawne process spawner 0
           r0_

QMNC       kwqmnc_    AQ Coordinator
RBAL       kfgbrm_    ASM Rebalance master

KSBDDIDN   KSMFSNAM   KSBDDDSC
---------- ---------- --------------------------------------------------
RECO       k2vbkg_    distributed recovery
RSM0       rfrmp0_    Data Guard Broker Resource Guard Process 0
RSM1       rfrmp1_    Data Guard Broker Resource Guard Process 1
RVWR       krfwrp_    Recovery Writer
SMON       ktmprc_    System Monitor Process
TEST       ksbtstp_   test background process

158 rows selected.

进一步，，我们可以通过以level 2 级别dump sga信息查看后台进程在内存中的地址

引用
SQL> ALTER SESSION SET EVENTS 'immediate trace name global_area level 2';

Session altered.

获取trc文件

引用
SQL> SELECT    d.VALUE
  2         || '/'
  3         || LOWER (RTRIM (i.INSTANCE, CHR (0)))
  4         || '_ora_'
  5         || p.spid
  6         || '.trc' trace_file_name
  7    FROM (SELECT p.spid
  8            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
12           WHERE v.NAME = 'thread'
           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14         (SELECT VALUE
15            FROM v$parameter
16           WHERE NAME = 'user_dump_dest') d
17  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/ora10g/app/admin/super/udump/super_ora_31179.trc

打开/ora10g/app/admin/super/udump/super_ora_31179.trc文件可以看到DBW9等一系列后台进程描述是以ksbdp 开始的，并进一步可以看到其内存地址，结构体描述名字

引用
...
ksbdp kcbdw9_ [200097F4, 20009820) = 00000036 00000000 00000000 00000000 ...
Dump of memory from 0x20009804 to 0x20009820
20009800          39574244 00000000 00000000      [DBW9........]
20009810 00000000 00000000 00000000 00010009  [................]
        KSBDPPRO = 0X00000000
        KSBDPSER = 0
        KSBDPERR = 0
        KSBDPNAM = 'DBW9'
        KSBDPFLG = 0
ksbdp kcbdw10_ [20009820, 2000984C) = 00000037 00000000 00000000 00000000 ...
Dump of memory from 0x20009830 to 0x2000984C
20009830 61574244 00000000 00000000 00000000  [DBWa............]
20009840 00000000 00000000 0001000A           [............]
        KSBDPPRO = 0X00000000
        KSBDPSER = 0
        KSBDPERR = 0
        KSBDPNAM = 'DBWa'
        KSBDPFLG = 0
...

有了以上基本信息，即可深入研究了