/*********oracle并行恢复的概念*************/
1,通过io并行化减少恢复时间
2,对于数据文件存储在多个磁盘作用显著
3,用于实例和介质恢复
/**********oracle并行恢复相关的参数************/
RECOVERY_PARALLELISM
1,指定启用几个并行恢复的进程
2,受限于parallel_max_servers
4,仅适用于多cpu环境
parallel_max_servers
1,指定一个实例最大可用的并行进程和并行恢复进程的个数
2,其默认值为:parallel_threads_per_cpu * cpu_count * curcurrent_parallel_users* 5
3,取值范围为:0-3600
4,在rac环境下,各实例可取不同值
5,上述的参数curcurrent_parallel_users取值和实例的内存管理方式有关
1,禁用了automatic memory management,其值为1
2,启了pga_aggrregate_target,其值为2
3,启用了sga自动管理及pga自动管理,其值为4
6,此参数取值太低,某些查询在并行处理操作期间会出现缺少可用的并行执行进程
此参数取值太高,在业务高峰,导致资源欠缺,反而降低整体性能
parallel_threads_per_cpu
1,与操作系统有关,一般默认为2
2,指定实例的默认并行度
3,确定并行的动态负载均衡算法
4,确认并行执行操作期间每个cpu可以处理的并行执行进程数或者每个cpu可以处理的线程数threads
/*****介质恢复*****/
SQL> show parameter recovery_parallel
NAME TYPE
------------------------------------ --------
VALUE
------------------------------
recovery_parallelism integer
0
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: 'D:\NEW_TBS.DBF'
/****并行恢复*****/
SQL> recover datafile 6 parallel 2;
Media recovery complete.
/*****对于recovery_parallelism未深入理解********/
/***查看相关官方手册*****/
---源于sqlplus user manual,关于sqlplus recover的用法及简介
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve033.htm#i2698238
PARALLEL [integer]
1,此参数根据实例可用cpu个数及parallel_threads_per_cpu选用合理的并行度
2,此参数优先于recovery_parallelism,会覆盖后者
3,此参数指定并行度,即并行操作期间启用的并行线程个数
4,上述每个并行线程可能会使用1或2个并行执行进程,即线程与进程是1:m关系
/*******如果我配置parallel_threads_per_cpu为1****是否recovery就不能启用并行了?*********/
/****修改上述参数1*******/
SQL> show parameter parallel_threads
NAME TYPE
------------------------------------ ----------
VALUE
------------------------------
parallel_threads_per_cpu integer
2
SQL> alter system set parallel_threads_per_cpu=
System altered.
SQL> show parameter parallel_threads
NAME TYPE
------------------------------------ ----------
VALUE
------------------------------
parallel_threads_per_cpu integer
1
/*******修改cpu为1*********/
SQL> show parameter cpu_count
NAME TYPE
------------------------------------ ---------
VALUE
------------------------------
cpu_count integer
4
SQL> alter system set cpu_count=1;
System altered.
SQL> show parameter cpu_count
NAME TYPE
------------------------------------ ---------
VALUE
------------------------------
cpu_count integer
1
/***上述2参数为1可以启用并行恢复****/
SQL> recover datafile 6 parallel 2;
Media recovery complete.
SQL> alter database open;
小结:
1,上述的thead可对应1或2个并行执行进程,所以上述2参数配置为1也可以启用并行恢复
/****启用>2的并行恢复可以吗?***********/
-->2也可以并行恢复****
SQL> recover datafile 6 parallel 3;
Media recovery complete.
SQL> recover datafile 6 parallel 4;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
/******recovery_parallelism测试*******/
---此参数静态须重启
SQL> show parameter recovery_para
NAME TYPE
------------------------------------ ---------------------
VALUE
------------------------------
recovery_parallelism integer
0
SQL> alter system set recovery_parallelism=2;
alter system set recovery_parallelism=2
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be mo
SQL> alter system set recovery_parallelism=2 scope=spfile;
System altered.
---在sqlplus运行recover datafile 7,如下日志表明为序列式恢复,非并行恢复
ALTER DATABASE RECOVER datafile 7
Media Recovery Start
Serial Media Recovery started
--启用parallel还是序列化恢复
SQL> recover datafile 7 parallel 2;
Media recovery complete.
Media Recovery Start
Serial Media Recovery started
/*********************如下recovery_parallelism=2*************/
/*******会话一恢复一个文件7******/
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database open;
Database altered.
/*******另启会话一恢复一个文件8******/
SQL> recover datafile 8;
Media recovery complete.
SQL>
/*******测试开启3个会话也可以恢复3个文件***********/
小结:1,recovery_parallelism不是指的是开启恢复会话的个数,而是指定并行恢复的进程
/*******如下为实例恢复后的并行进程信息,记录刚好和recovery_parallelism参数相等********/
SQL> select * from v$px_process;
SERVER_N STATUS PID
-------- ------------------ ----------
SPID SID SERIAL#
------------------------------------------------ ---------- ----------
P000 AVAILABLE 24
10012
P001 AVAILABLE 25
5996
/******重启库后上述视图消失为空******/
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) speci
ORACLE instance started.
Total System Global Area 1670221824 bytes
Fixed Size 2176328 bytes
Variable Size 1056967352 bytes
Database Buffers 603979776 bytes
Redo Buffers 7098368 bytes
Database mounted.
Database opened.
SQL> select * from v$px_process;
no rows selected
/****验证下recovery_parallelism与v$px_process的关系********/
SQL> alter system set recovery_parallelism=3 scope=spfile;
System altered.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified f
ORACLE instance started.
Total System Global Area 1670221824 bytes
Fixed Size 2176328 bytes
Variable Size 1056967352 bytes
Database Buffers 603979776 bytes
Redo Buffers 7098368 bytes
Database mounted.
Database opened.
SQL>
/****shutdown abort实例恢复日志如下*******/
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes --明确指示使用3个并行恢复进程,与recovery_parallelism相等
Started redo scan
Completed redo scan
read 102160 KB redo, 4834 data blocks need recovery
Started redo application at
/*******v$px_process也验证上述日志**************/
SQL> select * from v$px_process;
SERVER_N STATUS PID
-------- ------------------ ----------
SPID SID SERIAL#
------------------------------------------------ ---------- ----------
P000 AVAILABLE 20
3244
P001 AVAILABLE 21
6404
P002 AVAILABLE 22
9336
小结:
1,recovery_parallelism指定实例或介质恢复启用的并行恢复进程
2,并行恢复进程用v$px_process进行查询
3,recovery_parallelism与max_parallel_servers有关联
4,max_parallel_servers与这几个参数parallel_threads_per_cpu ,cpu_count,curcurrent_parallel_users有关
5,parallel_threads_per_cpu指定每个cpu并行的线程数,而每个线程可对应1或多个并行执行进程
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-758200/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-758200/