使用v$session_longops来监控RMAN备份进度

这次备份的数据库是个大块头，数据文件达到10TB。可是管理方只允许使用4个通道备份，直接扼杀了备份速度。通过glance命令查看cpu，磁盘、内存的压力都不高，即使开8个通道或是16个通道也没问题。该主机是双节点RAC，每台主机配有32个cpu，并且是在周末业务较低的时候备份。这4个通道的限制就如同一辆法拉利挂着一档行驶在高速公路上，这要多久才能跑完...1，备份之前了解一下目标数据库的状态看看dba_segments,实际数据块的总大小为5TB
SQL> select sum（bytes）/1024/1024/1024 GB from dba_segments; GB
----------
5287.02454看看dba_data_files，数据文件总大小大约为10TB
SQL> select sum（bytes）/1024/1024/1024 GB from dba_data_files; GB
----------
9402.70592临时备份路径为/orabak,磁盘空间大小为为9TB
bdf
/dev/vx/dsk/bakdg/bakvol
9961472000 634128 9883018840 0% /orabak
2，这是一个普通压缩方式的数据库全备脚本，包含控制文件、参数文件和归档日志文件。最突出的部分是这4通道，让人痛不欲生。
vi backup.cmdrman target / <<EOF
run{
allocate channel c1 device type disk maxpiecesize = 20G;
allocate channel c2 device type disk maxpiecesize = 20G;
allocate channel c3 device type disk maxpiecesize = 20G;
allocate channel c4 device type disk maxpiecesize = 20G;
backup tag "sh_db_full" as compressed backupset format "/orabak/sh_db_full_%U" database
include current controlfile;
sql "alter system archive log current";
backup tag "sh_arch" as compressed backupset archivelog all format "/orabak/sh_arch_%U";
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
EOF3，在Oracle用户下授权备份脚本
chmod 755 backup.cmd4，在后台执行备份脚本
nohup backup.cmd &5，通过nohup.out日志来监控rman备份输出备份时间为2014/10/12日 16:45
tail -f nohup.out6，通过glance命令来观察备份时的系统状态，发现CPU的使用率只有23%，磁盘压力只有15%，4个通道所占用的cpu分别为100%左右。其实我们的可以资源非常多，却不允许使用。Glance 11.13.007 16:47:43 jccmsdb1 ia64 Current Avg High
------------------------------------------------------------------------------------------------------------------------------------
CPU Util SSN NU UW W | 23% 23% 33%
Disk Util F F | 15% 11% 30%
Mem Util S SU UF F | 70% 70% 70%
Networkil U UR R | 54% 54% 54%
------------------------------------------------------------------------------------------------------------------------------------
PROCESS LIST Users= 9
User CPU % Thrd Disk Memory Block
Process Name PID Name （2400% max） Cnt IO rate RSS VSS On
------------------------------------------------------------------------------------------------------------------------------------
oraclesgpmdb 12326 oracle 100 1 51.9 92.0mb 104mb PRI
oraclesgpmdb 12280 oracle 100 1 54.2 92.0mb 104mb PRI
oraclesgpmdb 12281 oracle 99.6 1 54.4 92.0mb 104mb PRI
oraclesgpmdb 12304 oracle 99.4 1 57.6 92.0mb 104mb PRI
ora_m000_sgp 28333 oracle 15.6 1 0.0 131mb 163mb PRI
perl 18601 root 9.1 1 0.0 712kb 724kb died
ora_dia0_sgp 6943 oracle 7.6 1 0.0 134mb 136mb SLEEP
java 9109 root 7.4 41 0.7 305mb 759mb SLEEP
df 18605 root 6.2 1 0.0 84kb 160kb died
glance 19308 quest 6.0 1 0.0 20.1mb 23.9mb STRMS
ocssd.bin 16787 grid 5.1 19 2.8 138mb 138mb SLEEP
vxfsd 342 root 4.5 217 117.3 79.1mb 89.0mb SYSTM
7，通过v$session_longops视图查看rman备份的进度，这部分也是本片博客要阐述的重点。SQL> / SID SERIAL# OPNAME TARGET_DESC HOURS CONTEXT SOFAR TOTALWORK %_complete
---------- ---------- ----------------------------------- --------------- ---------- ---------- ---------- ---------- ----------
6440 52989 RMAN: full datafile backup Set Count 8.30555556 1 35966318 137179774 26.22
6050 62853 RMAN: full datafile backup Set Count 6.25861111 1 43865710 136921086 32.04
3058 52919 RMAN: full datafile backup Set Count 8.01722222 1 36874224 137048704 26.91
1548 52825 RMAN: aggregate input backup 18.7455556 3 115521340 1232568339 9.37
3806 34287 RMAN: full datafile backup Set Count 8.19888889 1 36235504 136924544 26.46
OPNAME=aggregate input的这行数据是聚合行，SID为1548，是RMAN的主会话号，它表示当前RAMN中所有任务的整体进度。OPNAME=full database backup的行为细节行，一共有4个，每个细节行对应一个通道channel。我们的备份启动了4个channel，所以这里就相对4个细节行。如果在这里能看到16个细节行该多好！totalwor表示当前行需要处理的工作量，sofar表示已经能够完成的工作量，%comlete为sofar/totalwork的百分比。该视图中聚合行中的总量为1232568339，在RMAN备份中totalwork的单位为blocks（通过UNITS字段能查到）。通过totalwork总大小1232568339 x 8（数据块大小） / 1024/1024/1024 = 9.18TB
%_complete完成度是9.37%。细节行的工作总量完成后，会把完成进度加到聚合行。在备份过程中每次执行该视图看到细节行的%_complete在增长，而聚合行的%_complete却不会每次随着查询二改变就是这个原因。备份器件还要关注一下sofar的增长量，如果隔2分钟以上查询该视图发现sofar停止增长了，就应该关注一下v$session_wait视图看看RMAN回话在等待什么事件。
我们看看第二天早上的备份进度，一夜都没备份完oracle@jccmsdb1:/home/oracle> bdf /orabak
Filesystem kbytes used avail %used Mounted on
/dev/vx/dsk/bakdg/bakvol
9961472000 872303704 9018159296 9% /orabakSQL> / SID SERIAL# OPNAME TARGET_DESC HOURS CONTEXT SOFAR TOTALWORK %_complete
---------- ---------- ----------------------------------- --------------- ---------- ---------- ---------- ---------- ----------
6050 62853 RMAN: full datafile backup Set Count 3.25861111 1 95604078 136896374 69.84
6440 52989 RMAN: full datafile backup Set Count 2.95 1 97688238 136890366 71.36
1548 52825 RMAN: aggregate input backup 58.1516667 3 229714732 1232568339 18.64
3058 52919 RMAN: full datafile backup Set Count 3.08333333 1 98322032 136919040 71.81
3806 34287 RMAN: full datafile backup Set Count 4.92305556 1 82772078 136895998 60.46oracle@jccmsdb1:/home/oracle> ls -ltr /orabak
total 1719089792
drwxr-xr-x 2 root root 96 Oct 10 18:40 lost+found
-rw-r----- 1 oracle oinstall 98304 Oct 12 10:31 datafile6_ptpks4no_1_1.bak
-rw-r----- 1 oracle oinstall 21472854016 Oct 12 18:29 sh_db_full_q5pksqkh_1_1
-rw-r----- 1 oracle oinstall 21472911360 Oct 12 18:32 sh_db_full_q3pksqkf_1_1
-rw-r----- 1 oracle oinstall 21472804864 Oct 12 18:37 sh_db_full_q2pksqke_1_1
-rw-r----- 1 oracle oinstall 21472837632 Oct 12 18:41 sh_db_full_q4pksqkg_1_1
-rw-r----- 1 oracle oinstall 21472772096 Oct 12 20:11 sh_db_full_q5pksqkh_2_1
-rw-r----- 1 oracle oinstall 21472862208 Oct 12 20:15 sh_db_full_q3pksqkf_2_1
-rw-r----- 1 oracle oinstall 21472870400 Oct 12 20:30 sh_db_full_q2pksqke_2_1
-rw-r----- 1 oracle oinstall 21472862208 Oct 12 20:34 sh_db_full_q4pksqkg_2_1
-rw-r----- 1 oracle oinstall 21472878592 Oct 12 21:54 sh_db_full_q5pksqkh_3_1
-rw-r----- 1 oracle oinstall 21472796672 Oct 12 22:06 sh_db_full_q3pksqkf_3_1
-rw-r----- 1 oracle oinstall 21472968704 Oct 12 22:21 sh_db_full_q2pksqke_3_1
-rw-r----- 1 oracle oinstall 21472919552 Oct 12 22:24 sh_db_full_q4pksqkg_3_1
-rw-r----- 1 oracle oinstall 21472804864 Oct 12 23:35 sh_db_full_q5pksqkh_4_1
-rw-r----- 1 oracle oinstall 21472894976 Oct 12 23:58 sh_db_full_q3pksqkf_4_1
-rw-r----- 1 oracle oinstall 21472813056 Oct 13 00:06 sh_db_full_q4pksqkg_4_1
-rw-r----- 1 oracle oinstall 21472903168 Oct 13 00:11 sh_db_full_q2pksqke_4_1
-rw-r----- 1 oracle oinstall 21472993280 Oct 13 01:20 sh_db_full_q5pksqkh_5_1
-rw-r----- 1 oracle oinstall 21472927744 Oct 13 01:48 sh_db_full_q3pksqkf_5_1
-rw-r----- 1 oracle oinstall 21472763904 Oct 13 01:50 sh_db_full_q4pksqkg_5_1
-rw-r----- 1 oracle oinstall 21472878592 Oct 13 02:00 sh_db_full_q2pksqke_5_1
-rw-r----- 1 oracle oinstall 21472788480 Oct 13 03:09 sh_db_full_q5pksqkh_6_1
-rw-r----- 1 oracle oinstall 21473034240 Oct 13 03:39 sh_db_full_q4pksqkg_6_1
-rw-r----- 1 oracle oinstall 21472894976 Oct 13 03:41 sh_db_full_q3pksqkf_6_1
-rw-r----- 1 oracle oinstall 1160511488 Oct 13 03:47 sh_db_full_q3pksqkf_7_1
-rw-r----- 1 oracle oinstall 21472829440 Oct 13 03:57 sh_db_full_q2pksqke_6_1
-rw-r----- 1 oracle oinstall 10962452480 Oct 13 04:06 sh_db_full_q5pksqkh_7_1
-rw-r----- 1 oracle oinstall 5458075648 Oct 13 04:07 sh_db_full_q4pksqkg_7_1
-rw-r----- 1 oracle oinstall 3678208000 Oct 13 04:17 sh_db_full_q2pksqke_7_1
-rw-r----- 1 oracle oinstall 21472804864 Oct 13 05:41 sh_db_full_q6pku1ep_1_1
-rw-r----- 1 oracle oinstall 21472788480 Oct 13 05:55 sh_db_full_q8pku2je_1_1
-rw-r----- 1 oracle oinstall 21472772096 Oct 13 05:57 sh_db_full_q7pku2hk_1_1
-rw-r----- 1 oracle oinstall 21472878592 Oct 13 06:04 sh_db_full_q9pku37d_1_1
-rw-r----- 1 oracle oinstall 21472780288 Oct 13 07:30 sh_db_full_q6pku1ep_2_1
-rw-r----- 1 oracle oinstall 21472911360 Oct 13 07:42 sh_db_full_q8pku2je_2_1
-rw-r----- 1 oracle oinstall 21472944128 Oct 13 07:44 sh_db_full_q7pku2hk_2_1
-rw-r----- 1 oracle oinstall 21472788480 Oct 13 07:53 sh_db_full_q9pku37d_2_1
-rw-r----- 1 oracle oinstall 21472903168 Oct 13 09:18 sh_db_full_q6pku1ep_3_1
-rw-r----- 1 oracle oinstall 21472788480 Oct 13 09:32 sh_db_full_q8pku2je_3_1
-rw-r----- 1 oracle oinstall 21472780288 Oct 13 09:33 sh_db_full_q7pku2hk_3_1
-rw-r----- 1 oracle oinstall 21472878592 Oct 13 09:42 sh_db_full_q9pku37d_3_1
-rw-r----- 1 oracle oinstall 21472985088 Oct 13 11:01 sh_db_full_q6pku1ep_4_1
-rw-r----- 1 oracle oinstall 21472763904 Oct 13 11:21 sh_db_full_q7pku2hk_4_1
-rw-r----- 1 oracle oinstall 21472870400 Oct 13 11:27 sh_db_full_q8pku2je_4_1
-rw-r----- 1 oracle oinstall 21472763904 Oct 13 11:30 sh_db_full_q9pku37d_4_1此时是周日工作日的正常时段，也是业务高峰期，cpu使用率依然不高才56%而已。
Glance 11.13.007 09:24:26 jccmsdb1 ia64 Current Avg High
------------------------------------------------------------------------------------------------------------------------------------
CPU Util S SN NU UW W | 56% 51% 56%
Disk Util F F |100% 89% 100%
Mem Util S SU UF F | 72% 72% 72%
Networkil U UR R | 56% 56% 56%
------------------------------------------------------------------------------------------------------------------------------------
PROCESS LIST Users= 9
User CPU % Thrd Disk Memory Block
Process Name PID Name （2400% max） Cnt IO rate RSS VSS On
------------------------------------------------------------------------------------------------------------------------------------
oraclesgpmdb 12326 oracle 100 1 55.0 91.6mb 102mb PRI
oraclesgpmdb 12304 oracle 99.2 1 52.8 92.3mb 101mb PRI
oraclesgpmdb 12281 oracle 99.2 1 60.9 91.6mb 101mb PRI
oraclesgpmdb 12280 oracle 99.0 1 69.4 92.8mb 101mb PRI
oraclesgpmdb 14200 grid 99.0 1 0.0 56.9mb 58.9mb PRI
oraclesgpmdb 22978 grid 98.0 1 0.0 93.8mb 105mb PRI
oraclesgpmdb 22997 grid 90.6 1 0.1 98.6mb 107mb SOCKT
oraclesgpmdb 18182 grid 31.3 1 320.7 55.3mb 56.9mb SYSTM
oraclesgpmdb 14398 grid 30.7 1 801.5 99.5mb 105mb PRI
oraclesgpmdb 6414 grid 24.1 1 0.0 98.1mb 105mb SOCKT
oraclesgpmdb 23038 grid 23.7 1 6.5 99.7mb 105mb SOCKT
ora_lms1_sgp 6965 oracle 19.3 1 0.0 95.9mb 102mb SLEEP
ora_lms0_sgp 6963 oracle 19.0 1 0.0 93.9mb 102mb SLEEP
oraclesgpmdb 29596 grid 13.3 1 6.9 80.2mb 85.3mb SOCKT
oraclesgpmdb 230--------------------------------------推荐阅读 --------------------------------------RMAN 配置归档日志删除策略 http://www.linuxidc.com/Linux/2013-11/92670.htmOracle基础教程之通过RMAN复制数据库 http://www.linuxidc.com/Linux/2013-07/87072.htmRMAN备份策略制定参考内容 http://www.linuxidc.com/Linux/2013-03/81695.htmRMAN备份学习笔记 http://www.linuxidc.com/Linux/2013-03/81892.htmOracle数据库备份加密 RMAN加密 http://www.linuxidc.com/Linux/2013-03/80729.htm--------------------------------------分割线 --------------------------------------本文永久更新链接地址