oracle的J00,Oracle致命的后台进程

oracle数据库有几十个后台进程，常常讨论哪些后台进程是致命的，有些进程kill会导致实例立即重启，常识中像smon, pmon,lgwr,ckpt.Unnn(container process for thread). , 但是有些进程kill并不会影响实例可用性， 甚至会立即进程级重启，从db alert log可以观测到，如mmon,reco,jnnn, pnnn等，当有些后台进程占用系统资源无法释放时想kill可以提供参考。 前两天看到Poder在其BLOG分享v$process的基表X$KSUPR中中有记录哪些是oracle的致命进程,在X$KSUPR.KSUPRFLG第3位, 下面我做个测试， kill 点X$KSUPR.KSUPRFLG第3位都不为1的进程。

— 环境 oracle 19.3 on-primise noarchivelog no workload, no job, no parallel

致命进程SELECT indx,ksuprpnm,TO_CHAR(ksuprflg,'XXXXXXXXXXXXXXXX'),KSUPROSID

FROM x$ksupr

WHERE BITAND(ksuprflg,4) = 4 ORDER BY indx

4

INDX KSUPRPNM TO_CHAR(KSUPRFLG, KSUPROSID

---------- ------------------------------------------------ ----------------- ------------------------

2 oracle@oel7db1 (PMON) E 2245

3 oracle@oel7db1 (CLMN) E 2247

4 oracle@oel7db1 (PSP0) 6 2249

5 oracle@oel7db1 (VKTM) 6 2251

6 oracle@oel7db1 (GEN0) 6 2255

8 oracle@oel7db1 (MMAN) 6 2259

13 oracle@oel7db1 (DBRM) 6 2270

16 oracle@oel7db1 (PMAN) 6 2276

18 oracle@oel7db1 (DBW0) 6 2280

19 oracle@oel7db1 (LGWR) 6 2282

20 oracle@oel7db1 (CKPT) 6 2284

21 oracle@oel7db1 (SMON) 16 2286

25 oracle@oel7db1 (LREG) 6 2294

非致命进程SELECT indx,ksuprpnm,TO_CHAR(ksuprflg,'XXXXXXXXXXXXXXXX'),KSUPROSID

FROM x$ksupr

WHERE BITAND(ksuprflg,4) != 4 and KSUPROSID is not null ORDER BY indx

/

INDX KSUPRPNM TO_CHAR(KSUPRFLG, KSUPROSID

---------- ------------------------------------------------ ----------------- ------------------------

0 oracle@oel7db1 (MZ00) 0 3949

7 oracle@oel7db1 (DIAG) 2 2265

9 oracle@oel7db1 (GEN1) 2 2261_2263

10 oracle@oel7db1 (SCMN) 2 2261_2261

11 oracle@oel7db1 (OFSD) 2 2267_2268

12 oracle@oel7db1 (SCMN) 2 2267_2267

14 oracle@oel7db1 (VKRM) 2 2272

15 oracle@oel7db1 (SVCB) 2 2274

17 oracle@oel7db1 (DIA0) 2 2278

22 oracle@oel7db1 (SMCO) 2 2288

23 oracle@oel7db1 (RECO) 2 2290

24 oracle@oel7db1 (W000) 2 2292

26 oracle@oel7db1 (W001) 2 2296

27 oracle@oel7db1 (PXMN) 2 2298

28 oracle@oel7db1 (S000) 40 2308

29 oracle@oel7db1 (MMON) 2 2302

30 oracle@oel7db1 (MMNL) 2 2304

31 oracle@oel7db1 (D000) 80 2306

32 oracle@oel7db1 (TMON) 2 2310

33 oracle@oel7db1 (TNS V1-V3) 0 2722

34 oracle@oel7db1 (M000) 2 2316

35 oracle@oel7db1 (TT00) 2 2324

36 oracle@oel7db1 (TT01) 2 2326

37 oracle@oel7db1 (TT02) 2 2328

38 oracle@oel7db1 (W002) 2 2332

39 oracle@oel7db1 (AQPC) 2 2336

40 oracle@oel7db1 (W003) 2 2339

41 oracle@oel7db1 (W004) 2 2349

42 oracle@oel7db1 (P000) 0 2344

43 oracle@oel7db1 (P001) 0 2346

44 oracle@oel7db1 (M004) 0 3498

45 oracle@oel7db1 (CJQ0) 2 2357

46 oracle@oel7db1 (QM02) 2 2435

47 oracle@oel7db1 (Q001) 2 2437

48 oracle@oel7db1 (W005) 2 3179

49 oracle@oel7db1 (Q003) 2 2442

50 oracle@oel7db1 (W006) 2 3183

51 oracle@oel7db1 (W007) 2 3188

52 oracle@oel7db1 (MZ00) 0 3949

53 oracle@oel7db1 (J001) 0 3802

54 oracle@oel7db1 (MZ00) 0 3793

55 oracle@oel7db1 (J004) 0 2523

56 oracle@oel7db1 (J005) 0 2525

57 oracle@oel7db1 (J006) 0 2527

58 oracle@oel7db1 (J007) 0 2530

59 oracle@oel7db1 (J008) 0 2532

60 oracle@oel7db1 (J009) 0 2534

61 oracle@oel7db1 (J00A) 0 2536

62 oracle@oel7db1 (J00B) 0 2538

63 oracle@oel7db1 (J00C) 0 2540

64 oracle@oel7db1 (J00D) 0 2543

65 oracle@oel7db1 (J00E) 0 2545

66 oracle@oel7db1 (M001) 2 2569

67 oracle@oel7db1 (M002) 2 2571

68 oracle@oel7db1 (M003) 2 2573

69 oracle@oel7db1 (Q005) 0 2610

70 oracle@oel7db1 (Q006) 0 2612

71 oracle@oel7db1 (Q007) 0 2614

72 oracle@oel7db1 (Q008) 0 2616

73 oracle@oel7db1 (Q009) 0 2618

74 oracle@oel7db1 (Q00A) 0 2620

75 oracle@oel7db1 (Q00B) 0 2622

76 oracle@oel7db1 (Q00C) 0 2624

77 oracle@oel7db1 (Q00D) 0 2626

78 oracle@oel7db1 (Q00E) 0 2628

79 oracle@oel7db1 (Q00F) 0 2630

80 oracle@oel7db1 (Q00G) 0 2633

81 oracle@oel7db1 (Q00H) 0 2635

82 oracle@oel7db1 (Q00I) 0 2637

83 oracle@oel7db1 (Q00J) 0 2639

84 oracle@oel7db1 (Q00K) 0 2641

85 oracle@oel7db1 (Q00L) 0 2643

86 oracle@oel7db1 (Q00M) 0 2645

299 oracle@oel7db1 (TNS V1-V3) 0 2233

KILL ALL 非致命进程SQL> SELECT 'host kill -9 '||KSUPROSID

FROM x$ksupr

WHERE BITAND(ksuprflg,4) != 4 and KSUPROSID is not null ORDER BY indx

/

— 执行后，果然实例没有重启， 甚至有些进程会立即自重启

DB ALERT LOGRestarting dead background process DIAG

Starting background process DIAG

DIAG started with pid=17, OS id=4520

Restarting dead background process OFSD

Starting background process OFSD

OFSD started with pid=22, OS id=4522_4524

Restarting dead background process VKRM

Starting background process VKRM

Oracle running with ofslib:'Oracle File Server Library' version=2

VKRM started with pid=24, OS id=4526

Restarting dead background process SVCB

Starting background process SVCB

SVCB started with pid=26, OS id=4528

Restarting dead background process DIA0

Starting background process DIA0

DIA0 started with pid=27, OS id=4530

Restarting dead background process TMON

Starting background process TMON

TMON started with pid=28, OS id=4532

Restarting dead background process SMCO

Starting background process SMCO

SMCO started with pid=29, OS id=4534

Restarting dead background process RECO

Starting background process RECO

RECO started with pid=30, OS id=4536

Restarting dead background process CJQ0

Starting background process CJQ0

CJQ0 started with pid=32, OS id=4540

Restarting dead background process PXMN

Starting background process PXMN

PXMN started with pid=35, OS id=4544

Restarting dead background process AQPC

Starting background process AQPC

AQPC started with pid=36, OS id=4546

Restarting dead background process MMON

Starting background process MMON

MMON started with pid=37, OS id=4548

Restarting dead background process MMNL

Starting background process MMNL

MMNL started with pid=38, OS id=4550

2020-05-12 11:25:58.025000 -04:00

TT00 (PID:4560): Gap Manager starting

进程自动重启， cool!

喜欢请转载