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
...

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