[20191203]後臺程式dbrm關閉.txt

[20191203]後臺程式dbrm關閉.txt

--//上午分析了resmgr:cpu quantum等待事件,分析主要參考連結:https://www.iteye.com/blog/dbzone-2359348.
--//裡面提到:
alter system set "_resource_manager_always_off"=true scope=spfile;
alter system set "_resource_manager_always_on"=false scope=spfile;

--//可以關閉後臺程式dbrm.自己測試看看.

1.環境:
SYS@book> @ ver1
PORT_STRING         VERSION        BANNER
------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ bg dbrm
old   1: select  addr,pid,spid,program from v$process where background=1 and PROGRAM like upper('%&1%')
new   1: select  addr,pid,spid,program from v$process where background=1 and PROGRAM like upper('%dbrm%')
ADDR                 PID SPID   PROGRAM
---------------- ------- ------ --------------------------
00000000861FA4D8       7 2317   oracle@gxqyydg4 (DBRM)
--//注:我在測試環境測試過,直接kill -9 2317 資料庫會crash.千萬不要在生產系統做這步測試!!

SYS@book> @ hide _resource_manager_always_o
NAME                           DESCRIPTION                         DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------------------ ----------------------------------- ------------- ------------- ------------ ----- ---------
_io_resource_manager_always_on io resource manager always on       TRUE          FALSE         FALSE        FALSE FALSE
_resource_manager_always_off   disable the resource manager always TRUE          FALSE         FALSE        FALSE FALSE
_resource_manager_always_on    enable the resource manager always  TRUE          TRUE          TRUE         FALSE FALSE

--//注:oracle這2個引數很奇怪_resource_manager_always_off,_resource_manager_always_on,理論有1個就ok了.
--//不知道的用意何在...

2.繼續:
SYS@book> alter system set "_resource_manager_always_on"=false;
alter system set "_resource_manager_always_on"=false
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
--//無法線上修改.

--//重啟資料庫:
SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

SYS@book> @ hide _resource_manager_always_o
NAME                           DESCRIPTION                         DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------------------ ----------------------------------- ------------- ------------- ------------ ----- ---------
_io_resource_manager_always_on io resource manager always on       TRUE          FALSE         FALSE        FALSE FALSE
_resource_manager_always_off   disable the resource manager always TRUE          FALSE         FALSE        FALSE FALSE
_resource_manager_always_on    enable the resource manager always  FALSE         FALSE         FALSE        FALSE FALSE

SYS@book> @ bg dbrm
ADDR                 PID SPID   PROGRAM
---------------- ------- ------ --------------------------
00000000861FA4D8       7 4496   oracle@gxqyydg4 (DBRM)
--//一樣還是啟動.

3.繼續測試:
SYS@book> alter system set "_resource_manager_always_off"=true;
alter system set "_resource_manager_always_off"=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SYS@book> alter system set "_resource_manager_always_off"=true scope=spfile;
System altered.

--//再次重啟資料庫:
SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

SYS@book> set verify off
SYS@book> @ hide _resource_manager_always_o
NAME                           DESCRIPTION                         DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------------------ ----------------------------------- ------------- ------------- ------------ ----- ---------
_io_resource_manager_always_on io resource manager always on       TRUE          FALSE         FALSE        FALSE FALSE
_resource_manager_always_off   disable the resource manager always FALSE         TRUE          TRUE         FALSE FALSE
_resource_manager_always_on    enable the resource manager always  FALSE         FALSE         FALSE        FALSE FALSE

SYS@book> @ bg dbrm
no rows selected

--//也就是關閉dbrm程式要設定:
_resource_manager_always_off=TRUE
_resource_manager_always_on=FALSE

4.收尾還原:
SYS@book> show spparameter _resource_manager_always_o
SID NAME                          TYPE    VALUE
--- ----------------------------- ------- -----
*   _resource_manager_always_off  boolean TRUE
*   _resource_manager_always_on   boolean FALSE

SYS@book> alter system reset "_resource_manager_always_on" scope=spfile;
System altered.

SYS@book> alter system reset "_resource_manager_always_off" scope=spfile;
System altered.

SYS@book> show spparameter _resource_manager_always_o

--//重啟資料庫:
SYS@book> @ bg dbrm
ADDR                 PID SPID   PROGRAM
---------------- ------- ------ --------------------------
00000000861FA4D8       7 4690   oracle@gxqyydg4 (DBRM)

SYS@book> select * from v$bgprocess where NAME='DBRM';
PADDR              PSERIAL# NAME DESCRIPTION               ERROR
---------------- ---------- ---- ------------------------- -----
00000000861FA4D8          1 DBRM DataBase Resource Manager 0