修改dbwr后台进程数量

由于昨天数据库重启，备库没有启用恢复进程，导致很多日志需要恢复。

SQL> show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     4
parallel_threads_per_cpu             integer     2
resource_manager_cpu_allocation      integer     32

SQL> col name for a20
SQL> col value for a20
SQL> col datum_time for a20
SQL>  select name,value,datum_time from v$dataguard_stats;

NAME                 VALUE                DATUM_TIME
-------------------- -------------------- --------------------
transport lag        +00 00:00:00         03/04/2015 12:43:47
apply lag            +00 09:38:14         03/04/2015 12:43:47
apply finish time    +00 02:10:02.519
estimated startup ti 10
me

恢复的时候发现恢复的日志很慢，听说增加dbwr进程能够加快的恢复的速度：

SQL> select name,paddr from v$bgprocess where name like 'DBW%';

NAME  PADDR
----- ----------------
DBW0  000000104A80B880
DBW1  00
DBW2  00
DBW3  00
DBW4  00
DBW5  00
DBW6  00

----- ----------------
DBWx  00
DBWy  00
DBWz  00

36 rows selected.

paddr不为 0表示该进程正在使用，查看db_writer_processes 

SQL> show parameter db_writer_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes                  integer     1

查看介质恢复的速度

SQL> select START_TIME,ITEM,SOFAR from v$recovery_progress 
  2  where (item='Active Apply Rate' or item='Average Apply Rate');

START_TIM ITEM                                  SOFAR
--------- -------------------------------- ----------
04-MAR-15 Active Apply Rate                      6898
04-MAR-15 Average Apply Rate                     6287
03-MAR-15 Active Apply Rate                        44
03-MAR-15 Average Apply Rate                      774

SQL> alter system set db_writer_processes=4 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
SQL> startup 
ORACLE instance started.
Total System Global Area 6.8413E+10 bytes
SQL> show parameter db_writer_processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes                  integer     1

修改后重启值还是没变，资料上又说设置了如果使用了 DBWR_IO_SLAVES，则只能使用一个数据库写进程

SQL> show parameter dbwr_io_slaves

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dbwr_io_slaves                       integer     8

自己虚拟机上  dbwr_io_slaves 为0， db_writer_processes能够修改成功

SQL> select START_TIME,ITEM,SOFAR from v$recovery_progress 
  2  where (item='Active Apply Rate' or item='Average Apply Rate');

START_TIM ITEM                                  SOFAR
--------- -------------------------------- ----------
04-MAR-15 Active Apply Rate                     16193
04-MAR-15 Average Apply Rate                    14977
04-MAR-15 Active Apply Rate                      9309
04-MAR-15 Average Apply Rate                    13887

SQL> show parameter db_writer_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes                  integer     4
SQL> 

从上面的情况看，增加 db_writer_processes 数，介质恢复明显快了

三 

SQL> alter system set db_writer_processes=8 scope=spfile;

START_TIM ITEM                                  SOFAR
--------- -------------------------------- ----------
04-MAR-15 Active Apply Rate                     22844
04-MAR-15 Average Apply Rate                    25821
04-MAR-15 Active Apply Rate                       760
04-MAR-15 Average Apply Rate                     9671

START_TIME          ITEM                                  SOFAR
------------------- -------------------------------- ----------
2015-03-04 15:36:38 Active Apply Rate                      7841
2015-03-04 15:36:38 Average Apply Rate                     8105
2015-03-04 15:34:43 Active Apply Rate                       760
2015-03-04 15:34:43 Average Apply Rate                     9671

当我将 db_writer_processes 设置为8的时候，开始时速度很块，可是运行一段时间后，应用的速度反而和db_writer_processes 为1 差不多了。